aggs
Description
Performs statistical operations on data using internal storage mechanisms.
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 percentage. |
median | median(<field>) | Calculates the median for the specified field. |
Optional Arguments
| Parameter | Syntax | Default | Description |
|---|---|---|---|
composite | composite=<boolean> | false | Allows the use of scrolling in aggregations to obtain all possible segments (buckets) with multiple queries (similar to scroll in stats). It can only be used if there is grouping (by fields). The number of segments (buckets) that will be returned is fixed—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_*_*)
Aggregations in OpenSearch perform statistical processing on numeric fields or keyword if the field is text-based. For text fields, you need to append <field-name>.keyword, which should be done for both specified and by fields. Exceptions are specified fields in functions like earliest and latest. 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 using by fields in a query, a row is returned for each unique value of the by field, containing that value and the results of the statistical functions. Since this example uses three by fields (event.code, status, and host), each unique combination of these values will be on a separate row. The sort command sorts by the count field by default.
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 list of hosts and the count of actions for each user and their action on the hosts.
source wineventlog
| aggs count, values(host.name.keyword) as hosts by user.name.keyword, event.action.keyword
For each unique pair event.action and user.name, the query computes the count of events 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, the response.status field in the data contains HTTP status values. After executing the command, the system will return pairs: 200 and a list of unique source.address.keyword values (where response.status = 200), 403 and a list of unique source.address.keyword values (where response.status = 403), and similarly for response.status = 401:
... | 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
List of unique values for user.keyword and action.keyword that share the same combination of user.keyword and action.keyword field values:
...
| 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 (user.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
In this example, the system will return the value of the user field that occurs in 50 percent of cases:
... | aggs perc(user, 50)
In this example, the system groups data by delivery status status and for each group returns an array of unique vehicle identifiers vehicle, calculates the total distance distance, and determines the delivery time delivery_time that occurs in 90 percent of cases:
... | 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_id.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 | 1500 | 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 |
It is important to note that this query uses the numeric aggregation type max – meaning the calculation will be performed on numeric fields. In this case, two fields match the pattern: audit_delivery_status and audit_delivery_time. However, the values in the audit_delivery_status field are not numeric, so the calculation was performed only on the audit_delivery_time field.