4.3 ES|QL for Multi-Stage Security Analysis

Traditional search queries in SIEM platforms operate on a single-pass document paradigm: filters isolate matching records, and aggregations compute metric summaries. However, complex security threat hunting requires multi-stage analytical workflows—filtering preliminary events, calculating derived values, parsing embedded strings, enriching records against threat intelligence, aggregating metrics across entities, and applying secondary thresholds to isolate malicious behaviors.

Introduced as a technical preview in Elastic Stack 8.11 and generally available since 8.14, ES|QL (Elasticsearch Query Language) introduces an expressive, pipe-based (|) query architecture powered by a ground-up vectorized compute engine. For security analysts, ES|QL unifies search, aggregation, and transformation into a single intuitive syntax, fundamentally streamlining advanced triage and hunt operations.

---

The ES|QL Vectorized Compute Architecture

Traditional Elasticsearch queries evaluate documents row-by-row, translating JSON query DSL objects into Lucene collector executions. In contrast, ES|QL operates on a vectorized execution model:

1. Columnar Data Processing: Data is transferred from storage directly into memory structured as columns rather than complete JSON documents. Operations execute across column arrays, optimizing CPU cache locality.
2. Block-at-a-Time Operators: Operators work on whole blocks of values instead of one document at a time, which reduces per-row overhead and lets the JVM use vectorized CPU instructions where it can.
3. Pipelined Execution Pipeline: The pipe symbol (|) passes tabular intermediate results from one processing command directly to the next. Shards execute early filtering and partial aggregations concurrently before passing condensed tabular streams to coordinating nodes.

---

Core ES|QL Commands for Security Operations

An ES|QL query begins with a source command (FROM) followed by a sequential series of processing commands separated by pipes (|):

ES|QL string literals always use double quotes ("failure"); single quotes are not valid string delimiters.

1. FROM — Target Data Definition

Specifies the data stream, index pattern, or alias to query. Metadata columns (such as _index or @timestamp) can be projected directly.

FROM logs-endpoint.events.*, logs-system.*

2. WHERE — Stage Filtering

Applies boolean filtering criteria. WHERE can be invoked at any stage: early in the pipe to prune raw documents at the shard level, or late in the pipe to filter aggregated metric outputs.

| WHERE event.category == "authentication" AND event.outcome == "failure"

3. STATS ... BY — Aggregation & Metrics

Computes statistical metrics (such as COUNT(*), SUM(), AVG(), COUNT_DISTINCT()) grouped by one or more categorical dimensions.

| STATS fail_count = COUNT(*), distinct_targets = COUNT_DISTINCT(user.name) BY source.ip

4. EVAL — Dynamic Column Calculation

Creates or transforms columns during query execution using mathematical expressions, string functions, or conditional logic (CASE).

| EVAL total_bytes = source.bytes + destination.bytes,
       mb_transferred = total_bytes / (1024 * 1024)

5. DISSECT & GROK — In-Line Unstructured Parsing

Extracts structured fields from raw text columns (like message) on the fly. DISSECT uses delimiter-based pattern matching (ultra-fast), whereas GROK utilizes regular expressions.

| DISSECT message "%{src_ip} %{dst_ip} %{protocol} %{action}"

6. ENRICH — In-Stream Intelligence Correlation

Enriches streaming results against an existing Elasticsearch Enrich Policy, appending external threat intelligence, asset ownership, or vulnerability scores directly into the query result.

| ENRICH threat_intel_ipv4_policy ON source.ip WITH indicator_type, threat_score

7. SORT, LIMIT, KEEP, DROP — Output Shaping

• KEEP: Retains only specified columns, discarding temporary parsing fields.
• DROP: Omits specific noisy or sensitive columns.
• SORT: Orders the output table by one or more columns ascending (ASC) or descending (DESC).
• LIMIT: Restricts output to a defined row count. If a query has no LIMIT, ES|QL applies a default of 1,000 rows, and no query returns more than 10,000 rows.

---

Practical Threat Hunting & SecOps Scenarios

Scenario 1: Detecting Distributed Password Spraying

In password spraying attacks, an adversary attempts authentication against numerous accounts from a single external IP address or small pool of IPs, keeping attempts per user low to evade per-account lockouts.

FROM logs-auth.*
| WHERE event.category == "authentication" 
    AND event.outcome == "failure"
    AND @timestamp >= NOW() - 2 HOURS
| STATS 
    total_failures = COUNT(*),
    targeted_accounts = COUNT_DISTINCT(user.name)
  BY source.ip
| WHERE total_failures >= 25 AND targeted_accounts >= 5
| EVAL accounts_per_failure = targeted_accounts / (total_failures * 1.0)
| SORT total_failures DESC
| LIMIT 20

Analytical Walkthrough: The query filters failed authentication events over the past two hours, aggregates total failures and distinct usernames per source IP, applies a secondary WHERE stage to isolate IPs targeting at least 5 distinct users, calculates the account-to-failure ratio, and outputs the top 20 suspicious sources.

Scenario 2: Data Exfiltration Threat Hunting by Egress Volume

Identifying internal endpoints communicating with external IP addresses transferring abnormal outbound byte volumes:

FROM logs-network.*
| WHERE network.direction == "outbound" 
    AND destination.ip != null
    AND NOT CIDR_MATCH(destination.ip, "10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16")
| EVAL egress_mb = destination.bytes / (1024 * 1024)
| STATS 
    total_egress_mb = SUM(egress_mb),
    session_count = COUNT(*)
  BY source.ip, destination.ip, destination.port
| WHERE total_egress_mb > 500
| SORT total_egress_mb DESC
| LIMIT 50

Analytical Walkthrough: Filters outbound network telemetry, excludes internal RFC 1918 subnets using the built-in CIDR_MATCH function, converts raw destination bytes to megabytes via EVAL, aggregates total volume and connection counts per IP pair, and retains only pairs exceeding 500 MB.

Scenario 3: In-Line Syslog Dissection with Threat Intel Enrichment

Triage of unparsed legacy firewall telemetry where key fields reside in a raw string message:

FROM logs-firewall.raw-*
| WHERE message IS NOT NULL AND @timestamp >= NOW() - 1 HOUR
| DISSECT message "%{ts} %{fw_host} %{action}: %{src_ip}:%{src_port} -> %{dst_ip}:%{dst_port}"
| WHERE action == "PERMIT"
| ENRICH threat_intel_ip_policy ON dst_ip WITH threat_family, confidence_score
| WHERE confidence_score >= 80
| KEEP ts, fw_host, src_ip, dst_ip, dst_port, threat_family, confidence_score
| SORT confidence_score DESC
| LIMIT 100

Analytical Walkthrough: The query pulls raw firewall messages, executes delimiter-based DISSECT to parse IPs and ports, filters for permitted connections, joins the parsed destination IP against a threat intelligence enrich policy, isolates high-confidence malicious destinations, and emits an executive triage table.

---

ES|QL Command Reference Guide