ES|QL Query Examples for Elasticsearch and Kibana

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 50

What 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 DESC

What 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 100

What 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 10

What 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 DESC

What 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, city

What 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 ASC

What 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 DESC

What 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 50

What 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 50

What 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 500

What 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 50

What 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.

Resources

• Official ES|QL getting started guide
• ES|QL examples reference
• Threat hunting with ES|QL tutorial
• LOOKUP JOIN command documentation
• Community ES|QL cheat sheet
• Elastic detection-rules hunting queries

When an ES|QL query feels slow or returns too much data, the fix is often in the shape of the pipeline. Filter early, keep only useful columns, sort only when you need deterministic order, and limit the rows returned to Kibana or the API.

FROM logs-*
| WHERE @timestamp >= NOW() - 15 minutes
| WHERE service.name == "checkout"
| KEEP @timestamp, service.name, log.level, message, trace.id
| SORT @timestamp DESC
| LIMIT 100

Why this ES|QL pattern helps

Every ES|QL pipe receives the table produced by the previous step. The earlier you reduce rows and columns, the less work later steps usually need to do. This matters most for broad index patterns, high-cardinality fields, joins, parsing, and dashboards that refresh often.

Practical performance tips

• Use a narrow time range while building and testing the query.
• Put selective WHERE clauses near the top of the pipeline.
• Use KEEP or DROP to avoid returning unnecessary fields.
• Add LIMIT for investigation tables and API calls that only need sample rows.
• Filter before expensive operations such as parsing, joins, or broad aggregations whenever possible.
• Avoid querying many indices without filters unless you truly need all matching data.

When to use it

This checklist fits Kibana ES|QL support cases, dashboard tuning, alert query review, and Elasticsearch users who are moving from raw document searches to pipeline-style analysis.

Source reference: Elastic documentation: Optimize ES|QL query performance.

Use LOOKUP JOIN when your ES|QL result needs context from another Elasticsearch index. Common examples include adding service owner, environment, host inventory, or threat intelligence fields to events during an investigation.

FROM app-logs-*
| WHERE @timestamp >= NOW() - 1 hour
| WHERE log.level IN ("error", "fatal")
| LOOKUP JOIN service_owners ON service.id
| KEEP @timestamp, service.id, service.name, team.name, escalation.email, message
| SORT @timestamp DESC
| LIMIT 50

Why this ES|QL pattern helps

Raw logs often tell you what failed but not who owns the service or where the alert should go. A lookup join adds that operational context directly to the query result, which can speed up triage and reduce manual cross-checking during incidents.

What to change for your data

• Replace service_owners with a lookup index or alias configured for your environment.
• Join on a stable key such as service.id, host.name, user.id, or an IP address.
• Filter before the join when possible so fewer rows need enrichment.

When to use it

This is useful for Elastic Security enrichment, observability ownership mapping, incident routing, asset inventory lookups, and ES|QL support questions about replacing spreadsheet-based context during investigations.

Source reference: Elastic documentation: ES|QL LOOKUP JOIN command.

Use ES|QL parsing when your logs contain useful information inside a single message field and you need answers before an ingest pipeline or mapping change is ready. DISSECT is a good first choice for predictable delimiter-based strings.

FROM logs-*
| WHERE message LIKE "*checkout*"
| DISSECT message """%{service} %{level} %{request_id} %{duration_ms}ms %{details}"""
| EVAL duration_ms = TO_LONG(duration_ms)
| WHERE duration_ms > 500
| KEEP @timestamp, service, level, request_id, duration_ms, details
| SORT duration_ms DESC
| LIMIT 25

Why this ES|QL pattern helps

Query-time parsing is useful during incident response, data exploration, and migration work. It lets you test the value of a field extraction before you commit to a permanent ingest pipeline. DISSECT is usually easier and faster for stable log formats, while GROK is better when the pattern needs regular expressions.

What to change for your data

