Correlate logs, metrics, and traces in one ES|QL query

ES|QL can now filter one observability signal by the live result of a query against another. WHERE field IN (subquery) is in technical preview in Elastic Stack 9.5 and on Elastic Cloud Serverless, and because these subqueries nest, one query can reach across logs, metrics, and traces at the same time.

The gain is in where the intermediate set lives. When you ask what the saturated hosts logged, the list of saturated hosts is computed and consumed inside Elasticsearch. Six host names or 500 trace IDs never land in a clipboard or in an AI agent's context window, and the set is recomputed from current data every time the query runs.

That changes the unit of investigation. You go from "one slow request stalled on a lock" to "most of the slowest requests did," and only the second answer tells you which team to page. It matters more the further apart your signals are: in many observability stacks, logs, metrics, and traces live in three separate systems, each with its own query language, its own time picker, and its own idea of what a host is, so the same question has to be asked two or three times and the answers joined by hand.

In this post we walk through four investigations, each of which is self-contained. For every one of them, we set out the scenario that started it, the query that answers it, the table it returns, and a note on the difficulties you run into when you try to get the same answer any other way.

The data was collected with the Elastic Distributions of OpenTelemetry and lands in the logs-*.otel-*, traces-*.otel-*, and metrics-*.otel-* data streams, where fields keep their semantic convention names rather than being rewritten into another schema. The correlation pattern works just as well on Elastic Agent integrations, though the queries need translating rather than just renaming: ECS carries log severity as the text field log.level instead of a numeric severity_number, the System integration reports CPU as separate system.cpu.*.pct fields instead of one metric with a state dimension, and APM records durations in microseconds. All of it lands in the same cluster either way, which is the part the subquery depends on.

In Discover, the time picker already applies the range, so the examples below omit an explicit @timestamp filter. Outside Discover, add a filter by time yourself, either with literal timestamps in the query or with ?_tstart and ?_tend in the query and values in the params array of your _query request. Every result below comes from a one hour window over a synthetic fleet of 300 hosts.

Metrics to logs: what are the saturated hosts complaining about?

An infrastructure alert tells you a handful of hosts in a fleet of a few hundred sat above 90% CPU over the last hour. That tells you which hosts are hot and nothing about why. The question worth answering is whether those hosts share a failure mode, or whether they are busy for unrelated reasons and the alert is a coincidence.

FROM logs-*.otel-*
| WHERE severity_number >= 17
  AND resource.attributes.host.name IN (
      TS metrics-hostmetrics.otel-*
      | WHERE attributes.state == "idle"
      | STATS idle = AVG(AVG_OVER_TIME(metrics.system.cpu.utilization))
          BY resource.attributes.host.name
      | WHERE idle < 0.1
      | KEEP resource.attributes.host.name
    )
| STATS errors = COUNT(*), hosts = COUNT_DISTINCT(resource.attributes.host.name)
    BY pattern = CATEGORIZE(body.text), service = resource.attributes.service.name
| SORT errors DESC

The two halves of the query map onto the two halves of the question. The subquery works out which hosts were saturated, averaging CPU utilization per host and keeping the ones that averaged under 10% idle, which is another way of saying above 90% busy for the window. The outer query then works out what those hosts were complaining about, pulling their error logs and using CATEGORIZE to collapse thousands of individual lines into a handful of error classes.

Two choices in there are worth pausing on.

The subquery uses TS rather than FROM because a host does not report one CPU number. It reports a separate time series per CPU state, and per logical core as well if your collector is configured to break them out, so the reduction has to happen in two stages. AVG_OVER_TIME collapses each series to a single value first, and the outer AVG then combines those into one figure per host.

Naming that inner function matters more than it looks. Write AVG(metrics.system.cpu.utilization) on its own and TS supplies LAST_OVER_TIME for you, averaging each series' final sample rather than its average over the window. In this dataset that one substitution moves a host from 7% idle to 10% idle, which is the difference between appearing in the results and not. Querying metrics with the TS command goes into the two aggregation phases in more depth.

The log filter tests severity_number (17 is the ERROR floor on the OpenTelemetry scale) rather than the severity text, because the numeric scale is fixed by the spec while the text is whatever the emitting library decided to write. That is not a hypothetical distinction here: the error logs in this cluster carry four different labels, including SEVERE from a Java service. Matching on the text alone returns 2,754 of checkout's errors and misses the billing service entirely, while the numeric filter returns all 5,910 of them and keeps billing too.

