KQL

Kusto Query Language (KQL) is Microsoft's query language for telemetry data, used in Microsoft Defender XDR, Microsoft Sentinel, Azure Monitor, Log Analytics, and other Microsoft cloud services. KQL is read-only by design — it's optimised for fast queries over very large log datasets, with operators for filtering (where), projection (project), aggregation (summarize), joining (join), time-series analysis (make-series), and machine-learning-driven anomaly detection. SOC analysts use KQL daily for advanced hunting and detection rule authoring. Syntax is more readable than SQL for log scenarios — the typical query pipes data through a sequence of transformations. The single most useful skill for working with Microsoft's security and observability stack.

The pipe is the whole idea

Where SQL nests a query inside itself to express multiple steps, KQL reads top to bottom as a pipeline: start with a table, then pipe (|) it through one operator at a time, each one transforming what the previous one produced. where filters rows, project picks and renames columns, summarize groups and aggregates, order by sorts, take limits the result set. That linear structure is why analysts describe KQL as more readable than SQL for log-hunting scenarios — a five-line query reads like five sequential instructions, not one nested expression that has to be parsed inside-out.

Worked example

A hunt for suspicious PowerShell activity on endpoints in the last day:

DeviceProcessEvents
| where Timestamp > ago(1d)
| where FileName == "powershell.exe"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| order by Timestamp desc
| take 100

Read top to bottom: take the DeviceProcessEvents table (available in Defender XDR advanced hunting), filter to the last day with ago(1d), filter to PowerShell launches, project four useful columns, sort newest first, and return the top 100 rows. A query like this can be saved directly as a custom detection rule in Defender XDR, or the equivalent scheduled analytics rule in Sentinel, so a one-off hunt becomes a standing detection that fires automatically going forward.

Where the same skill applies across the stack

The exact same language works, with largely the same table shapes, across Defender XDR's advanced hunting, Microsoft Sentinel, Azure Monitor's Log Analytics, and Application Insights — which is why KQL fluency compounds unusually well for a Microsoft-stack analyst. Learning to filter, project, and summarise once transfers directly whether the analyst is hunting a phishing campaign in EmailEvents, investigating a sign-in anomaly in IdentityLogonEvents, or debugging application performance in Log Analytics. That shared foundation is a large part of why Sentinel's own onboarding path treats a KQL primer as a near-immediate next step after connecting the first data sources.

Common pitfalls

The most common beginner mistake is filtering on time too late in the query, or not at all — where Timestamp > ago(1h) placed early in the pipeline lets Kusto skip scanning data outside that window entirely, while the same filter placed after an expensive join or summarize forces the engine to process far more data than necessary first. The second is forgetting KQL is read-only and case-sensitive on string comparisons by default (== is case-sensitive; =~ is not) — a query that silently returns zero rows because of a casing mismatch is a common source of "the detection isn't finding anything" confusion. The third is writing a hunting query that works well as a one-off but would generate excessive noise as a scheduled detection rule — testing a query's actual hit rate over a representative time range before promoting it to an always-on rule avoids flooding an incident queue with false positives.