When a phishing email lands in hundreds of inboxes, the clock starts. Who received it? Who clicked? Did anyone enter a password, and did the attacker sign in from somewhere new? In Microsoft Defender XDR (formerly Microsoft 365 Defender), advanced hunting queries written in KQL answer all of these in minutes, if you have the right queries ready.
This guide is a practical advanced hunting playbook: 20 ready-to-run KQL queries for phishing campaign scoping, click analysis, Entra ID sign-in investigation, device code phishing, lateral movement, endpoint hunting and daily alert triage. They come from hands-on incident response work in a Microsoft 365 environment, with company details replaced by examples. Copy a query, fill in the yellow values, and run it.
What is advanced hunting in Microsoft Defender XDR?
Advanced hunting is the query-based threat hunting tool in the Microsoft Defender portal. It lets you search up to 30 days of raw data from email, identities, endpoints and cloud apps with Kusto Query Language (KQL), the same language used in Microsoft Sentinel and Azure Data Explorer. Unlike alerts, which only show what Microsoft detected, advanced hunting lets you ask your own questions across all that data at once.
- Email: EmailEvents, EmailUrlInfo, UrlClickEvents and related tables (Defender for Office 365).
- Identity: Entra ID sign-in events and identity logons.
- Endpoints: DeviceProcessEvents, DeviceFileEvents, DeviceNetworkEvents and more (Defender for Endpoint).
- Cloud apps: CloudAppEvents for Microsoft 365 activity (Defender for Cloud Apps).
- Alerts: AlertInfo and AlertEvidence across all Defender products.
Licensing note: which tables you see depends on your licenses. Email tables need Defender for Office 365 Plan 2, device tables need Defender for Endpoint, and Microsoft 365 E5 includes them all. Check Microsoft's current licensing documentation for your tenant.
Quick links: Phishing scoping · Click analysis · Identity · Device code phishing · Lateral movement · Endpoint hunting · Daily triage · Reference
How to use this playbook
- Where to run: Microsoft Defender portal → Investigation & response → Hunting → Advanced hunting.
- One query at a time: paste one query, or select it and click Run query. If several queries are in the editor, only the last one returns results.
- Placeholders: replace values in angle brackets, such as <subject> or <user@contoso.com>, before running. Most queries put these in let lines at the top.
- Retention: Advanced hunting keeps 30 days of data. ago(30d) is the maximum look-back.
- Time zone: the Timestamp column is stored in UTC. IST = UTC + 5:30. Where a query needs IST boundaries, it calculates them.
- Result limit: 10,000 rows per query. For larger results, narrow the time range or split by day.
- Export: use Export above the results grid to download CSV for reports or tickets.
Values you must fill in are listed in a yellow table under each query. Queries without a yellow table run as they are.
Tables used
| Table | What it contains |
|---|---|
| EmailEvents | Every email processed: sender, recipient, subject, delivery action and location |
| EmailUrlInfo | URLs found in each email |
| UrlClickEvents | Safe Links clicks: user, URL, allowed or blocked, clicked through |
| EntraIdSignInEvents | Entra ID interactive and non-interactive sign-ins (older tenants: AADSignInEventsBeta) |
| IdentityInfo | User attributes from Entra ID: company, department, country, job title |
| AlertInfo / AlertEvidence | Defender alerts and the users, devices, emails and URLs attached to them |
| CloudAppEvents | Microsoft 365 activity: inbox rules, sharing, consent, admin actions |
| DeviceFileEvents / DeviceProcessEvents / DeviceNetworkEvents | Endpoint file, process and network activity from Defender for Endpoint |
1. Phishing campaign scoping
Use these when a phishing email is reported. Start with 1.1 to get every recipient and their click status, then 1.2 for a per-company summary. Edit the badSubject and badDomain lines; leave one as "" if unknown.
1.1 Full recipient list with click status and company
One row per recipient. Clicked-through users sort to the top.
let lookback = 30d;
let badSubject = "<unique part of subject>";
let badDomain = "<sender domain>";
let msgs = EmailEvents
| where Timestamp > ago(lookback)
| where (isnotempty(badSubject) and Subject contains badSubject)
or (isnotempty(badDomain) and (SenderFromDomain =~ badDomain or SenderMailFromDomain =~ badDomain))
| extend Recipient = tolower(RecipientEmailAddress);
let recipients = msgs
| summarize Received = min(Timestamp), Sender = take_any(SenderFromAddress),
Subject = take_any(Subject), DeliveryAction = take_any(DeliveryAction),
LatestDeliveryLocation = take_any(LatestDeliveryLocation)
by Recipient;
let clicks = UrlClickEvents
| where Timestamp > ago(lookback)
| where NetworkMessageId in ((msgs | distinct NetworkMessageId))
| extend Recipient = tolower(AccountUpn)
| summarize FirstClick = min(Timestamp), TotalClicks = count(),
ClickedThrough = countif(IsClickedThrough),
ClickIPs = make_set(IPAddress, 5), ClickedUrls = make_set(Url, 3)
by Recipient;
let company = IdentityInfo
| where Timestamp > ago(lookback)
| summarize arg_max(Timestamp, CompanyName, Department, Country, JobTitle)
by Recipient = tolower(AccountUpn)
| project Recipient, CompanyName, Department, Country, JobTitle;
recipients
| join kind=leftouter clicks on Recipient
| join kind=leftouter company on Recipient
| extend Status = case(ClickedThrough > 0, "CLICKED THROUGH",
TotalClicks > 0, "Clicked (blocked)",
"No click"),
Company = iff(isempty(CompanyName), "Unknown", CompanyName)
| project Received, Recipient, Company, Department, Country, JobTitle, Status,
FirstClick, TotalClicks, ClickedThrough, DeliveryAction,
LatestDeliveryLocation, Sender, Subject, ClickIPs, ClickedUrls
| order by Status asc, Company asc, Recipient asc| Fill in | Example |
|---|---|
| badSubject | "Document Requires Your Signature" |
| badDomain | "fake-docs.example" (leave "" if unknown) |
1.2 Company-wise summary
Run after 1.1 when management asks how many people per company were affected. Use the same let values.
let lookback = 30d;
let badSubject = "<unique part of subject>";
let msgs = EmailEvents
| where Timestamp > ago(lookback)
| where Subject contains badSubject
| extend Recipient = tolower(RecipientEmailAddress)
| project NetworkMessageId, Recipient, LatestDeliveryLocation;
let clicks = UrlClickEvents
| where Timestamp > ago(lookback)
| where NetworkMessageId in ((msgs | distinct NetworkMessageId))
| extend Recipient = tolower(AccountUpn)
| summarize ClickedThrough = countif(IsClickedThrough) by Recipient;
let company = IdentityInfo
| where Timestamp > ago(lookback)
| summarize arg_max(Timestamp, CompanyName) by Recipient = tolower(AccountUpn)
| project Recipient, CompanyName;
msgs
| join kind=leftouter clicks on Recipient
| join kind=leftouter company on Recipient
| extend Company = iff(isempty(CompanyName), "Unknown", CompanyName)
| summarize Recipients = dcount(Recipient),
StillInInbox = dcountif(Recipient, LatestDeliveryLocation == "Inbox/folder"),
Clicked = dcountif(Recipient, isnotnull(ClickedThrough)),
ClickedThrough = dcountif(Recipient, ClickedThrough > 0)
by Company
| order by Clicked desc| Fill in | Example |
|---|---|
| badSubject | "Document Requires Your Signature" |
1.3 Sender domain activity over 30 days
Shows every subject (campaign wave) sent from a domain, with sender IPs and authentication results.
EmailEvents
| where Timestamp > ago(30d)
| where SenderFromDomain =~ "<sender domain>" or SenderMailFromDomain =~ "<sender domain>"
| summarize Emails = count(), Recipients = dcount(RecipientEmailAddress),
FirstSeen = min(Timestamp), LastSeen = max(Timestamp),
Delivered = countif(DeliveryAction == "Delivered"),
StillInInbox = countif(LatestDeliveryLocation == "Inbox/folder"),
SenderIPs = make_set(SenderIPv4, 10), Verdicts = make_set(ThreatTypes, 5),
AuthResults = make_set(AuthenticationDetails, 3)
by Subject
| order by FirstSeen asc| Fill in | Example |
|---|---|
| <sender domain> (2 places) | fake-docs.example |
If Company shows Unknown, the Company attribute is empty in Entra ID; try Department, or match the export against AD. Emails still in Inbox/folder must be removed through Email & collaboration → Explorer → Take action → Soft delete.
2. Click analysis and URLs
2.1 Users who clicked, with URLs and IPs
Fastest way to list only the users who clicked.
let msgIds = EmailEvents
| where Timestamp > ago(30d)
| where Subject contains "<unique part of subject>"
| distinct NetworkMessageId;
UrlClickEvents
| where Timestamp > ago(30d)
| where NetworkMessageId in (msgIds)
| summarize FirstClick = min(Timestamp), Clicks = count(),
ClickedThrough = countif(IsClickedThrough == true),
IPs = make_set(IPAddress, 5), Urls = make_set(Url, 5)
by AccountUpn
| extend Status = iff(ClickedThrough > 0, "CLICKED THROUGH", "Clicked (blocked)")
| order by Status asc, FirstClick asc| Fill in | Example |
|---|---|
| <unique part of subject> | Payment Batch Scheduled |
2.2 URLs inside the phishing email
Use the domains found here for blocking and endpoint hunting.
EmailEvents
| where Timestamp > ago(30d)
| where Subject contains "<unique part of subject>"
| join kind=inner (EmailUrlInfo | where Timestamp > ago(30d)) on NetworkMessageId
| summarize Emails = dcount(NetworkMessageId) by Url, UrlDomain
| order by Emails desc| Fill in | Example |
|---|---|
| <unique part of subject> | Payment Batch Scheduled |
2.3 Is the sender domain also used for legitimate mail?
Run before blocking a whole domain. If only phishing subjects appear, block the domain; if normal business mail also appears, block only the sender IP and the URL.
EmailEvents
| where Timestamp > ago(30d)
| where SenderFromDomain =~ "<sender domain>"
| summarize Emails = count(), Recipients = dcount(RecipientEmailAddress),
Phish = countif(ThreatTypes has "Phish"),
SenderIPs = make_set(SenderIPv4, 10)
by Subject
| order by Emails desc| Fill in | Example |
|---|---|
| <sender domain> | mail.example.net |
Note: UrlClickEvents only records clicks that pass through Safe Links. A link copied into another browser or opened on a personal device is not recorded, so also run the endpoint queries in section 6 for clicked domains.
3. Identity investigation (Entra ID sign-ins)
Use for any user who clicked through, or who has a risky sign-in alert. Older tenants may need AADSignInEventsBeta instead of EntraIdSignInEvents.
3.1 A user's sign-in locations (last 7 days)
Builds the user's normal pattern: office IP, home ISP, mobile carrier. Anything outside it needs review.
EntraIdSignInEvents
| where Timestamp > ago(7d)
| where AccountUpn =~ "<user@contoso.com>"
| summarize SignIns = count(), Success = countif(ErrorCode == 0),
First = min(Timestamp), Last = max(Timestamp),
Apps = make_set(Application, 5), UserAgents = make_set(UserAgent, 3)
by IPAddress, Country, City
| order by First asc| Fill in | Example |
|---|---|
| <user@contoso.com> | firstname.lastname@contoso.com |
3.2 Successful sign-ins from unknown IPs
List the user's known IPs from 3.1, then find any success from elsewhere.
let knownIPs = dynamic(["<office IP>", "<home IP>"]);
EntraIdSignInEvents
| where Timestamp > datetime(<incident start, e.g. 2026-10-12 18:00>)
| where AccountUpn =~ "<user@contoso.com>"
| where ErrorCode == 0 and IPAddress !in (knownIPs)
| summarize SignIns = count(), Apps = make_set(Application, 10),
First = min(Timestamp), Last = max(Timestamp)
by IPAddress, Country, City| Fill in | Example |
|---|---|
| knownIPs | The user's normal IPs from 3.1, e.g. "203.0.113.10", "198.51.100.25" |
| <incident start> | 2026-10-12 18:00 (UTC) |
| <user@contoso.com> | firstname.lastname@contoso.com |
3.3 Detailed sign-ins with error codes
Shows whether the attacker had the password (50074/50076) or not (50126). See section 8 for codes.
EntraIdSignInEvents
| where Timestamp > ago(7d)
| where AccountUpn =~ "<user@contoso.com>"
| where IPAddress in ("<suspicious IP 1>", "<suspicious IP 2>")
| project Timestamp, IPAddress, ErrorCode, Application, LogonType,
ConditionalAccessStatus, AuthenticationRequirement,
DeviceName, IsManaged, IsCompliant, UserAgent
| order by Timestamp asc| Fill in | Example |
|---|---|
| <user@contoso.com> | firstname.lastname@contoso.com |
| <suspicious IP 1>, <suspicious IP 2> | 203.0.113.45, 198.51.100.77 |
3.4 Attacker IPs across all accounts
Answers "did the attacker get into any other account?" Any other account with Success > 0 is compromised.
let iocIPs = dynamic(["<IP 1>", "<IP 2>", "<IP 3>"]);
EntraIdSignInEvents
| where Timestamp > ago(30d)
| where IPAddress in (iocIPs)
| summarize Attempts = count(), Success = countif(ErrorCode == 0),
Apps = make_set(Application, 5), First = min(Timestamp)
by AccountUpn, IPAddress
| order by Success desc| Fill in | Example |
|---|---|
| iocIPs | All attacker IPs found, e.g. "203.0.113.45", "192.0.2.15" |
3.5 Sign-ins from a suspicious device
An attacker-registered device can sign in from any new IP. This query follows the device, not the IP.
EntraIdSignInEvents
| where Timestamp > datetime(<incident start>)
| where DeviceName =~ "<DEVICE-NAME>"
| project Timestamp, AccountUpn, IPAddress, Country, Application, ErrorCode, LogonType
| order by Timestamp asc| Fill in | Example |
|---|---|
| <incident start> | 2026-10-12 18:00 (UTC) |
| <DEVICE-NAME> | The unknown device name from Entra, e.g. SUSPECT-DEVICE-01 |
3.6 Password spray and scripted sign-in attempts
Automated clients such as Apache-HttpAsyncClient or python-requests indicate credential stuffing.
EntraIdSignInEvents
| where Timestamp > ago(7d)
| where UserAgent has_any ("Apache-HttpAsyncClient", "python-requests", "axios", "curl", "Go-http-client")
or IPAddress == "<suspicious IP>"
| summarize Attempts = count(), Success = countif(ErrorCode == 0),
ErrorCodes = make_set(ErrorCode), Users = dcount(AccountUpn),
SampleUsers = make_set(AccountUpn, 20)
by IPAddress, Country, UserAgent
| order by Users desc| Fill in | Example |
|---|---|
| <suspicious IP> (optional) | 203.0.113.88, or delete the or IPAddress == ... line |
4. Device code phishing and device registration
Device code phishing gets a token without breaking MFA: the user enters an attacker's code at microsoft.com/devicelogin, and the token goes to the attacker. The attacker often then joins a device to Entra ID to obtain a Primary Refresh Token.
4.1 Users with device code or device registration alerts
AlertInfo
| where Timestamp > ago(14d)
| where Title has_any ("device code", "token exchange", "Entra device join", "Device registration after")
| join kind=inner (AlertEvidence | where EntityType == "User" | project AlertId, AccountUpn, AccountName) on AlertId
| summarize Alerts = make_set(Title, 10), First = min(Timestamp) by AccountName, AccountUpn
| order by First desc4.2 What to check in the Entra portal
Advanced hunting does not show every device code field, so confirm in Entra admin center → Identity → Monitoring & health → Sign-in logs:
- Filter User = the affected user and Authentication protocol = Device code.
- Open each entry and record IP address, Application, Resource, Original transfer method, MFA result and Conditional Access status.
- Treat as phishing when the application is Microsoft Authentication Broker with resource Device Registration Service, the IPs are external, and each success follows error 50199.
- Then go to Identity → Devices → All devices, search the user, and compare device names, join type and MDM with the user's known devices.
4.3 Immediate actions when confirmed
- Record the device ID and object ID, then Disable the unknown device.
- Revoke sessions for the user and reset the password.
- Review authentication methods and remove unknown ones.
- Check Defender → Action center → History for Attack Disruption actions.
- Run 3.4 and 3.5 with the attacker IPs and device name.
5. Post-compromise and lateral movement
Run these for every account confirmed or suspected compromised. If all are clean and 3.4 shows no other accounts, record "No evidence of lateral movement identified."
5.1 Mail sent from the compromised mailbox
Internal phishing from a trusted colleague is the most common next step.
EmailEvents
| where Timestamp > datetime(<incident start>)
| where SenderFromAddress =~ "<user@contoso.com>"
| where EmailDirection in ("Intra-org", "Outbound")
| summarize Recipients = dcount(RecipientEmailAddress),
SampleRecipients = make_set(RecipientEmailAddress, 20),
Sent = min(Timestamp)
by Subject, EmailDirection
| order by Recipients desc| Fill in | Example |
|---|---|
| <incident start> | 2026-10-12 18:00 (UTC) |
| <user@contoso.com> | firstname.lastname@contoso.com |
5.2 Who clicked links in mail from that mailbox
let msgs = EmailEvents
| where Timestamp > datetime(<incident start>)
| where SenderFromAddress =~ "<user@contoso.com>"
| distinct NetworkMessageId;
UrlClickEvents
| where Timestamp > datetime(<incident start>)
| where NetworkMessageId in (msgs)
| summarize Clicks = count(), ClickedThrough = countif(IsClickedThrough), Urls = make_set(Url, 5)
by AccountUpn| Fill in | Example |
|---|---|
| <incident start> (2 places) | 2026-10-12 18:00 (UTC) |
| <user@contoso.com> | firstname.lastname@contoso.com |
5.3 Microsoft 365 actions by the account
Use the user's Entra object ID (Entra → Users → user → Overview).
CloudAppEvents
| where Timestamp > datetime(<incident start>)
| where AccountObjectId == "<user object ID>"
| summarize Count = count(), IPs = make_set(IPAddress, 5), First = min(Timestamp)
by Application, ActionType
| order by First asc| Fill in | Example |
|---|---|
| <incident start> | 2026-10-12 18:00 (UTC) |
| <user object ID> | Entra → Users → user → Object ID, e.g. 6f4ec9a9-... |
| ActionType | What it means |
|---|---|
| New-InboxRule, Set-InboxRule, UpdateInboxRules | Rule created to hide or forward mail |
| Send, SendAs, SendOnBehalf | Mail sent in the user's name |
| AnonymousLinkCreated, SharingSet, AddedToGroup | Files shared outside or access widened |
| Consent to application. | OAuth app given access to the account |
| Reset user password., Add member to role., Update user. | Another account changed; possible only with an admin role |
Also check Entra → Users → user → Assigned roles. If the user holds any admin role, review Entra audit logs filtered by Initiated by = the user.
6. Endpoint hunting: remote access tools and payloads
Some phishing links deliver a remote management (RMM) installer such as ScreenConnect instead of a credential page. Confirm first whether the tool is approved at your organization; if not, any hit is malicious.
6.1 Download, execution and network activity for a tool or domain
Replace ScreenConnect and the domain with what you found in 2.2.
let tool = "ScreenConnect";
let domain = "<malicious domain>";
union
(DeviceFileEvents
| where Timestamp > ago(7d)
| where FileName has tool or FileOriginUrl has domain or FileOriginUrl has tool
| project Timestamp, DeviceName, User = InitiatingProcessAccountUpn, Event = "File",
Detail = strcat(FolderPath, " | ", FileOriginUrl), SHA256),
(DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName has tool or ProcessCommandLine has tool
| project Timestamp, DeviceName, User = AccountUpn, Event = "Process",
Detail = ProcessCommandLine, SHA256),
(DeviceNetworkEvents
| where Timestamp > ago(7d)
| where RemoteUrl has domain or RemoteUrl has tool
| project Timestamp, DeviceName, User = InitiatingProcessAccountUpn, Event = "Network",
Detail = strcat(RemoteUrl, ":", RemotePort), SHA256 = "")
| order by DeviceName, Timestamp asc| Fill in | Example |
|---|---|
| tool | "ScreenConnect" (or the RMM tool found) |
| domain | "attacker-tenant.screenconnect.com" |
| Event | Meaning | Action |
|---|---|---|
| File | Installer downloaded | Delete the file, check for Process events |
| Process | Installer or client service ran | Device compromised: Isolate device, uninstall, reset user credentials |
| Network | Connection to the relay | Attacker may have live remote access: isolate immediately |
Also add the domain under Settings → Endpoints → Indicators → URLs/Domains with action Block.
7. Daily alert triage
Advanced hunting has no incidents table; incidents are built from alerts, so these queries use AlertInfo. Time boundaries are calculated in IST (yesterday 00:00 IST to now).
7.1 Today's and yesterday's alerts with users and devices
let IST = 5h + 30m;
let todayStartIST = startofday(now() + IST) - IST;
let fromTime = todayStartIST - 1d;
AlertInfo
| where Timestamp >= fromTime
| join kind=leftouter (
AlertEvidence
| where Timestamp >= fromTime
| summarize Users = make_set_if(AccountUpn, isnotempty(AccountUpn), 10),
Devices = make_set_if(DeviceName, isnotempty(DeviceName), 10)
by AlertId
) on AlertId
| extend Day = iff(Timestamp >= todayStartIST, "Today", "Yesterday"),
TimeIST = Timestamp + IST
| project TimeIST, Day, Severity, Title, Category, ServiceSource,
DetectionSource, AttackTechniques, Users, Devices, AlertId
| order by TimeIST desc7.2 Alert summary by title and source
let IST = 5h + 30m;
let todayStartIST = startofday(now() + IST) - IST;
let fromTime = todayStartIST - 1d;
AlertInfo
| where Timestamp >= fromTime
| extend Day = iff(Timestamp >= todayStartIST, "Today", "Yesterday")
| summarize Alerts = count(),
High = countif(Severity == "High"), Medium = countif(Severity == "Medium"),
Low = countif(Severity == "Low"), Info = countif(Severity == "Informational")
by Day, Title, ServiceSource
| order by Day asc, High desc, Alerts desc7.3 Incidents list (only if Microsoft Sentinel is connected)
If this returns "table not found", Sentinel is not connected; use 7.1 and 7.2.
let IST = 5h + 30m;
let fromTime = startofday(now() + IST) - IST - 1d;
SecurityIncident
| where CreatedTime >= fromTime
| summarize arg_max(LastModifiedTime, *) by IncidentNumber
| project CreatedIST = CreatedTime + IST, IncidentNumber, Title, Severity,
Status, Classification, Owner = tostring(Owner.assignedTo),
AlertCount = array_length(AlertIds)
| order by CreatedIST desc8. Reference: reading results and fixing errors
Click status
| Status | Meaning | Action |
|---|---|---|
| No click | User did not click the link | Remove the email from the inbox |
| Clicked (blocked) | Safe Links blocked the click; user did not reach the site | Remove the email; send an awareness reminder |
| CLICKED THROUGH | User reached the malicious site (click allowed, or warning overridden) | Run sections 3 and 6; reset password and revoke sessions if any suspicious sign-in |
Entra ID sign-in error codes
| Code | Meaning | Risk |
|---|---|---|
| 0 | Successful sign-in | Check IP, app and location |
| 50126 | Invalid username or password | Low: attacker does not have the password |
| 50053 | Account locked (smart lockout) | Low: spray was stopped |
| 50074 / 50076 | Password correct, MFA required | High: password is exposed, reset it |
| 53003 | Blocked by Conditional Access | Policy worked; check why it was attempted |
| 50140 | "Stay signed in" interrupt | Normal |
| 50158 | External security challenge | Usually normal |
| 50199 | User confirmation prompt ("Are you trying to sign in to...?") | Before a device code success, indicates the user approved an attacker's code |
Common query problems
| Problem | Fix |
|---|---|
| Only one query returns results | Run one query at a time, or select the query before Run query |
| Failed to resolve table EntraIdSignInEvents | Use AADSignInEventsBeta |
| SecurityIncident not found | Sentinel is not connected; use section 7.1 |
| 0 rows for a subject search | Use contains instead of has for multi-word text; shorten the phrase |
| 0 rows for a time window | Timestamps are UTC; widen with ago(1d) or convert IST to UTC |
| Company shows Unknown | Company attribute is empty in Entra ID; use Department or match against AD |
| Results capped at 10,000 rows | Narrow the time range or split the query by day |
| Email delivered despite Phish verdict | Check Final system override; an organization policy or third-party filter rule may bypass Defender |
Notes
- Coordinate tenant-level changes (Conditional Access, Tenant Allow/Block List, device settings) with your IT team and change control.
- Attach exported CSVs and screenshots to the incident ticket as evidence.
Tips for writing better advanced hunting queries
- Filter on Timestamp first. Put
where Timestamp > ago(...)right after the table name; it makes queries much faster. - Use
hasinstead ofcontainswhen matching whole words. It uses the index and is faster. - Use
letstatements for values you change often, as in the queries above, so one edit updates the whole query. - Turn proven queries into custom detection rules so they run on a schedule and create alerts automatically.
- Check the schema (the panel on the left of the query editor) when a column or table name fails; Microsoft renames tables over time, for example the move from AADSignInEventsBeta to EntraIdSignInEvents.
- Export evidence early. Data is only kept for 30 days, so download CSVs during an investigation.
Want to automate the follow-up steps? See our PowerShell automation scripts for IT admins, and our real case of device code phishing on Microsoft 365 for the attack behind section 4.
FAQ
What are advanced hunting queries in Microsoft Defender XDR?
They are KQL queries you run in the Defender portal (Hunting > Advanced hunting) to search raw email, identity, endpoint and cloud app data from the last 30 days, for threat hunting and incident investigation.
Is Microsoft 365 Defender the same as Microsoft Defender XDR?
Yes. Microsoft renamed Microsoft 365 Defender to Microsoft Defender XDR. Advanced hunting works the same way, and older queries usually still run, apart from renamed tables.
Where do I find the advanced hunting page?
In the Microsoft Defender portal, go to Investigation & response > Hunting > Advanced hunting.
Why does my query say "Failed to resolve table"?
Either the table needs a license your tenant doesn't have, or it has been renamed. For sign-in data, try AADSignInEventsBeta instead of EntraIdSignInEvents.
Can advanced hunting queries run automatically?
Yes. Save a query as a custom detection rule with a schedule, and Defender will create alerts (and optionally take actions) when it returns results.
How far back can I search?
Advanced hunting keeps up to 30 days of data. For longer retention, stream the data to Microsoft Sentinel or another SIEM.
.png)
0 Comments