Inspect grouped STATS results in Discover

If you group results with STATS BY, you can look inside each group to see the documents behind a count or an average. When a query groups by one field, Discover shows each group as a row that you can expand, instead of a flat table with one row per group. Each row stands for one unique value of the grouping field, which is the field after BY.

• Make sure Discover is in ES|QL mode. Classic mode doesn't support this functionality. To switch, refer to Switch to Discover's ES|QL mode.
• If you're new to ES|QL in Discover, start with Get started with ES|QL in Discover.
• To get expandable groups, your query needs to group by a single field, or by a single CATEGORIZE call on a field. If your query has more than one STATS command, Discover uses the last one. The CATEGORIZE function requires a Platinum or Enterprise subscription or an active trial.
• These queries keep the standard flat results table:
  • Queries that group by more than one field, for example BY clientip, extension.keyword.
  • Queries that use other grouping functions, such as BUCKET or TBUCKET, or that nest a function inside CATEGORIZE.
  • Queries where a later KEEP command drops the grouping field.
  • Queries whose data source is a subquery.
  • Serverless Preview Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Preview This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. Stack Preview 9.5+ The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview since 9.5 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Queries that use TS_INFO or METRICS_INFO. Their rows describe metrics and have no documents to expand.

1. In Discover, in ES|QL mode, enter a STATS BY query with a single grouping field. For example:

   FROM kibana_sample_data_logs
   | STATS Visits = COUNT(*), AvgBytes = AVG(bytes) BY geo.dest
   | SORT Visits DESC
   

2. Select Search.

   Result: The table lists one row per group. The results count reports the number of groups instead of the number of documents.

   
   

3. Expand a row to inspect the underlying documents.

When the grouping field uses CATEGORIZE, each row title shows a detected pattern, which is a group of similar messages, with the changing words highlighted. This helps you scan repeated message structures at a glance.

FROM kibana_sample_data_logs
| STATS Count = COUNT(*) BY Pattern = CATEGORIZE(message)
| SORT Count DESC

When the query also computes a SPARKLINE over time, Discover renders a sparkline, which is a small trend chart, next to the row aggregates. For example, the following query categorizes log messages and renders a sparkline for each pattern:

FROM kibana_sample_data_logs
| WHERE @timestamp >= ?_tstart AND @timestamp < ?_tend
| STATS Count = COUNT(*),
        Sparkline = SPARKLINE(COUNT(*), @timestamp, 40, ?_tstart, ?_tend)
    BY Pattern = CATEGORIZE(message)
| SORT Count DESC

On larger data sets, add a SAMPLE command before STATS to keep the categorization fast. Because SAMPLE 0.001 keeps about one document in a thousand, divide COUNT(*) by the same fraction, Count = COUNT(*) / 0.001, so the counts stay representative.

Select the actions button on any group row to:

• Copy to clipboard: Copy the group's value.
• Filter in: Add a WHERE clause before the STATS command that keeps only documents matching this group.
• Filter out: Add a WHERE clause before the STATS command that excludes documents matching this group.
• Open in new tab: Open the documents in this group in a new Discover tab, with a query scoped to that group.

If the grouping field isn't filterable, Filter in and Filter out are unavailable.

If you prefer the flat results table, you can switch grouped results off. When grouped results are active, Discover replaces the regular results table toolbar with a Group by button that carries a badge and shows the number of active groupings. Discover preselects the grouping field from your STATS BY clause. To go back to the flat results table and the regular toolbar, open the Group by menu and select none. The Group by button stays in the toolbar of the flat table. To bring the grouping back, open the menu again and select the grouping field.

• How to query in Discover's ES|QL mode
• Run a pattern analysis on your log data
• STATS command reference
• Get faster results with approximate STATS