For Microsoft Defender and Microsoft Sentinel

KQL cheat sheet

The KQL you use most in Microsoft Defender advanced hunting and Microsoft Sentinel, on one page. The examples use Defender tables. In Sentinel, swap Timestamp for TimeGenerated.

Checked against Microsoft’s KQL documentation in November 2026.

To do this	Write this
Start from a table	`DeviceProcessEvents`
Last 24 hoursSentinel tables use `TimeGenerated`.	`\| where Timestamp > ago(24h)`
Between two times	`\| where Timestamp between (datetime(2026-10-01) .. datetime(2026-10-02))`
Exact match, case matters	`\| where FileName == "cmd.exe"`
Exact match, ignore case	`\| where FileName =~ "cmd.exe"`
Not equal, ignore case	`\| where InitiatingProcessFileName !~ "explorer.exe"`
One of several, ignore case`in` without the `~` is case-sensitive.	`\| where FileName in~ ("cmd.exe", "powershell.exe")`
Contains a whole wordFastest text search. Ignores case.	`\| where ProcessCommandLine has "EncodedCommand"`
Contains any of several words	`\| where ProcessCommandLine has_any ("DownloadString", "DownloadFile")`
Contains text anywhere, even mid-wordSlower than `has`. Ignores case.	`\| where ProcessCommandLine contains "http"`
Starts with	`\| where FolderPath startswith @"C:\Users\"`
Matches a regexCase-sensitive.	`\| where ProcessCommandLine matches regex @"[A-Za-z0-9+/]{100,}"`
Has a value	`\| where isnotempty(RemoteUrl)`

Choose columns

To do this	Write this
Keep only these columns	`\| project Timestamp, DeviceName, ProcessCommandLine`
Drop a column	`\| project-away ReportId`
Rename a column	`\| project-rename Host = DeviceName`
Add a calculated column	`\| extend Exe = tolower(FileName)`

Count, sort and group

To do this	Write this
Count all rows	`\| count`
Count by a columnThe new column is called `count_`.	`\| summarize count() by DeviceName`
Count, with your own column name	`\| summarize Runs = count() by DeviceName`
Count distinct values`dcount()` is an estimate. It can be slightly off on large data.	`\| summarize Devices = dcount(DeviceName) by FileName`
Count per hour	`\| summarize count() by bin(Timestamp, 1h)`
First and last seen	`\| summarize FirstSeen = min(Timestamp), LastSeen = max(Timestamp) by DeviceName`
Latest row for each device	`\| summarize arg_max(Timestamp, *) by DeviceName`
List the values in each group	`\| summarize Files = make_set(FileName) by DeviceName`
Unique combinations	`\| distinct DeviceName, AccountName`
SortWith no `asc` or `desc`, `sort` is descending.	`\| sort by Timestamp desc`
Top 10 by a column	`\| top 10 by Runs`
Any 10 rows, to look at the dataNo order. Use `top` when order matters.	`\| take 10`

Combine tables

To do this	Write this
Join another tableAlways set `kind`. The default drops duplicate keys on the left.	`\| join kind=inner (DeviceNetworkEvents) on DeviceId`
Join on columns with different names	`\| join kind=inner (DeviceNetworkEvents) on $left.ProcessId == $right.InitiatingProcessId`
Keep rows with no match on the right	`\| join kind=leftanti (DeviceNetworkEvents) on DeviceId`
Stack two tables	`union DeviceProcessEvents, DeviceFileEvents`
Reuse a list of values	`let Shells = dynamic(["cmd.exe", "powershell.exe"]); DeviceProcessEvents \| where FileName in~ (Shells)`

Read messy fields

To do this	Write this
Read a JSON column	`\| extend Data = parse_json(AdditionalFields)`
Take one value out of JSON	`\| extend Value = tostring(Data.SomeKey)`
Pull text out with a regex`0` returns the whole match, `1` the first group.	`\| extend Url = extract(@"https?://\S+", 0, ProcessCommandLine)`
One row per array item	`\| mv-expand Data`
Split text	`\| extend Domain = tostring(split(AccountUpn, "@")[1])`

Watch out for

== is case-sensitive and =~ isn’t. Outlook.EXE and outlook.exe are different to ==.
has matches whole words, so has "Explorer" won’t find KustoExplorerQueryRun. Use contains for part of a word.
A join with no kind is innerunique, which keeps only one left row per key. Write kind=inner when you want every match.
take returns any rows, in no order. Use top or sort when order matters.
Advanced hunting covers up to 30 days of Defender data, and all times are in UTC.
Table, column and function names are case-sensitive. deviceprocessevents won’t work.

Official docs and cheat sheets

Microsoft publishes its own quick reference and two cheat sheets for people coming from SQL and Splunk.

Keep going

Practise these in real exercises

A cheat sheet tells you the syntax. SOC Fluent gives you exercises to solve with it, on realistic lab data, and shows you what your query missed. Join the waitlist and we’ll email you once, on launch day.

Join the waitlist See pricing

Free to join. Students get 50% off at launch.