TI Map IP Entity to SigninLogs | Microsoft Sentinel Analytic Rules

Back


Id	edfc9d8a-6fb3-49e2-80c9-fea15d941799
Rulename	TI Map IP Entity to SigninLogs
Description	This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SigninLogs.
Severity	Medium
Tactics	CommandAndControl
Techniques	T1071
Required data connectors	AzureActiveDirectory 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_SigninLogs_Updated.yaml
Version	1.3.2
Arm template	edfc9d8a-6fb3-49e2-80c9-fea15d941799.json

KQL

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let Signins = materialize(union isfuzzy=true
  (SigninLogs
  | where TimeGenerated >= ago(dt_lookBack)),
  (AADNonInteractiveUserSignInLogs
  | where TimeGenerated >= ago(dt_lookBack)
  | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)));
let SigninIPs = Signins | summarize make_list(IPAddress);
let TI = materialize(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 = NetworkSourceIP
  | extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
  | where TI_ipEntity in (SigninIPs)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
  | extend Description = tostring(parse_json(Data).description)
  | where IsActive and (ValidUntil > now() or isempty(ValidUntil))
  | where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;");
TI
   | project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, Type, TI_ipEntity
// 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 (Signins) on $left.TI_ipEntity == $right.IPAddress
| project-rename SigninLogs_TimeGenerated = TimeGenerated
| where SigninLogs_TimeGenerated < ValidUntil
| extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)
| summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by Id, IPAddress
| extend Description = tostring(parse_json(Data).description)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| project SigninLogs_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, TI_ipEntity, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, StatusReason, NetworkSourceIP, Type, Url
| extend timestamp = SigninLogs_TimeGenerated, Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])

YAML

relevantTechniques:
- T1071
tactics:
- CommandAndControl
queryFrequency: 1h
queryPeriod: 14d
triggerThreshold: 0
name: TI Map IP Entity to SigninLogs
kind: Scheduled
description: |
  'This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SigninLogs.'
id: edfc9d8a-6fb3-49e2-80c9-fea15d941799
entityMappings:
- fieldMappings:
  - columnName: UserPrincipalName
    identifier: FullName
  - columnName: Name
    identifier: Name
  - columnName: UPNSuffix
    identifier: UPNSuffix
  entityType: Account
- fieldMappings:
  - columnName: IPAddress
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: Url
    identifier: Url
  entityType: URL
version: 1.3.2
requiredDataConnectors:
- dataTypes:
  - ThreatIntelIndicators
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelIndicators
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - ThreatIntelIndicators
  connectorId: MicrosoftDefenderThreatIntelligence
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat%20Intelligence%20%28NEW%29/Analytic%20Rules/IPEntity_SigninLogs_Updated.yaml
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let Signins = materialize(union isfuzzy=true
    (SigninLogs
    | where TimeGenerated >= ago(dt_lookBack)),
    (AADNonInteractiveUserSignInLogs
    | where TimeGenerated >= ago(dt_lookBack)
    | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)));
  let SigninIPs = Signins | summarize make_list(IPAddress);
  let TI = materialize(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 = NetworkSourceIP
    | extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
    | where TI_ipEntity in (SigninIPs)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
    | extend Description = tostring(parse_json(Data).description)
    | where IsActive and (ValidUntil > now() or isempty(ValidUntil))
    | where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;");
  TI
     | project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, Type, TI_ipEntity
  // 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 (Signins) on $left.TI_ipEntity == $right.IPAddress
  | project-rename SigninLogs_TimeGenerated = TimeGenerated
  | where SigninLogs_TimeGenerated < ValidUntil
  | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)
  | summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by Id, IPAddress
  | extend Description = tostring(parse_json(Data).description)
  | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  | project SigninLogs_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, TI_ipEntity, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, StatusReason, NetworkSourceIP, Type, Url
  | extend timestamp = SigninLogs_TimeGenerated, Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])
severity: Medium
triggerOperator: gt

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/edfc9d8a-6fb3-49e2-80c9-fea15d941799')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/edfc9d8a-6fb3-49e2-80c9-fea15d941799')]",
      "properties": {
        "alertRuleTemplateName": "edfc9d8a-6fb3-49e2-80c9-fea15d941799",
        "customDetails": null,
        "description": "'This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SigninLogs.'\n",
        "displayName": "TI Map IP Entity to SigninLogs",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "UserPrincipalName",
                "identifier": "FullName"
              },
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "IP",
            "fieldMappings": [
              {
                "columnName": "IPAddress",
                "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_SigninLogs_Updated.yaml",
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nlet Signins = materialize(union isfuzzy=true\n  (SigninLogs\n  | where TimeGenerated >= ago(dt_lookBack)),\n  (AADNonInteractiveUserSignInLogs\n  | where TimeGenerated >= ago(dt_lookBack)\n  | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)));\nlet SigninIPs = Signins | summarize make_list(IPAddress);\nlet TI = materialize(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 = NetworkSourceIP\n  | extend Url = iff(ObservableKey == \"url:value\", ObservableValue, \"\")\n  | where TI_ipEntity in (SigninIPs)\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue\n  | extend Description = tostring(parse_json(Data).description)\n  | where IsActive and (ValidUntil > now() or isempty(ValidUntil))\n  | where Description !contains_cs \"State: inactive;\" and Description !contains_cs \"State: falsepos;\");\nTI\n   | project-reorder *, Tags, TrafficLightProtocolLevel, NetworkSourceIP, Type, TI_ipEntity\n// 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\n| join kind=innerunique (Signins) on $left.TI_ipEntity == $right.IPAddress\n| project-rename SigninLogs_TimeGenerated = TimeGenerated\n| where SigninLogs_TimeGenerated < ValidUntil\n| extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)\n| summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by Id, IPAddress\n| extend Description = tostring(parse_json(Data).description)\n| extend ActivityGroupNames = extract(@\"ActivityGroup:(\\S+)\", 1, tostring(parse_json(Data).labels))\n| project SigninLogs_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, TI_ipEntity, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, StatusReason, NetworkSourceIP, Type, Url\n| extend timestamp = SigninLogs_TimeGenerated, Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "subTechniques": [],
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "CommandAndControl"
        ],
        "techniques": [
          "T1071"
        ],
        "templateVersion": "1.3.2",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}