The SPL2 stats command calculates aggregate statistics, such as average, count, and sum, over the incoming search results set. This is similar to SQL aggregation.
If the stats command is used without a BY clause, only one row is returned, which is the aggregation over the entire incoming result set. If a BY clause is used, one row is returned for each distinct value in the field specified in the BY clause.
Syntax
The required syntax is in bold.
- stats
- [allnum = <boolean>] [delim = <"string">] [partitions = <num>]
- <aggregation> ...
- ( [<by-clause>] [span=<time-span>] )
How the SPL2 stats command works
What's important to remember about the SPL2 stats command is that the command returns only the fields used in the aggregation.
Suppose these are some of the events in your dataset:
| _time | host | action | quantity | productId | method |
|---|---|---|---|---|---|
| 6 Apr 2022 9:39:48.000 PM | www2 | purchase | 1 | PZ-SG-G05 | POST |
| 6 Apr 2022 9:34:10.000 PM | www1 | view | 1 | GET | |
| 6 Apr 2022 9:34:02.000 PM | www3 | purchase | 2 | SC-MG-G10 | POST |
| 6 Apr 2022 9:34:01.000 PM | www2 | remove | 1 | CU-PG-G06 | GET |
| 6 Apr 2022 9:34:01.000 PM | www1 | purchase | 3 | POST | |
| 6 Apr 2022 9:29:55.000 PM | www3 | addtocart | 2 | SC-MG-G10 | GET |
| 6 Apr 2022 9:20:51.000 PM | www1 | addtocart | DB-SG-G01 | GET | |
| 6 Apr 2022 9:12:56.000 PM | www2 | changequantity | 2 | FS-SG-G03 | GET |
| 6 Apr 2022 9:12:53.000 PM | www1 | 1 | DB-SG-G01 | GET |
Using functions
You can use a wide range of statistical functions that you can use with the stats command. See Quick Reference for SPL2 Stats and Charting Functions.
The following search performs several aggregate calculations. When you perform more than one aggregation, separate each aggregation with a comma.
...| stats count(productId), sum(quantity), max(quantity), min(quantity)
The results look like this:
| count(productId) | sum(quantity) | max(quantity) | min(quantity) |
|---|---|---|---|
| 7 | 13 | 3 | 1 |
Grouping results
Use a BY clause when you want to group search results by a specific field.
The following search groups the results by the action field:
...| stats count(action) BY action
The results look like this:
| action | count(action) |
|---|---|
| addtocart | 2 |
| changequantity | 1 |
| purchase | 3 |
| remove | 1 |
| view | 1 |
You can perform an aggregation on one field and group the results by another field. The following search groups the results by the host field:
...| stats count(productId) BY host
The results look like this:
| host | count(productId) |
|---|---|
| www1 | 4 |
| www2 | 3 |
| www3 | 2 |
Renaming fields
Use the AS clause to rename a field. The following search renames the count(action) field to count:
...| stats count(action) AS count BY action
The results look like this:
| action | count |
|---|---|
| addtocart | 2 |
| changequantity | 1 |
| purchase | 3 |
| remove | 1 |
| view | 1 |
For additional examples, see stats command examples.
You can also use the from command to specify aggregate functions, group by a field, and rename a field. See stats command usage for examples.
