KQL Search

Query Details

HomeAI ToolsDevice Query

Security Alert Find Recipientsof Potential Phishing

Query

//When a user reports an email as potential phishing find all other users who received that email

//Data connector required for this query - Security Alert (free table that other Defender products send alert info to)
//Data connector required for this query - M365 Defender - Email* tables

SecurityAlert
| where TimeGenerated > ago(1d)
| where ProviderName == "OATP"
| where AlertName has "Email reported by user as malware or phish"
| mv-expand todynamic(Entities)
| project Entities
| extend SenderMailFromAddress = tostring(Entities.P1Sender)
| extend Subject = tostring(Entities.Subject)
| where isnotempty(SenderMailFromAddress) and isnotempty(Subject)
| distinct SenderMailFromAddress, Subject
| join kind=inner(
    EmailEvents
    | where TimeGenerated > ago(2d)
    | project RecipientEmailAddress, SenderMailFromAddress, Subject
    )
    on SenderMailFromAddress, Subject
| summarize Recipients=make_set(RecipientEmailAddress) by Subject, SenderMailFromAddress

Explanation

This query finds all users who received an email that was reported as potential phishing by another user. It uses the Security Alert and M365 Defender - Email tables as data sources. The query filters for alerts with the specific alert name and expands the entities column. It then joins the results with the EmailEvents table based on the sender's email address and subject. Finally, it summarizes the recipients of each email by subject and sender's email address.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SecurityAlertEmailEvents

Keywords

SecurityAlert,TimeGenerated,ProviderName,AlertName,Entities,SenderMailFromAddress,Subject,isnotempty,distinct,join,EmailEvents,RecipientEmailAddress,make_set,summarize

Operators

whereTimeGeneratedagoProviderName==hasmv-expandtodynamicprojectextendtostringisnotemptydistinctjoinkindonsummarizemake_setby

Actions