stats command overview

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.

See also

stats command

stats command syntax details

stats command usage

stats command examples

Functions

Overview of SPL2 stats functions

SPL2 Stats and Charting Functions Quick Reference