KQL Cheat Sheet for Threat Hunting

The KQL operators you use every day in Microsoft Sentinel and Defender, explained with one line each, and a set of hunting queries you can adapt: brute force, encoded PowerShell, Office spawning shells and rare connections.

Detection10 min readPublished 16 November 2026
FR

Francisco RamosGREM

Founder · Malware Analyst · Lead Instructor

What is KQL?

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize Failed = count() by TargetAccount
| top 10 by Failed
Read it top to bottom: take the table, filter, group, sort.

One detail that trips people up: the time column has a different name depending on the product. Sentinel tables use TimeGenerated; Defender advanced hunting tables (DeviceProcessEvents and the rest) use Timestamp.

The operators you will use every day

OperatorWhat it doesExample
whereKeeps the rows that match| where EventID == 4688
projectChooses (and renames) columns| project TimeGenerated, Account, CommandLine
extendAdds a calculated column| extend Hour = datetime_part("hour", TimeGenerated)
summarizeGroups and aggregates| summarize count() by Computer
bin()Rounds time into buckets| summarize count() by bin(TimeGenerated, 1h)
ago()Relative time| where TimeGenerated > ago(7d)
has / has_anyWhole-term match, fast| where CommandLine has_any ("-enc", "-EncodedCommand")
containsSubstring match, slower| where Url contains "login"
in~In a list, case-insensitive| where FileName in~ ("cmd.exe", "powershell.exe")
dcount()Distinct count| summarize Hosts = dcount(Computer) by Account
joinCombines two tables| join kind=inner (OtherTable) on DeviceId
letNames a value or a subquerylet lookback = 7d;
top / sortOrders the result| top 20 by Count desc
renderDraws a chart| render timechart

Prefer has over contains whenever you are looking for a whole word: it uses the index and is much faster on large tables.

Hunting queries you can adapt

Brute force: many failed logons from one IP

SecurityEvent
| where TimeGenerated > ago(1d) and EventID == 4625
| summarize Failed = count(), Accounts = dcount(TargetAccount) by IpAddress, bin(TimeGenerated, 10m)
| where Failed > 20
| sort by Failed desc
Many accounts from one IP looks like password spraying; one account, like guessing its password.

Encoded PowerShell

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine has_any ("-enc", "-EncodedCommand", "FromBase64String")
| project Timestamp, DeviceName, AccountName, InitiatingProcessFileName, ProcessCommandLine

Office applications starting a shell

DeviceProcessEvents
| where Timestamp > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "powerpnt.exe", "outlook.exe")
| where FileName in~ ("cmd.exe", "powershell.exe", "wscript.exe", "cscript.exe", "mshta.exe", "rundll32.exe")
| project Timestamp, DeviceName, InitiatingProcessFileName, FileName, ProcessCommandLine
A classic sign of a malicious document with macros.

Rare destinations: IPs that only one machine talks to

DeviceNetworkEvents
| where Timestamp > ago(7d) and RemoteIPType == "Public"
| summarize Devices = dcount(DeviceName), Connections = count() by RemoteIP
| where Devices == 1 and Connections > 50
| sort by Connections desc
Many connections from a single machine to an IP no one else uses can be a beacon.

Where to practise KQL

Microsoft offers a free demo environment for Azure Data Explorer and the Log Analytics demo workspace, both good for learning the syntax. To practise hunting with security data, the KQL threat hunting course runs the queries against a prepared dataset of an intrusion, so every result means something.

Continue learning

Courses related to this topic

From article to practice, with nothing to install

What you just read is learned by doing it. Here is what it looks like inside.

The SOC simulator, inside

This is the simulator from the courses, driven by a script: you can pause it and step through it. With a free account you solve it yourself.

A guided lab, step by step

hacktheknowledge.com/app/labs/ssh-brute-force

Example from the SOC track: the machine is real and the flag is checked when you submit it.