H3AD-REF / CHEATSHEETS / KQL XQL SPL SYNTAX

KQL. XQL. SPL.
Same Query, Three Accents.

A side-by-side reference for the query you run every shift, translated across Kusto (Microsoft Sentinel / Defender), XQL (Cortex XDR / XSIAM), and SPL (Splunk): filtering, projection, aggregation, joins, and sorting the same event log three different ways. No query runner here, just the syntax and the default behaviors that trip an analyst switching platforms mid-shift. For ready-to-run queries in these same languages, see TRACERULES.

Query Structure

Every query starts by naming a data source, then narrows it. The three languages disagree hard on how explicit that first step has to be.

Filter Process-Creation Events For powershell.exe

KQL — Microsoft Sentinel / Defender
DeviceProcessEvents
| where FileName == "powershell.exe"
// table name first, then pipe — the query only ever narrows from that starting table
XQL — Cortex XDR / XSIAM
dataset = xdr_data
| filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
// dataset is its own stage and always comes first — xdr_data is the default correlated dataset
SPL — Splunk
index=main sourcetype=sysmon EventCode=1 Image="*\powershell.exe"
// no explicit "search" keyword needed — SPL assumes search as the implied first pipeline stage

Field Selection

Same job, different verb — and in Splunk, two verbs that look interchangeable but aren't.

Keep Only The Fields That Matter

KQL
DeviceProcessEvents
| where FileName == "powershell.exe"
| project TimeGenerated, DeviceName, FileName, ProcessCommandLine
// project also reorders and can rename inline: project NewName = OldName
XQL
dataset = xdr_data
| filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
| fields agent_hostname, action_process_image_name, action_process_image_command_line
// fields narrows the result set the same way project does — supports "as" for aliasing
SPL
index=main sourcetype=sysmon EventCode=1 Image="*\powershell.exe"
| table _time, host, Image, CommandLine
// table formats output for display; fields keeps/drops columns without reformatting —
// the two commands are not interchangeable, table is heavier and meant for the final stage

Filtering & Comparison

The highest-value section on this page — operator names diverge the most here, and the case-sensitivity defaults diverge in the direction most analysts don't expect. See the gotchas section below for the specifics.

Equals, Substring, Regex, IN, Time Range

KQL
| where FileName == "powershell.exe"              // case-sensitive equals
| where FileName =~ "PowerShell.exe"              // case-insensitive equals
| where ProcessCommandLine contains "-enc"       // case-insensitive substring
| where ProcessCommandLine matches regex @"-[Ee]nc\w*"
| where FileName in ("powershell.exe","pwsh.exe") // case-sensitive IN
| where TimeGenerated > ago(24h)                 // relative time range, must be explicit
XQL
filter action_process_image_name = "powershell.exe"
filter action_process_image_command_line contains "-enc"
filter action_process_image_command_line ~= "-[Ee]nc\w*"   // ~= is the regex-match operator
filter action_process_image_name in ("powershell.exe","pwsh.exe")
SPL
Image="powershell.exe"                       // equals; wildcards work directly: Image="*\powershell.exe"
| where match(CommandLine, "-[Ee]nc\\w*")   // regex via the match() eval function
| regex CommandLine="-[Ee]nc\\w*"          // regex via the dedicated regex command
| where Image IN ("powershell.exe", "pwsh.exe")
earliest=-24h latest=now                     // time range as search-time modifiers, not a piped filter

