skip to content

In Splunk SPL, why do detection engineers rewrite a join between two indexes as stats?

level: middleimportance: should knowfreq 58%

answer

  1. the subsearch runs first, alone
  2. capped, and truncated without an error
  3. one pass over both sources instead
  4. aggregate by the shared entity key
  5. KQL join defaults to innerunique

basics

~20 s

Because join runs a subsearch that is capped and truncated without an error, so the correlation can silently omit the account you are hunting. Reading both sources in one search and aggregating with stats by the shared key is complete.

solid answer

~50 s

`join` in SPL is backed by a subsearch: the right-hand search runs first, its results are held, and only then is the outer search matched against them. That subsearch has result-count and runtime limits, and when it hits one it is **truncated silently** — no error, just fewer rows — so a contractor account can drop off the right-hand list and the correlation reports nothing. It also concentrates work on the search head instead of spreading it across indexers. The idiom is to select both sources in one base search with an `OR`, then `stats` by the shared key and keep the rows that carry evidence from both sides. Same intent, one pass, no cap. The equivalent trap in KQL is that the default `join` flavour is `innerunique`, which deduplicates left-hand rows by join key before matching — another silent row loss.

code

text · 15 lines
text
# join: the contractor list is a subsearch, run first and capped
index=db_audit statement_type=SELECT object=customers earliest=-24h
| join type=inner user
    [ search index=identity_hr employment_type=contractor | fields user ]
| stats count as stmts by user, database

# stats: both sources selected once, aggregated by the shared key
(index=db_audit statement_type=SELECT object=customers)
  OR (index=identity_hr employment_type=contractor)
  earliest=-24h
| stats count(eval(statement_type="SELECT")) as stmts,
        values(employment_type) as emp,
        dc(client_ip) as src_ips
        by user
| search emp=contractor stmts>500

go deeper

for a junior

Know that join in SPL is backed by a subsearch that runs first and has limits, and that the usual alternative is to read both sources in one search and aggregate with stats by a shared field such as the user.

for a middle

Explain the execution order and the failure mode: the subsearch is materialised on the search head, capped, and truncated without an error, while stats can be computed across indexers in one pass.

for a senior

Demonstrate that you rewrite correlations before scheduling them, verify the rewrite by comparing entity counts, and can name the equivalent trap in another language, such as KQL's innerunique default.

for a principal

Own the guidance that scheduled correlations avoid subsearch-backed joins, and be ready to justify it in terms of missed detections rather than query cost alone.

## What join actually does When you write `... | join type=inner user [ search ... ]`, Splunk does not stream two sources together. The bracketed **subsearch** is executed first, on the search head; its output is materialised and then used to match against the outer result set. That execution order has three consequences that matter for a detection. **It is capped.** Subsearches used by `join` have configured limits on the number of rows they may return and how long they may run. Both are set in configuration and both can be raised, but the default is finite, and it is finite specifically because the results have to be held in memory on one node. **Truncation is silent.** When the subsearch hits its cap, the search does not fail. It carries on with a shorter list. The outer search then joins against a partial right-hand side and produces fewer rows than the data supports. Nothing in the result table distinguishes that from a genuine no-match. **It does not distribute well.** `stats` can be computed in map-reduce fashion, with partial aggregates produced at the indexers and combined at the search head. A join concentrates the work in one place, which is why the same correlation costs far more when it is run every fifteen minutes as a scheduled search. ## Why the security consequence is not merely cost Suppose the question is which accounts read the customer table heavily, restricted to accounts the HR feed marks as contractors. Written as a join, the contractor list is the subsearch. If that list is truncated, the account you needed may simply not be in it, and the search returns an empty or short table. The analyst reads *no contractors were bulk-reading customer data*, when the true statement is *the search compared against part of the contractor list*. That is the same direction-of-claim error as reading an unfinished search as a clean result, and it is worse in a scheduled rule, because nobody re-reads a rule that produced no alerts. ## The stats idiom Select both sources in a single base search and aggregate by the key they share: - put both source selections in the base search joined by `OR`, so both are read once and in parallel across indexers; - `stats` by the shared entity (`user`), using `count(eval(...))` to count only the events of one kind and `values(...)` to carry across the attribute that came from the other source; - filter afterwards for rows that carry evidence from both sides, which is what `type=inner` was expressing. The result is one row per entity, computed in one pass, with no intermediate result set to overflow. As a bonus it is the shape a triaging analyst wants anyway: one row per user with the evidence fields attached, rather than a raw event list. ## The same idea in other query languages In Kusto (Sentinel), the analogue is `union` followed by `summarize`. The specific trap to know is that `join` without an explicit `kind` defaults to **innerunique**, which deduplicates the left table by join key before matching — so a left table with many rows per user silently collapses. Stating `kind=inner` or `kind=leftouter` explicitly is the habit; if the right side is a small dimension table, the `lookup` operator is the better tool than any join. In Elastic EQL, cross-event correlation is not expressed as a join at all: a `sequence by <field>` states the relationship between events directly in the language, which is why EQL reads more naturally for behaviour that spans several records. ## When join is still acceptable Interactive hunting, where a human is looking at the output and can notice something is off, and the right-hand side is comfortably small. And even then, if the right-hand side is a static table rather than a search — a CSV of service accounts and their owning teams — you should not be using `join` at all: that is enrichment, and `lookup` is the command for it. ## How to verify a rewrite Run the subsearch on its own and note its row count, then run the stats version and compare the entities each produces. If the join version returns fewer entities than the stats version, you have just demonstrated the truncation to yourself, and that comparison is worth keeping in the rule's test notes.

  • How would you detect that a join's subsearch was being truncated?
    Run the subsearch standalone and compare its row count with the number of distinct keys the join matched against; a shortfall is the truncation. The search job's messages and log also record subsearch finalization. The durable fix is not raising the limit but rewriting to the stats form, which has no intermediate result set to overflow.
  • The right-hand side is a two-hundred-row CSV of service accounts. Is join still wrong?
    It is the wrong command for a different reason: a static table is enrichment, not correlation, so `lookup` is the idiom. It is cheaper, it does not spawn a subsearch, and it has left-outer semantics, so events whose key is missing from the table survive rather than disappearing from the result.
  • Does the stats rewrite change what the detection can claim?
    It should not change the intent, but it changes the evidence shape: you get one row per entity with aggregates rather than raw events, so the rule must carry through the fields an analyst needs, such as client addresses, database, and first and last seen. What it does change is confidence, because no part of the correlation was silently dropped.

A join is like asking a colleague for the full contractor list before you start; they hand you the first page and never mention there were more pages.

saying these in an interview costs you the question

  • Thinks join returns everything and merely costs more
  • Assumes a truncated subsearch raises an error
  • Uses join to attach a static CSV instead of lookup
  • Believes stats and join always return identical rows
  • Expects KQL's default join to keep every left-hand row

context

resolving links…