At a glance
- Owner
- StratQuad
- Version
- November 2026
- Applies to
- Microsoft Defender XDR advanced hunting, Microsoft Sentinel, Log Analytics
- Mandatory
- Reference
Key points
- A query starts with a table and pipes rows through operators:
Table | where ... | project ... | summarize .... - Filter on time and the most selective column first; it is the biggest factor in query speed.
- Defender advanced hunting uses Device*, Email*, Identity* and Cloud* tables with a Timestamp column; Sentinel and Log Analytics tables use TimeGenerated.
- Table and column names are case sensitive; string comparison with
==is case sensitive,=~is not. - Queries below are starting points. Tune thresholds and exclusions to your estate before using them for alerting.
Core operators
| Operator | Use |
|---|---|
where | Filter rows |
project / project-away | Choose / drop columns |
extend | Add calculated columns |
summarize ... by | Aggregate (count, dcount, make_set, arg_max) |
join kind=inner | Combine tables on a key |
top N by / sort by | Order results |
bin(Timestamp, 1h) | Group times into buckets |
has / contains / startswith | String matching; has uses the term index and is faster |
in~ (...) | Case insensitive list match |
ago(7d) | Relative time |
parse_json() / mv-expand | Work with dynamic (JSON) columns |
Sign ins
Failed sign ins by user and result code (Sentinel, Microsoft Entra ID):
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize Failures = count(), IPs = dcount(IPAddress) by UserPrincipalName, ResultType
| sort by Failures desc
Password spraying pattern: one IP, many accounts:
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType in ("50126", "50053")
| summarize Accounts = dcount(UserPrincipalName), Attempts = count() by IPAddress
| where Accounts > 10
| sort by Accounts desc
Successful sign ins from countries a user has not used in the previous 30 days. Users with no earlier sign ins are kept and flagged as NewUser, so new accounts are not silently dropped:
let lookback = SigninLogs
| where TimeGenerated between (ago(30d) .. ago(1d)) and ResultType == "0"
| summarize KnownCountries = make_set(tostring(LocationDetails.countryOrRegion)) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(1d) and ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=leftouter lookback on UserPrincipalName
| extend NewUser = isnull(array_length(KnownCountries))
| where NewUser or not(set_has_element(KnownCountries, Country))
| project TimeGenerated, UserPrincipalName, Country, IPAddress, AppDisplayName, NewUser
Email and mailbox
New or changed inbox rules (Defender, CloudAppEvents); check RuleConfig for forwarding, redirect or delete actions:
CloudAppEvents
| where Timestamp > ago(7d)
| where ActionType in ("New-InboxRule", "Set-InboxRule", "UpdateInboxRules")
| project Timestamp, AccountDisplayName, ActionType, IPAddress, RuleConfig = RawEventData.Parameters, RawEventData
| sort by Timestamp desc
Who received a known phishing message (Defender):
EmailEvents
| where Timestamp > ago(7d)
| where Subject has "invoice" and SenderFromDomain == "example.com"
| project Timestamp, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction
Endpoint
Encoded PowerShell command lines (Defender). PowerShell accepts any short form of -EncodedCommand, such as -e, -ec or -enc, so match the pattern followed by a Base64 string:
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine matches regex @"(?i)\s[-/](e|ec|en[a-z]*)\s+[A-Za-z0-9+/=]{20,}"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName
Remote monitoring and management tools starting for the first time on a device (Defender):
let rmm = dynamic(["anydesk.exe", "screenconnect.windowsclient.exe", "ateraagent.exe", "teamviewer.exe"]);
DeviceProcessEvents
| where Timestamp > ago(30d)
| where FileName in~ (rmm)
| summarize FirstSeen = min(Timestamp) by DeviceName, FileName
| where FirstSeen > ago(1d)
Performance tips
- Put the time filter first and keep the window as small as the question allows.
- Prefer
hasovercontainsfor whole terms. projectonly the columns you need before ajoin.- Use
summarizeinstead of returning raw rows when hunting across large tables.
Practitioner note
Save tuned versions of these as hunting queries or analytics rules rather than retyping them during an incident. Note the Sentinel and Defender column names differ for similar data (TimeGenerated against Timestamp, for example), so test each query in the console it will run in.