Time-range handling is the one place all three diverge structurally, not just syntactically: KQL needs an explicit ago() comparison inside where or the query is unbounded; SPL's earliest/latest prune the index before the pipeline runs at all; XQL doesn't time-box inline at all — there's no ago()-style clause in the query body. It's set through a separate time_frame argument passed alongside the query (via the XSOAR integration, API call, or the UI's time picker), accepting values like 1 day, 3 weeks ago, or an explicit between <start> and <end> range — defaulting to the last 24 hours if omitted.

Aggregation & Grouping

Count events per value of a field — the one operation every SIEM makes fast, worded three different ways.

Count Matching Events, Grouped By Host

KQL
DeviceProcessEvents
| where FileName == "powershell.exe"
| summarize Count = count() by DeviceName
XQL
dataset = xdr_data
| filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
| comp count() as event_count by agent_hostname
SPL
index=main sourcetype=sysmon EventCode=1 Image="*\powershell.exe"
| stats count by host

Joins

One example each — enough to recognize the shape when it shows up in a real query, not a full join reference.

Correlate The Process Event With A Network Event

KQL
DeviceProcessEvents
| where FileName == "powershell.exe"
| join kind=inner (DeviceNetworkEvents) on DeviceId
// kind must be stated explicitly — see the gotchas section for why that matters
XQL
dataset = xdr_data | filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
| join type = inner ((dataset = xdr_data | filter event_type = ENUM.NETWORK) as net_evt agent_id = net_evt.agent_id)
// the right side is itself a full XQL query, aliased with "as" and matched on a boolean expression
SPL
index=main sourcetype=sysmon EventCode=1 Image="*\powershell.exe"
| join type=inner host [search index=main sourcetype=sysmon EventCode=3]
// the bracketed clause is a subsearch — join runs it independently, then stitches results
// together on the shared field named before the bracket

Sorting & Limiting

Newest first, top N only — the pattern that closes almost every hunting query.

Newest 10 Matching Events

KQL
DeviceProcessEvents
| where FileName == "powershell.exe"
| sort by TimeGenerated desc
| take 10
// top 10 by TimeGenerated desc does the sort and the limit in a single operator
XQL
dataset = xdr_data | filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
| sort desc _time
| limit 10
// limit caps the result set — if omitted, XQL defaults to a very high ceiling, not "all"
SPL
index=main sourcetype=sysmon EventCode=1 Image="*\powershell.exe"
| sort -_time
| head 10
// the leading "-" means descending; sort with no sign defaults to ascending

Cross-Language Gotchas

Defaults that read fine, pass a quick test, and then behave differently than the analyst expected the first time it actually matters.

PITFALL

KQL's Case-Sensitivity Runs Backwards

The operator that looks strict (==) is the case-sensitive one — most string operators aren't
== and in are case-sensitive by default in KQL. contains, has, startswith, endswith and their relatives are case-insensitive by default, and only become case-sensitive with the _cs suffix (contains_cs, has_cs...). An analyst assuming the reverse gets a query that silently under-matches instead of erroring.
PITFALL

KQL Join Defaults To innerunique, Not inner

Omitting kind= doesn't fall back to a standard SQL-style inner join
If kind is left off entirely, KQL's join operator defaults to innerunique, which deduplicates the left table down to one row per join key before matching — not the inner join most analysts expect coming from SQL. Rows that should have matched can silently disappear. Write kind=inner explicitly whenever that's the behavior actually wanted.
PITFALL

SPL's Case Sensitivity Splits By Command

The same value can match case-insensitively in one stage and case-sensitively two lines later
search matches field values case-insensitively, but stats and sort treat those same values as case-sensitive — two events differing only in case can land in separate stats groups even though the search stage that found them treated them as identical. Field names themselves are always case-sensitive, in every command, with no exceptions.
PITFALL

Implicit Search vs Explicit Source

SPL assumes a starting point; KQL and XQL both require naming one
SPL treats the start of a pipeline as an implied search command, so index=main sourcetype=x field=y works with no keyword at all, and earliest/latest prune the index before the pipeline runs. KQL and XQL both require an explicit source first — a table name, or dataset = — but they diverge on where time-boxing lives: KQL needs an explicit where ... > ago() clause in the query body or the result set is unbounded by design. XQL has no equivalent inline clause at all — time-boxing is a separate time_frame argument passed alongside the query (default: last 24 hours), not something you write inside the XQL itself. Forgetting this isn't a syntax error in XQL the way an unbounded KQL query technically "works" — it just means the time range came from wherever that argument defaulted to, not from anything visible in the query text.
← Back to H3AD-REF