id: f231cbcd-66d9-45be-a6a3-4a915e53d46c
name: TI Map File Entity to VMConnection Event
description: |
  'This query finds matches in VMConnection Event data for known FileName Indicators of Compromise from Threat Intelligence sources. FileName matches may produce false positives, so use this for hunting rather than real-time detection.'
description-detailed: |
  'This query identifies any matches in the VMConnection Event data that correspond to any known FileName Indicators of Compromise (IOC) from Threat Intelligence (TI) sources. 
  Since file name matches may produce a significant amount of false positives, it is recommended to use this query for hunting purposes rather than for real-time detection.'
requiredDataConnectors:
  - connectorId: AzureMonitor(VMInsights)
    dataTypes:
      - VMConnection
  - connectorId: ThreatIntelligence
    dataTypes:
      - ThreatIntelligenceIndicator
  - connectorId: ThreatIntelligenceTaxii
    dataTypes:
      - ThreatIntelligenceIndicator
  - connectorId: MicrosoftDefenderThreatIntelligence
    dataTypes:
      - ThreatIntelligenceIndicator
tactics:
  - Impact
query: |
  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let ioc_lookBack = 14d;
  ThreatIntelIndicators
  | where TimeGenerated >= ago(ioc_lookBack) and ValidUntil > now()
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id
  | where IsActive == true
  //extract key part of kv pair
  | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
  | where IndicatorType == "file"
  | extend FileName = ObservableValue
  | where isnotempty(FileName)
  | extend TI_ProcessEntity = tostring(split(FileName, ".")[-2])
  // using innerunique to keep perf fast and result set low, we only need one match to indicate potential malicious activity that needs to be investigated
  | join kind=innerunique (
    VMConnection 
    | where TimeGenerated between(starttime..endtime)
    | where isnotempty(ProcessName)
    | extend VMConnection_TimeGenerated = TimeGenerated
  )
  on $left.TI_ProcessEntity == $right.ProcessName
  | where VMConnection_TimeGenerated < ValidUntil
  | summarize VMConnection_TimeGenerated = arg_max(VMConnection_TimeGenerated, *) by Id, ProcessName
  | extend Description = tostring(parse_json(Data).description)
  | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  | project VMConnection_TimeGenerated, Description, ActivityGroupNames, Id, Type, ValidUntil, Confidence,
  FileName, Computer, Direction, SourceIp, DestinationIp, RemoteIp, DestinationPort, Protocol//, Url
  | extend timestamp = VMConnection_TimeGenerated, HostName = split(Computer, '.', 0)[0], DnsDomain = strcat_array(array_slice(split(Computer, '.'), 1, -1), '.')
  | extend Host_0_HostName = HostName
  | extend Host_0_DnsDomain = DnsDomain
  | extend IP_0_Address = RemoteIp
  | extend IP_1_Address = SourceIp
  //| extend URL_0_Url = Url
entityMappings:
  - entityType: Host
    fieldMappings:
      - identifier: HostName
        columnName: HostName
      - identifier: DnsDomain
        columnName: DnsDomain
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: RemoteIp
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SourceIp
  - entityType: URL
    fieldMappings:
      - identifier: Url
        columnName: Url
version: 1.0.3
