ES|QL TS command

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

• Targets only time series indices
• Enables the use of time series aggregation functions inside the STATS command

TS index_pattern [METADATA fields]
		

index_pattern

A list of indices, data streams or aliases. Supports wildcards and date math.

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.

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.

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.

• 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.
• Use the TS command for aggregations on time series data, rather than FROM. The FROM command is still available (for example, for listing document contents), but it's not optimized for processing time series data and may produce unexpected results.
• The TS command can't be combined with certain operations (such as FORK) before the STATS command is applied. Once STATS is applied, you can process the tabular output with any applicable ES|QL operations.
• 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.

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 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)
		

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
		

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
		

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
		

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))