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.5
Arm templatef2eb15bd-8a88-4b24-9281-e133edfba315.json
Deploy To Azure
let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let aadFunc = (tableName:string){
ThreatIntelligenceIndicator
| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true
// Picking up only IOC's that contain the entities we want
| where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
// As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.
// Taking the first non-empty value based on potential IOC match availability
| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, 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 (
    table(tableName) | where TimeGenerated >= ago(dt_lookBack)
    | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)
    | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)
    | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)
    // renaming time column so it is clear the log this came from
    | extend SigninLogs_TimeGenerated = TimeGenerated, Type = Type
)
on $left.TI_ipEntity == $right.IPAddress
| where SigninLogs_TimeGenerated < ExpirationDateTime
| 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])
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt
severity: Medium
queryFrequency: 1h
triggerOperator: gt
tactics:
- Impact
kind: Scheduled
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let aadFunc = (tableName:string){
  ThreatIntelligenceIndicator
  | where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true
  // Picking up only IOC's that contain the entities we want
  | where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)
  // As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.
  // Taking the first non-empty value based on potential IOC match availability
  | extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)
  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)
  | extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, 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 (
      table(tableName) | where TimeGenerated >= ago(dt_lookBack)
      | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)
      | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)
      | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)
      // renaming time column so it is clear the log this came from
      | extend SigninLogs_TimeGenerated = TimeGenerated, Type = Type
  )
  on $left.TI_ipEntity == $right.IPAddress
  | where SigninLogs_TimeGenerated < ExpirationDateTime
  | 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])
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  union isfuzzy=true aadSignin, aadNonInt  
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_SigninLogs.yaml
queryPeriod: 14d
version: 1.2.5
name: TI Map IP Entity to SigninLogs
requiredDataConnectors:
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence
entityMappings:
- entityType: Account
  fieldMappings:
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
- entityType: IP
  fieldMappings:
  - identifier: Address
    columnName: IPAddress
- entityType: URL
  fieldMappings:
  - identifier: Url
    columnName: Url
id: f2eb15bd-8a88-4b24-9281-e133edfba315
description: |
    'This query maps any IP indicators of compromise (IOCs) from threat intelligence (TI), by searching for matches in SigninLogs.'
triggerThreshold: 0
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "workspace": {
      "type": "String"
    }
  },
  "resources": [
    {
      "id": "[concat(resourceId('Microsoft.OperationalInsights/workspaces/providers', parameters('workspace'), 'Microsoft.SecurityInsights'),'/alertRules/f2eb15bd-8a88-4b24-9281-e133edfba315')]",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/f2eb15bd-8a88-4b24-9281-e133edfba315')]",
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules",
      "kind": "Scheduled",
      "apiVersion": "2022-11-01-preview",
      "properties": {
        "displayName": "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.'\n",
        "severity": "Medium",
        "enabled": true,
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nlet aadFunc = (tableName:string){\nThreatIntelligenceIndicator\n| where TimeGenerated >= ago(ioc_lookBack) and ExpirationDateTime > now()\n| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n| where Active == true\n// Picking up only IOC's that contain the entities we want\n| where isnotempty(NetworkIP) or isnotempty(EmailSourceIpAddress) or isnotempty(NetworkDestinationIP) or isnotempty(NetworkSourceIP)\n// As there is potentially more than 1 indicator type for matching IP, taking NetworkIP first, then others if that is empty.\n// Taking the first non-empty value based on potential IOC match availability\n| extend TI_ipEntity = iff(isnotempty(NetworkIP), NetworkIP, NetworkDestinationIP)\n| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(NetworkSourceIP), NetworkSourceIP, TI_ipEntity)\n| extend TI_ipEntity = iff(isempty(TI_ipEntity) and isnotempty(EmailSourceIpAddress), EmailSourceIpAddress, 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 (\n    table(tableName) | where TimeGenerated >= ago(dt_lookBack)\n    | extend Status = todynamic(Status), LocationDetails = todynamic(LocationDetails)\n    | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails), StatusReason = tostring(Status.failureReason)\n    | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city), Region = tostring(LocationDetails.countryOrRegion)\n    // renaming time column so it is clear the log this came from\n    | extend SigninLogs_TimeGenerated = TimeGenerated, Type = Type\n)\non $left.TI_ipEntity == $right.IPAddress\n| where SigninLogs_TimeGenerated < ExpirationDateTime\n| summarize SigninLogs_TimeGenerated = arg_max(SigninLogs_TimeGenerated, *) by IndicatorId, IPAddress\n| project SigninLogs_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,\nTI_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};\nlet aadSignin = aadFunc(\"SigninLogs\");\nlet aadNonInt = aadFunc(\"AADNonInteractiveUserSignInLogs\");\nunion isfuzzy=true aadSignin, aadNonInt\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0,
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "alertRuleTemplateName": "f2eb15bd-8a88-4b24-9281-e133edfba315",
        "customDetails": null,
        "entityMappings": [
          {
            "fieldMappings": [
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ],
            "entityType": "Account"
          },
          {
            "fieldMappings": [
              {
                "columnName": "IPAddress",
                "identifier": "Address"
              }
            ],
            "entityType": "IP"
          },
          {
            "fieldMappings": [
              {
                "columnName": "Url",
                "identifier": "Url"
              }
            ],
            "entityType": "URL"
          }
        ],
        "templateVersion": "1.2.5",
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/IPEntity_SigninLogs.yaml"
      }
    }
  ]
}