When you run the query in Discover, the result is a short table of error classes, scoped to the saturated hosts:

The subquery returned six saturated hosts, and on all six the same service is timing out against an upstream dependency and draining its connection pool. The hosts column is what lets you set the other two rows aside without opening anything: the certificate errors reach only two of the six, and the gateway declines amount to five lines in an hour. Neither tracks the cohort the way the checkout patterns do.

Having all three signals in one store already removed the exports from this investigation. The subquery removes the step after that, and closing that last gap matters more than it sounds. By the time you have read six host names off a chart and typed them into a log search, the set has moved: a host that crossed the threshold a minute ago is missing from your list, and one that has since recovered is still in it. Here the host list is derived from current data on every run, so re-running the query during an incident gives you the current cohort.

The stale list is only half of it. A correlation done by hand exists only in the head of the person who did it, so nobody else can check it, save it, or run it again tomorrow.

Logs to metrics: do the erroring hosts look different from the healthy ones?

Filtering metrics by a log-derived host set answers the opposite question. The payments service is throwing errors on some hosts and not others, and you want to know whether resource pressure explains the split before you start reading deploy history.

That is a comparison, so the query needs both cohorts. FORK runs two branches over the same input, and IN and NOT IN against the same log-derived host set divide the fleet between them.

TS metrics-hostmetrics.otel-*
| WHERE attributes.state == "idle"
| STATS idle = AVG(AVG_OVER_TIME(metrics.system.cpu.utilization))
    BY host = resource.attributes.host.name
| EVAL busy = 1 - idle
| FORK
    ( WHERE host IN (
        FROM logs-*.otel-*
        | WHERE severity_number >= 17
          AND resource.attributes.service.name == "payments"
          AND resource.attributes.host.name IS NOT NULL
        | STATS errors = COUNT(*) BY resource.attributes.host.name
        | KEEP resource.attributes.host.name )
      | EVAL cohort = "logging errors" )
    ( WHERE host NOT IN (
        FROM logs-*.otel-*
        | WHERE severity_number >= 17
          AND resource.attributes.service.name == "payments"
          AND resource.attributes.host.name IS NOT NULL
        | STATS errors = COUNT(*) BY resource.attributes.host.name
        | KEEP resource.attributes.host.name )
      | EVAL cohort = "no errors" )
| STATS hosts = COUNT(*), mean_busy = AVG(busy), busiest_host = MAX(busy)
    BY cohort

The query reads top to bottom as three stages. The metrics query runs first and reduces the whole fleet to one busy figure per host. FORK then splits that fleet in two using the same log query in both branches, separating the hosts that appear in it from the hosts that do not. The final STATS summarizes each group, so both cohorts come back as two rows of one table, measured the same way over the same window.

This query is longer than the others, and three parts of it are less obvious than they look.

The natural way to label the two cohorts would be EVAL cohort = CASE(host IN (...), "erroring", "healthy"), and ES|QL rejects it. In 9.5 an IN subquery has to be a top-level predicate in a WHERE condition rather than an argument to a scalar function, which is why the split happens at the command level with FORK.

The STATS ... BY inside each subquery looks redundant, since KEEP alone would return the same host names. It is not: without it, the subquery returns one row per matching log document instead of one row per host, and those rows are all held in memory for the outer query to filter against. Aggregating first turns millions of rows into a few hundred host names.

The IS NOT NULL filter guards the sharpest edge here, and this dataset is a live example rather than a hypothetical. NOT IN follows SQL null semantics, so a single null in the subquery result makes the predicate match nothing at all. Five of the payments error logs in this cluster came through a sidecar that dropped the host name. Remove that one line from both branches and the query still succeeds, but it returns a single row: the nulls quietly delete the entire 286-host "no errors" cohort, and what is left looks like a perfectly plausible answer to a different question.

Run the query in Discover and you get two rows, one per cohort:

CPU does not explain the split, and the numbers say so twice. The 14 erroring hosts run slightly cooler on average than the 286 quiet ones, and the busiest machine among them averaged 50% over the hour while the quiet cohort contains a host that averaged 95%. Whatever is failing on those 14, they had headroom the entire time, and deploy history is a better place to spend the next ten minutes.

