Study guide
4.3 ES|QL for Multi-Stage Security Analysis
Elastic Certified SIEM Analyst Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & Study Strategy
1.1 Elastic Certified SIEM Analyst Exam Facts, Objectives & Study Plan
2 Chapter 2: Elastic Stack Architecture & SIEM Foundations
2.1 Elastic Stack Architecture for Security Operations 2.2 Telemetry Ingestion Architecture & Data Pipelines 2.3 Security Data Storage, Data Streams & ILM
3 Chapter 3: Elastic Common Schema (ECS) & Ingestion Normalization
3.1 ECS Structure, Core Principles & Essential Field Sets 3.2 Ingest Pipelines, Processors & Normalization Workflows 3.3 Fleet Server & Elastic Agent Integration Management
4 Chapter 4: Discover, Data Views & Security Query Languages
4.1 Kibana Discover, Security Data Views & Runtime Fields 4.2 Kibana Query Language (KQL) & Lucene for SecOps 4.3 ES|QL for Multi-Stage Security Analysis
5 Chapter 5: Security Visualizations & Threat Analytics
5.1 Metric, Aggregation & Histogram Visualizations for SOC 5.2 Data Tables, Heatmaps & Specialized Security Charts 5.3 Elastic Maps & Geospatial Telemetry Analysis
6 Chapter 6: Kibana Lens for Security Workflows
6.1 Lens Architecture & Multi-Layer Telemetry Visualizations 6.2 Lens Formulas, Ratios & Anomaly Baseline Metrics 6.3 Operationalizing Lens in SOC Triage & Drilldowns
7 Chapter 7: Security Dashboards & SOC Operations
7.1 Security Dashboard Architecture & SOC Layout Design 7.2 Interactive Controls, Time Sync & Cross-Filtering 7.3 Dashboard Exporting, Reporting & Spaces Sharing
8 Chapter 8: The Elastic Security App: Capabilities, Explore & Dashboards
8.1 Security App Capabilities & the Explore Pages (Hosts, Network, Users) 8.2 Monitoring with the Security App Dashboards
9 Chapter 9: Elastic Security Detection Engine & Core Rules
9.1 Detection Engine Architecture & Prebuilt Rule Management 9.2 Custom Query & Threshold Detection Rules 9.3 Event Correlation Rules with Event Query Language (EQL)
10 Chapter 10: Advanced Detection Rules, ML & Rule Tuning
10.1 Threat Intelligence & Indicator Match Rules 10.2 New Terms, ES|QL & Machine Learning Detection Rules 10.3 Detection Rule Tuning, Exceptions & Alert Suppression
11 Chapter 11: Alert Triage, Timelines & Investigation Workflows
11.1 Alert Triage Workflow & the Alert Details Flyout 11.2 Elastic Security Timeline & Timeline Templates 11.3 Visual Event Analyzer & Session View Forensics
12 Chapter 12: Case Management, Integrations & AI Assistant
12.1 Elastic Security Case Management & Triage Workflows 12.2 Case Connectors & External Incident Escalation 12.3 Elastic AI Assistant for Security & Attack Discovery
13 Chapter 13: Security Administration, RBAC & Kibana Spaces
13.1 Kibana Spaces for SOC Multitenancy & Segmentation 13.2 Role-Based Access Control, Feature Privileges & Index Security 13.3 Elastic Defend Administration & Endpoint Policy Controls
14 Chapter 14: End-to-End Threat Hunting & Incident Investigation Capstone
14.1 Hypothesis-Driven Threat Hunting & Baselining in Elastic 14.2 Investigating Lateral Movement & Credential Access Campaigns 14.3 Data Exfiltration, C2 Detection & Incident Reconstruction
4.3 ES|QL for Multi-Stage Security Analysis
Key Takeaways Explain This Topic AI
ES|QL (Elasticsearch Query Language) utilizes an iterative, piped execution model (|) where tabular data flows sequentially from source extraction to final aggregation.
ES|QL runs on a dedicated compute engine in Elasticsearch that processes columnar blocks of values rather than one document at a time.
The DISSECT and GROK commands in ES|QL allow analysts to parse unstructured message fields on the fly during active investigations without modifying ingest pipelines.
In-stream data enrichment via the ENRICH command correlates telemetry directly with threat intelligence or asset classification tables within the query execution pipeline.
Post-aggregation filtering in ES|QL enables multi-stage threshold detection (such as identifying brute-force attacks across distinct source IPs) within a single query.
Last updated: September 2026
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 :
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.
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.
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
Command Syntax Prototype SecOps Use Case
FROM FROM <indices/streams> [METADATA <fields>] Initializes the pipeline with target telemetry streams.
WHERE WHERE <boolean_expression> Filters records before or after aggregation stages.
STATS STATS <metric = func()> ... BY <fields> Aggregates event volume, unique entities, or totals.
EVAL EVAL <new_col = expression> ... Normalizes units, calculates ratios, or derives flags.
DISSECT DISSECT <string_field> "<pattern>" Parses fixed-pattern delimiter strings on the fly.
GROK GROK <string_field> "<pattern>" Parses complex or irregular regex strings on the fly.
ENRICH ENRICH <policy> ON <key> WITH <fields> Merges threat intel or asset metadata in-stream.
SORT SORT <col> [ASC|DESC] [NULLS FIRST|LAST] Ranks outputs by threat score, count, or bytes.
LIMIT LIMIT <integer> Caps intermediate or final tabular row returns.
KEEP / DROP KEEP <cols...> | DROP <cols...> Sanitizes and shapes table columns for reporting.