TI Map IP Entity to W3CIISLog | Microsoft Sentinel Analytic Rules

Back


Id	888c4736-e604-48eb-b2c7-3462356d9510
Rulename	TI Map IP Entity to W3CIISLog
Description	This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in W3CIISLog.
Severity	Medium
Tactics	CommandAndControl
Techniques	T1071
Required data connectors	AzureMonitor(IIS) MicrosoftDefenderThreatIntelligence ThreatIntelligence ThreatIntelligenceTaxii
Kind	Scheduled
Query frequency	1h
Query period	14d
Trigger threshold	0
Trigger operator	gt
Source Uri	https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat%20Intelligence%20%28NEW%29/Analytic%20Rules/IPEntity_W3CIISLog.yaml
Version	1.4.6
Arm template	888c4736-e604-48eb-b2c7-3462356d9510.json

KQL

let dt_lookBack = 1h; // Look back 1 hour for W3CIISLog 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 IndicatorType in ("ipv4-addr", "ipv6-addr", "network-traffic")
     | extend NetworkSourceIP = toupper(ObservableValue)
     | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
  | 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)
  | extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
  | 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, ObservableValue
  | where IsActive and (ValidUntil > now() or isempty(ValidUntil));
// Perform a join between IP indicators and W3CIISLog 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 (
      W3CIISLog
      | where TimeGenerated >= ago(dt_lookBack)
      | where isnotempty(cIP)
      | where ipv4_is_private(cIP) == false and cIP !startswith "fe80" and cIP !startswith "::" and cIP !startswith "127."
      | extend W3CIISLog_TimeGenerated = TimeGenerated
  )
  on $left.TI_ipEntity == $right.cIP
  // Filter out W3CIISLog events that occurred after the expiration of the corresponding indicator
  | where W3CIISLog_TimeGenerated < ValidUntil
  // Group the results by IndicatorId and keep the W3CIISLog event with the latest timestamp
  | summarize W3CIISLog_TimeGenerated = arg_max(W3CIISLog_TimeGenerated, *) by Id, cIP
  // Select the desired output fields
  | extend Description = tostring(parse_json(Data).description)
  | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  | project timestamp = W3CIISLog_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence,
    TI_ipEntity, Computer, sSiteName, cIP, sIP, sPort, csMethod, csUserName, scStatus, scSubStatus, scWin32Status,
   NetworkSourceIP, Type, Url

YAML

tactics:
- CommandAndControl
triggerOperator: gt
id: 888c4736-e604-48eb-b2c7-3462356d9510
kind: Scheduled
queryFrequency: 1h
triggerThreshold: 0
version: 1.4.6
requiredDataConnectors:
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
- connectorId: ThreatIntelligenceTaxii
  dataTypes:
  - ThreatIntelIndicators
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
- connectorId: AzureMonitor(IIS)
  dataTypes:
  - W3CIISLog
name: TI Map IP Entity to W3CIISLog
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat%20Intelligence%20%28NEW%29/Analytic%20Rules/IPEntity_W3CIISLog.yaml
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: csUserName
  entityType: Account
- fieldMappings:
  - identifier: HostName
    columnName: Computer
  entityType: Host
- fieldMappings:
  - identifier: Address
    columnName: cIP
  entityType: IP
- fieldMappings:
  - identifier: Url
    columnName: Url
  entityType: URL
severity: Medium
description: |
  This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in W3CIISLog.
query: |
  let dt_lookBack = 1h; // Look back 1 hour for W3CIISLog 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 IndicatorType in ("ipv4-addr", "ipv6-addr", "network-traffic")
       | extend NetworkSourceIP = toupper(ObservableValue)
       | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
    | 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)
    | extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
    | 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, ObservableValue
    | where IsActive and (ValidUntil > now() or isempty(ValidUntil));
  // Perform a join between IP indicators and W3CIISLog 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 (
        W3CIISLog
        | where TimeGenerated >= ago(dt_lookBack)
        | where isnotempty(cIP)
        | where ipv4_is_private(cIP) == false and cIP !startswith "fe80" and cIP !startswith "::" and cIP !startswith "127."
        | extend W3CIISLog_TimeGenerated = TimeGenerated
    )
    on $left.TI_ipEntity == $right.cIP
    // Filter out W3CIISLog events that occurred after the expiration of the corresponding indicator
    | where W3CIISLog_TimeGenerated < ValidUntil
    // Group the results by IndicatorId and keep the W3CIISLog event with the latest timestamp
    | summarize W3CIISLog_TimeGenerated = arg_max(W3CIISLog_TimeGenerated, *) by Id, cIP
    // Select the desired output fields
    | extend Description = tostring(parse_json(Data).description)
    | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
    | project timestamp = W3CIISLog_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence,
      TI_ipEntity, Computer, sSiteName, cIP, sIP, sPort, csMethod, csUserName, scStatus, scSubStatus, scWin32Status,
     NetworkSourceIP, Type, Url
