A KQL query is a read-only request that starts with a data source, such as a table, and passes the rows through a pipeline of operators separated by | to filter, transform, aggregate and join them, for example SigninLogs | where ResultType != 0 | summarize count() by UserPrincipalName.

How to write KQL query?

Start with the table, filter by time first (where TimeGenerated > ago(1d)), then add where clauses on indexed columns, project only the columns you need, and finish with summarize or order by. Build it one pipe at a time and run it after each step to check the results.

Is KQL similar to SQL?

They solve similar problems, but KQL reads top to bottom as a pipeline rather than in SQL’s SELECT-FROM-WHERE order, and it is read-only. Many concepts map directly (where, joins, aggregation with summarize ≈ GROUP BY), and Azure Data Explorer can even translate simple SQL to KQL with the explain keyword.

How does summarize work in KQL?

summarize groups rows by the columns after “by” and computes aggregations for each group, like SQL GROUP BY: SecurityEvent | summarize count(), dcount(Account) by Computer, bin(TimeGenerated, 1h). Columns not listed in by or an aggregation are dropped.

What join kinds does Kusto support?

innerunique (the default, which deduplicates the left side), inner, leftouter, rightouter, fullouter, leftanti, rightanti, leftsemi and rightsemi. Use kind=inner for SQL-like inner joins and leftanti to find left rows with no match.

What is the difference between has and contains in KQL?

has matches whole terms using the term index, so it is fast; contains matches any substring and has to scan. Both are case-insensitive, and has_cs / contains_cs are case-sensitive. Prefer has whenever the value is a complete word or token.

How do I extract a value with regex in Loki LogQL?

Use the regexp parser with named groups, which become labels: {job="sshd"} | regexp `Failed password for (?P \S+)`. Then aggregate on the label, for example sum by (user) (count_over_time(... [5m])).

KQL Cheat Sheet & Splunk SPL Query Builder

KQL & SPL Query Builder KQL and Splunk SPL Threat Hunting Query & Security Architecture Banner

Build Microsoft Sentinel KQL and Splunk SPL queries side by side from visual filter rows, or start from one of 25 SOC hunting presets covering brute force, encoded PowerShell, LSASS access, new admin accounts and more. The Rosetta Stone table maps every common Splunk command to its KQL equivalent, which is useful when you are migrating detections or working in both SIEMs. Field names and values are never sent anywhere.

KQL query basics

Kusto Query Language (KQL) is the read-only query language behind Microsoft Sentinel, Defender XDR advanced hunting, Azure Monitor Log Analytics and Azure Data Explorer. A KQL query starts with a table and pipes rows through operators: SecurityEvent | where EventID == 4625 | summarize count() by Account.

The most useful operators are where (filter), project (choose columns), extend (computed columns), summarize (aggregate), join (combine tables), parse and extract (pull values from strings) and top / order by (sort). Always filter on TimeGenerated early; it is the cheapest filter and keeps queries inside quota.

== is case-sensitive; =~ is case-insensitive.
has matches whole terms and is much faster than contains, which matches substrings.
KQL join defaults to innerunique, which deduplicates the left side. Specify kind=inner when you need every match.

How it works: KQL & SPL Query Builder

Diagram of the query builder: visual filter rows form a query syntax tree that emits a Microsoft Sentinel KQL query and an equivalent Splunk SPL query, with a Rosetta table mapping where to search, summarize to stats and project to table.

Splunk SPL basics

The Splunk query language (SPL) starts with a search over indexed events, written as index=… sourcetype=… field=value, and pipes the results through commands: stats, eval, table, rex, where, dedup, timechart and lookup. The implicit search at the start is the fastest place to filter, because it uses the index.

Splunk regex extraction uses rex: | rex field=_raw "user=(?<user>\S+)" creates a user field. In Splunk regex, named groups use (?<name>…), and backslashes inside quotes must be escaped once more than in a raw pattern.

Translating detections between SPL and KQL

Most Splunk SPL detections translate one command at a time. stats count by user becomes summarize count() by user; eval becomes extend; table becomes project; rex becomes parse or extract(); earliest=-24h becomes where TimeGenerated > ago(24h). Field names usually differ more than syntax does. Windows logon events are EventCode in Splunk’s Windows TA but EventID in Sentinel’s SecurityEvent table.

The builder keeps one filter model and emits both dialects, so you can check that a translated detection still uses the same logic. Review the field mapping for your data sources before deploying.

Using the SOC hunting presets

Each preset is a hunting starting point, not a production analytic rule. Run it over a recent window, baseline what is normal for your environment, then add allow-lists and thresholds before alerting on it. The presets are mapped to MITRE ATT&CK techniques so you can track coverage. They are also concrete Splunk query examples and KQL query examples for training new analysts.

SPL to KQL Rosetta Stone

Equivalent commands in Splunk SPL and Microsoft Sentinel KQL.
Splunk SPL	KQL	Purpose
search / implicit	where	Filter rows
stats count by x	summarize count() by x	Aggregate
eval y=…	extend y = …	Computed column
table a, b	project a, b	Choose columns
rename a AS b	project-rename b = a	Rename a column
rex "(?<u>\S+)"	parse / extract()	Regex field extraction
dedup x	summarize arg_max(TimeGenerated, *) by x	Latest row per key
sort - count	order by count desc	Sort
head 10	take 10	Limit rows
top 10 x	top 10 by count_	Most frequent values
timechart span=1h count	summarize count() by bin(TimeGenerated, 1h)	Time series
earliest=-24h	where TimeGenerated > ago(24h)	Time range
join type=inner x	join kind=inner (…) on x	Join
stats dc(x) by y	summarize dcount(x) by y	KQL summarize: distinct count
join type=left x	join kind=leftouter (…) on x	Kusto join: keep unmatched left rows
NOT [search … \| fields x]	join kind=leftanti (…) on x	Kusto join: left rows with no match
x="text"	where x has "text" / contains	KQL contains (has is faster, whole terms)
regex x="pat"	where x matches regex @"pat"	KQL regex filter
rex field=x "(?<u>\S+)"	extend u = extract(@"(\S+)", 1, x)	KQL regex extract
(Loki) \| regexp `(?P<u>\S+)`	parse / extract()	LogQL regex extract → label
lookup file.csv x	join (_GetWatchlist("x")) on x	Enrichment

Equivalent commands in Splunk SPL and Microsoft Sentinel KQL.

Command-line examples

Run KQL from the Azure CLI

az monitor log-analytics query -w $WORKSPACE_ID --analytics-query "SecurityEvent | where EventID == 4625 | take 10"

Run SPL from the Splunk CLI

splunk search "index=wineventlog EventCode=4625 | stats count by Account_Name" -earliest_time -24h

Run SPL over the REST API

curl -k -u admin https://splunk:8089/services/search/jobs/export -d search="search index=main | head 5" -d output_mode=json

Frequently asked questions

KQL (Kusto Query Language) is the language security analysts use to hunt and write detections in Microsoft Sentinel and Microsoft Defender XDR. It queries security logs such as sign-ins, process creation, network connections and alerts at scale.

Report an issue or send feedback