id: cdd1933b-ef94-48a4-b94a-18d45b902751
name: TI Map IP Entity to CommonSecurityLog
description: |
  This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in CommonSecurityLog.
severity: Medium
requiredDataConnectors:
  - connectorId: ThreatIntelligence
    dataTypes:
      - ThreatIntelligenceIndicator
  - connectorId: ThreatIntelligenceTaxii
    dataTypes:
      - ThreatIntelligenceIndicator
  - connectorId: CEF
    dataTypes:
      - CommonSecurityLog
  - connectorId: MicrosoftDefenderThreatIntelligence
    dataTypes:
      - ThreatIntelligenceIndicator
queryFrequency: 1h
queryPeriod: 14d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - CommandAndControl
relevantTechniques:
  - T1071
query: |
  let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
  let dt_lookBack = 1h; // Look back 1 hour for CommonSecurityLog events
  let ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators
  // Fetch threat intelligence indicators related to IP addresses
  let IP_Indicators = ThreatIntelIndicators
  //extract key part of kv pair
       | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
       | where isnotempty(IndicatorType) and IndicatorType == "ipv4-addr"
       | extend NetworkSourceIP = toupper(ObservableValue)
       | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
    | where isnotempty(NetworkSourceIP) 
    | where TimeGenerated >= ago(ioc_lookBack)
    | extend TI_ipEntity = iff(isnotempty(NetworkSourceIP), NetworkSourceIP, NetworkSourceIP)
    | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
    | where ipv4_is_private(TI_ipEntity) == false and  TI_ipEntity !startswith "fe80" and TI_ipEntity !startswith "::" and TI_ipEntity !startswith "127."
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id
    | where IsActive == true and ValidUntil > now();
  // Perform a join between IP indicators and CommonSecurityLog events
  IP_Indicators
     | project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, Type, TI_ipEntity
    // Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation
    | join kind=innerunique (
        CommonSecurityLog
        | where TimeGenerated >= ago(dt_lookBack)
        | extend MessageIP = extract(IPRegex, 0, Message)
        | extend CS_ipEntity = iff(isnotempty(SourceIP), SourceIP, DestinationIP)
        | extend CS_ipEntity = iff(isempty(CS_ipEntity) and isnotempty(MessageIP), MessageIP, CS_ipEntity)
        | extend CommonSecurityLog_TimeGenerated = TimeGenerated
    )
    on $left.TI_ipEntity == $right.CS_ipEntity
    // Filter out logs that occurred after the expiration of the corresponding indicator
    | where CommonSecurityLog_TimeGenerated < ValidUntil
    // Group the results by IndicatorId and CS_ipEntity, and keep the log entry with the latest timestamp
    | summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by Id, CS_ipEntity
    // Select the desired output fields
    | project timestamp = CommonSecurityLog_TimeGenerated, SourceIP, DestinationIP, MessageIP, Message, DeviceVendor, DeviceProduct, Id, ValidUntil, Confidence, TI_ipEntity, CS_ipEntity, LogSeverity, DeviceAction, Type
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: CS_ipEntity
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: SourceIP   
version: 1.2.4
kind: Scheduled
