ES|QL TS command

The TS source command is similar to the FROM source command, with the following key differences:

TS index_pattern [METADATA fields]
		
index_pattern

A list of indices, data streams or aliases with the time_series index mode. Supports wildcards and date math. Wildcards only match time series indices and silently skip everything else.

index_pattern can't reference views or subqueries, even if they're backed by time series indices. If you name a view explicitly, the query fails. To query a view, use FROM instead.

fields
A comma-separated list of metadata fields to retrieve.

The TS source command enables time series semantics and adds support for time series aggregation functions to the STATS command, such as AVG_OVER_TIME(), or RATE. These functions are implicitly evaluated per time series, then aggregated by group using a secondary aggregation function. For an example, refer to Calculate the rate of search requests per host.

This paradigm (a pair of aggregation functions) is standard for time series querying. For supported inner (time series) functions per metric type, refer to ES|QL time series aggregation functions. These functions also apply to downsampled data, with the same semantics as for raw data.

Tip

To discover which metrics, metric types, and dimensions are available before writing a TS query, use METRICS_INFO or TS_INFO.

Note

If a query is missing an inner (time series) aggregation function, TS selects a default based on the field type. Numeric fields use LAST_OVER_TIME(), while histogram fields are merged . For example, two equivalent queries that return the average of the last memory usage values per time series are shown in Aggregate with implicit LAST_OVER_TIME. To calculate the average memory usage across per-time-series averages, refer to Calculate the average of per-time-series averages. For histogram examples, refer to Work with histogram metrics.

You can use time series aggregation functions directly in the STATS command ( ). The output will contain one aggregate value per time series and time bucket (if specified). For an example, refer to Use time series aggregation functions directly.

You can also combine time series aggregation functions with regular aggregation functions such as SUM(), as outer aggregation functions. For examples, refer to Combine SUM and RATE and Combine SUM and AVG_OVER_TIME.

However, using a time series aggregation function in combination with an inner time series function causes an error. For an example, refer to Invalid query: nested time series functions.

If there is no STATS command in the query, the output of the TS command gets sorted by @timestamp in descending order by default. This helps listing recent values across many time series, as opposed to listing the results based on index sort configuration that may just return data points for a single time series.

TS is the right choice for aggregating metrics. It's optimized for time series data and avoids correctness issues that FROM | STATS runs into on raw metric data. The two most common examples where FROM silently produces wrong results that TS handles correctly are counter handling and uneven publish intervals for metrics.

Counter metrics (such as a request_count that monotonically increases) record a running total, not per-interval events. Summing or averaging the raw counter column has no meaningful interpretation: the result reflects how many samples each series happened to publish, not the underlying rate. Counter resets on process restart make the problem worse by mixing pre-reset and post-reset values:

FROM metrics | STATS SUM(request_count) BY host
		

TS with RATE() or INCREASE() first computes the per-bucket delta for each time series (handling resets correctly along the way), then lets you aggregate those deltas across series:

TS metrics | STATS SUM(RATE(request_count)) BY host
		

Different hosts and agents publish at different cadences. A host emitting CPU samples every second contributes 120x more rows than one emitting every two minutes, so AVG() over the raw column weights the chatty host accordingly:

FROM metrics | STATS AVG(cpu_usage) BY cluster
		

TS first reduces each time series to a single value per time bucket (using AVG_OVER_TIME or another inner function), then aggregates across series. Each series contributes equally to the outer average:

TS metrics
| STATS AVG(AVG_OVER_TIME(cpu_usage)) BY cluster, TBUCKET(1 minute)
		

Histogram metrics contain distributions rather than individual numeric samples. With TS, use regular aggregation functions such as COUNT, SUM, AVG, MIN, MAX, MEDIAN, and PERCENTILE directly on histogram metrics. TS implicitly merges the histograms for each time series before aggregating across time series. Using *_OVER_TIME functions on histogram fields is typically not useful.

For an exponential_histogram or tdigest field, the following query calculates the number of garbage collection events, along with their average and 99th-percentile duration, in each time bucket:

