TI map Domain entity to EmailUrlInfo

Back


Id	87cc75df-d7b2-44f1-b064-ee924edfc879
Rulename	TI map Domain entity to EmailUrlInfo
Description	Identifies a match in EmailUrlInfo table from any Domain IOC from TI.
Severity	Medium
Tactics	Impact
Required data connectors	MicrosoftDefenderThreatIntelligence Office365 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 Intelligence/Analytic Rules/DomainEntity_EmailUrlInfo.yaml
Version	1.0.1
Arm template	87cc75df-d7b2-44f1-b064-ee924edfc879.json

KQL

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let EmailUrlInfo_ = materialize(EmailUrlInfo
| where isnotempty(UrlDomain)
| where TimeGenerated > ago(dt_lookBack)
| project-rename Email_Url = Url);
let Domains = EmailUrlInfo_
| distinct UrlDomain
| summarize make_list(UrlDomain);
let Candidates = ThreatIntelligenceIndicator
| where isnotempty(DomainName)
| where TimeGenerated >= ago(ioc_lookBack)
| extend TI_Domain = tolower(DomainName)
| where TI_Domain in (Domains)
| 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;"
| join kind=innerunique EmailUrlInfo_ on $left.TI_Domain == $right.UrlDomain
| join kind=innerunique (EmailEvents | where TimeGenerated >= ago(dt_lookBack) | project-rename EmailEvents_TimeGenerated = TimeGenerated) on $left.NetworkMessageId == $right.NetworkMessageId
| where DeliveryLocation !has "Quarantine"
// Customize and uncomment the following line to remove security related mailboxes
//| where tolower(RecipientEmailAddress) !in ("secmailbox1@example.com", "secmailbox2@example.com")
| where EmailEvents_TimeGenerated < ExpirationDateTime
| summarize EmailEvents_TimeGenerated = arg_max(EmailEvents_TimeGenerated, *) by IndicatorId, RecipientEmailAddress;
let Candidate_Domains = Candidates | distinct TI_Domain | summarize make_list(TI_Domain);
ThreatIntelligenceIndicator
| where isnotempty(Url)
| where TimeGenerated > ago(ioc_lookBack)
| extend Host = tostring(parse_url(Url).Host)
| where Host in (Candidate_Domains)
| 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;"
| join kind=innerunique (Candidates | extend parsed_url = parse_url(Email_Url) | extend BaseUrl = strcat(parsed_url.Scheme, "://", parsed_url.Host, parsed_url.Path)) on $left.Url == $right.BaseUrl
| where DeliveryAction !has "Blocked"
| project EmailEvents_TimeGenerated, RecipientEmailAddress, IndicatorId, TI_Domain, ConfidenceScore, Description, Tags, TrafficLightProtocolLevel, Url = Email_Url, DeliveryAction, DeliveryLocation, EmailDirection, NetworkMessageId, AuthenticationDetails, SenderFromAddress, SenderIPv4, Subject
| extend Name = tostring(split(RecipientEmailAddress, '@', 0)[0]), UPNSuffix = tostring(split(RecipientEmailAddress, '@', 1)[0])
| extend timestamp = EmailEvents_TimeGenerated

YAML

id: 87cc75df-d7b2-44f1-b064-ee924edfc879
triggerThreshold: 0
severity: Medium
requiredDataConnectors:
- connectorId: Office365
  dataTypes:
  - EmailUrlInfo
- connectorId: ThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: ThreatIntelligenceTaxii
  dataTypes:
  - ThreatIntelligenceIndicator
- connectorId: MicrosoftDefenderThreatIntelligence
  dataTypes:
  - ThreatIntelligenceIndicator
version: 1.0.1
queryFrequency: 1h
queryPeriod: 14d
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let EmailUrlInfo_ = materialize(EmailUrlInfo
  | where isnotempty(UrlDomain)
  | where TimeGenerated > ago(dt_lookBack)
  | project-rename Email_Url = Url);
  let Domains = EmailUrlInfo_
  | distinct UrlDomain
  | summarize make_list(UrlDomain);
  let Candidates = ThreatIntelligenceIndicator
  | where isnotempty(DomainName)
  | where TimeGenerated >= ago(ioc_lookBack)
  | extend TI_Domain = tolower(DomainName)
  | where TI_Domain in (Domains)
  | 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;"
  | join kind=innerunique EmailUrlInfo_ on $left.TI_Domain == $right.UrlDomain
  | join kind=innerunique (EmailEvents | where TimeGenerated >= ago(dt_lookBack) | project-rename EmailEvents_TimeGenerated = TimeGenerated) on $left.NetworkMessageId == $right.NetworkMessageId
  | where DeliveryLocation !has "Quarantine"
  // Customize and uncomment the following line to remove security related mailboxes
  //| where tolower(RecipientEmailAddress) !in ("secmailbox1@example.com", "secmailbox2@example.com")
  | where EmailEvents_TimeGenerated < ExpirationDateTime
  | summarize EmailEvents_TimeGenerated = arg_max(EmailEvents_TimeGenerated, *) by IndicatorId, RecipientEmailAddress;
  let Candidate_Domains = Candidates | distinct TI_Domain | summarize make_list(TI_Domain);
  ThreatIntelligenceIndicator
  | where isnotempty(Url)
  | where TimeGenerated > ago(ioc_lookBack)
  | extend Host = tostring(parse_url(Url).Host)
  | where Host in (Candidate_Domains)
  | 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;"
  | join kind=innerunique (Candidates | extend parsed_url = parse_url(Email_Url) | extend BaseUrl = strcat(parsed_url.Scheme, "://", parsed_url.Host, parsed_url.Path)) on $left.Url == $right.BaseUrl
  | where DeliveryAction !has "Blocked"
  | project EmailEvents_TimeGenerated, RecipientEmailAddress, IndicatorId, TI_Domain, ConfidenceScore, Description, Tags, TrafficLightProtocolLevel, Url = Email_Url, DeliveryAction, DeliveryLocation, EmailDirection, NetworkMessageId, AuthenticationDetails, SenderFromAddress, SenderIPv4, Subject
  | extend Name = tostring(split(RecipientEmailAddress, '@', 0)[0]), UPNSuffix = tostring(split(RecipientEmailAddress, '@', 1)[0])
  | extend timestamp = EmailEvents_TimeGenerated  
