mal-email-subject-and-url.kql

//Provides more detail to malicious emails
SecurityAlert
| project todynamic(Entities), TimeGenerated, AlertName
| mv-expand Entities
| project AlertName, TimeGenerated, NetworkMessageId = trim(@'^\["|"]$', tostring(Entities.NetworkMessageIds))
| join kind=inner (EmailUrlInfo) on NetworkMessageId
| project TimeGenerated, AlertName, Url, NetworkMessageId
| join kind=inner (EmailEvents) on NetworkMessageId
| project TimeGenerated, NetworkMessageId,Subject, Url, SenderFromAddress, SenderFromDomain, SenderIPv4, RecipientEmailAddress, AlertName

//Provides P1,P2 and Url info
SecurityAlert
| where TimeGenerated > ago(7d)
| where AlertName has "phish" and Entities != ''
| project todynamic(Entities), TimeGenerated, AlertName
| mv-apply Entities on
    (
         summarize make_set(Entities.NetworkMessageIds)
        | mv-expand set_Entities_NetworkMessageIds
    )
| project TimeGenerated, AlertName, NetworkMessageId = tostring(set_Entities_NetworkMessageIds)
| join kind=inner (EmailEvents) on NetworkMessageId
| project TimeGenerated, DeliveryAction, DeliveryLocation, Subject, SenderDisplayName, SenderFromAddress,SenderMailFromAddress, SenderFromDomain, SenderIPv4, RecipientEmailAddress, AlertName,  NetworkMessageId
| join kind=leftouter (EmailUrlInfo) on NetworkMessageId
//| where NetworkMessageId == ''
| project   TimeGenerated, DeliveryAction, DeliveryLocation, RecipientEmailAddress, Subject, Url,SenderDisplayName,P2Sender = SenderFromAddress,P1Sender = SenderMailFromAddress, SenderFromDomain, SenderIPv4, AlertName, NetworkMessageId