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 battery temperature for each host.
source apc_snmp
| stats max(ups_adv_battery_temperature) as 'Maximum Battery Temperature' by host
In this example, the maximum value for the field ups_adv_battery_temperature is calculated for each unique host and the result is stored in the field 'Maximum Battery Temperature'.
The result of the query might be the following table:
| host | Maximum Battery Temperature |
|---|---|
| host1 | 58 |
| host2 | 87 |
| host3 | 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 clientip, request
| 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 clientip and request. Then, the results are sorted by 'Number of Requests'.
The result of the query might be the following table:
| clientip | request | Number of Requests | Used Methods |
|---|---|---|---|
| client1 | /log/ | 8 | GET, POST |
| client1 | / | 4 | GET |
| client2 | / | 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)
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 3
In this example, the system shows a list of all unique values in the user and message fields:
... | stats values(user), values(message)
Example 4
In this example, the system returns a list of unique user and message values where the user and message fields have the same combination of values:
... | stats values(user), values(message) by user, message
Example 5
In this example, the system returns the count of documents containing the user field:
... | stats count(user)
Example 6
In this example, the system returns the count of documents containing the message field for each unique user value:
... | stats count(message) by user
Example 7
In this example, the system returns the count of documents containing the user field and the count of documents containing the message field:
... | stats count(user), count(message)
Example 8
In this example, the system returns the count of documents containing the user field for each unique combination of user and message fields:
... | stats count(user) by user, message
Example 9
In this example, the system returns the average value of the log.offset field, the content of the event field for the first record, and the content of the event field for the last record:
... | stats avg(log.offset), first(event), last(event)
Example 10
In this example, the system returns the content of the event field for the earliest record and the content of the event field for the latest record:
... | stats earliest(event), latest(event)
Example 11
An advanced example using earliest and latest:
source radius_logs | sort indextime | eval indextime=substr(indextime, 1, (len(indextime) - 1) ) | stats latest(event, indextime), earliest(event, indextime)
Example 12
In this example, the system returns the count of unique values in the user field:
... | stats dc(user)
Example 13
In this example, the system returns a list of all values in the user field:
... | stats list(user)
Example 14
In this example, the system returns the standard deviation of the log.offset field:
... | stats stdev(log.offset)