TI map Email entity to SecurityEvent

Back


Id	2fc5d810-c9cc-491a-b564-841427ae0e50
Rulename	TI map Email entity to SecurityEvent
Description	Identifies a match in SecurityEvent table from any Email IOC from TI
Severity	Medium
Tactics	Impact
Required data connectors	MicrosoftDefenderThreatIntelligence SecurityEvents ThreatIntelligence ThreatIntelligenceTaxii WindowsForwardedEvents WindowsSecurityEvents
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/EmailEntity_SecurityEvent.yaml
Version	1.3.5
Arm template	2fc5d810-c9cc-491a-b564-841427ae0e50.json

KQL

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
ThreatIntelligenceIndicator
//Filtering the table for Email related IOCs
| where isnotempty(EmailSenderAddress)
| where TimeGenerated >= ago(ioc_lookBack)
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
| where Active == true and ExpirationDateTime > now()
// 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 (
(union isfuzzy=true
(SecurityEvent
| where TimeGenerated >= ago(dt_lookBack) and isnotempty(TargetUserName)
//Normalizing the column to lower case for exact match with EmailSenderAddress column
| extend TargetUserName = tolower(TargetUserName)
// renaming timestamp column so it is clear the log this came from SecurityEvent table
| extend SecurityEvent_TimeGenerated = TimeGenerated
),
(WindowsEvent
| where TimeGenerated >= ago(dt_lookBack)
| extend TargetUserName = tostring(EventData.TargetUserName)
| where isnotempty(TargetUserName)
//Normalizing the column to lower case for exact match with EmailSenderAddress column
| extend TargetUserName = tolower(TargetUserName)
// renaming timestamp column so it is clear the log this came from SecurityEvent table
| extend SecurityEvent_TimeGenerated = TimeGenerated
))
)
on $left.EmailSenderAddress == $right.TargetUserName
| where SecurityEvent_TimeGenerated < ExpirationDateTime
| summarize SecurityEvent_TimeGenerated = arg_max(SecurityEvent_TimeGenerated, *) by IndicatorId, TargetUserName
| project SecurityEvent_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,
EmailSenderName, EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType, Computer, EventID, TargetUserName, Activity, IpAddress, AccountType,
LogonTypeName, LogonProcessName, Status, SubStatus
| extend HostName = tostring(split(Computer, '.', 0)[0]), DnsDomain = tostring(strcat_array(array_slice(split(Computer, '.'), 1, -1), '.'))
| extend timestamp = SecurityEvent_TimeGenerated

YAML

severity: Medium
queryFrequency: 1h
kind: Scheduled
version: 1.3.5
triggerOperator: gt
description: |
    'Identifies a match in SecurityEvent table from any Email IOC from TI'
queryPeriod: 14d
id: 2fc5d810-c9cc-491a-b564-841427ae0e50
entityMappings:
- entityType: Account
  fieldMappings:
  - columnName: TargetUserName
    identifier: Name
- entityType: Host
  fieldMappings:
  - columnName: HostName
    identifier: HostName
  - columnName: DnsDomain
    identifier: DnsDomain
- entityType: IP
  fieldMappings:
  - columnName: IpAddress
    identifier: Address
- entityType: URL
  fieldMappings:
  - columnName: Url
    identifier: Url
tactics:
- Impact
name: TI map Email entity to SecurityEvent
triggerThreshold: 0
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence/Analytic Rules/EmailEntity_SecurityEvent.yaml
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
  ThreatIntelligenceIndicator
  //Filtering the table for Email related IOCs
  | where isnotempty(EmailSenderAddress)
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId
  | where Active == true and ExpirationDateTime > now()
  // 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 (
  (union isfuzzy=true
  (SecurityEvent
  | where TimeGenerated >= ago(dt_lookBack) and isnotempty(TargetUserName)
  //Normalizing the column to lower case for exact match with EmailSenderAddress column
  | extend TargetUserName = tolower(TargetUserName)
  // renaming timestamp column so it is clear the log this came from SecurityEvent table
  | extend SecurityEvent_TimeGenerated = TimeGenerated
  ),
  (WindowsEvent
  | where TimeGenerated >= ago(dt_lookBack)
  | extend TargetUserName = tostring(EventData.TargetUserName)
  | where isnotempty(TargetUserName)
  //Normalizing the column to lower case for exact match with EmailSenderAddress column
  | extend TargetUserName = tolower(TargetUserName)
  // renaming timestamp column so it is clear the log this came from SecurityEvent table
  | extend SecurityEvent_TimeGenerated = TimeGenerated
  ))
  )
  on $left.EmailSenderAddress == $right.TargetUserName
  | where SecurityEvent_TimeGenerated < ExpirationDateTime
  | summarize SecurityEvent_TimeGenerated = arg_max(SecurityEvent_TimeGenerated, *) by IndicatorId, TargetUserName
  | project SecurityEvent_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,
  EmailSenderName, EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType, Computer, EventID, TargetUserName, Activity, IpAddress, AccountType,
  LogonTypeName, LogonProcessName, Status, SubStatus
  | extend HostName = tostring(split(Computer, '.', 0)[0]), DnsDomain = tostring(strcat_array(array_slice(split(Computer, '.'), 1, -1), '.'))
  | extend timestamp = SecurityEvent_TimeGenerated  
