KQL for security analysts: where, project, summarize, and extend in Microsoft Sentinel

How KQL reads

A KQL query starts with a table name and pipes rows through operators. where filters, project picks columns, extend adds computed columns, summarize aggregates, order by or sort sorts, and take or top limits rows. String operators include has (fast, term-based), contains (substring), startswith, and matches regex. == is case-sensitive; =~ is case-insensitive.

Time filters go first: where TimeGenerated > ago(1d) is the most important line in nearly every query.

Security examples

The first query lists process creations of PowerShell with encoded commands from Microsoft Defender for Endpoint data. The second counts failed Windows logons per account and source.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ "powershell.exe" and ProcessCommandLine has_any ("-enc", "-EncodedCommand")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName
| order by Timestamp desc

SecurityEvent
| where TimeGenerated > ago(1d) and EventID == 4625
| summarize Failures = count(), Accounts = dcount(TargetAccount) by IpAddress, bin(TimeGenerated, 1h)
| where Failures > 25

Lab: use the free demo environment

Microsoft provides a free Log Analytics demo workspace and the Azure Data Explorer help cluster with sample data such as the StormEvents table, which is useful for learning syntax. If you have a Sentinel trial, connect the Entra ID sign-in logs. Write three queries: sign-ins by result code over the last day, the top 10 applications by sign-in count, and users who signed in from more than three countries in 24 hours.

SigninLogs
| where TimeGenerated > ago(1d)
| summarize Countries = dcount(tostring(LocationDetails.countryOrRegion)), List = make_set(tostring(LocationDetails.countryOrRegion)) by UserPrincipalName
| where Countries > 3

Good habits

Prefer has over contains for speed on whole terms. Project only the columns you need before joins. Use let statements to name thresholds and lists at the top of a query so tuning is easy. Save working queries as functions or analytics rules once they are stable.