aggs
Description
Performs statistical operations on data using internal storage mechanisms.
When a search across a Space includes multiple Data Units, only count, sum, min, max, avg, values, and dc are supported. values and dc are subject to limits on the number of unique values. If some Data Units return no data, the aggregate is calculated from the data that was returned. For details, see Aggregations Across a Space.
Using aggs in a query is valid only if it follows commands that also use internal storage mechanisms. These include source, inputlookup, search, and peval. This condition must also hold for all subqueries within the query.
Syntax
| aggs [composite=<boolean>] <functions-expression> ["," <functions-expression>] [<by_expression>]
Required Arguments
At least one function must be used:
| Parameter | Syntax | Description |
|---|---|---|
count | count | count(<field>) | Calculates the number of events containing a field. If no field is specified, it calculates the total number of events. |
values | values(<field>) | Computes an array of unique values for the given field. |
avg | avg(<field>) | Computes the average value for the given field. |
dc | dc(<field>) | Counts the number of unique values in the specified field. |
earliest | earliest(<field>) | Computes the field value for the earliest event. An optional second parameter is the name of the timestamp field (default: @timestamp). |
latest | latest(<field>) | Computes the field value for the latest event. An optional second parameter is the name of the timestamp field (default: @timestamp). |
max | max(<field>) | Calculates the maximum value for the given field. |
min | min(<field>) | Calculates the minimum value for the given field. |
sum | sum(<field>) | Computes the sum of values for the given field. |
perc | perc(<field>, <percent>) | Calculates the percentile for the specified field and percentile value. |
median | median(<field>) | Calculates the median for the specified field. |
Optional Arguments
| Parameter | Syntax | Default | Description |
|---|---|---|---|
composite | composite=<boolean> | false | Allows scrolling in aggregations to retrieve all segments (buckets) across multiple queries (similar to scroll in stats). Use this argument only when grouping by fields (by). The maximum number of segments (buckets) in the response is 1000. |
<by_expression> | <by_field> ["," <by_field> ...] | The name of the field (or fields) for grouping values. |
The composite argument is available when querying OpenSearch.
The * symbol is supported for bulk field selection based on a pattern in aggregation functions. It can be used to select all fields matching the pattern, as well as to rename them using aliases.
Usage specifics in aggregations:
- when using numerical aggregations (
avg,sum,min,perc, etc.), only numerical fields will be considered. If the pattern matches other field types, the system will warn the user that these fields are excluded from the calculation - multiple uses of
*within a single pattern are allowed - the number of fields for any aggregation is limited by the cluster configuration parameter
max_fields_under_pattern, which defaults to 250
... | aggs avg(audit_*) as *
... | aggs count(audit_*_*)
In OpenSearch, aggregations operate on numeric fields and use the keyword type for text fields. Therefore, append the .keyword suffix to text field names both in function arguments and in by fields. The exception is fields specified in the earliest and latest functions. For example:
...
| aggs avg(user.keyword), earliest(user) by event.keyword, user_count
Query Examples
The examples below use datasets for mapping the .keyword field. If default indexing is configured, the .keyword suffix must be specified.
A text field is broken down into individual words during indexing, allowing for partial matches to be found (e.g., finding "cat" within the word "kitten"). This is convenient for full-text search but requires more resources.
A keyword field is not tokenized—it is stored and searched as a whole. It is suitable for exact matches (e.g., searching for "Dubai" only as a complete value). It performs faster.
Basic Examples
Example 1
Calculating the count of events for each combination of DNS status code and host name.
source winlogbeat_dns
| aggs count by event.code, host.keyword
| sort - count
When by fields are used, the query returns a row for each unique combination of their values. This example groups by two fields: event.code and host.keyword. By default, the sort command sorts by the count field.
The result of the query could be the following table:
| count | event.code | host.keyword |
|---|---|---|
| 1595 | 3 | VLG-DC-01 |
| 1498 | 5504 | MSK-DC-02 |
| 356 | 4015 | SPB-DC-03 |
| 254 | 4013 | VLG-DC-01 |
| 123 | 4007 | SPB-DC-03 |
| 98 | 4013 | MSK-DC-02 |
| 79 | 5504 | SPB-DC-03 |
Example 2
Calculating the number of actions and the list of hosts for each user and action.
source wineventlog
| aggs count, values(host.name.keyword) as hosts by user.name.keyword, event.action.keyword
For each unique combination of user.name.keyword and event.action.keyword, the query computes the event count and a list of unique hosts, stored in the count and hosts fields, respectively.
The result of the query could be the following table:
| user.name.keyword | event.action.keyword | count | hosts |
|---|---|---|---|
| IvanovMG | credential-validated | 41 | MSK-WS-01 SPB-WS-02 |
| IvanovMG | logged-in | 26 | SPB-WS-02 VLG-WS-03 KZN-WS-05 |
| IvanovMG | logged-out | 25 | SPB-WS-02 VLG-WS-03 KZN-WS-05 |
| PetrovSI | logged-out | 33 | SPB-WS-02 KZN-WS-05 |
| PetrovSI | added-member-to-group | 6 | MSK-WS-01 |
| AndreevAE | added-member-to-group | 14 | SPB-WS-02 VLG-WS-03 |
Advanced Examples
Example 1
Getting a list of unique values for the user field:
... | aggs values(user.keyword)
Returns an array of unique values from the user.keyword field across all documents matching the query.
Sample input data:
| user | source.address | event |
|---|---|---|
| IvanovMG | 192.168.1.10 | login |
| PetrovSI | 192.168.1.15 | logout |
| IvanovMG | 192.168.1.20 | login |
| AndreevAE | 10.0.0.1 | password_reset |
| SidorovVA | 10.0.0.5 | password_reset |
The query execution result may be the following table:
| values (user.keyword) |
|---|
| IvanovMG PetrovSI AndreevAE SidorovVA |
Example 2
Getting a list of unique values for the user field for each unique event field value.
In this example, the event field in the data has values start and end. After running the command, the system will return pairs of results: start with a list of unique user values (event = "start"), and end with a list of unique user values (event = "end").
... | aggs values(user.keyword) by event.keyword
The query execution result may be the following table:
| event.keyword | values (user.keyword) |
|---|---|
| start | Ivanov Petrov |
| end | Andreev |
Example 2.2
In this example, response.status contains HTTP status codes. The query returns a list of unique source.address.keyword values for each code:
... | aggs values(source.address.keyword) by response.status
Sample input data:
| user.keyword | response.status | source.address.keyword | request.referrer.keyword | network.bytes |
|---|---|---|---|---|
| IvanovMG | 200 | 192.168.1.10 | /data | 2048 |
| PetrovSI | 200 | 192.168.1.15 | /login | 1024 |
| IvanovMG | 403 | 175.100.1.20 | /data | 512 |
| AndreevAE | 401 | 10.0.0.1 | /config | 128 |
The query execution result may be the following table:
| response.status | values (source.address.keyword) |
|---|---|
| 200 | 192.168.1.10 192.168.1.15 |
| 403 | 175.100.1.20 |
| 401 | 10.0.0.1 |
Example 3
The following examples will use input data from user action audits, a portion of which is presented below. The dataset used in the examples does not have index mapping configured.
| l_time | host | user | target | action | outcome |
|---|---|---|---|---|---|
| 2025-05-30 13:47:08 | ws-01 | smith | ford | locked-out-user-account changed-password added-member-to-group | success |
| 2025-05-30 13:47:08 | ws-01 | avdeev | ford | reset-password changed-password | failure |
| 2025-05-30 13:47:08 | ws-01 | dawson | cooper | enabled-user-account | success |
| 2025-05-30 13:47:08 | ws-02 | holmes | jenkins | changed-password | failure |
| 2025-05-30 13:47:08 | ws-01 | owen | turner | removed-member-from-group | success |
| 2025-05-30 13:47:08 | ws-01 | tayler | watson | added-member-to-group | failure |
| 2025-05-30 13:47:08 | ws-02 | wright | lloyd | removed-member-from-group | failure |
Example 3.1
List of all unique values for the user and action fields:
...
| aggs values(user.keyword), values(action.keyword)
Returns arrays of unique values for user.keyword and action.keyword independently of each other.
The query execution result may be the following table:
| values (user.keyword) | values (action.keyword) |
|---|---|
| denisov avdeev abramova popov shishkin | locked-out-user-account reset-password enabled-user-account changed-password removed-member-from-group added-member-to-group |
Example 3.2
Lists of unique values for user.keyword and action.keyword, grouped by each unique combination of these fields:
...
| aggs values(user.keyword), values(action.keyword) by user.keyword, action.keyword
Groups documents by unique combinations of user.keyword and action.keyword, returning the values of these fields for each group.
The query execution result may be the following table:
| user.keyword | action.keyword | values (user.keyword) | values (action.keyword) |
|---|---|---|---|
| denisov | locked-out-user-account | denisov | locked-out-user-account changed-password added-member-to-group |
| denisov | changed-password | denisov | locked-out-user-account changed-password added-member-to-group |
| denisov | added-member-to-group | denisov | locked-out-user-account changed-password added-member-to-group |
| avdeev | reset-password | avdeev | reset-password changed-password |
| avdeev | changed-password | avdeev | reset-password changed-password |
| abramova | enabled-user-account | abramova | ["enabled-user-account"] |
| popov | changed-password | popov | changed-password |
| abramova | removed-member-from-group | abramova | removed-member-from-group |
| denisov | added-member-to-group | denisov | added-member-to-group |
| shishkin | removed-member-from-group | shishkin | removed-member-from-group |
Example 3.3
Number of documents containing the user.keyword field:
... | aggs count(user.keyword)
The query execution result may be the following table:
| count (user.keyword) |
|---|
| 5 |
Example 3.4
Number of documents containing the action.keyword field for each unique value of the user.keyword field:
... | aggs count(action.keyword) by user.keyword
The query execution result may be the following table:
| user.keyword | count (action.keyword) |
|---|---|
| denisov | 955 |
| avdeev | 34 |
| abramova | 1007 |
| popov | 1022 |
| shishkin | 873 |
Example 3.5
Number of documents containing the user.keyword field and number of documents containing the action.keyword field:
... | aggs count(user.keyword), count(action.keyword)
The query execution result may be the following table:
| count (user.keyword) | count (action.keyword) |
|---|---|
| 5 | 3891 |
Example 3.6
Number of documents containing the user.keyword field for each unique combination of user.keyword and action.keyword field values:
... | aggs count(user.keyword) by user.keyword, action.keyword
The query execution result may be the following table showing the first 7 entries:
| user.keyword | action.keyword | count (user.keyword) |
|---|---|---|
| denisov | locked-out-user-account | 201 |
| denisov | changed-password | 40 |
| denisov | added-member-to-group | 46 |
| shishkin | locked-out-user-account | 271 |
| popov | locked-out-user-account | 511 |
| denisov | disabled-user-account | 23 |
| abramova | added-user-account | 51 |
| avdeev | reset-password | 1 |
| avdeev | changed-password | 2 |
| popov | added-user-account | 59 |
Example 3.7
In this example, the system will return the contents of the action.keyword field for the first record in the sample, the contents of the action.keyword field for the last record in the sample, and the average value of the action_count field (which was calculated by the command from the previous example) for each unique user.keyword:
... | aggs count(user.keyword) as action_count by user.keyword, event.keyword
| aggs first(action.keyword), last(action.keyword), avg(action_count.keyword) by user.keyword
The query execution result may be the following table:
| user.keyword | first (action.keyword) | last (action.keyword) | avg (action_count.keyword) |
|---|---|---|---|
| denisov | locked-out-user-account | changed-password | 95.8 |
| abramova | locked-out-user-account | changed-password | 100.1 |
| shishkin | removed-member-from-group | reset-password | 102.2 |
| popov | removed-member-from-group | reset-password | 113.22 |
| avdeev | changed-password | reset-password | 1.5 |
Example 3.8
In this example, the system will return the contents of the action.keyword field for the earliest record in the sample and the contents of the user field for the latest record in the sample:
... | aggs earliest(action.keyword), latest(user.keyword) by @timestamp
The query execution result may be the following table showing the first 7 entries:
| @timestamp | earliest (action.keyword) | latest (user.keyword) |
|---|---|---|
| 2025-05-30T10:20:00.000000+0000 | removed-member-from-group | abramova |
| 2025-05-30T10:21:00.000000+0000 | added-member-to-group | abramova |
| 2025-05-30T10:22:00.000000+0000 | removed-member-from-group | denisov |
| 2025-05-30T10:23:00.000000+0000 | deleted-user-account | popov |
| 2025-05-30T10:24:00.000000+0000 | enabled-user-account | abramova |
| 2025-05-30T10:25:00.000000+0000 | locked-out-user-account | abramova |
| 2025-05-30T10:26:00.000000+0000 | unlocked-user-account | shishkin |
Example 4
Number of unique values in the user field:
... | aggs dc(user)
Sample input data
| user |
|---|
| Ivanov |
| Petrov |
| Andreev |
| Ivanov |
The query execution result may be the following table:
| dc(user) |
|---|
| 3 |
Example 5
This example calculates the 50th percentile of the user field values:
... | aggs perc(user, 50)
This example groups data by delivery status status. For each group, the query returns a list of unique vehicles in vehicle, the sum of the distance values, and the 90th percentile of delivery_time:
... | aggs values(vehicle.keyword), sum(distance), perc(delivery_time, 90) by status.keyword
Sample input data:
| vehicle | status | distance | delivery_time | @timestamp |
|---|---|---|---|---|
| MTL Pounder | delivered | 100 | 24 | 2025-04-30T10:00:00Z |
| Maibatsu Mule | delivered | 150 | 30 | 2025-04-30T10:10:00Z |
| MTL Pounder | in_transit | 200 | 48 | 2025-04-30T10:20:00Z |
| Jobiult Hauler | in_transit | 120 | 36 | 2025-04-30T10:30:00Z |
| Maibatsu Mule | delivered | 80 | 28 | 2025-04-30T10:40:00Z |
| Vapid Benson | pending | 50 | 12 | 2025-04-30T10:50:00Z |
The query execution result may be the following table:
| status.keyword | values (vehicle.keyword) | sum (distance) | perc (delivery_time, 90) |
|---|---|---|---|
| delivered | MTL Pounder Maibatsu Mule | 330 | 29.6 |
| in_transit | MTL Pounder Jobiult Hauler | 320 | 46.8 |
| pending | Vapid Benson | 50 | 12 |
Example 6
In this example, the system groups data by combinations of transaction type transaction_type and currency currency, and for each group counts the number of unique clients client, calculates the total transaction amount amount, and returns the identifier of the earliest transaction transaction_id:
... | aggs dc(client.keyword), sum(amount), earliest(transaction_id) by transaction_type.keyword, currency.keyword
Sample input data:
| client | transaction_type | currency | amount | transaction_id | @timestamp |
|---|---|---|---|---|---|
| Ivanov | deposit | USD | 1000 | 1 | 2025-04-30T10:00:00Z |
| Petrov | deposit | USD | 500 | 2 | 2025-04-30T10:10:00Z |
| Ivanov | withdrawal | EUR | 200 | 3 | 2025-04-30T10:20:00Z |
| Andreev | deposit | EUR | 400 | 4 | 2025-04-30T10:30:00Z |
| Petrov | withdrawal | USD | 300 | 5 | 2025-04-30T10:40:00Z |
| Ivanov | deposit | USD | 600 | 6 | 2025-04-30T09:50:00Z |
The query execution result may be the following table:
| transaction_type.keyword | currency.keyword | dc (client.keyword) | sum (amount) | earliest (transaction_id) |
|---|---|---|---|---|
| deposit | USD | 2 | 2100 | 6 |
| deposit | EUR | 1 | 400 | 4 |
| withdrawal | EUR | 1 | 200 | 3 |
| withdrawal | USD | 1 | 300 | 5 |
Example 7
This example will perform several calculations – for each field matching the pattern audit_* (all fields whose names start with audit_). The output name will be formed from the dynamic part of the field name * with the suffix _count:
... | aggs count(audit_*) as *_count
Sample input data:
| audit_name | audit_status | distance | delivery_time | @timestamp |
|---|---|---|---|---|
| MTL Pounder | delivered | 100 | 24 | 2025-04-30T10:00:00Z |
| Maibatsu Mule | delivered | 150 | 30 | 2025-04-30T10:10:00Z |
| MTL Pounder | in_transit | 200 | 48 | 2025-04-30T10:20:00Z |
| Jobiult Hauler | in_transit | 120 | 36 | 2025-04-30T10:30:00Z |
| Maibatsu Mule | delivered | 80 | 28 | 2025-04-30T10:40:00Z |
| Vapid Benson | pending | 50 | 12 | 2025-04-30T10:50:00Z |
The query execution result might be the following table:
| name_count | status_count |
|---|---|
| 6 | 6 |
Example 8
This example uses a more complex field pattern containing two * wildcards:
... | aggs max(audit_*_*) as *_count_*
Sample input data:
| audit_name | audit_delivery_status | audit_distance | audit_delivery_time | @timestamp |
|---|---|---|---|---|
| MTL Pounder | delivered | 100 | 24 | 2025-04-30T10:00:00Z |
| Maibatsu Mule | delivered | 150 | 30 | 2025-04-30T10:10:00Z |
| MTL Pounder | in_transit | 200 | 48 | 2025-04-30T10:20:00Z |
| Jobiult Hauler | in_transit | 120 | 36 | 2025-04-30T10:30:00Z |
| Maibatsu Mule | delivered | 80 | 28 | 2025-04-30T10:40:00Z |
| Vapid Benson | pending | 50 | 12 | 2025-04-30T10:50:00Z |
The query execution result might be the following table:
| delivery_count_time |
|---|
| 48 |
The max function works with numeric fields. In this example, the pattern matches audit_delivery_status and audit_delivery_time. The values in audit_delivery_status are not numeric, so the calculation is performed only for audit_delivery_time.