The Mental Model — Pipes, Filters, Transforms

SIEM query languages look different on the surface — SPL uses | commands, KQL uses | operators, EQL uses sequence keywords. But underneath, they’re all doing the same thing: filter → transform → project. Learn the mental model once, learn every language faster.

How SIEMs Store and Search Data

Modern SIEMs store events in inverted indexes — a data structure that maps field values to the events that contain them. When you search EventID: 4625, the SIEM looks up the EventID field in the index, retrieves the list of event IDs that match, and returns those events.

This is why:

Querying on indexed fields is fast (microseconds)
Querying with wildcards at the start of a string (*evil.exe) is slow (has to scan every value)
Full-text search on message content is slower than field-value search
Time range is always the first filter applied — it dramatically reduces the data set

Search: EventID == 4625 AND Computer == "WORKSTATION01"

SIEM execution:
1. Restrict to time range → 50GB → 500MB
2. Look up EventID=4625 in index → list of 50,000 event IDs
3. Look up Computer="WORKSTATION01" in index → list of 10M event IDs
4. Intersect: events matching both → ~100 events
5. Return those 100 events

The Filter → Transform → Project Pipeline

Every SIEM query, regardless of language, follows this pattern:

Raw Events
    │
    ▼ FILTER (WHERE / search / where clause)
Filtered Events
    │
    ▼ TRANSFORM (stats / summarize / aggregate)
Aggregated Data
    │
    ▼ PROJECT (table / fields / project)
Output

SPL (Splunk)

index=windows EventCode=4625    ← FILTER: source + event type
| stats count by Account_Name, host    ← TRANSFORM: aggregate
| where count > 5    ← FILTER on aggregate
| sort -count    ← PROJECT/SORT: order output
| table host, Account_Name, count    ← PROJECT: select columns

KQL (Kusto / Microsoft Sentinel)

SecurityEvent    ← SOURCE (table)
| where EventID == 4625    ← FILTER
| summarize FailedLogons=count() by Account, Computer    ← TRANSFORM
| where FailedLogons > 5    ← FILTER on aggregate
| order by FailedLogons desc    ← SORT
| project Computer, Account, FailedLogons    ← PROJECT

EQL (Elastic)

// EQL is for single-event and sequence queries
authentication where
  event.outcome == "failure"
  and event.type == "start"
  | head 100

All three queries find the same thing (accounts with > 5 failed logons). The syntax differs; the logic is identical.

Cardinality and Aggregation Thinking

The most powerful detection queries use aggregation to find statistical anomalies:

Count-based (brute force):

Count of failed logons per account per 10 minutes > threshold

Uniqueness-based (discovery):

Number of unique hosts accessed per user in 1 hour > threshold
(attacker lateral movement leaves a high-cardinality host footprint)

Ratio-based (password spray):

Ratio of unique accounts to total failed logon attempts > threshold
(spray: one attacker, many accounts → high unique account count, low per-account failure count)

Sequence-based (attack chains):

Failed logon on host A, then within 5 minutes, successful logon on host B from same source IP

Join vs Correlation

SIEM joins combine two event sets based on a shared key field:

// KQL: find users who failed logon then succeeded (logon after brute force)
let failures = SecurityEvent
  | where EventID == 4625
  | summarize FailCount=count() by Account, Computer, bin(TimeGenerated, 1h);
let successes = SecurityEvent
  | where EventID == 4624
  | project Account, Computer, SuccessTime=TimeGenerated;
failures
| join kind=inner successes on Account, Computer
| where SuccessTime > TimeGenerated
| where FailCount > 5

Joins are powerful but expensive — they cross-reference large event sets. Use them when you need to correlate events from the same source. For events from different sources (Windows + CloudTrail), denormalize first (normalize both to ECS), then join.

Splunk Search Reference— Splunk Docs KQL Reference— Microsoft Docs

Retrieval Practice

You write a Splunk SPL query: `index=windows | where like(message, '%mimikatz%')`. Your team says it's slow. What is the root cause, and how would you rewrite it for better performance?

Reveal answer

The root cause: `like()` on the `message` field does a full-text scan — it reads every event in the index and checks if the message contains 'mimikatz'. This bypasses the inverted index. Better approaches: (1) `index=windows mimikatz` — Splunk full-text searches the index directly for the term before filtering, using the inverted index; (2) `index=windows CommandLine='*mimikatz*'` — if CommandLine is indexed, this uses the field index for the wildcard scan; (3) Most effective: `index=windows EventCode=4688 CommandLine='*mimikatz*'` — pre-filter on EventCode (exact match, fast) then apply the wildcard only to the filtered set. Always apply the most selective indexed filter first.

Retrieval Practice

A KQL query returns: `SecurityEvent | where EventID == 4688 | summarize count() by Computer`. The results show 'WORKSTATION01' with count=50000. Is this a detection signal? What additional context would you add to make it actionable?

Reveal answer

Count=50000 process creation events from one workstation is potentially high but not directly actionable — some workstations legitimately spawn many processes (developer machines, build servers). To make it actionable: (1) Compare to a baseline — `summarize Baseline=avg(count())` over the past 7 days by Computer, then flag hosts more than 2 standard deviations above baseline; (2) Filter by suspicious process patterns — `| where NewProcessName has_any ('cmd.exe', 'powershell.exe', 'wscript.exe')` before aggregating; (3) Add time bucketing — `bin(TimeGenerated, 1h)` to see if the spike is continuous or a sudden burst. A raw count without baseline comparison is almost never an actionable alert.