queryPeriod: 14d
relevantTechniques:
- T1071

ARM

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2024-01-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/888c4736-e604-48eb-b2c7-3462356d9510')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/888c4736-e604-48eb-b2c7-3462356d9510')]",
      "properties": {
        "alertRuleTemplateName": "888c4736-e604-48eb-b2c7-3462356d9510",
        "customDetails": null,
        "description": "This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in W3CIISLog.\n",
        "displayName": "TI Map IP Entity to W3CIISLog",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "csUserName",
                "identifier": "Name"
              }
            ]
          },
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "Computer",
                "identifier": "HostName"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "cIP",
                "identifier": "Address"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "Url",
                "identifier": "Url"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat%20Intelligence%20%28NEW%29/Analytic%20Rules/IPEntity_W3CIISLog.yaml",
        "query": "let dt_lookBack = 1h; // Look back 1 hour for W3CIISLog events\nlet ioc_lookBack = 14d; // Look back 14 days for threat intelligence indicators\n// Fetch threat intelligence indicators related to IP addresses\nlet IP_Indicators = ThreatIntelIndicators\n//extract key part of kv pair\n     | extend IndicatorType = replace(@\"\\[|\\]|\\\"\"\", \"\", tostring(split(ObservableKey, \":\", 0)))\n     | where IndicatorType in (\"ipv4-addr\", \"ipv6-addr\", \"network-traffic\")\n     | extend NetworkSourceIP = toupper(ObservableValue)\n     | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)\n  | where TimeGenerated >= ago(ioc_lookBack)\n  | extend TI_ipEntity = iff(isnotempty(NetworkSourceIP), NetworkSourceIP, NetworkSourceIP)\n  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)\n  | extend Url = iff(ObservableKey == \"url:value\", ObservableValue, \"\")\n  | where ipv4_is_private(TI_ipEntity) == false and TI_ipEntity !startswith \"fe80\" and TI_ipEntity !startswith \"::\" and TI_ipEntity !startswith \"127.\"\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue\n  | where IsActive and (ValidUntil > now() or isempty(ValidUntil));\n// Perform a join between IP indicators and W3CIISLog events\nIP_Indicators\n  | project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, Type, TI_ipEntity\n  // Use innerunique to keep performance fast and result set low, as we only need one match to indicate potential malicious activity that needs investigation\n  | join kind=innerunique (\n      W3CIISLog\n      | where TimeGenerated >= ago(dt_lookBack)\n      | where isnotempty(cIP)\n      | where ipv4_is_private(cIP) == false and cIP !startswith \"fe80\" and cIP !startswith \"::\" and cIP !startswith \"127.\"\n      | extend W3CIISLog_TimeGenerated = TimeGenerated\n  )\n  on $left.TI_ipEntity == $right.cIP\n  // Filter out W3CIISLog events that occurred after the expiration of the corresponding indicator\n  | where W3CIISLog_TimeGenerated < ValidUntil\n  // Group the results by IndicatorId and keep the W3CIISLog event with the latest timestamp\n  | summarize W3CIISLog_TimeGenerated = arg_max(W3CIISLog_TimeGenerated, *) by Id, cIP\n  // Select the desired output fields\n  | extend Description = tostring(parse_json(Data).description)\n  | extend ActivityGroupNames = extract(@\"ActivityGroup:(\\S+)\", 1, tostring(parse_json(Data).labels))\n  | project timestamp = W3CIISLog_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence,\n    TI_ipEntity, Computer, sSiteName, cIP, sIP, sPort, csMethod, csUserName, scStatus, scSubStatus, scWin32Status,\n   NetworkSourceIP, Type, Url\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl"
        ],
        "techniques": [
          "T1071"
        ],
        "templateVersion": "1.4.6",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}