What is KQL?
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize Failed = count() by TargetAccount
| top 10 by FailedOne 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
| Operator | What it does | Example |
|---|---|---|
| where | Keeps the rows that match | | where EventID == 4688 |
| project | Chooses (and renames) columns | | project TimeGenerated, Account, CommandLine |
| extend | Adds a calculated column | | extend Hour = datetime_part("hour", TimeGenerated) |
| summarize | Groups 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_any | Whole-term match, fast | | where CommandLine has_any ("-enc", "-EncodedCommand") |
| contains | Substring 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 |
| join | Combines two tables | | join kind=inner (OtherTable) on DeviceId |
| let | Names a value or a subquery | let lookback = 7d; |
| top / sort | Orders the result | | top 20 by Count desc |
| render | Draws 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 descEncoded 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, ProcessCommandLineOffice 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, ProcessCommandLineRare 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 descWhere 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.
