KQL vs SPL: query languages compared for SOC investigations
KQL (Sentinel, Defender, Data Explorer) vs SPL (Splunk): data model, syntax, performance and a side by side cheat sheet for analysts.
Both are pipe-based languages that do the same job, so the choice follows your SIEM, not the other way round: KQL if you run Microsoft Sentinel, Defender XDR or Azure Data Explorer, SPL if you run Splunk. KQL queries typed tables with a fixed schema, so it is stricter and usually faster to write correctly. SPL extracts fields at search time, so it is more forgiving with messy data but punishes you for searching without an index and time range. Learn the concepts once (filter early, aggregate, pivot) and the syntax maps almost one to one.
| KQL | SPL | |
|---|---|---|
| Where it runs | Sentinel, Defender XDR, Log Analytics, Azure Data Explorer | Splunk Enterprise and Splunk Cloud |
| Data model | Typed tables, schema on ingest | Events in indexes, fields extracted at search time |
| First line | Table name: SecurityEvent | Implicit search: index=wineventlog EventCode=4625 |
| Filter | where | search terms, then | search or | where |
| Aggregate | summarize count() by x | stats count by x |
| New field | extend | eval |
| Time buckets | bin(TimeGenerated, 1h), make-series | bin _time span=1h, timechart |
| String match | has (term, fast), contains (substring), =~ (case-insensitive) | Search terms case-insensitive, eval == case-sensitive |
| Regex extraction | parse, extract() | rex |
| Joins | join kind=..., lookup; default kind is innerunique | join type=...; subsearch capped (50,000 rows by default) |
| Reuse | let statements, functions | Macros, saved searches, eventtypes |
| Speed lever | Time filter first, has over contains | index, sourcetype and time first; tstats on data models |
01How each one works
KQL (Kusto Query Language) runs against tables that already have a schema. SecurityEvent, SigninLogs and DeviceProcessEvents each have typed columns, so a typo in a column name fails at parse time rather than returning nothing. A query starts with a table and flows data through operators separated by pipes.
SPL (Search Processing Language) starts with an implicit search command over raw events in one or more indexes. Fields such as user or src are extracted at search time by props and transforms, usually shipped in a Technology Add-on and mapped to the Common Information Model (CIM). The same pipe model follows: search, then transforming commands like stats and timechart.
In KQL the schema tells you what fields exist. In SPL the field names depend on which add-on parsed the data, so the first step on an unfamiliar sourcetype is often | fieldsummary or checking the CIM data model.
02The same hunt in both languages
Task: failed Windows logons (event 4625) in the last 24 hours, bucketed per hour, showing accounts with more than 20 failures and how many source IPs they came from.
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize Failures = count(), SourceIPs = dcount(IpAddress)
by TargetAccount, bin(TimeGenerated, 1h)
| where Failures > 20
| order by Failures descindex=wineventlog EventCode=4625 earliest=-24h
| bin _time span=1h
| stats count AS failures dc(src_ip) AS source_ips BY user _time
| where failures > 20
| sort - failuresCheat sheet
| Task | KQL | SPL |
|---|---|---|
| Filter rows | where Field == "x" | Field="x" or | where Field="x" |
| Pick columns | project a, b | table a b or fields a b |
| Compute a field | extend d = a * 2 | eval d = a * 2 |
| Group and count | summarize count() by a | stats count by a |
| Distinct count | dcount(a) | dc(a) |
| Time chart | make-series count() on TimeGenerated step 1h | timechart span=1h count |
| Top values | top 10 by count_ (after summarize) | top limit=10 a |
| Regex extract | parse or extract(@"re", 1, Field) | rex field=Field "(?<name>re)" |
| Enrich from list | lookup kind=leftouter Watchlist on a | lookup mylookup a OUTPUT b |
| Sort | order by a desc (sort by is an alias) | sort - a |
03When to pick which
- You rarely pick the language. You pick the SIEM, and the language follows. Optimise for the platform your SOC already pays for.
- If you write detections for both, author them in Sigma and convert, then hand-tune the output. Converted queries are a starting point, not a finished rule.
- KQL suits teams that want strict schemas, Microsoft-native telemetry and detection rules versioned as code against stable table names.
- SPL suits heterogeneous estates with many custom log sources, where search-time extraction lets you onboard data before you have perfected the parsing.
- For speed in Splunk, accelerated data models and tstats beat raw searches by orders of magnitude on large ranges. In KQL, the equivalent wins come from filtering on TimeGenerated first and using has instead of contains.
04Common mistakes when moving between them
- Assuming KQL join behaves like SQL. The default kind is innerunique, which deduplicates the left side. Write kind=inner explicitly when you mean it.
- Using contains everywhere in KQL. has uses the term index and is much faster; contains forces a substring scan.
- Forgetting that KQL == is case-sensitive. Use =~ for case-insensitive equality, or has, which is case-insensitive by default.
- Running SPL without index= and a time range. It works, it is just slow and expensive, and it may search indexes you did not mean to.
- Treating SPL dedup as KQL distinct. dedup keeps the first whole event per value; distinct returns unique combinations of the columns you name.
- Relying on SPL join for large datasets. The subsearch is truncated silently at its limit; stats by a shared field is usually the better join.
Splunk subsearches and joins stop at their configured result and time limits without failing the search. A hunt that returns nothing may simply have been cut short. Check the job inspector.
05How to say it in an interview
"The concepts are identical: filter early on time and source, aggregate, then pivot. KQL queries typed tables so the schema guides me, SPL extracts fields at search time so I check the add-on's field names first. I can translate between them, and for rules that have to live in both I write Sigma and tune the converted output."
Questions people ask
Q1Is KQL easier to learn than SPL?+
Most analysts find KQL quicker to get right because the schema is fixed and the editor autocompletes columns. SPL is quicker to start with on raw data. Both take a few weeks of daily use to become fluent.
Q2Can I convert SPL queries to KQL automatically?+
Partly. Sigma converters and Microsoft's SIEM migration tooling translate common patterns, but field names, joins and macros need manual review. Always test the converted query against known-bad data.
Q3Is KQL the same as SQL?+
No. KQL is read-only and pipe-based, closer in spirit to SPL or a Unix pipeline. Azure Data Explorer accepts a subset of T-SQL, but detections and hunting are written in KQL.
Q4What is the KQL equivalent of Splunk stats?+
summarize. stats count by user becomes summarize count() by user, and dc(x) becomes dcount(x).
Q5What is the KQL equivalent of Splunk rex?+
parse for simple patterns and extract() or parse-where for regex. Both return the captured groups as new columns.