TI map Email entity to PaloAlto CommonSecurityLog

let dt_lookBack = 1h;
let ioc_lookBack = 14d;
let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
ThreatIntelIndicators
//Filtering the table for Email related IOCs
| extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
| where IndicatorType == "email-addr"
| extend EmailSenderAddress = ObservableValue
| extend EmailSourceDomain = substring(EmailSenderAddress, indexof(EmailSenderAddress, "@") + 1, strlen(EmailSenderAddress) - indexof(EmailSenderAddress, "@") - 1)
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| extend IndicatorId = tostring(split(Id, "--")[2])
| extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
| where TimeGenerated >= ago(ioc_lookBack)
| summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
| where IsActive and (ValidUntil > now() or isempty(ValidUntil))
| project-reorder *, TrafficLightProtocolLevel, EmailSenderAddress, EmailSourceDomain, Type
// 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 (
    CommonSecurityLog | where TimeGenerated >= ago(dt_lookBack) and isnotempty(DestinationUserID)
    // Filtering PAN Logs for specific event type to match relevant email entities
    | where DeviceVendor == "Palo Alto Networks" and  DeviceEventClassID == "wildfire" and ApplicationProtocol in ("smtp","pop3")
    | extend DestinationUserID = tolower(DestinationUserID)
    | where DestinationUserID matches regex emailregex
    | extend CommonSecurityLog_TimeGenerated = TimeGenerated
)
on $left.EmailSenderAddress == $right.DestinationUserID
| where CommonSecurityLog_TimeGenerated < ValidUntil
| extend Description = tostring(parse_json(Data).description)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by Id, DestinationUserID
| project CommonSecurityLog_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, EmailSenderAddress, DestinationUserID, DeviceEventClassID, LogSeverity, DeviceAction, SourceIP, SourcePort,
DestinationIP, DestinationPort, Protocol, ApplicationProtocol, Url//,  EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType,
| extend timestamp = CommonSecurityLog_TimeGenerated

description: |
    'Identifies a match in CommonSecurityLog table from any Email IOC from TI'
version: 1.2.9
queryFrequency: 1h
triggerThreshold: 0
tactics:
- InitialAccess
queryPeriod: 14d
OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Solutions/Threat Intelligence (NEW)/Analytic Rules/EmailEntity_PaloAlto.yaml
query: |
  let dt_lookBack = 1h;
  let ioc_lookBack = 14d;
  let emailregex = @'^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$';
  ThreatIntelIndicators
  //Filtering the table for Email related IOCs
  | extend IndicatorType = replace(@"\[|\]|\""", "", tostring(split(ObservableKey, ":", 0)))
  | where IndicatorType == "email-addr"
  | extend EmailSenderAddress = ObservableValue
  | extend EmailSourceDomain = substring(EmailSenderAddress, indexof(EmailSenderAddress, "@") + 1, strlen(EmailSenderAddress) - indexof(EmailSenderAddress, "@") - 1)
  | extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
  | extend IndicatorId = tostring(split(Id, "--")[2])
  | extend Url = iff(ObservableKey == "url:value", ObservableValue, "")
  | where TimeGenerated >= ago(ioc_lookBack)
  | summarize LatestIndicatorTime = arg_max(TimeGenerated, *) by Id, ObservableValue
  | where IsActive and (ValidUntil > now() or isempty(ValidUntil))
  | project-reorder *, TrafficLightProtocolLevel, EmailSenderAddress, EmailSourceDomain, Type
  // 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 (
      CommonSecurityLog | where TimeGenerated >= ago(dt_lookBack) and isnotempty(DestinationUserID)
      // Filtering PAN Logs for specific event type to match relevant email entities
      | where DeviceVendor == "Palo Alto Networks" and  DeviceEventClassID == "wildfire" and ApplicationProtocol in ("smtp","pop3")
      | extend DestinationUserID = tolower(DestinationUserID)
      | where DestinationUserID matches regex emailregex
      | extend CommonSecurityLog_TimeGenerated = TimeGenerated
  )
  on $left.EmailSenderAddress == $right.DestinationUserID
  | where CommonSecurityLog_TimeGenerated < ValidUntil
  | extend Description = tostring(parse_json(Data).description)
  | extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
  | summarize CommonSecurityLog_TimeGenerated = arg_max(CommonSecurityLog_TimeGenerated, *) by Id, DestinationUserID
  | project CommonSecurityLog_TimeGenerated, Description, ActivityGroupNames, Id, ValidUntil, Confidence, EmailSenderAddress, DestinationUserID, DeviceEventClassID, LogSeverity, DeviceAction, SourceIP, SourcePort,
  DestinationIP, DestinationPort, Protocol, ApplicationProtocol, Url//,  EmailRecipient, EmailSourceDomain, EmailSourceIpAddress, EmailSubject, FileHashValue, FileHashType,
  | extend timestamp = CommonSecurityLog_TimeGenerated  
id: 17fe80fe-072f-44d4-b62c-97a5bce56a64
name: TI map Email entity to PaloAlto CommonSecurityLog
triggerOperator: gt
severity: Medium
kind: Scheduled
entityMappings:
- fieldMappings:
  - columnName: DestinationUserID
    identifier: Name
  entityType: Account
- fieldMappings:
  - columnName: SourceIP
    identifier: Address
  entityType: IP
- fieldMappings:
  - columnName: Url
    identifier: Url
  entityType: URL
relevantTechniques:
- T1566
requiredDataConnectors:
- dataTypes:
  - CommonSecurityLog
  connectorId: PaloAltoNetworks
- dataTypes:
  - ThreatIntelIndicators
  connectorId: ThreatIntelligence
- dataTypes:
  - ThreatIntelIndicators
  connectorId: ThreatIntelligenceTaxii
- dataTypes:
  - ThreatIntelIndicators
  connectorId: MicrosoftDefenderThreatIntelligence