TH2.16 Module Summary

Module Summary: Advanced KQL for Hunting

This module taught the KQL patterns that separate hunting queries from detection rules. Every operator and pattern has a specific application in the campaign modules that follow.

Statistical and time-series analysis (TH2.1–TH2.4)

Percentile and statistical deviation (TH2.1). Two approaches: population comparison (percentile, z-score — “is this user unusual compared to all users?”) and self-baseline comparison (deviation ratio — “is this user unusual compared to themselves?”). Campaign modules combine both.

make-series (TH2.2). Transforms discrete events into continuous time series with explicit bin sizes. Daily for sustained behavioral changes. Hourly for authentication bursts. 5-minute for beacon periodicity. Always specify from, to, and step explicitly.

series_decompose_anomalies (TH2.3). Automated anomaly detection on time series — decomposes into baseline, seasonality, and residual, then flags data points where the residual exceeds the threshold. Threshold 1.5 default; 3.0 for conservative hunting. The pattern: make-series → series_decompose_anomalies → mv-expand → where anomalies != 0.

series_fir smoothing (TH2.4). Moving average filters that reveal trends hidden by noise. Use anomaly detection for acute spikes. Use smoothing for sustained behavioral shifts. Both are needed.

Structural analysis (TH2.5–TH2.8)

top-nested (TH2.5). Hierarchical frequency profiling — top N values within each group. Finds per-entity anomalies that flat summarize misses.

autocluster (TH2.6). Automatic pattern grouping. Identify the dominant clusters in hunt results. Investigate the outliers that do not fit any cluster.

arg_max/arg_min (TH2.7). Extract the most recent, earliest, or highest-value event per entity in a single pass. Essential for enrichment queries.

Dynamic column parsing (TH2.8). Five patterns for extracting data from nested JSON columns in M365 logs. Every campaign module uses at least one.

Query architecture (TH2.9–TH2.12)

materialize (TH2.9). Cache subquery results to eliminate redundant computation. The difference between a query that completes and one that times out.

Entity pivoting (TH2.10). Union-based chronological timelines across all data sources for a suspect entity. The step 4 (pivot) query template.

Graph semantics (TH2.11). Process tree reconstruction from DeviceProcessEvents. Parent-child relationship analysis for endpoint hunting.

Time window correlation (TH2.12). Three patterns: sequential (A then B within window), chain (A then B then C), proximity (A near B regardless of order). Establishes temporal relationships between events from different tables.

Operational KQL (TH2.13–TH2.15)

Functions and saved queries (TH2.13). Inline functions for single queries. Saved functions for persistent logic. Watchlists for updatable exclusion data. Write once, hunt many times.

Advanced aggregation (TH2.14). countif, make_set + set_difference, multi-pass aggregation, make_bag. Each answers layered questions that count/dcount cannot.

Anti-patterns (TH2.15). Seven KQL mistakes that produce false negatives: case sensitivity, truncated results, join type mismatch, pre-filter too aggressive, baseline contamination, dynamic field matching, time zone confusion. The peer review checklist catches these.

What comes next

TH3 applies these KQL patterns to your own detection data — the ATT&CK coverage analysis that produces your first hunt backlog. TH3 is the first paid module and the bridge from methodology to execution.

TH4–TH13 are the campaigns. Each module specifies which TH2 patterns it uses. If a campaign references series_decompose_anomalies() and you are not comfortable with it, return to TH2.3 before proceeding.