TS metrics-*
| WHERE TRANGE(1 hour)
| STATS count = COUNT(jvm.gc.duration),
        avg = AVG(jvm.gc.duration),
        p99 = PERCENTILE(jvm.gc.duration, 99)
  BY jvm.gc.action, TBUCKET(5 minutes)
		

If your metric has mixed types (for example, newer indices use exponential_histogram, while legacy indices use histogram) or has the type histogram, you must add an explicit cast: jvm.gc.duration::exponential_histogram. Typically, ::exponential_histogram is the right choice, unless you know that your metric actually stores T-Digest data.

You can use METRICS_INFO to inspect the field type.

For the full list of supported aggregation functions, casting between histogram types, and using FROM with histogram fields, refer to Query histogram fields in ES|QL.

When the first STATS after TS uses a bare time series aggregation function (that is, a time series function not wrapped in an outer aggregation such as AVG() or SUM()), the rows are implicitly grouped by all dimensions of each time series. The result set includes a _timeseries keyword column that contains a JSON-encoded object with the dimension key/value pairs identifying each group. Only the dimensions that are actually present for a given time series are included — not every dimension declared in the index mappings — so different rows in the result may carry different dimension keys. For an example, refer to Group by all dimensions implicitly.

You can make this grouping explicit, or narrow it to a subset of dimensions, using the WITHOUT grouping function in the BY clause ( ):

  • BY WITHOUT(dim1, dim2, ...) groups by all dimensions except those listed. See Exclude dimensions with WITHOUT.
  • BY WITHOUT() (no arguments) explicitly groups by every dimension; it is equivalent to the implicit "group by all" behavior.

When combining a bare time series function with other groupings, only grouping functions (such as TBUCKET or WITHOUT) are allowed in the BY clause — bare dimension columns are not. For example, TS k8s | STATS rate(network.total_bytes_in) BY host is rejected; use BY TBUCKET(1 hour) or wrap the time series function with an outer aggregation instead.

Note

WITHOUT can only be used inside the first STATS command under TS source. Using it in a FROM | STATS ... BY WITHOUT(...) query leads to an error.

Exemplars are individual measurements recorded alongside aggregated metrics, for example the latency of a single request together with the trace and span it belongs to. They let you drill down from a metric to the requests behind it. Exemplars are typically ingested through OTLP or Prometheus remote write, which store them in exemplars-* data streams next to the metrics-* data streams of the metrics (refer to xpack.metrics.exemplars.enabled).

With the exemplars setting, a TS ... | STATS query returns the exemplars that are relevant to it instead of its result. This lets tools such as Kibana show the exemplars of an existing metrics query by issuing the same query again with the exemplars setting enabled:

SET exemplars=true;
TS metrics-cpu
| WHERE attributes.state == "idle"
| STATS AVG(cpu_time) BY attributes.cpu
		
@timestamp:date metric_name:keyword value:double trace_id:keyword span_id:keyword _source
2024-05-10T00:02:20.000Z cpu_time 2.9 trace-idle-cpu1-cpu span-5 {"@timestamp": ..., "attributes": {"cpu": "cpu1", "state": "idle"}, ...}
2024-05-10T00:00:10.000Z cpu_time 1.7 trace-idle-cpu0-cpu span-1 {"@timestamp": ..., "attributes": {"cpu": "cpu0", "state": "idle"}, ...}

The setting works the same way for PROMQL. Instead of prepending SET, you can also pass the setting in the settings object of the request body, which leaves the query itself unchanged:

{
  "query": "TS metrics-cpu | STATS AVG(cpu_time) BY attributes.cpu",
  "settings": { "exemplars": true }
}
		

The metrics query is not executed. Instead, ES|QL statically analyzes it and finds the relevant exemplars using heuristics that take into account the metrics the query aggregates and its filters on @timestamp and on dimensions.

The exemplars are read from the exemplar data streams that belong to the metrics data streams the query targets: the exemplars of metrics-<name> are stored in exemplars-<name>. Aliases and wildcards are resolved to the underlying data streams first. A metrics data stream without an exemplar data stream, or one whose name does not start with metrics-, contributes no exemplars, but doesn't cause an error.