requiredDataConnectors:
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - SecurityEvent
  connectorId: SecurityEvents
- dataTypes:
  - SecurityEvents
  connectorId: WindowsSecurityEvents
- dataTypes:
  - WindowsEvent
  connectorId: WindowsForwardedEvents
- dataTypes:
  - ThreatIntelligenceIndicator
  connectorId: MicrosoftDefenderThreatIntelligence

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/2fc5d810-c9cc-491a-b564-841427ae0e50')]",
      "kind": "Scheduled",
      "name": "[concat(parameters('workspace'),'/Microsoft.SecurityInsights/2fc5d810-c9cc-491a-b564-841427ae0e50')]",
      "properties": {
        "alertRuleTemplateName": "2fc5d810-c9cc-491a-b564-841427ae0e50",
        "customDetails": null,
        "description": "'Identifies a match in SecurityEvent table from any Email IOC from TI'\n",
        "displayName": "TI map Email entity to SecurityEvent",
        "enabled": true,
        "entityMappings": [
          {
            "entityType": "Account",
            "fieldMappings": [
              {
                "columnName": "TargetUserName",
                "identifier": "Name"
              }
            ]
          },
          {
            "entityType": "Host",
            "fieldMappings": [
              {
                "columnName": "HostName",
                "identifier": "HostName"
              },
              {
                "columnName": "DnsDomain",
                "identifier": "DnsDomain"
              }
            ]
          },
          {
            "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/EmailEntity_SecurityEvent.yaml",
        "query": "let dt_lookBack = 1h;\nlet ioc_lookBack = 14d;\nlet emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\\.[a-zA-Z0-9-.]+$';\nThreatIntelligenceIndicator\n//Filtering the table for Email related IOCs\n| where isnotempty(EmailSenderAddress)\n| where TimeGenerated >= ago(ioc_lookBack)\n| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by IndicatorId\n| where Active == true and ExpirationDateTime > now()\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(union isfuzzy=true\n(SecurityEvent\n| where TimeGenerated >= ago(dt_lookBack) and isnotempty(TargetUserName)\n//Normalizing the column to lower case for exact match with EmailSenderAddress column\n| extend TargetUserName = tolower(TargetUserName)\n// renaming timestamp column so it is clear the log this came from SecurityEvent table\n| extend SecurityEvent_TimeGenerated = TimeGenerated\n),\n(WindowsEvent\n| where TimeGenerated >= ago(dt_lookBack)\n| extend TargetUserName = tostring(EventData.TargetUserName)\n| where isnotempty(TargetUserName)\n//Normalizing the column to lower case for exact match with EmailSenderAddress column\n| extend TargetUserName = tolower(TargetUserName)\n// renaming timestamp column so it is clear the log this came from SecurityEvent table\n| extend SecurityEvent_TimeGenerated = TimeGenerated\n))\n)\non $left.EmailSenderAddress == $right.TargetUserName\n| where SecurityEvent_TimeGenerated < ExpirationDateTime\n| summarize SecurityEvent_TimeGenerated = arg_max(SecurityEvent_TimeGenerated, *) by IndicatorId, TargetUserName\n| project SecurityEvent_TimeGenerated, Description, ActivityGroupNames, IndicatorId, ThreatType, Url, ExpirationDateTime, ConfidenceScore,\nEmailSenderName, EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType, Computer, EventID, TargetUserName, Activity, IpAddress, AccountType,\nLogonTypeName, LogonProcessName, Status, SubStatus\n| extend HostName = tostring(split(Computer, '.', 0)[0]), DnsDomain = tostring(strcat_array(array_slice(split(Computer, '.'), 1, -1), '.'))\n| extend timestamp = SecurityEvent_TimeGenerated\n",
        "queryFrequency": "PT1H",
        "queryPeriod": "P14D",
        "severity": "Medium",
        "suppressionDuration": "PT1H",
        "suppressionEnabled": false,
        "tactics": [
          "Impact"
        ],
        "templateVersion": "1.3.5",
        "triggerOperator": "GreaterThan",
        "triggerThreshold": 0
      },
      "type": "Microsoft.OperationalInsights/workspaces/providers/alertRules"
    }
  ]
}