Most KQL write-ups hand you a query and move on. Real investigations don't work that way — the query is only useful if you know which question it answers, how to read the result, and where it points you next. This is the full decision tree for two scenarios that cover the vast majority of phishing reports:
- Scenario 1 — email received, not clicked. A scoping-and-cleanup job. Six checks.
- Scenario 2 — received, clicked, then forwarded internally and externally. A live incident with a spreading blast radius. Eight more checks, plus correlation.
Scenario 2 is literally Scenario 1 plus the moment one check comes back with rows — so we build it as one continuous investigation. Everything below uses generic placeholders (contoso.com, evil-domain.com); swap in your real values.
The one join key that ties it all together
Defender assigns every message a unique NetworkMessageId. Find it once, and it's the pivot for the entire email side of the investigation — recipients, URLs, attachments, clicks, and post-delivery actions all join back to it. Get this ID first; everything else hangs off it.
00The tables, and what each one answers
Know which table holds which fact, or you'll hunt in the wrong place.
| Table | The question it answers |
|---|---|
| EmailEvents | Who sent what to whom, and what happened to it (delivery, auth) |
| EmailUrlInfo | What URLs were in the message |
| EmailAttachmentInfo | What files were attached, and their hashes |
| UrlClickEvents | Did anyone actually click — and was it allowed or blocked |
| EmailPostDeliveryEvents | Did ZAP or another action clean it up after delivery |
| AADSignInEventsBeta | Entra sign-ins — new IPs, countries, risk after a click |
| IdentityLogonEvents | Logons (fallback/corroboration when the Beta table is empty) |
| CloudAppEvents | Post-compromise persistence: inbox rules, MFA changes, forwarding |
| DeviceNetworkEvents | Did a click lead to an outbound connection / download on the endpoint |
01Scenario 1 — received, not clicked
Find → scope → extract IOCs → prove click/no-click → check ZAP → read auth.
Pick a check to expand its query and how to read it. Work them top to bottom — Check 4 is the hinge that decides whether you're done or you've just walked into Scenario 2.
Scenario 1 · foundation
01 · Find the message, establish posture
Anchor the investigation on the exact message(s) and grab the NetworkMessageId.
EmailEvents | where Timestamp > ago(7d) | where SenderFromAddress == "[email protected]" or Subject has "Shared invoice document" | project Timestamp, NetworkMessageId, SenderFromAddress, SenderMailFromAddress, RecipientEmailAddress, Subject, DeliveryAction, DeliveryLocation, ThreatTypesHow to read it
Grab the NetworkMessageId — that's your anchor. DeliveryAction (Delivered / Junked / Blocked / Replaced) and DeliveryLocation (Inbox / Junk / Quarantine) give you posture instantly: a message in Inbox is live and dangerous; one already in Quarantine is contained. ThreatTypes shows if Defender already tagged it Phish/Malware.
02 · Scope — who else received it
It's never one recipient. Pivot to the full campaign footprint.
EmailEvents | where Timestamp > ago(7d) | where SenderFromAddress == "[email protected]" or Subject has "Shared invoice document" | summarize Recipients = make_set(RecipientEmailAddress), Count = dcount(RecipientEmailAddress) by Subject, SenderFromAddress, DeliveryAction, DeliveryLocationHow to read it
The Recipients set is your true blast radius. Watch the DeliveryAction split — often some copies were Delivered and some Junked, which tells you who's actually exposed. Attackers vary subjects across a wave, so for precision, match on the URL or attachment hash (next check) rather than subject alone.
03 · Extract the IOCs
You need these for the click check and for blocking.
// URLs in the message(s) EmailEvents | where NetworkMessageId in ("<id1>", "<id2>") | join kind=inner EmailUrlInfo on NetworkMessageId | project NetworkMessageId, RecipientEmailAddress, Url, UrlDomain // attachments + hashes EmailAttachmentInfo | where NetworkMessageId in ("<id1>", "<id2>") | project NetworkMessageId, FileName, FileType, SHA256, ThreatTypesHow to read it
These Url / UrlDomain / SHA256 values are what you'll block in the Tenant Allow/Block List, and the URLs feed straight into the click check next. Note the domain as well as the full URL — attackers rotate paths on the same host.
04 · Did anyone actually click? the hinge
"Not clicked" is proven by an empty result, never assumed.
let msgIds = dynamic(["<id1>", "<id2>"]);
let phishUrls = EmailUrlInfo
| where NetworkMessageId in (msgIds)
| distinct Url;
UrlClickEvents
| where Timestamp > ago(7d)
| where NetworkMessageId in (msgIds) or Url in (phishUrls)
| project Timestamp, AccountUpn, Url, ActionType, IsClickedThrough, IPAddress
How to read it — this is the fork in the whole investigation
Any rows → someone clicked; you've escalated to Scenario 2. Then read the detail: ActionType = ClickAllowed vs ClickBlocked (did Safe Links stop them?), and IsClickedThrough = 1 means they clicked past the warning page and landed on the site — the worst case. That Timestamp becomes your pivot for the compromise check.
05 · Did the system already clean it up?
Check ZAP and other post-delivery actions before you remediate by hand.
EmailPostDeliveryEvents
| where NetworkMessageId in ("<id1>", "<id2>")
| project Timestamp, NetworkMessageId, RecipientEmailAddress,
ActionType, Action, ActionResult
How to read it
A ZAP action with ActionResult == "Success" means the message was auto-pulled from inboxes post-delivery — don't double-remediate what's already gone. An empty result means nothing was auto-actioned; it's still wherever it landed, and it's on you to purge.
06 · Auth posture — spoof or compromised sender?
This changes your entire response.
EmailEvents
| where NetworkMessageId == "<id1>"
| extend Auth = parse_json(AuthenticationDetails)
| project SenderFromAddress, SenderMailFromAddress, SenderIPv4,
SPF = Auth.SPF, DKIM = Auth.DKIM, DMARC = Auth.DMARC, CompAuth = Auth.CompAuth
How to read it
Auth all passes from a known partner domain = the sender's mailbox is likely compromised, not spoofed. Now the real response is warning that vendor — a spoof block does nothing, because the mail is authentically theirs.
Scenario 1 in one line
Find the message, scope the recipients, pull the IOCs, prove whether anyone clicked, confirm ZAP status, and read the auth. If Check 4 was empty — block the IOCs, purge any delivered copies, done. If it had rows, keep going.
02Scenario 2 — clicked, then forwarded internally + externally
Now there are three blast fronts: the clicker's identity, the internal forwards, and the external forwards. Chase all three to ground.
Scenario 2 · the live incident
07 · Confirm & characterize the click
Lock the exact click time — it's the pivot for everything identity-side.
UrlClickEvents | where Timestamp > ago(7d) | where AccountUpn == "[email protected]" | where Url in (phishUrls) or NetworkMessageId in (msgIds) | project Timestamp, AccountUpn, Url, ActionType, IsClickedThrough, IPAddress, DeviceName, Workload | sort by Timestamp ascHow to read it
IsClickedThrough == 1 + ActionType == "ClickAllowed" = they reached the credential-harvest page; treat credentials as exposed. IPAddress and DeviceName tell you where they clicked (corporate device? BYOD?). The earliest Timestamp is your clickTime pivot for Checks 8–9 and 13.
08 · Did the click compromise the account?
Pivot to identity. Both tables — not every tenant streams the Beta one.
// Entra sign-ins in the 24h after the click let clickTime = datetime(2026-01-01T00:00:00Z); // from Check 7 AADSignInEventsBeta | where Timestamp between (clickTime .. (clickTime + 24h)) | where AccountUpn == "[email protected]" | project Timestamp, IPAddress, Country, City, ClientAppUsed, ErrorCode, RiskLevelDuringSignIn, ConditionalAccessStatus, UserAgent | sort by Timestamp asc
// fallback / corroboration if the Beta table isn't populated IdentityLogonEvents | where Timestamp between (clickTime .. (clickTime + 24h)) | where AccountUpn == "[email protected]" | project Timestamp, LogonType, Application, IPAddress, DeviceName, ActionTypeHow to read it
Successful sign-ins (ErrorCode == 0) from a new country/IP shortly after the click = likely token theft / AiTM. RiskLevelDuringSignIn = high, or ClientAppUsed showing legacy/unusual clients, are red flags. A successful sign-in that satisfied Conditional Access from an attacker IP is the token-replay signature — cross-reference the account-takeover playbook for the full identity response.
09 · Persistence — the part that keeps them in critical
Inbox rules, MFA tampering, forwarding. A password reset alone removes none of these.
let clickTime = datetime(2026-01-01T00:00:00Z);
CloudAppEvents
| where Timestamp between (clickTime .. (clickTime + 24h))
| where AccountId == "<victim-object-id>" or AccountDisplayName has "victim"
| where ActionType in ("New-InboxRule","Set-InboxRule","Set-Mailbox",
"Update user.","Add app role assignment to service principal.",
"Register security info.","Disable Strong Authentication.",
"Update StsRefreshTokenValidFrom Timestamp.","Add-MailboxPermission")
| project Timestamp, ActionType, AccountDisplayName, IPAddress, RawEventData
| sort by Timestamp asc
How to read it
10 · Trace the forwards (the victim re-sent the phish)
Two directions to catch — internal and external.
let phishUrls = EmailUrlInfo | where NetworkMessageId in (msgIds) | distinct Url; EmailEvents | where Timestamp > ago(7d) | where SenderFromAddress == "[email protected]" | join kind=leftouter EmailUrlInfo on NetworkMessageId | where Url in (phishUrls) or Subject has "Shared invoice document" or Subject has "FW:" or Subject has "Fwd:" | project Timestamp, NetworkMessageId, RecipientEmailAddress, EmailDirection, Subject, Url, DeliveryAction, DeliveryLocation | sort by Timestamp ascHow to read it
EmailDirection splits your two blast fronts — Intraorg (internal recipients — brand-new potential victims inside the org) vs Outbound (external recipients — reputational damage, and you must notify them). Collect every RecipientEmailAddress; that's wave two.
11 · Scope wave two by direction
A clean two-line answer: how many internal, how many external.
EmailEvents | where Timestamp > ago(7d) | where SenderFromAddress == "[email protected]" | where Subject has_any ("Shared invoice document", "FW:", "Fwd:") | summarize Recipients = make_set(RecipientEmailAddress), Count = dcount(RecipientEmailAddress) by EmailDirectionHow to read it
Internal names go straight into the same investigation — check their clicks next (Check 12). External names go onto a notification list; those organisations need to know a phish came from your user. The counts also size the incident for your report.
12 · Did wave-two recipients click? (recurse the hinge)
The same payload, carried in the forward, against new people.
let wave2Urls = phishUrls; // same payload carried in the forward UrlClickEvents | where Timestamp > ago(7d) | where Url in (wave2Urls) | where AccountUpn != "[email protected]" // exclude patient zero | project Timestamp, AccountUpn, Url, ActionType, IsClickedThrough, IPAddress | sort by Timestamp ascHow to read it
13 · Endpoint fallout, if a link led to a download
Catch the case where the "document" pulled a payload.
let clickTime = datetime(2026-01-01T00:00:00Z); DeviceNetworkEvents | where Timestamp between (clickTime .. (clickTime + 2h)) | where InitiatingProcessAccountUpn == "[email protected]" | where RemoteUrl in (phishUrls) or RemoteUrl has "evil-domain.com" | project Timestamp, DeviceName, RemoteUrl, RemoteIP, InitiatingProcessFileName, InitiatingProcessCommandLineHow to read it
Outbound connections to the phish domain, and the process that made them. If the initiating process is a browser it's likely just the click; if it's a script host (powershell.exe, mshta.exe) you've got execution — pivot to DeviceProcessEvents / DeviceFileEvents for the dropped file and treat it as an endpoint compromise.
14 · Containment & the consolidated IOC block
One query to assemble every indicator to block.
EmailEvents | where NetworkMessageId in (msgIds)
| join kind=leftouter EmailUrlInfo on NetworkMessageId
| join kind=leftouter EmailAttachmentInfo on NetworkMessageId
| summarize Senders = make_set(SenderFromAddress),
Domains = make_set(UrlDomain),
Urls = make_set(Url),
Hashes = make_set(SHA256)
How to read it → then act
03Correlation — one timeline, all signals
Individual checks confirm facts. Correlation turns them into a story you can act on and defend.
The power of Advanced Hunting isn't any single table — it's that Email, Identity, Endpoint, and Cloud share timestamps and identifiers, so you can lay every event on one timeline and see cause and effect. A phishing report that looked like six unrelated alerts becomes a single, legible chain:
Message delivered to Inbox from a compromised partner domain; auth passed (so not a spoof).
ClickAllowed, IsClickedThrough = 1 — the user reached the credential page.
Successful sign-in from a new country, CA satisfied — token replay. Account is compromised.
New-InboxRule hiding replies + Register security info. (attacker MFA device). Persistence established.
Victim mailbox sends the phish onward — Intraorg to 8 colleagues, Outbound to 3 clients.
Wave-two click check on the forwards — one internal colleague clicked; recurse the investigation for them.
Correlating like this is what separates "we blocked a phishing email" from "we understand exactly what happened, how far it went, and what we removed." A useful correlation query joins the signals directly — here, tying each click to the sign-ins that followed it in the same hour:
// clicks correlated with the sign-ins that followed within the hour
let clicks = UrlClickEvents
| where Timestamp > ago(7d) and ActionType == "ClickAllowed"
| project ClickTime = Timestamp, AccountUpn, Url;
clicks
| join kind=inner (
AADSignInEventsBeta
| where Timestamp > ago(7d) and ErrorCode == 0
| project SignInTime = Timestamp, AccountUpn, IPAddress, Country, RiskLevelDuringSignIn
) on AccountUpn
| where SignInTime between (ClickTime .. (ClickTime + 1h))
| project AccountUpn, ClickTime, Url, SignInTime, IPAddress, Country, RiskLevelDuringSignIn
| sort by ClickTime asc
Correlation is signal, not proof of causation
A sign-in after a click is strongly suggestive, not conclusive — the user might have legitimately logged in from a new location. That's exactly why the final step is human validation: the timeline focuses attention, a person confirms intent. Never auto-close (or auto-escalate) purely on a time correlation.
04The IR report template
Fill this in as you run the checks. It doubles as your assessment worksheet and your human-validation record.
Every query above maps to a line in this template. Filling it in as you go means that by the time you reach containment, your report is already written — and any teammate can validate your conclusions against the raw check outputs. Copy it into your ticket and replace the bracketed fields.
PHISHING INVESTIGATION — INCIDENT REPORT
Fill each field from the corresponding check. "N/A" and "None found" are valid, valuable answers.
## SUMMARY Incident ID : [ ticket # ] Reported by : [ user / detection ] Date/Time (UTC): [ ... ] Analyst : [ you ] Severity: [ Low / Med / High / Critical ] One-line verdict : [ e.g. "Phish clicked, account compromised, contained" ] ## THE MESSAGE (Checks 1–3, 6) NetworkMessageId : [ id(s) ] Sender (From) : [ addr ] Mail-From: [ addr ] Sender IP: [ ip ] Subject : [ ... ] Delivery : [ Delivered / Junked / Quarantined ] Location: [ Inbox / ... ] Auth (SPF/DKIM/DMARC): [ pass/fail ] → Verdict: [ Spoof / Compromised sender ] URLs (IOCs) : [ url(s) / domain(s) ] Attachments : [ name / SHA256 ] or None ## SCOPE — WAVE ONE (Check 2) Total recipients : [ n ] Delivered to inbox: [ n ] Quarantined: [ n ] Recipient list : [ ... ] ## INTERACTION (Checks 4, 7) Clicked? : [ Yes / No ] ← the hinge If yes → who : [ upn ] When (clickTime): [ ... ] ClickAllowed? : [ Yes/No ] Clicked through to page: [ Yes/No ] Device / IP : [ ... ] ## COMPROMISE ASSESSMENT (Checks 8, 9, 13) Suspicious sign-in post-click : [ Yes/No ] New country/IP: [ ... ] Risk: [ ... ] Persistence found: - Inbox rule : [ Yes/No — name/action ] - MFA method registered : [ Yes/No — when ] - Forwarding / delegation: [ Yes/No ] Endpoint execution : [ Yes/No — process / file ] ## SPREAD — WAVE TWO (Checks 10–12) Forwarded by victim? : [ Yes/No ] Internal recipients : [ n ] → clicked: [ n ] (new victims: [ list ]) External recipients : [ n ] → notify: [ list ] ## CONTAINMENT (Check 14) IOCs blocked (TABL) : [ senders / domains / urls / hashes ] Messages purged : [ original + forwards — count ] Identity remediation : [ sessions revoked / pwd reset / MFA cleaned / rules removed ] Post-containment sign-in check re-run: [ clean / not ] ## VALIDATION & CLOSURE Correlation reviewed by human : [ analyst initials ] Residual risk / follow-ups : [ ... ] Status : [ Open / Contained / Closed ]
The whole playbook in one breath
Anchor on NetworkMessageId. Find → scope → IOCs → prove the click. If clicked: characterize it, pivot to identity for compromise, rip out persistence, trace the internal and external forwards, recurse the click check on wave two, check the endpoint, then contain and block. Lay it all on one timeline, and let a human validate the story the correlation tells. No gap between the report and the incident being genuinely closed.
Further reading
- Advanced Hunting schema reference — Microsoft Learnevery table and column used above
- EmailEvents tabledelivery, auth, and NetworkMessageId
- UrlClickEvents tablethe click-through signal
- The iPhone she never owned — account-takeover responsethe identity playbook this pivots into
Queries use placeholder values; adapt table and column names to your tenant's licensing and schema version.
Comments
Questions or corrections welcome. Sign in with GitHub to join the thread.