KQL and SPL for investigations
One password-spray investigation written twice, in Kusto Query Language (Sentinel, Defender, Data Explorer) and Splunk SPL: filtering, summarize vs stats, join vs lookup, time bucketing and make-series, the performance rules that keep queries fast, and the silent mistakes that drop rows.
01Two languages, one shape
- Translate an investigation between KQL and SPL operator by operator
- Aggregate with summarize and stats without silently losing rows
- Choose between join, lookup and the stats-instead-of-join pattern
- Bucket time with bin, timechart and make-series, and spot anomalies
- Apply the performance rules both engines reward
KQL (Kusto Query Language) is the query language of Microsoft Sentinel, Defender XDR advanced hunting and Azure Data Explorer. SPL (Search Processing Language) is Splunk's. They look different but share one mental model: start from a big table of events, filter, reshape, aggregate, then filter the aggregate. Each | passes a table to the next step.
| Job | KQL | SPL |
|---|---|---|
| Pick the data | SecurityEvent (a table) | index=wineventlog sourcetype=XmlWinEventLog |
| Filter rows | where EventID == 4625 | EventCode=4625 in the base search, or | where |
| Keep columns | project / project-away | fields / table |
| New column | extend x = ... | eval x = ... |
| Aggregate | summarize count() by IpAddress | stats count BY IpAddress |
| Distinct count | dcount(TargetUserName) (approximate) | dc(TargetUserName) (exact) |
| Time bucket | bin(TimeGenerated, 1h) | bin _time span=1h |
| Time series | make-series ... step 1h | timechart span=1h |
| Enrich from a small table | lookup / watchlist | lookup asset_inventory.csv ... |
| Combine two result sets | join kind=inner | join type=inner (or better, one stats) |
KQL dcount() uses HyperLogLog and is accurate to within about 2% by default; pass an accuracy level for more. SPL dc() is exact but costs more memory on huge cardinalities (estdc() is the estimate). For "sprayed at least 10 users" the difference never matters; for billing-grade counts it does.
02Filter and aggregate: finding the spray
Scenario: overnight, the help desk gets three lockout calls. Hypothesis: a password spray (one source tries one or two common passwords against many accounts, staying under the lockout threshold). Windows records each failed logon as event 4625 on the authenticating host; SubStatus 0xC000006A means bad password for a real user, 0xC0000064 means the user does not exist.
// Sentinel: sources failing against many distinct accounts, per hour
SecurityEvent
| where TimeGenerated between (datetime(2026-11-13 00:00) .. datetime(2026-11-13 06:00))
| where EventID == 4625
| summarize failures = count(),
users = dcount(TargetUserName),
bad_pw = countif(SubStatus =~ "0xc000006a"),
no_user = countif(SubStatus =~ "0xc0000064"),
sample = make_set(TargetUserName, 5)
by IpAddress, bin(TimeGenerated, 1h)
| where users >= 10
| order by users descindex=wineventlog sourcetype=XmlWinEventLog EventCode=4625 earliest="11/13/2026:00:00:00" latest="11/13/2026:06:00:00"
| bin _time span=1h
| stats count AS failures dc(TargetUserName) AS users
count(eval(SubStatus="0xc000006a")) AS bad_pw
count(eval(SubStatus="0xc0000064")) AS no_user
values(TargetUserName) AS sample
BY IpAddress _time
| where users >= 10
| sort - usersIpAddress TimeGenerated failures users bad_pw no_user sample
198.51.100.23 2026-11-13 02:00:00 412 388 301 111 ["a.ilunga","m.okafor","svc_backup","j.chen","admin"]
198.51.100.23 2026-11-13 03:00:00 397 379 290 107 ["p.shah","k.mensah","test","r.novak","helpdesk"]
10.20.8.14 2026-11-13 04:00:00 19 11 19 0 ["svc_sql","svc_sql01","svc_sqlagent", ...]Read the result like an analyst: 198.51.100.23 tried about 390 accounts an hour with roughly one attempt each, a textbook spray, and over a quarter of the names do not exist, so it is working from a guessed list. 10.20.8.14 is internal, 19 failures across 11 similar service accounts: more likely a misconfigured app with a stale password than an attacker, but it goes in the notes as a separate lead.
SPL stats ... BY IpAddress silently drops every event where IpAddress is null, and 4625 events from some logon types have IpAddress = "-" or none at all. Use fillnull value="(none)" IpAddress first if absence matters. KQL summarize keeps empty values as their own group, which surprises people in the other direction.
03Join, lookup, and the stats-instead-of-join pattern
The question that matters is not who sprayed, but did any spray succeed. That needs successes (4624, network logon type 3 or RDP type 10) correlated with the sprayer list. Both languages offer join; both have a trap.
let window = datetime(2026-11-13 00:00) .. datetime(2026-11-13 08:00);
let sprayers = SecurityEvent
| where TimeGenerated between (window) and EventID == 4625
| summarize users = dcount(TargetUserName) by IpAddress
| where users >= 10;
SecurityEvent
| where TimeGenerated between (window)
| where EventID == 4624 and LogonType in (3, 10)
| where IpAddress in ((sprayers | project IpAddress)) // cheap semi-join filter first
| join kind=inner sprayers on IpAddress // then bring the users column across
| lookup kind=leftouter (_GetWatchlist('HighValueAssets') | project Computer = SearchKey, Owner, Tier) on Computer
| project TimeGenerated, IpAddress, TargetUserName, Computer, LogonType, users, Owner, Tierindex=wineventlog sourcetype=XmlWinEventLog earliest="11/13/2026:00:00:00" latest="11/13/2026:08:00:00"
(EventCode=4625 OR (EventCode=4624 LogonType IN (3, 10)))
| stats dc(eval(if(EventCode=4625, TargetUserName, null()))) AS failed_users
values(eval(if(EventCode=4624, TargetUserName, null()))) AS ok_users
values(eval(if(EventCode=4624, Computer, null()))) AS ok_hosts
BY IpAddress
| where failed_users >= 10 AND isnotnull(ok_users)
| mvexpand ok_hosts
| lookup asset_inventory.csv host AS ok_hosts OUTPUT owner tierThe SPL version shows the most useful Splunk habit: replace joins with a single stats over both event types. Search once for everything either side needs, tag rows with eval, then stats ... BY the join key. It scales, it never truncates, and it runs in parallel across indexers. lookup is the right tool for enrichment from a small, static table (asset owner, VIP list), and KQL lookup likewise expects a small right-hand dimension table.
| Trap | KQL | SPL |
|---|---|---|
| Default join drops rows | Default join is innerunique: it keeps one arbitrary left row per key before joining. Always write kind=inner or kind=leftouter explicitly | join defaults to max=1: only the first matching subsearch row is joined to each event |
| Size limits | Put the smaller table on the left; use hint.strategy=broadcast when the left is small and the right is huge | The joined subsearch is capped (50,000 rows and 60 seconds by default) and truncates with only a warning banner |
| Case | == is case-sensitive, =~ is not: TargetUserName == "Admin" misses admin | Field values compare case-insensitively in the base search but case-sensitively in where and eval |
Your KQL join of 4624 events (left) to a list of sprayer IPs (right) returns 3 rows, but you know one IP logged into 40 accounts. What is the most likely cause?
04Time bucketing and make-series
A table of counts per hour hides the gaps. An hour with zero failures produces no row from summarize, so averages and charts lie. make-series builds a dense array per entity with a value for every step, which is what anomaly functions need.
// Per-host failed logons as an hourly series over 14 days; flag spikes
SecurityEvent
| where TimeGenerated > ago(14d) and EventID == 4625
| make-series failures = count() default = 0
on TimeGenerated from startofday(ago(14d)) to now() step 1h
by Computer
| extend (flag, score, baseline) = series_decompose_anomalies(failures, 2.5)
| mv-expand TimeGenerated to typeof(datetime), failures to typeof(long),
flag to typeof(int), score to typeof(double), baseline to typeof(double)
| where flag == 1 and failures > 50
| project TimeGenerated, Computer, failures, baseline = round(baseline, 1), score = round(score, 1)index=wineventlog sourcetype=XmlWinEventLog EventCode=4625 earliest=-14d@d
| timechart span=1h limit=0 count BY Computer
| fillnull value=0
| untable _time Computer failures
| eventstats avg(failures) AS avg stdev(failures) AS sd BY Computer
| where failures > avg + 3 * sd AND failures > 50
| eval avg=round(avg, 1), sd=round(sd, 1)series_decompose_anomalies splits each series into seasonal (the daily login rhythm), trend and residual, and flags points whose residual is beyond the threshold. That is why it beats a flat mean-plus-three-sigma: Monday 09:00 is busy every week, and a seasonal model knows it. The SPL version is the flat version; add date_hour to the BY of eventstats for a crude hourly seasonality.
limit=0ontimechartmatters: the default keeps only the top 10 series and lumps the rest intoOTHER, which is exactly where the one odd host hides.bin()andbin _timealign buckets to fixed boundaries (00:00, 01:00), so a burst from 00:55 to 01:05 is split across two buckets. Use a smaller span or a sliding window (streamstats time_window=1hin SPL) for burst detection.- Time ranges: in KQL filter on
TimeGeneratedfirst; in SPL setearliest/latestor the time picker. Both engines use time to skip whole chunks of data before reading anything else.
"I write the hunt as filter, aggregate, then filter the aggregate. For correlation I prefer one stats over both event types to a join, and for baselines I use make-series so empty hours count as zero, then a seasonal decomposition so Monday mornings are not anomalies."
05Performance rules and common mistakes
Both engines are column stores with indexes over terms and time. Fast queries tell the engine early what it can skip.
| Rule | KQL | SPL |
|---|---|---|
| Time first | where TimeGenerated > ago(1d) as the first line | Narrow time picker or earliest=; never "All time" while exploring |
| Index-friendly matching | has (whole term, uses the term index) beats contains (substring scan) | Literal terms in the base search beat | where like(...); avoid leading wildcards like *admin |
| Name the data | Query the specific table, not search * or union * | Always set index= and sourcetype= |
| Shrink early | project the columns you need before join or mv-expand | fields early; table only at the end for display |
| Use summaries | Summary rules or materialised views for repeated aggregates | tstats over accelerated data models, e.g. the CIM Authentication model |
| Avoid the expensive verbs | Unbounded join and mv-expand on wide rows | transaction, join, append subsearches: prefer stats |
| tstats summariesonly=true count dc(Authentication.user) AS users
FROM datamodel=Authentication
WHERE Authentication.action="failure" earliest=-24h
BY Authentication.src _time span=1h
| rename "Authentication.*" AS *
| where users >= 10A query that returns fewer rows than reality is worse than one that errors. The classic culprits: innerunique joins, truncated subsearches, timechart folding entities into OTHER, null keys dropped from stats BY, case-sensitive == on user names, and time zones (Sentinel stores UTC; a Splunk user's time picker follows their profile zone).
Interviewers hand you a query and ask why it is slow or why it misses a known event. Talk through time filter placement, has vs contains, join defaults and limits, and null handling. Being able to translate a KQL hunt to SPL on a whiteboard is a common SOC and detection engineering screen.
Q1When would you still use join in SPL?+
When the two sides genuinely come from different searches that cannot be combined in one base search, and the right side is small and bounded. Even then, a lookup generated by a scheduled search (outputlookup) is usually more robust.
Q2How do you validate a hunting query?+
Plant or replay a known positive (an emulation from Atomic Red Team, or a past incident's events) and confirm it appears. Then check counts against a simpler query, for example total 4625s, so you know nothing was silently dropped.
Q3has or contains for command lines?+
has when you are looking for a whole token such as -enc or mimikatz, because it uses the term index. contains only when you need a substring inside a token, and put a has pre-filter before it if you can.
Q4Why might the same hunt return different counts in Sentinel and Splunk?+
Different parsers and field values (SubStatus casing, IpAddress "-"), different ingestion (one may drop noisy event IDs at the collector), approximate dcount vs exact dc, and time zone or boundary differences.