• Replace logs-* with the data stream or index that contains the raw message field.
• Adapt the DISSECT pattern to the exact order and separators in your log messages.
• Use conversion functions such as TO_LONG or TO_DATETIME when extracted values should be treated as numbers or dates.

When to use it

This pattern is helpful for Elastic Observability troubleshooting, temporary field extraction, log format discovery, and support questions about parsing with ES|QL instead of changing Logstash or ingest pipelines immediately.

Source reference: Elastic documentation: ES|QL DISSECT command.

Some Elasticsearch indices do not use @timestamp as their time field. In Kibana, ES|QL can still work with the time picker when your query compares the correct date field to the ?_tstart and ?_tend parameters.

FROM kibana_sample_data_ecommerce
| WHERE order_date >= ?_tstart AND order_date < ?_tend
| STATS orders = COUNT(*), revenue = SUM(taxful_total_price) BY customer_gender
| SORT revenue DESC

Why this ES|QL pattern helps

Kibana visualizations and Discover sessions are more useful when they respond to the global time picker. This pattern connects ES|QL to the selected time range even when the index uses a business date such as order_date, event.ingested, or transaction.time.

What to change for your data

• Replace order_date with the date field that should drive the analysis.
• Use < ?_tend for the upper bound to avoid overlap when adjacent time windows are compared.
• Keep the time filter near the start of the query so fewer rows flow into later processing steps.

When to use it

This is useful for Kibana ES|QL dashboards, business data sets, ecommerce analytics, custom application events, and any support case where the time picker appears inactive because the index has no standard @timestamp field.

Source reference: Elastic documentation: ES|QL and time series data in Discover.

Use STATS when you want ES|QL to summarize many Elasticsearch documents into a smaller result set. This example counts web responses by status code and destination country so you can quickly spot error patterns.

FROM kibana_sample_data_logs
| WHERE @timestamp >= NOW() - 24 hours
| STATS events = COUNT(*), total_bytes = SUM(bytes) BY response.keyword, geo.dest
| SORT events DESC
| LIMIT 20

Why this ES|QL pattern helps

A raw event list is useful for inspection, but operations teams often need a grouped view first. STATS turns logs into metrics you can sort, visualize, save to a dashboard, or use as the starting point for a deeper investigation.

What to change for your data

• Group by service.name, host.name, url.path, or error.type if those fields are more useful than geo.dest.
• Use AVG(), MAX(), MIN(), or percentiles when analyzing latency or duration fields.
• Keep the time window small while testing, then broaden it once the query returns the expected groups.

When to use it

This pattern fits Kibana dashboard panels, ES|QL alert previews, service health checks, and support workflows where users search for Elasticsearch ES|QL aggregation examples.

Source reference: Elastic documentation: Use ES|QL in the Kibana UI.

Use this ES|QL query when you need a clean first look at log events in Kibana Discover. It keeps the fields that matter for troubleshooting, filters noisy rows, sorts the newest events first, and limits the result set so the table stays useful.

FROM kibana_sample_data_logs
| WHERE response.keyword != "200"
| KEEP @timestamp, clientip, request, response.keyword, bytes, geo.dest
| SORT @timestamp DESC
| LIMIT 50

Why this ES|QL pattern helps

Log indices often contain many fields and many successful events. Starting with WHERE and KEEP lets you focus on the events that are likely to need attention. Sorting by @timestamp makes the result behave like a live investigation view.

What to change for your data

• Replace kibana_sample_data_logs with your log index, data stream, or alias.
• Change response.keyword to the status, outcome, or event field in your own data.
• Add service, host, cloud, or Kubernetes fields to KEEP if they help your team triage incidents.

When to use it

This is a good starter query for Elasticsearch log support, Kibana Discover troubleshooting, and operational handoffs where you want a readable table of relevant events instead of a raw document dump.

Source reference: Elastic documentation: Using ES|QL in Discover.