triggerOperator: gt
tactics:
- Impact
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/DomainEntity_EmailUrlInfo.yaml
description: |
    'Identifies a match in EmailUrlInfo table from any Domain IOC from TI.'
name: TI map Domain entity to EmailUrlInfo
kind: Scheduled
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: RecipientEmailAddress
  - identifier: Name
    columnName: Name
  - identifier: UPNSuffix
    columnName: UPNSuffix
  entityType: Account
- fieldMappings:
  - identifier: Url
    columnName: Url
  entityType: URL

ARM

{
  "$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/87cc75df-d7b2-44f1-b064-ee924edfc879')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/87cc75df-d7b2-44f1-b064-ee924edfc879')]",
      "properties": {
        "alertRuleTemplateName": "87cc75df-d7b2-44f1-b064-ee924edfc879",
        "customDetails": null,
        "description": "'Identifies a match in EmailUrlInfo table from any Domain IOC from TI.'\n",
        "displayName": "TI map Domain entity to EmailUrlInfo",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "RecipientEmailAddress",
                "identifier": "FullName"
              },
              {
                "columnName": "Name",
                "identifier": "Name"
              },
              {
                "columnName": "UPNSuffix",
                "identifier": "UPNSuffix"
              }
            ]
          },
          {
            "entityType": "URL",
            "fieldMappings": [
              {
                "columnName": "Url",
                "identifier": "Url"
              }
            ]
          }
        ],
        "OriginalUri": "https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/DomainEntity_EmailUrlInfo.yaml",
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nlet EmailUrlInfo_ = materialize(EmailUrlInfo\n| where isnotempty(UrlDomain)\n| where TimeGenerated > ago(dt_lookBack)\n| project-rename Email_Url = Url);\nlet Domains = EmailUrlInfo_\n| distinct UrlDomain\n| summarize make_list(UrlDomain);\nlet Candidates = ThreatIntelligenceIndicator\n| where isnotempty(DomainName)\n| where TimeGenerated >= ago(ioc_lookBack)\n| extend TI_Domain = tolower(DomainName)\n| where TI_Domain in (Domains)\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;\"\n| join kind=innerunique EmailUrlInfo_ on $left.TI_Domain == $right.UrlDomain\n| join kind=innerunique (EmailEvents | where TimeGenerated >= ago(dt_lookBack) | project-rename EmailEvents_TimeGenerated = TimeGenerated) on $left.NetworkMessageId == $right.NetworkMessageId\n| where DeliveryLocation !has \"Quarantine\"\n// Customize and uncomment the following line to remove security related mailboxes\n//| where tolower(RecipientEmailAddress) !in (\"secmailbox1@example.com\", \"secmailbox2@example.com\")\n| where EmailEvents_TimeGenerated < ExpirationDateTime\n| summarize EmailEvents_TimeGenerated = arg_max(EmailEvents_TimeGenerated, *) by IndicatorId, RecipientEmailAddress;\nlet Candidate_Domains = Candidates | distinct TI_Domain | summarize make_list(TI_Domain);\nThreatIntelligenceIndicator\n| where isnotempty(Url)\n| where TimeGenerated > ago(ioc_lookBack)\n| extend Host = tostring(parse_url(Url).Host)\n| where Host in (Candidate_Domains)\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;\"\n| join kind=innerunique (Candidates | extend parsed_url = parse_url(Email_Url) | extend BaseUrl = strcat(parsed_url.Scheme, \"://\", parsed_url.Host, parsed_url.Path)) on $left.Url == $right.BaseUrl\n| where DeliveryAction !has \"Blocked\"\n| project EmailEvents_TimeGenerated, RecipientEmailAddress, IndicatorId, TI_Domain, ConfidenceScore, Description, Tags, TrafficLightProtocolLevel, Url = Email_Url, DeliveryAction, DeliveryLocation, EmailDirection, NetworkMessageId, AuthenticationDetails, SenderFromAddress, SenderIPv4, Subject\n| extend Name = tostring(split(RecipientEmailAddress, '@', 0)[0]), UPNSuffix = tostring(split(RecipientEmailAddress, '@', 1)[0])\n| extend timestamp = EmailEvents_TimeGenerated\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "templateVersion": "1.0.1",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}