KQL Email Security Queries

EmailEvents
| where TimeGenerated > ago(24h)
| where ThreatTypes has "Phish"
| summarize
    Count = count(),
    Recipients = make_set(RecipientEmailAddress),
    Subjects = make_set(Subject)
    by SenderFromAddress
| sort by Count desc

EmailUrlInfo
| where TimeGenerated > ago(24h)
| where UrlLocation == "EmailBody"
| join kind=inner (
    EmailEvents
    | where ThreatTypes has "Phish" or ThreatTypes has "Malware"
) on NetworkMessageId
| summarize count() by Url, SenderFromAddress, Subject
| sort by count_ desc

EmailAttachmentInfo
| where TimeGenerated > ago(24h)
| where FileType in ("exe", "scr", "bat", "ps1", "vbs", "js", "hta", "iso", "img")
| join kind=inner (EmailEvents) on NetworkMessageId
| project TimeGenerated, SenderFromAddress, RecipientEmailAddress, Subject, FileName, FileType
| sort by TimeGenerated desc

// Emails impersonating executives
let executives = dynamic(["ceo@", "cfo@", "ciso@", "vp."]);
EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromAddress !endswith "@chla.usc.edu"  // external senders
| where Subject contains "wire" or Subject contains "transfer"
    or Subject contains "payment" or Subject contains "urgent"
    or Subject contains "invoice"
| summarize count() by SenderFromAddress, Subject
| sort by count_ desc

EmailEvents
| where TimeGenerated > ago(24h)
| summarize
    Total = count(),
    Delivered = countif(DeliveryAction == "Delivered"),
    Blocked = countif(DeliveryAction == "Blocked"),
    Junked = countif(DeliveryAction == "Junked"),
    Replaced = countif(DeliveryAction == "Replaced")
| extend BlockRate = round(todouble(Blocked) / Total * 100, 1)

EmailEvents
| where TimeGenerated > ago(7d)
| where ThreatTypes != ""
| summarize
    ThreatCount = count(),
    ThreatTypes = make_set(ThreatTypes),
    Senders = dcount(SenderFromAddress)
    by RecipientEmailAddress
| sort by ThreatCount desc
| take 20