A negative result like this is worth as much as a positive one, and it is usually the one people skip. Getting it the long way means running the metrics query twice against two hand-built host lists and lining the numbers up afterwards, which is enough friction that the check often just does not happen. Both cohorts here come from the same log query in the same execution, over identical time windows, so there is nothing to reconcile and no reason not to check.

Traces to logs: what did every service log during the slow requests?

A service level objective (SLO) burn alert fires on checkout latency. Tracing gives you the slow requests and their spans, and the next question is what the services involved were writing to their logs while those specific requests were in flight.

The trace ID is the join key, and there are far too many of them to move by hand.

FROM logs-*.otel-*
| WHERE trace_id IN (
      FROM traces-*.otel-*
      | WHERE kind == "Server"
        AND resource.attributes.service.name == "checkout"
        AND name == "POST /api/orders"
        AND duration > 2000000000
      | SORT duration DESC
      | LIMIT 500
      | KEEP trace_id
    )
| STATS lines = COUNT(*), traces = COUNT_DISTINCT(trace_id)
    BY pattern = CATEGORIZE(body.text),
       service = resource.attributes.service.name,
       severity_text
| SORT traces DESC

The subquery answers "which requests were slow." It looks at the inbound request span for the checkout endpoint rather than the client and internal spans beneath it, then keeps the 500 slowest requests over two seconds. Durations are recorded in nanoseconds, which is why the threshold has so many zeros.

The outer query answers "what got logged while they were running," gathering every log line that shares one of those trace IDs and grouping them into patterns.

Counting distinct traces per pattern is what makes the output readable. A log pattern that appears 30,000 times across four traces is one chatty request, while a pattern that shows up in 470 of the 500 slowest traces is a property of being slow.

From the results above, the top row is there by construction and can be set aside: checkout writes one order submitted line per order, so it appears in all 500 traces and says nothing about why these particular 500 were slow.

From the results above, the third row is the answer, and it points at a service nobody was looking at. A lock wait timeout in inventory, two hops downstream from where the alert fired, shows up in 470 of the 500 slowest requests, and the 947 lines behind those 470 traces mean a good share of them retried more than once. The payment gateway declines are real failures, and at 19 traces out of 500 they are not what is burning the SLO.

Done by hand, this means opening slow traces one at a time and reading the correlated logs for each, which is tedious at ten traces and nobody's idea of a plan at 500. When the spans and the logs are held in different systems, every trace you check is a copied ID and a context switch, and the sample size you can afford drops to about three. Three traces is enough to form a theory and not enough to test one. Treating the slow requests as a population is what turns "this trace had a lock wait" into "470 of the 500 slowest requests had a lock wait," and that difference decides whether you page the inventory team.

All three signals: from pod memory pressure to the log lines behind the failures

IN subqueries nest, so the pattern extends to as many signal types as the question needs.

A node pool starts reporting memory pressure after a rollout. You want the log lines from the requests that actually failed on the pods under pressure, which means going from metrics to traces to logs without stopping in between.

FROM logs-*.otel-*
| WHERE trace_id IN (
      FROM traces-*.otel-*
      | WHERE kind == "Server"
        AND status.code == "Error"
        AND resource.attributes.k8s.pod.uid IN (
            TS metrics-kubeletstats.otel-*
            | STATS peak = MAX(MAX_OVER_TIME(metrics.k8s.pod.memory_limit_utilization))
                BY resource.attributes.k8s.pod.uid
            | WHERE peak > 0.95
            | KEEP resource.attributes.k8s.pod.uid
          )
      | STATS failures = COUNT(*) BY trace_id
      | SORT failures DESC
      | LIMIT 1000
      | KEEP trace_id
    )
| STATS lines = COUNT(*), traces = COUNT_DISTINCT(trace_id)
    BY pattern = CATEGORIZE(body.text), service = resource.attributes.service.name
| SORT traces DESC

Reading the query inside out, you can see each layer answering one part of the question. The innermost subquery identifies the pods whose memory peaked above 95% of their limit, using MAX_OVER_TIME to take each pod's peak rather than its average. The middle one narrows to the requests that actually failed on those pods and reduces them to at most 1,000 trace IDs. The outer query then collects the logs for those traces from every service that took part, including services running on pods that were entirely healthy, and that last part turns out to be where the answer is.

