These practical ES|QL query examples move from beginner-friendly searches to advanced security investigations. They are designed for Elastic users who want copyable Elasticsearch Query Language examples for Kibana Discover, dashboards, alerting, Elastic Security, and troubleshooting workflows.
The easy and medium examples use built-in Kibana sample data where possible, so they are simple to try in a demo environment. The complex examples use generic security index patterns that you can adapt to your own Elastic Security, Windows event, process, and network data.
Easy ES|QL examples
1. Filter logs by field value
Use case: Find HTTP 404 errors in web access logs.
FROM kibana_sample_data_logs
| WHERE response == "404"
| KEEP @timestamp, clientip, request, response
| SORT @timestamp DESC
| LIMIT 50What it teaches: The core ES|QL workflow with FROM, WHERE, KEEP, SORT, and LIMIT.
2. Count events per response code
Use case: See which HTTP status codes appear most often.
FROM kibana_sample_data_logs
| STATS count = COUNT(*) BY response
| SORT count DESCWhat it teaches: Basic STATS ... BY aggregation, similar to a SQL GROUP BY.
3. Filter out private IPs with CIDR_MATCH
Use case: Find high-traffic requests from non-local IP addresses.
FROM kibana_sample_data_logs
| KEEP clientip, bytes
| WHERE bytes > 2000
| EVAL is_local = CIDR_MATCH(clientip, "127.0.0.0/8", "10.0.0.0/8", "192.168.0.0/16")
| WHERE NOT is_local
| SORT bytes DESC
| LIMIT 100What it teaches: Use EVAL for derived fields, CIDR_MATCH for IP range checks, and boolean logic across multiple WHERE steps.
4. Top 10 destinations by flight distance
Use case: Find the longest international flights arriving at JFK.
FROM kibana_sample_data_flights
| KEEP Carrier, FlightNum, OriginCountry, DestCountry, DestAirportID, DistanceKilometers
| WHERE DestAirportID == "JFK" AND OriginCountry != "US"
| SORT DistanceKilometers DESC
| LIMIT 10What it teaches: Combine equality and inequality filters, sort numeric fields, and select only relevant columns with KEEP.
Medium ES|QL examples
5. Calculate average ticket price per airline
Use case: Compare pricing across carriers.
FROM kibana_sample_data_flights
| STATS avg_price = AVG(AvgTicketPrice),
total_flights = COUNT(*),
avg_distance_km = AVG(DistanceKilometers)
BY Carrier
| SORT avg_price DESCWhat it teaches: Use multiple aggregations in one STATS command, including AVG and COUNT.
6. Parse unstructured log lines with GROK
Use case: Extract structured fields from a raw log string on the fly, without changing an index mapping first.
ROW raw = "2024-11-15T14:32:10.000Z 203.0.113.42 user@example.com 8192"
| GROK raw "%{TIMESTAMP_ISO8601:ts} %{IP:src_ip} %{EMAILADDRESS:user_email} %{NUMBER:bytes:int}"
| KEEP ts, src_ip, user_email, bytes
| EVAL event_time = TO_DATETIME(ts)What it teaches: Use ROW to create inline test data, GROK for pattern-based extraction, and TO_DATETIME for type conversion. This is useful for prototyping parsers before changing ingest pipelines.
7. Enrich query results from a second dataset
Use case: Add geo or context data to IPs seen in logs using an enrich policy.
FROM kibana_sample_data_logs
| KEEP clientip
| EVAL clientip = TO_STRING(clientip)
| WHERE clientip == "69.191.135.194"
| ENRICH geo-policy ON clientip WITH country, cityWhat it teaches: Use ENRICH to add external lookup data into query results at query time, similar to a lightweight left join.
8. Bucket events into hourly time windows
Use case: See request volume per hour to spot traffic spikes.
FROM kibana_sample_data_logs
| EVAL hour = DATE_TRUNC(1 hour, @timestamp)
| STATS requests = COUNT(*), total_bytes = SUM(bytes) BY hour
| SORT hour ASCWhat it teaches: Use DATE_TRUNC for time bucketing, SUM for totals, and ES|QL aggregations for time-series views.
9. Classify flight delays with CASE
Use case: Label flights as on time, delayed, or cancelled based on delay minutes.
FROM kibana_sample_data_flights
| EVAL status = CASE(
FlightDelayMin == 0, "On Time",
FlightDelayMin <= 30, "Minor Delay",
FlightDelayMin <= 120, "Significant Delay",
FlightDelayMin > 120, "Major Delay",
"Unknown"
)
| STATS flights = COUNT(*) BY status
| SORT flights DESCWhat it teaches: Use CASE for conditional logic and categorical bucketing.
Complex ES|QL examples
10. Detect lateral movement: users logging into multiple hosts
Use case: Identify accounts that authenticated to three or more distinct machines in a short window, a common lateral movement signal.
FROM windows-security-logs
| WHERE event.code == "4624" AND logon.type == "3"
| EVAL time_bucket = DATE_TRUNC(30 minute, @timestamp)
| STATS unique_hosts = COUNT_DISTINCT(host.name),
active_periods = COUNT_DISTINCT(time_bucket),
first_login = MIN(@timestamp),
last_login = MAX(@timestamp)
BY user.name
| WHERE unique_hosts > 2
| EVAL time_span_hours = DATE_DIFF("hour", first_login, last_login)
| EVAL movement_velocity = ROUND(unique_hosts / (time_span_hours + 1), 2)
| SORT movement_velocity DESC
| LIMIT 50What it teaches: Use COUNT_DISTINCT for cardinality, DATE_DIFF for duration, EVAL for computed risk scores, and chained transformations for investigation logic.
11. Detect data exfiltration with threat intel enrichment
Use case: Flag large outbound transfers to IPs known in a threat-intelligence lookup index.
FROM network-logs
| WHERE NOT CIDR_MATCH(destination.ip, "10.0.0.0/8", "192.168.0.0/16")
| EVAL indicator_value = TO_STRING(destination.ip)
| LOOKUP JOIN `threat-intel` ON indicator_value
| WHERE threat.name IS NOT NULL
| STATS total_bytes = SUM(network.bytes),
connection_count = COUNT(*)
BY host.name, destination.ip, threat.name
| EVAL mb_transferred = ROUND(total_bytes / 1048576, 2)
| EVAL risk_score = CASE(
mb_transferred > 100, "CRITICAL",
mb_transferred > 10, "HIGH",
"MEDIUM"
)
| SORT total_bytes DESC
| LIMIT 50What it teaches: Use LOOKUP JOIN to correlate events with threat intelligence, CIDR_MATCH for private range filtering, chained EVAL steps, and conditional risk scoring.
Prerequisite: LOOKUP JOIN requires a lookup index. Review the Elastic LOOKUP JOIN documentation before adapting this query.
12. Build an APT attack timeline across multiple indices
Use case: Correlate authentication, process execution, and network events for a suspect user, then map each event to an investigation stage.
FROM windows-security-logs, process-logs, network-logs
| LOOKUP JOIN `asset-inventory` ON host.name
| LOOKUP JOIN `user-context` ON user.name
| WHERE user.name == "jsmith" OR user.name == "admin"
| EVAL event_type = CASE(
event.code IS NOT NULL, "Authentication",
process.name IS NOT NULL, "Process Execution",
destination.ip IS NOT NULL, "Network Activity",
"Unknown"
)
| EVAL dest_ip_str = TO_STRING(destination.ip)
| EVAL attack_stage = CASE(
process.parent.name LIKE "*word*", "Initial Compromise",
process.name IN ("net.exe", "nltest.exe"), "Reconnaissance",
event.code == "4624" AND logon.type == "3", "Lateral Movement",
process.name IN ("sqlcmd.exe", "ntdsutil.exe"), "Data Access",
NOT CIDR_MATCH(destination.ip, "10.0.0.0/8"), "Exfiltration",
"Other"
)
| SORT @timestamp ASC
| KEEP @timestamp, event_type, attack_stage, host.name, asset.criticality, user.name, process.name, destination.ip
| LIMIT 500What it teaches: Query multiple indices in one FROM, use multiple LOOKUP JOIN steps, apply complex CASE logic, check fields with IS NOT NULL, and build an investigation timeline.
13. Hunt scheduled task persistence bursts
Use case: Detect accounts that create multiple scheduled tasks, a common persistence technique, within a short time window.
FROM process-logs
| WHERE process.name == "schtasks.exe" AND process.command_line : "/create"
| LOOKUP JOIN `asset-inventory` ON host.name
| LOOKUP JOIN `user-context` ON user.name
| EVAL time_bucket = DATE_TRUNC(1 hour, @timestamp)
| STATS task_creations = COUNT(*),
creation_hours = COUNT_DISTINCT(time_bucket)
BY user.name, host.name, asset.criticality, user.privileged
| EVAL persistence_pattern = CASE(
creation_hours > 1, "Spread Across Hours",
task_creations > 1, "Burst Creation",
"Single Task"
)
| SORT task_creations DESC
| LIMIT 50What it teaches: Use the : match operator for full-text search on a field, COUNT_DISTINCT on a computed bucket field, and enrichment with user and asset context.