4.1.2. Building Advanced Hunting Queries

💡 First Principle: A good hunting query narrows fast and connects carefully. Filter each table to the smallest relevant slice first, by time, ActionType, and key values. Then join or summarize to reveal the pattern, and project only the columns an analyst needs to act. Queries written this way run quickly, stay under service limits, and convert cleanly into detections.

Advanced hunting builds on the pipeline from 1.5. Several patterns cover most hunts:

Find a known indicator across the estate:

DeviceFileEvents
| where Timestamp > ago(30d)
| where SHA256 == "<malicious-file-hash>"
| summarize FirstSeen = min(Timestamp), Devices = dcount(DeviceId) by FileName, FolderPath

Find a suspicious parent-child relationship (Office spawning a script host):

DeviceProcessEvents
| where Timestamp > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "outlook.exe")
| where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "mshta.exe")
| project Timestamp, DeviceName, AccountName, InitiatingProcessFileName, FileName, ProcessCommandLine

Correlate across domains (users who clicked a phishing URL and then ran PowerShell on their device):

let Clickers = UrlClickEvents
    | where Timestamp > ago(3d)
    | where Url has "malicious-domain.example"
    | project ClickTime = Timestamp, AccountUpn;
DeviceProcessEvents
| where Timestamp > ago(3d)
| where FileName =~ "powershell.exe"
| join kind=inner Clickers on $left.InitiatingProcessAccountUpn == $right.AccountUpn
| where Timestamp between (ClickTime .. (ClickTime + 1h))
| project Timestamp, DeviceName, AccountUpn, ProcessCommandLine

The let statement names an intermediate result for reuse, join kind=inner returns every matching pair (remember the default innerunique from 1.5.2), and the time-window filter after the join keeps only PowerShell launched within an hour of the click.

Other useful operators: summarize make_set(X) collects distinct values into an array, arg_max(Timestamp, *) keeps the latest row per group, bin(Timestamp, 1h) buckets time for trend analysis, parse_json() and extend unpack the JSON in AdditionalFields, and render timechart visualizes results. The query editor helps too: the schema pane, shared queries, community queries in the query library, and, with Security Copilot, natural-language-to-KQL drafting (3.1.3).

Advanced hunting enforces limits: a query returns up to 100,000 rows and 64 MB of results (larger results are cut off and marked partial), each query times out after 10 minutes, and a tenant CPU quota resets every 15 minutes, blocking queries once it's exhausted. The fixes are the same as good practice: filter on time and specific columns early, prefer has over contains, avoid joining unfiltered tables, and summarize before returning large result sets.

From a hunting query, you can take action on results (for example, isolate devices or soft-delete emails listed in the results), link results to an incident, or select Create detection rule to turn it into a custom detection (2.5.1), provided the query returns the required columns.

⚠️ Exam Trap: A hotspot asks which operator returns only the most recent event per device. The answer is summarize arg_max(Timestamp, *) by DeviceId, not take 1 (one row total, arbitrary) and not top 1 by Timestamp (one row total, not per device).

Reflection Question: Your join between EmailEvents and DeviceProcessEvents times out. Describe three changes, in order of likely impact, that would make it run.