Microsoft Sentinel Analytic Rules
cloudbrothers.infoAzure Sentinel RepoToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage

TI Map IP Entity to W3CIISLog

Back
Id888c4736-e604-48eb-b2c7-3462356d9510
RulenameTI Map IP Entity to W3CIISLog
DescriptionThis query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in W3CIISLog.
SeverityMedium
TacticsCommandAndControl
TechniquesT1071
Required data connectorsAzureMonitor(IIS)
MicrosoftDefenderThreatIntelligence
ThreatIntelligence
ThreatIntelligenceTaxii
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/IPEntity_W3CIISLog.yaml
Version1.4.6
Arm template888c4736-e604-48eb-b2c7-3462356d9510.json
Deploy To Azure
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
queryFrequency: 1h
description: |
    This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in W3CIISLog.
queryPeriod: 14d
name: TI Map IP Entity to W3CIISLog
severity: Medium
tactics:
- CommandAndControl
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  
id: 888c4736-e604-48eb-b2c7-3462356d9510
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/IPEntity_W3CIISLog.yaml
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: csUserName
- entityType: Host
  fieldMappings:
  - identifier: HostName
    columnName: Computer
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: cIP
- entityType: URL
  fieldMappings:
  - identifier: Url
    columnName: Url
triggerThreshold: 0
requiredDataConnectors:
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
- connectorId: ThreatIntelligenceTaxii
  dataTypes:
  - ThreatIntelIndicators
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelIndicators
- connectorId: AzureMonitor(IIS)
  dataTypes:
  - W3CIISLog
version: 1.4.6
triggerOperator: gt
kind: Scheduled
relevantTechniques:
- T1071
{
  "$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 Intelligence (NEW)/Analytic Rules/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"
    }
  ]
}