TH201 Ep4 — Clustering: Full ES|QL Queries

Companion page for Threat Hunting 201: Technique 4 | Clustering in Elastic SIEM (ES|QL) | Episode 4 on THOR-HQ. All three ES|QL queries below are lab-validated against real Sysmon telemetry in logs-*.

Hunt 4.1 — Process Parent-Child Matrix

What it finds: rare parent→child process pairs — the combinations frequency-based Stacking can't see, because Stacking only ranks a single field at a time. A process that runs 400 times a day (e.g. powershell.exe) looks completely normal under Stacking. But if it was launched by winword.exe — a parent→child combination that's happened exactly once in the whole dataset — Clustering catches it instantly.

FROM logs-*
| WHERE event.code == "1"
AND winlog.event_data.Image IS NOT NULL
AND winlog.event_data.ParentImage IS NOT NULL
| EVAL child_process = MV_LAST(SPLIT(winlog.event_data.Image, "\\"))
| EVAL parent_process = MV_LAST(SPLIT(winlog.event_data.ParentImage, "\\"))
| STATS execution_count = COUNT(*) BY parent_process, child_process
| WHERE execution_count <= 5
| SORT execution_count ASC
| LIMIT 50

Result: 38 rare parent→child pairs surfaced, including powershell.exe → quser.exe and explorer.exe → vmtoolsd.exe — each occurring only once in the dataset.

Hunt 4.2 — DNS Structural Clustering (DGA Detection)

What it finds: DGA (Domain Generation Algorithm) malware domains by string structure, not query frequency. A legitimate domain queried 5,000 times a day is still short. A DGA domain queried 5,000 times a day (because the malware is beaconing to its C2 constantly) is still long and structurally unusual. Stacking would see high frequency and assume "normal, common domain." Clustering on structure catches it regardless of how often it's queried.

FROM logs-*
| WHERE dns.question.name IS NOT NULL
AND dns.question.name != ""
| EVAL
domain_length = LENGTH(dns.question.name),
subdomain_depth = LENGTH(dns.question.name)
- LENGTH(REPLACE(dns.question.name, "[.]", ""))
| STATS
query_count = COUNT(*),
avg_length = AVG(domain_length),
avg_depth = AVG(subdomain_depth)
BY dns.question.name
| WHERE avg_length > 30
| SORT avg_length DESC
| LIMIT 20

Gotcha: ES|QL's REPLACE uses regex — a plain . in the replace pattern matches any character, not just a literal dot. Use [.] to match a literal dot only, or you'll strip every character, not just the dots.

Result: 20 structurally anomalous domains surfaced, including 3u894n5s1nbygrlfgnky2079hncd541vw.telemetry-sync.org (avg length 52) and storagecatalogrevocation.storagelity.microsoft.com (avg length 49, a lookalike/typosquat pattern).

Hunt 4.3 — Host Behavioral Fingerprinting

What it finds: a multi-dimensional behavioral profile per host — combining 5 dimensions Stacking and Grouping can't produce together, because they only look at one dimension at a time. Weights are tunable per environment; here, unique users carries the highest weight (5x) because multiple distinct users logging into one machine is a strong lateral-movement signal.

FROM logs-*
| WHERE host.name IS NOT NULL
| STATS
total_events = COUNT(*),
event_type_variety = COUNT_DISTINCT(event.category),
process_diversity = COUNT_DISTINCT(winlog.event_data.Image),
dest_ip_spread = COUNT_DISTINCT(winlog.event_data.DestinationIp),
unique_users = COUNT_DISTINCT(user.name)
BY host.name
| EVAL behavioral_score =
(event_type_variety * 4) +
(process_diversity * 2) +
(dest_ip_spread * 3) +
(unique_users * 5)
| EVAL cluster_band = CASE(
behavioral_score >= 100, "OUTLIER",
behavioral_score >= 50, "ELEVATED",
"BASELINE"
)
| SORT behavioral_score DESC

Weight rationale: unique_users 5 > dest_ip_spread 3 > event_type_variety 4 > process_diversity 2 — tune these based on what's most suspicious in your own environment; there's no universal "correct" weighting.

Result: 2 hosts scored, one clear outlier — thorhq-windows (297,926 events, score 583, OUTLIER band) vs. 53e189d07962 (150,453 events, score 0, BASELINE).

Core teaching argument

Stacking ranks. Grouping pivots. Clustering profiles.

Full episode: Threat Hunting 201, Episode 4 — Clustering, on THOR-HQ.