stats
Description
Performs statistical operations on data.
The stats command supports a mode for handling large volumes of data without requiring additional memory.
Syntax
stats <functions-expression> ["," <functions-expression>] [<by_expression>]
Required Arguments
At least one of the following functions is required:
| Parameter | Syntax | Description |
|---|---|---|
count | count | count(<field>) | Calculates the number of events containing the field. If no field is specified, it counts the total number of events. |
values | values(<field>) | Calculates an array of unique values in a given field. |
avg | avg(<field>) | Calculates the average value in a given field. |
dc | dc(<field>) | Calculates the number of unique values in a given field. |
earliest | earliest(<field>) | Returns the field's value for the earliest event. An optional second parameter specifies the timestamp field name; the default is @timestamp. |
first | first(<field>) | Returns the first value in a given field. |
last | last(<field>) | Returns the last value in a given field. |
latest | latest(<field>) | Returns the field's value for the latest event. An optional second parameter specifies the timestamp field name; the default is @timestamp. |
list | list(<field>) | Calculates an array of all values in a given field. |
max | max(<field>) | Returns the maximum value in a given field. |
min | min(<field>) | Returns the minimum value in a given field. |
range | range(<field>) | Calculates the difference between the maximum and minimum values in a given field. |
stdev | stdev(<field>) | Calculates the standard deviation in a given field. |
sum | sum(<field>) | Calculates the sum of values in a given field. |
Optional Arguments
| Parameter | Syntax | Default | Description |
|---|---|---|---|
<by_expression> | <by_field> ["," <by_field> ...] | The field name(s) for grouping values. |
Query Examples
Basic Examples
Example 1
Calculate the maximum processor temperature for each host.
source apc_snmp
| stats max(cpu_temperature) as 'Maximum processor temperature' by host
In this example, the maximum value for the field cpu_processor_temperature is calculated for each unique host and the result is stored in the field 'Maximum processor temperature'.
The result of the query might be the following table:
| host | Maximum processor temperature |
|---|---|
| VLG-WS-01 | 87 |
| MSK-DB-02 | 58 |
| SPB-WS-03 | 46 |
Example 2
Calculate the number of requests and the list of methods used for each client and request.
source apache-*
| stats count as 'Number of Requests', values(method) as 'Used Methods' by user, path
| sort - 'Number of Requests'
In this example, the system calculates the number of requests and the list of methods used for each unique pair of user and path. Then, the results are sorted by Number of Requests.
The result of the query might be the following table:
| user | path | Number of Requests | Used Methods |
|---|---|---|---|
| IvanovMG | /posts | 8 | GET POST |
| PetrovSI | /about | 4 | GET |
| AndreevAE | /hot-chick.png | 3 | GET |
Advanced Examples
Example 1
In this example, the system returns a list of all unique values in the user field:
... | stats values(user)
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) |
|---|
| IvanovMG PetrovSI AndreevAE SidorovVA |
Example 2
In this example, the event field in the data has values start and end. After running this command, the system returns pairs of start with a list of unique user values (where event = "start") and end with a list of unique user values (where event = "end").
... | stats values(user) by event
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 values (where response.status = "200"), 403 and a list of unique source.address values (where response.status = "403"), and similarly for response.status = "401":
... | stats values(source.address) by response.status
Sample input data:
| user | response.status | source.address | request.referrer | 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) |
|---|---|
| 200 | 192.168.1.10 192.168.1.15 |
| 403 | 175.100.1.20 |
| 401 | 10.0.0.1 |
Example 3
In this example, the system shows a list of all unique values in the user and message fields:
| l_time | host | user | target | action | outcome |
|---|---|---|---|---|---|
| 2025-05-30 13:47:08 | ws-01 | denisov | sokolova | locked-out-user-account changed-password added-member-to-group | success |
| 2025-05-30 13:47:08 | ws-01 | avdeev | sokolova | reset-password changed-password | failure |
| 2025-05-30 13:47:08 | ws-01 | abramova | morozov | enabled-user-account | success |
| 2025-05-30 13:47:08 | ws-02 | popov | kirillova | changed-password | failure |
| 2025-05-30 13:47:08 | ws-01 | abramova | jakovleva | removed-member-from-group | success |
| 2025-05-30 13:47:08 | ws-01 | denisov | popov | added-member-to-group | failure |
| 2025-05-30 13:47:08 | ws-02 | shishkin | nikolaev | removed-member-from-group | failure |
Example 3.1
In this example, the system will display a list of all unique values of the user and action fields:
... | stats values(user), values(action)
The query execution result may be the following table:
| values (user) | values (action) |
|---|---|
| smith wadson wright taylor сooper | locked-out-user-account reset-password enabled-user-account changed-password removed-member-from-group added-member-to-group |
Example 3.2
In this example, the system will return a list of unique user and action values that share the same combination of user and action field values:
... | stats values(user), values(action) by user, action
The query execution result may be the following table:
| user | action | values (user) | values (action) |
|---|---|---|---|
| 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
In this example, the system will return the number of documents containing the user field:
... | stats count(user)
The query execution result may be the following table:
| count (user) |
|---|
| 5 |
Example 3.4
In this example, the system will return the number of documents containing the action field for each unique value of the user field:
... | stats count(action) by user
The query execution result may be the following table:
| user | count (user) |
|---|---|
| denisov | 955 |
| avdeev | 34 |
| abramova | 1007 |
| popov | 1022 |
| shishkin | 873 |
Example 3.5
In this example, the system will return the number of documents containing the user field and the number of documents containing the action field:
... | stats count(user), count(action)
The query execution result may be the following table:
| count (user) | count (action) |
|---|---|
| 5 | 3891 |
Example 3.6
In this example, the system will return the number of documents containing the user field for each unique combination of user and action field values:
... | stats count(user) by user, action
The query execution result may be the following table showing the first 7 entries:
| user | action | count (user) |
|---|---|---|
| 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 field for the first record in the sample, the contents of the action 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:
... | stats count(user) as action_count by user, action
| stats first(action), last(action), avg(action_count) by user
The query execution result may be the following table:
| user | first (action) | last (action) | avg (action_count) |
|---|---|---|---|
| denisov | deleted-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 field for the earliest record in the sample and the contents of the user field for the latest record in the sample:
... | stats earliest(action), latest(user) by @timestamp
The query execution result may be the following table showing the first 7 entries:
| @timestamp | earliest (action) | latest (user) |
|---|---|---|
| 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
Advanced example of using earliest and latest:
In this example, the system will extract the earliest and latest event by their indexing time indextime, after slightly cleaning the format of this time.
source radius_logs | sort indextime | eval indextime=substr(indextime, 1, (len(indextime) - 1) ) | stats latest(event, indextime), earliest(event, indextime)
Sample input data:
| indextime | username | nas_ip | acct_status_type | event |
|---|---|---|---|---|
| 2025-04-30T10:15:00Z | smith | 192.168.100.1 | Access-Reject | Authentication Failed |
| 2025-04-30T09:55:00Z | wadson | 192.168.100.15 | Interim-Update | Accounting Interim |
| 2025-04-30T10:45:00Z | smith | 192.168.100.1 | Start | Authentication Success |
| 2025-04-30T10:30:00Z | сooper | 10.10.0.23 | Stop | Accounting Stop |
The query execution result may be the following table:
| latest (event, indextime) | earliest (event, indextime) |
|---|---|
| Session timeout | User login failed |
Example 5
In this example, the system will return the number of unique values in the user field:
... | stats dc(user)
The query execution result may be the following table:
| user | dc (user) |
|---|---|
| ivanov petrov ivanov andreev | 3 |
Example 6
In this example, the system will return a list of all values of the user and items fields:
Collects all values of the specified field within a single group as an array, including duplicates.
... | stats list(user), list(items)
Sample input data:
| user | operation_status | total_price | items |
|---|---|---|---|
| ivanov | failure | 1657 | Chicken strips Fried squid rings Pepper sausages |
| petrov | success | 229 | Chicken tacos |
| ivanov | success | 1198 | Chicken tacos Chicken teriyaki |
| andreev | failure | 1198 | Gyros |
The query execution result may be the following table:
| list (user) | list (items) |
|---|---|
| ivanov petrov ivanov andreev | Chicken strips Fried squid rings Pepper sausages Chicken tacos Chicken tacos Chicken teriyaki Gyros |
Example 7
Example 7.1
In this example, the system will return the standard deviation of the log.offset field:
... | stats stdev(log.offset)
7.2
Analysis of CPU load host.cpu.usage for each host.ip field.
In this example, the system will return the event count count, the range between maximum and minimum values, average CPU load host.cpu.usage, standard deviation, and variance by host IP addresses host.ip:
... | stats count,
range(host.cpu.usage) as 'CPU Usage Range',
avg(host.cpu.usage) as 'CPU Usage Average',
stdev(host.cpu.usage) as 'Standard Deviation' by host.ip
| eval var=pow('Standard Deviation',2)
| rename var as 'Variance'
Sample input data:
| host.ip | host.os.name | system.cpu.cores | host.cpu.usage |
|---|---|---|---|
| 172.100.40.10 | Windows Server 2012 R2 Standard | 2 | 0.0581 |
| 172.95.150.218 | Windows Server 2016 Standard | 2 | 0.1611 |
| 172.255.215.11 | Ubuntu | 2 | 0.2011 |
| 172.100.40.10 | CentOS Linux | 2 | 0.0663 |
| 172.255.215.35 | Debian GNU/Linux | 2 | 0.295 |
The query execution result may be the following table:
| host.ip | count | CPU Usage Range | CPU Usage Average | Standard Deviation | Variance |
|---|---|---|---|---|---|
| 172.100.40.10 | 28 | 0.18012 | 0.17435 | 0.0693946143443423 | 0.004815612499999998 |
| 172.95.150.218 | 27 | 0.2692 | 0.16396 | 0.10991913189047463 | 0.01208221555555556 |
| 172.255.215.11 | 18 | 0.0686 | 0.28096 | 0.028258607341638205 | 0.0007985488888888887 |
| 172.100.40.10 | 15 | 0.2508 | 0.2165 | 0.11794679591522046 | 0.01391144666666667 |
| 172.255.215.35 | 13 | 0.03669 | 0.1342 | 0.0152343909187951 | 0.0002320866666666666 |
Example 8
Example 8.1
In this example, the system will return the value of the user field that occurs in 30 percent of cases:
... | stats perc(user, 30)
Example 8.2
Analysis of pollutant concentration values measurement.value by pollutant type event.category:
... | stats list(measurement.value),
avg(measurement.value),
median(measurement.value),
perc(measurement.value, 90) by event.category
The query execution result may be the following table:
| event.category | list (measurement.value) | avg (measurement.value) | median (measurement.value) | perc (measurement.value, 90) |
|---|---|---|---|---|
| PM2.5 | 15.5 17.9 16.3 18.0 | 16.925 | 17.1 | 17.94 |
| PM10 | 27.5 26.3 25.0 30.0 | 27.2 | 26.9 | 29.7 |
| NO2 | 40.0 43.8 41.1 45.0 | 42.475 | 42.45 | 44.82 |