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 SigninLogs

Back
Idf2eb15bd-8a88-4b24-9281-e133edfba315
RulenameTI Map IP Entity to SigninLogs
DescriptionThis query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SigninLogs.
SeverityMedium
TacticsImpact
Required data connectorsAzureActiveDirectory
MicrosoftDefenderThreatIntelligence
ThreatIntelligence
ThreatIntelligenceTaxii
KindScheduled
Query frequency1h
Query period14d
Trigger threshold0
Trigger operatorgt
Source Urihttps://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_SigninLogs.yaml
Version1.2.6
Arm templatef2eb15bd-8a88-4b24-9281-e133edfba315.json
Deploy To Azure
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(ThreatIntelligenceIndicator
  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
  | where TimeGenerated >= ago(ioc_lookBack)
  | extend TI_ipEntity = coalesce(NetworkIP, EmailSourceIpAddress, NetworkDestinationIP, NetworkSourceIP)
  | where TI_ipEntity in (SigninIPs)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now()
  | where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;");
TI
// 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 < ExpirationDateTime
| extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)
| summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by IndicatorId, IPAddress
| project SigninLogs_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, TI_ipEntity, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, StatusReason, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type
| extend timestamp = SigninLogs_TimeGenerated, Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])
name: TI Map IP Entity to SigninLogs
triggerOperator: gt
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(ThreatIntelligenceIndicator
    | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
    | where TimeGenerated >= ago(ioc_lookBack)
    | extend TI_ipEntity = coalesce(NetworkIP, EmailSourceIpAddress, NetworkDestinationIP, NetworkSourceIP)
    | where TI_ipEntity in (SigninIPs)
    | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
    | where Active == true and ExpirationDateTime > now()
    | where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;");
  TI
  // 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 < ExpirationDateTime
  | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)
  | summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by IndicatorId, IPAddress
  | project SigninLogs_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, TI_ipEntity, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, StatusReason, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type
  | extend timestamp = SigninLogs_TimeGenerated, Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])  
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_SigninLogs.yaml
description: |
    'This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SigninLogs.'
triggerThreshold: 0
id: f2eb15bd-8a88-4b24-9281-e133edfba315
kind: Scheduled
severity: Medium
version: 1.2.6
entityMappings:
- fieldMappings:
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Address
    columnName: IPAddress
  entityType: IP
- fieldMappings:
  - identifier: Url
    columnName: Url
  entityType: URL
queryFrequency: 1h
queryPeriod: 14d
requiredDataConnectors:
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
tactics:
- Impact
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "apiVersion": "2023-02-01-preview",
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/f2eb15bd-8a88-4b24-9281-e133edfba315')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/f2eb15bd-8a88-4b24-9281-e133edfba315')]",
      "properties": {
        "alertRuleTemplateName": "f2eb15bd-8a88-4b24-9281-e133edfba315",
        "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": "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 Intelligence/Analytic Rules/IPEntity_SigninLogs.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(ThreatIntelligenceIndicator\n  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)\n  | where TimeGenerated >= ago(ioc_lookBack)\n  | extend TI_ipEntity = coalesce(NetworkIP, EmailSourceIpAddress, NetworkDestinationIP, NetworkSourceIP)\n  | where TI_ipEntity in (SigninIPs)\n  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n  | where Active == true and ExpirationDateTime > now()\n  | where Description !contains_cs \"State: inactive;\" and Description !contains_cs \"State: falsepos;\");\nTI\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 < ExpirationDateTime\n| extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)\n| summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by IndicatorId, IPAddress\n| project SigninLogs_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore, TI_ipEntity, IPAddress, UserPrincipalName, AppDisplayName, StatusCode, StatusDetails, StatusReason, NetworkIP, NetworkDestinationIP, NetworkSourceIP, EmailSourceIpAddress, Type\n| extend timestamp = SigninLogs_TimeGenerated, Name = tostring(split(UserPrincipalName, '@', 0)[0]), UPNSuffix = tostring(split(UserPrincipalName, '@', 1)[0])\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "templateVersion": "1.2.6",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}