Important

The heuristics might change between versions, so the same query can return a different set of exemplars after an upgrade. If you need stable results, query the exemplar data streams directly with FROM instead:

FROM exemplars-cpu
| WHERE metric_name == "cpu_time" AND attributes.state == "idle"
| SORT @timestamp DESC
		

The result contains the following columns:

Column Description
@timestamp When the exemplar was recorded
metric_name The metric the exemplar belongs to, without the name of its passthrough object. For example, cpu_time for the metric metrics.cpu_time
value The observed value
trace_id The trace the exemplar was recorded in
span_id The span the exemplar was recorded in
_timeseries Only with include_timeseries. The dimensions of the time series the exemplar belongs to, as a JSON string
_source The full exemplar document, including the additional labels recorded with it

The most recent exemplars are returned first. Unless you configure a limit, the default result set size of a query applies. Use a map instead of true to configure the number of exemplars or to add the _timeseries column:

SET exemplars={"limit": 100, "include_timeseries": true};
TS metrics-cpu | STATS AVG(cpu_time) BY attributes.cpu
		
  • One metrics command: The query can contain at most one TS ... | STATS or PROMQL command, otherwise it is rejected. A query with neither returns no exemplars.
  • Views: Queries that use views are rejected.
  • No processing of exemplars: Commands after the metrics query are part of the metrics query, so they can't filter or transform the returned exemplars: For example, filters on trace_id or the exemplar value are not possible. To process exemplars, query the exemplar data streams with FROM.
  • Filtered aliases: Exemplar data streams are found by the names of the metrics data streams, not by alias. The filter of a filtered alias over the metrics doesn't apply to their exemplars. A user who can only read the metrics through such an alias gets no exemplars, unless they are allowed to read the exemplar data stream directly, which gives them access to all of its exemplars.
  • Privileges: Reading exemplars requires the read privilege on the exemplar data streams. Exemplar data streams the user can't read contribute no exemplars.
  • Cross-cluster search: Remote clusters must run version 9.3 or later.
  • Prefer TS over FROM for time series aggregations. FROM is still appropriate for non-aggregation uses such as listing document contents.
  • Avoid aggregating multiple metrics in the same query when those metrics have different dimensional cardinalities. For example, in STATS max(rate(foo)) + rate(bar)), if foo and bar don't share the same dimension values, the rate for one metric will be null for some dimension combinations. Because the + operator returns null when either input is null, the entire result becomes null for those dimensions. Additionally, queries that aggregate a single metric can filter out null values more efficiently.
  • Add a time range filter on @timestamp to limit the data volume scanned and improve query performance.
  • Time series aggregations produce large result sets, especially if they involve many dimensions and small time buckets. The limits are updated accordingly, with the default result truncation size increased to 10,000 rows. For more information on the limits and how to adjust them, refer to Result set size limitation.
  • COUNT(*) under TS: After the first STATS under TS, rows are grouped per time series rather than counted as raw metric documents, so COUNT(*) does not have the same meaning it does under FROM. To count samples for a specific metric, use COUNT(<field>) or COUNT_OVER_TIME() against a named field.
  • Commands that reorder rows: Commands that change row order, such as SORT or FORK, cannot appear between TS and the first STATS. Time series aggregations rely on the natural @timestamp ordering produced by TS. After the first STATS, results are tabular and these commands are available again — see Process tabular results after the first STATS.
  • Metric type compatibility: Time series aggregation functions enforce the field's metric_type mapping. For example, RATE() requires a counter and rejects gauge fields. See ES|QL time series aggregation functions > Metric type compatibility for the per-function mapping.

The following examples demonstrate common time series query patterns using TS.

Calculate the total rate of search requests (tracked by the search_requests counter) per host and hour. The RATE() function is applied per time series in hourly buckets. These rates are summed for each host and hourly bucket (since each host can map to multiple time series):

TS metrics
  | WHERE @timestamp >= now() - 1 hour
  | STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
		

The following two queries are equivalent, returning the average of the last memory usage values per time series. If a query on a numeric field is missing an inner (time series) aggregation function, LAST_OVER_TIME() is assumed and used implicitly:

TS metrics | STATS AVG(memory_usage)

TS metrics | STATS AVG(LAST_OVER_TIME(memory_usage))
		

This query calculates the average memory usage across per-time-series averages, rather than the average of all raw values:

TS metrics | STATS AVG(AVG_OVER_TIME(memory_usage))
		

You can use a time series aggregation function directly in STATS ( ):

TS metrics
| WHERE TRANGE(1 day)
| STATS RATE(search_requests) BY TBUCKET(1 hour)
		

Use SUM as the outer aggregation to sum counter rates across groups:

TS metrics | STATS SUM(RATE(search_requests)) BY host
		

Use AVG_OVER_TIME to compute per-time-series averages, then group the results by host and time bucket:

TS metrics
| WHERE @timestamp >= now() - 1 day
| STATS SUM(AVG_OVER_TIME(memory_usage)) BY host, TBUCKET(1 hour)
		

Once the first STATS under TS completes, the pipeline becomes a regular ES|QL table. Chain additional STATS, EVAL, SORT, and similar commands to derive secondary aggregations or computed columns. For example, take per-minute rates per cluster, then compute the 95th-percentile and maximum rate per cluster and rank clusters by headroom:

TS k8s
| WHERE @timestamp >= now() - 1 hour
| STATS rate = MAX(RATE(network.total_bytes_in)) BY cluster, TBUCKET(1 minute)
| STATS p95 = PERCENTILE(rate, 95), peak = MAX(rate) BY cluster
| EVAL headroom = peak - p95
| SORT headroom DESC
		

When a bare time series aggregation function is used without a BY clause, results are implicitly grouped by all dimensions of each time series and include a _timeseries column with the dimension key/value pairs. Note how the qa cluster rows only carry cluster and pod keys, while the prod and staging rows also include region — only the dimensions that actually exist for a given time series appear in _timeseries:

TS k8s
| STATS avg = avg_over_time(network.cost)
| SORT avg DESC
		
avg:double _timeseries:keyword
8.375 {"cluster":"prod","pod":"three","region":["eu","us"]}
7.262931034482759 {"cluster":"qa","pod":"one"}
6.870689655172414 {"cluster":"qa","pod":"two"}
6.635416666666667 {"cluster":"qa","pod":"three"}
6.46875 {"cluster":"staging","pod":"two","region":"us"}
5.869791666666667 {"cluster":"staging","pod":"three","region":"us"}
5.586956521739131 {"cluster":"prod","pod":"one","region":["eu","us"]}
5.1826923076923075 {"cluster":"staging","pod":"one","region":"us"}
4.0703125 {"cluster":"prod","pod":"two","region":["eu","us"]}

Use WITHOUT ( ) in the BY clause to exclude specific dimensions from the time series grouping. For example, group by every dimension except pod:

TS k8s
| STATS total_cost = sum(network.cost) BY WITHOUT(pod)
| SORT total_cost
		
total_cost:double _timeseries:keyword
15.875 {"cluster":"staging","region":"us"}
18.625 {"cluster":"prod","region":["eu","us"]}
26.5 {"cluster":"qa"}

WITHOUT can be combined with TBUCKET to add a time bucket to the grouping — useful for producing per-interval aggregates across the surviving dimensions:

TS k8s
| STATS total_cost = sum(network.cost) BY WITHOUT(pod), tbucket = TBUCKET(1 hour)
| SORT total_cost
		
total_cost:double _timeseries:keyword tbucket:datetime
15.875 {"cluster":"staging","region":"us"} 2024-05-10T00:00:00.000Z
18.625 {"cluster":"prod","region":["eu","us"]} 2024-05-10T00:00:00.000Z
26.5 {"cluster":"qa"} 2024-05-10T00:00:00.000Z

Passing no arguments (WITHOUT()) is equivalent to grouping by all dimensions — the same as omitting the BY clause entirely. Refer to the WITHOUT function reference for more examples.

Using a time series aggregation function in combination with an inner time series function causes an error:

TS metrics | STATS AVG_OVER_TIME(RATE(memory_usage))