The pods are matched on their UID rather than their name, because names repeat across namespaces and restarts. The query assumes Kubernetes metadata reaches your spans, which is what the collector's k8sattributes processor is for; if it does not, the host or container ID works the same way.

From the results above, the first row sits at exactly 1,000 because that is the subquery's LIMIT, so it describes the size of the sample rather than the size of the incident. Every trace in that sample carries the cart deadline line, which is the symptom you already knew about when you started.

Look at the second row instead. product-catalog is rejecting oversized payloads across 946 of the same 1,000 traces, and it never appeared in the pod subquery at all: its pods peaked at 75% of their memory limit, well under the 95% threshold. The rollout started sending larger payloads, which would account for both cart's memory climb and the failures. Checkout's retry budget gives out in about half of them, which is how the failure became visible to users.

Filtering logs directly by the pressured pods would have shown you the cart line and hidden the product-catalog one, which is to say it would have confirmed the symptom and buried the cause. Doing it without subqueries means three queries and two hand-built lists, and the second list is a thousand trace IDs. That is usually the point at which people stop after the first hop and go with the cart theory. The reason the second hop is cheap here is that all three signals sit in the same store behind the same query language, so widening from pods to traces to every service in the trace is a clause, not a project.

Why do ES|QL subqueries matter for AI agents?

Keeping the intermediate set inside the cluster is convenient for a person and close to essential for an agent querying on your behalf.

Split across two tool calls, the intermediate result has to travel. A list of 500 trace IDs comes back in a tool response and occupies the model's context, and the agent then has to rewrite every one of them into the next query. That costs tokens on every hop, and it is where truncation and transcription errors come from. With a subquery, the intermediate set stays inside Elasticsearch and the agent only ever sees the final table.

The problem compounds when the signals are spread across systems. An agent then needs credentials, a client, and a working knowledge of the query language for each one, plus the judgment to join results that use different names for the same host. One store and one query language reduce that to a single skill the agent has to be good at.

One ES|QL string is also a complete description of the correlation, which makes the investigation reproducible: an agent can put the query in its summary, and a human can paste it into Discover and get the same logic evaluated against current data. A two-call sequence with a hardcoded host list in the middle gives you neither. The one thing to watch is that an agent calling the _query API has to filter @timestamp itself, since nothing is binding a time picker.

Four things to know before writing ES|QL IN subqueries

IN subqueries are in technical preview in Elastic Stack 9.5 and on Elastic Cloud Serverless, while TS and FORK have been generally available since 9.4. CATEGORIZE has been generally available since 9.1 and requires a Platinum license; every query above works without it if you group by an existing field instead.

Four things are worth knowing before you write your own:

• In 9.5 the subquery returns exactly one column, which is what the trailing KEEP does in each example.
• Aggregate the subquery down to distinct values with STATS ... BY before returning them. Its result is materialized for the outer query to filter against, so handing back a few hundred host names instead of a few million rows is both faster and safer.
• Filter nulls out of any NOT IN subquery, because SQL null semantics mean one null makes the predicate match nothing.
• Subqueries are non-correlated. They run independently and cannot reference columns from the outer query, so this is a set filter rather than a row-by-row join. Reach for LOOKUP JOIN when you need per-row enrichment, which we covered in ES|QL joins for richer observability.

Try ES|QL signal correlation on your own data

The pattern under all four examples is the same. You start with a set you can describe in one signal and a question you can only answer in another, and the subquery carries that set across the boundary for you.

The syntax is the smaller part of what makes that work. It works because logs, metrics, and traces sit in one store behind one query engine, under field names they kept on the way in, so crossing from one signal to another is a clause in a query rather than an integration to build and maintain. Where that is not true, the same four investigations turn into a sequence of exports, translations, and manual joins, and that costs more than slower answers. It quietly shrinks the number of questions anyone is willing to ask, and the negative results are the first to go.

Each pattern here replaces two or three queries with one, and no host list or trace ID list has to move between them. Fewer steps mean fewer places to be wrong, and a correlation you can save as a single string and hand to someone else.

To try it:

1. Open an Observability project on Elastic Cloud Serverless, or upgrade to Elastic Stack 9.5.
2. Send data with the Elastic Distributions of OpenTelemetry, or point an existing collector at Elasticsearch.
3. In Discover, switch to ES|QL and start from the metrics to logs query above, swapping in your own data streams and thresholds.
4. Read the IN subquery reference for the full set of commands you can use inside a subquery.