KQL

KQL#

KQL, the Kusto Query Language, is Microsoft’s query DSL for Azure Data Explorer, Azure Sentinel (security analytics), Microsoft 365 Defender, Application Insights, and the broader Microsoft observability stack. Pipeline-shaped, much closer in feel to jq or shell pipes than to SQL.

In 2026, KQL is the query language type into every Microsoft-stack security tool: Sentinel rules, Defender hunts, ADX analytics, Log Analytics queries.

Pipeline Form#

Every KQL query reads as: “start with a table, then transform”.

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625              // Failed logon
| summarize FailedAttempts = count() by Account, Computer
| where FailedAttempts > 10
| sort by FailedAttempts desc
| take 50

Core Operators#

The verbs that show up in nearly every KQL query. Filtering, projecting, computed columns, summarization with grouping, sorting, top-N, joining, parsing, and time-series shaping – each is a single pipeline stage. Worth memorizing the table below; most queries draw from these dozen-plus operators.

Operator	Action
`where`	filter rows
`project`	select / rename columns (like SQL `SELECT`)
`project-away` / `-keep`	drop / keep columns
`extend`	add computed columns
`summarize`	aggregate (with optional `by`)
`sort by` / `order by`	sort
`top N by`	first N
`take N` / `limit N`	first N (no order guarantee)
`distinct`	unique values
`join`	join with another table
`union`	combine tables
`parse`	extract fields with patterns
`parse_json`	parse JSON strings
`mv-expand`	expand multi-value columns into rows
`make-series`	time-bucketed aggregation

Time Functions#

KQL is time-aware.

SecurityEvent
| where TimeGenerated > ago(7d)
| summarize count() by bin(TimeGenerated, 1h)
| render timechart

Common time helpers: ago(), now(), startofday(), startofweek(), datetime_diff(), bin().

Joins#

SigninLogs
| where ResultType != 0
| join kind=inner (
    AuditLogs
    | where OperationName == "Update user"
) on $left.UserId == $right.UserId
| project TimeGenerated, UserPrincipalName, Operation = OperationName

Aggregations and Buckets#

AzureDiagnostics
| where TimeGenerated > ago(24h)
| summarize
    Requests = count(),
    Errors   = countif(httpStatus_d >= 500),
    p95      = percentile(duration_d, 95)
  by bin(TimeGenerated, 5m), Resource
| extend ErrorRate = todouble(Errors) / Requests

JSON / Dynamic Columns#

KQL has first-class support for JSON-shaped data.

SigninLogs
| extend Device = todynamic(DeviceDetail)
| extend OS = Device.operatingSystem, Browser = Device.browser
| summarize count() by OS, Browser

parse_json / todynamic parse strings; Device.x.y accesses nested fields.

Threat Hunting Patterns#

The kinds of detection that hunt teams reach for in KQL. Baseline-relative anomalies, beaconing detection from network event regularity, and ATT&CK-tagged findings are the core patterns; each is a few lines of KQL once the table schema is in head.

Anomaly via baseline:

let baseline = SigninLogs
  | where TimeGenerated between (ago(30d) .. ago(1d))
  | summarize avg(toint(ResultType > 0)) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(1d)
| summarize today_failures = countif(ResultType > 0) by UserPrincipalName
| join baseline on UserPrincipalName
| where today_failures > 3 * avg_

Beaconing detection:

DeviceNetworkEvents
| where TimeGenerated > ago(24h)
| summarize beacons = count(), distinct_intervals = dcount(bin(TimeGenerated, 1m))
    by DeviceName, RemoteIP
| where beacons > 100 and distinct_intervals > 50

ATT&CK mapping, many Sentinel rules tag findings with ATT&CK techniques in the rule metadata.

Functions#

KQL has user-defined functions.

let suspicious_processes = (lookback:timespan = 24h) {
  DeviceProcessEvents
  | where TimeGenerated > ago(lookback)
  | where ProcessCommandLine has_any ("powershell", "cmd.exe", "wscript")
  | where ProcessCommandLine has "base64"
};

suspicious_processes(48h)
| join DeviceLogonEvents on DeviceName, AccountName

Where KQL Runs#

KQL is one language across many Microsoft surfaces. Azure Data Explorer, Sentinel, Microsoft 365 Defender, Application Insights, Log Analytics, and Azure Resource Graph all share the dialect with surface-specific table schemas. Skill in one transfers cleanly to the rest.

Azure Data Explorer (Kusto), the original; analytics over big data.
Azure Sentinel, KQL is the rule and hunting language.
Microsoft 365 Defender, advanced hunting in the security portal.
Application Insights / Log Analytics, application performance monitoring.
Azure Resource Graph, query Azure resources at scale.

Each surface has the same KQL language with slightly different table schemas.

Tooling#

Kusto Explorer, desktop client.
Azure Data Explorer Web UI.
VS Code “Kusto” extension.
kqlmagic for Jupyter.
lokql, run KQL against local CSV / Parquet (community).

Pitfalls#

The traps that catch operators new to KQL. Case sensitivity of columns versus operators, UTC time zones versus rendered display, the performance spread across string-match operators, implicit type coercion, and runaway union * queries all catch teams the first time around.

Case sensitivity, column names are case-sensitive; operators like has are case-insensitive but contains is not.
Time zones, TimeGenerated is UTC; render in local where humans read.
String contains operators, has, has_any, has_cs, contains, contains_cs, startswith, matches regex. Performance varies dramatically; has is fastest where applicable.
Datatype coercion, toint, tolong, todouble are often necessary; failed parses become null.
Cost, in Sentinel, union * can be very expensive; scope to specific tables.

KQL vs. SQL#

Pipeline-shaped, KQL reads top-to-bottom; SQL reads inside-out.
Time series first-class in KQL.
JSON / dynamic types built in to KQL.
Less mature join performance than mature SQL engines.

For Microsoft-stack security work, KQL is the standard. For tabular analytics on warehouses, SQL still rules.

KQL

Contents