Name: elasticsearch-esql
Author: elastic

Execute ES|QL (Elasticsearch Query Language) queries, use when the user wants to query Elasticsearch data, analyze logs, aggregate metrics, explore data, or create charts and dashboards from ES|QL results.

Elasticsearch ES QL Execute ES QL queries against Elasticsearch: discover the schema, choose the right ES QL feature for the task, generate the simplest correct query, and run it. <! begin partial: preamble Environment Configuration This skill executes Elasticsearch operations through the elastic CLI. If the [ elastic CLI](https://github.com/elastic/cli configuration) is not installed, tell the user what it is needed for. Do not guess credentials, call the HTTP API directly, or attempt other workarounds. This skill references operations in HTTP shorthand form (e.g., GET / , GET / cat/indices , GET /{index}/ mapping , GET /{index}/ settings/index.mode , POST / query ). The [Operations]( operations) table at the end of this document maps each shorthand to the equivalent elastic CLI command — always use the CLI rather than calling the HTTP API directly. <! end partial: preamble What is ES QL? ES QL (Elasticsearch Query Language) is a piped query language for Elasticsearch. It is NOT the same as: Elasticsearch Query DSL (JSON based) SQL EQL (Event Query Language) ES QL uses pipes ( ) to chain commands: FROM index WHERE condition STATS aggregation BY field SORT field LIMIT n Prerequisite: ES QL requires source to be enabled on queried indices. Indices with source disabled (e.g., " source": { "enabled": false } ) will cause ES QL queries to fail. Version Compatibility: ES QL was introduced in 8.11 (tech preview) and became GA in 8.14. Features like LOOKUP JOIN (8.18+), MATCH (8.17+), and INLINE STATS (9.2+) were added in later versions. On pre 8.18 clusters, use ENRICH as a fallback for LOOKUP JOIN (see generation tips). INLINE STATS and counter field RATE() have no fallback before 9.2. Check [references/esql version history.md](references/esql version history.md) for feature availability by version. Cluster Detection: Call GET / to determine the cluster type and version: build flavor: "serverless" — Elastic Cloud Serverless. version.number tracks the stack line under active development (next minor from main), so clients that only semver compare may treat Serverless as “latest.” Do not use version.number to gate features: if build flavor is "serverless" , assume all GA and preview ES QL features are available. build flavor: "default" — Stack (self managed or Cloud hosted). Use version.number for feature availability. Snapshot builds have version.number like 9.4.0 SNAPSHOT . Strip the SNAPSHOT suffix and use the major.minor for version checks. Snapshot builds include all features from that version plus potentially unreleased features from development — if a query fails with an unknown function/command, it may simply not have landed yet. Elastic employees commonly use snapshot builds for testing. Process 1. Verify the connection and detect the deployment type. Call GET / first. This confirms connectivity and detects whether the deployment is a Serverless project (all features available) or a versioned cluster (features depend on version). The build flavor field is the authoritative signal — if it equals "serverless" , ignore the reported version number and use all ES QL features freely. If the call fails, stop and point the user at the CLI configuration instructions rather than guessing endpoints or credentials. 2. Discover the schema (required — never guess index or field names). List candidate indices with GET / cat/indices (pass a pattern to narrow), then fetch field types for the chosen index with GET /{index}/ mapping . Always run schema discovery before generating queries. Index names and field names vary across deployments and cannot be reliably guessed. Even common sounding data (e.g., "logs") may live in indices named logs test , logs app , or application logs . Field names may use ECS dotted notation ( source.ip , service.name ) or flat custom names — the only way to know is to check. Prefer simplicity: Query a single index unless the user explicitly asks for data across multiple sources. Do not combine indices with different schemas using COALESCE unless specifically requested — pick the single most relevant index for the question. When multiple indices contain similar data, prefer the one with the most complete schema for the task at hand. Detect time series indices. Check the index mode with GET /{index}/ settings/index.mode . If it is time series , use TS <data stream (not FROM ), TBUCKET(interval) (not DATE TRUNC ), and wrap counter fields with SUM(RATE(...)) . Read the full TS section in [Generation Tips](references/generation tips.md) before writing any time series query. For TSDS indices on 9.4+, prefer the in language discovery commands METRICS INFO and TS INFO (both GA) over inspecting mappings — they enumerate the metric catalogue and the dimension labels of each time series directly, and are run as ES QL queries via POST / query . Treat METRICS INFO as authoritative for metric type ( counter / gauge / histogram ) and field type ( histogram , tdigest , exponential histogram for distribution metrics). Both must follow TS and must precede STATS / SORT / LIMIT . See [Time Series Queries](references/time series queries.md metric and time series discovery): 3. Choose the right ES QL feature for the task. Version gate (mandatory on Stack): before writing any query, compare version.number to the version tag of every command and function you plan to use. For each feature newer than the cluster (for example a 9.5 feature on 9.4), use its fallback from the table below or tell the user it is unavailable on their version — never emit it, and never silently drop the part of the question it answers. Serverless has every released GA and preview feature. Then match the user's intent to the most appropriate ES QL feature, preferring a single advanced query over multiple basic ones: "find patterns," "categorize," "group similar messages" → CATEGORIZE(field) "spike," "dip," "anomaly," "when did X change" → CHANGE POINT value ON key "trend over time," "time series" → STATS ... BY BUCKET(@timestamp, interval) or TS for TSDB "PromQL", "Prometheus query/dashboard/alert", sum by (instance) (...) , label matchers like {cluster="prod"} → PROMQL source command (GA in 9.5+/Serverless, preview in 9.4); see [PROMQL Command](references/promql command.md). Prefer TS for native ES QL phrasing. "search," "find documents matching" → MATCH (default), QSTR (advanced boolean), KQL (Kibana migration). For content/document relevance search, follow the [ES QL Search Strategy](references/esql search strategy.md) "count," "average," "breakdown" → STATS with aggregation functions "approximate," "estimate," "rough numbers," "fast/cheap stats on huge data" → SET approximation=true; before a STATS query (GA in 9.5+/Serverless, preview in 9.4); see [Query Approximation](references/query approximation.md) "random sample," "random subset of rows" → SAMPLE 0.1 (row probability; preview 8.19/9.1, GA 9.5), then LIMIT n if a row count is asked; there is no RANDOM() function "latest/first value per group," "most recent status per host" → STATS LAST(field, @timestamp) BY group (or FIRST ), not SORT + LIMIT 1 allow list, deny list, "exclude known IPs/users" from another index → WHERE field [NOT] IN (FROM idx KEEP col) (preview 9.5); use LOOKUP JOIN only when you need the other index's columns "unique combinations," "no duplicates" → STATS BY a, b ; Serverless only: KEEP a, b DEDUP (preview) IPs mapped as keyword → convert with TO IP() before CIDR MATCH , IP comparisons, IN (FROM …) , or LOOKUP JOIN on an ip field; for the last two EVAL ip = TO IP(src) first (the left side must be a column) "country/city of an IP," "geolocate IPs," ASN → IP LOCATION geo = ip field pipe command (preview 9.5) parse URLs (domain, path, query) → URI PARTS parts = url field pipe command (preview 9.4), not DISSECT / GROK "in my local time," "per day in New York" → SET time zone = "America/New York"; (9.4+), not manual offsets flattened field sub keys (e.g. OTel resource.attributes ) → FIELD EXTRACT(field, "host.name") (preview 9.5), not field.host.name ; it returns keyword , so cast ( TO INTEGER(...) ) before numeric comparisons embed text/images at query time for vector search → EMBEDDING(value, inference id) (preview 9.5) or TEXT EMBEDDING(text, inference id) inside KNN query files in S3 (Parquet/CSV/NDJSON) without ingesting, or a named federated dataset → FROM <dataset via [Data Federation](references/esql reference.md data federation 95 experimental not for production use) (experimental 9.5, not for production; ECH and self managed only, not Serverless); only when asked, and warn 9.5 or Serverless only feature Fallback on Stack without the feature DEDUP (Serverless preview only) LIMIT 1 BY cols (9.4+; SORT first picks the kept row) or STATS BY cols WHERE x NOT IN (FROM idx \ KEEP c) LOOKUP JOIN idx ON x == c \ WHERE c IS NULL ( IN : IS NOT NULL ); ON expressions need 9.2+, else RENAME x AS c first IP LOCATION No query time GeoIP: ENRICH with an IP range enrich policy, or the ingest geoip processor FIELD EXTRACT (or an unmapped key) FROM idx METADATA source \ EVAL v = JSON EXTRACT( source, "$.labels.key") — not labels.key , labels["key"] , or JSON EXTRACT(labels, …) ; COALESCE with "$.labels[\"k.sub\"]" for pre dotted keys 4. Read only the reference for the feature you need before generating the query (the bold "read before" ones are mandatory for their feature). Do not read every reference up front; open another only if the query fails: [Generation Tips](references/generation tips.md) key patterns (TS/TBUCKET/RATE, per agg WHERE, LOOKUP JOIN, CIDR MATCH), common templates, and ambiguity handling [Time Series Queries](references/time series queries.md) read before any TS query : inner/outer aggregation model, TBUCKET syntax, RATE constraints, histogram metrics [PROMQL Command](references/promql command.md) — read before any PROMQL query : options, output schema, handling unsupported PromQL, and PROMQL vs TS decision matrix (GA in 9.5+/Serverless, preview in 9.4) [ES QL Complete Reference](references/esql reference.md) full syntax for all commands and functions [ES QL Search Strategy](references/esql search strategy.md) — for content/document relevance search (retrieve → fuse → rerank) [ES QL Search Reference](references/esql search.md) — for full text search function syntax (MATCH, QSTR, KQL, scoring) [Query Approximation](references/query approximation.md) — read before using SET approximation : output columns, sampling/confidence level tuning, unsupported functions and patterns (GA in 9.5+/Serverless, preview in 9.4) 5. Generate the query following ES QL syntax. Prefer the simplest query that answers the question — do not add extra indices, fields, or transformations unless the user as