Skip to content

KQL for threat hunting: a primer

Kusto Query Language (KQL) is the read-only query language behind Microsoft Sentinel (Log Analytics) and Defender XDR advanced hunting. If you can read a few operators, you can read — and adapt — every query on this site. This primer covers the pipeline model and the handful of operators detections lean on. When you are ready, browse the query library.

The pipeline model

A KQL query starts with a table and pipes rows through a sequence of operators with the | character. Each operator takes the rows from the one before it and passes its output on. You read a query top to bottom, like a funnel: start broad, filter early, then shape and aggregate.

DeviceProcessEvents                         // 1. start with a table
| where Timestamp > ago(24h)                // 2. filter to a time range
| where FileName =~ "powershell.exe"        // 3. filter to the rows you care about
| summarize Runs = count() by DeviceName    // 4. aggregate
| sort by Runs desc                         // 5. order the result

The golden rule for performance and readability is the same: filter as early and as narrowly as possible, so every later operator works on fewer rows.

Operator reference

The operators and functions you will meet most often when hunting:

Operator / functionWhat it does
whereFilter rows by a condition. The single most-used operator; put the cheapest, most selective filters first.
project / project-awayChoose (or drop) columns. Narrows the result to what you need and speeds up the query.
extendAdd a calculated column, for example parsing a field out of a dynamic object.
summarizeAggregate rows into groups with functions like count(), dcount() and make_set(), grouped by one or more keys.
count / dcount / countifCount rows, distinct values, or rows matching a condition inside summarize.
make_set / make_listCollect a column's values into an array per group — useful for showing the IPs or accounts behind a count.
sort by / topOrder results, or keep the top N rows by an expression.
bin()Round a timestamp (or number) down to a bucket, e.g. bin(TimeGenerated, 1h), so you can aggregate per time window.
ago() / between()Express a time range: ago(24h) is 24 hours ago; between() bounds a window.
has / has_any / containsString search. has and has_any are term-indexed and fast; contains is a slower substring match.
=~ and in~Case-insensitive equality and set membership — safer than == for file names and values that vary in case.
matches regexMatch a column against a regular expression, for patterns like a Base64 blob after a flag.
parse_json / mv-expand / mv-applyWork with dynamic (JSON) columns: parse them, expand an array into rows, or apply a subquery per element.
join / unionCombine tables — join correlates rows by a key; union stacks rows from several tables.
ipv4_is_private()Test whether an IP is in a private (internal) range — handy for separating internal from internet traffic.

Filtering strings well

Most endpoint detections come down to matching a command line. Prefer term-indexed operators (has, has_any) for speed, use case-insensitive comparisons for file names, and reach for a regex only when you need a real pattern.

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")   // case-insensitive set match
| where ProcessCommandLine has_any ("-enc", "-EncodedCommand")  // fast term search
| where ProcessCommandLine matches regex @"[A-Za-z0-9+/=]{20,}" // Base64-looking blob

Aggregating to find outliers

Many detections are not about a single bad row but about a pattern — one source touching many targets, or a burst of failures. summarize with bin() turns raw events into per-window counts you can threshold.

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == "50126"              // failed sign-ins
| summarize
    FailedAttempts = count(),
    TargetedAccounts = dcount(UserPrincipalName)
    by IPAddress, bin(TimeGenerated, 15m)
| where TargetedAccounts >= 10             // one IP, many accounts = spray

That is the password spray detection in miniature. Every query on this site is explained the same way, line by line.

Sentinel vs. Defender XDR

The language is the same in both, but the tables and the timestamp column differ. Microsoft Sentinel (Log Analytics) tables such as SigninLogs use TimeGenerated; Defender XDR advanced-hunting tables such as DeviceProcessEvents use Timestamp. Many Defender tables are also queryable from Sentinel when the connector is enabled, so a query often ports with only a timestamp change. Each query page lists the exact tables and columns it needs.