stats command usage

Using the from command instead

Most of the things you can do with the stats command are also possible using the from command.

For example, if your search is ...| stats count() BY host, the following searches return the same results:

from command alternatives
| FROM <dataset> GROUP BY host SELECT count(), host
| SELECT count(), host FROM <dataset> GROUP BY host

For more information, see from command overview.

Aggregating multivalue fields

When you perform an aggregation over a multivalue field, each of the values in the field is included in the aggregation. Suppose that you have this set of data:

fieldX	fieldY
A	1
A	[2,3,4]
B	[5,6,7,8,9]

When you run this stats command ...| stats count, count(fieldY), sum(fieldY) BY fieldX, these results are returned:

fieldX	count	count(fieldY)	sum(fieldY)
A	2	4	10
B	1	5	35

• The results are grouped first by the fieldX.
• The count field contains a count of the rows that contain A or B.
• The count(fieldY) aggregation counts the rows for the fields in the fieldY column that contain a single value. However, if a field is a multivalue field, the aggregation counts the number of values in the fields.
• The sum(fieldY) aggregation adds up all of the values in both single value and multivalue fields.

Grouping results

Use the BY clause to group your search results.

For example, suppose you have the following events:

_time	clientip	action	pid
2024/01/20 12:00	192.0.2.0	purchase	DC-SG-G02
2024/01/20 11:58	203.0.113.0	addtochart	MB-AG-G07
2024/01/20 11:58	203.0.113.0	purchase	WC-SH-A01
2024/01/20 11:56	198.51.100.255	changequantity	PZ-SG-G05
2024/01/20 11:51	192.0.2.0	purchase	SF-BVS-01
2024/01/20 11:47	198.51.100.0	purchase	SF-BVS-G01
2024/01/20 11:42	192.0.2.0	purchase	WC-SH-T02
2024/01/20 11:39	198.51.100.0	purchase	PZ-SG-G05

To group the results by the type of action add | stats count(pid) BY action to your search.

The results look like this:

action	count(pid)
addtochart	1
changequantity	1
purchase	6

Group results by a timespan

To group search results by a timespan, use the span statistical function.

Group results by a multivalue field

When grouping by a multivalue field, the stats command produces one row for each value in the field. For example, suppose the incoming result set is this:

fieldA	fieldB	fieldC
1	x	V1, V2, V3
2	y	V4, V5
3	z	V2, V5

If you specify the fieldC in the <by-clause>, such as ...| stats sum(fieldA) BY fieldC, the results are:

fieldC	sum(fieldA)
V1	1
V2	4
V3	1
V4	2
V5	5

Differences between SPL and SPL2

Command options must be specified before command arguments

The <stats-options> are:

• allnum = <boolean>
• delim = <"string">
• partitions = <num>

New span option added to the <by-clause>

With SPL2 you can specify a time span. The field you use in the <by-clause> must be either the _time field, or another field in UNIX time. For example:

Version	Example
SPL	Not supported. The SPL equivalent is ...| bin _time span=5min | stats count (error) BY _time
SPL2	...| stats count(error) BY _time span=5min

This example returns the count of events in 5 minute intervals.

You can accomplish that same results using the from command. Here are several examples:

from command alternatives
| FROM <dataset> GROUP BY span(_time, 5min) SELECT count(error), _time
| SELECT count(error), _time FROM <dataset> GROUP BY span(_time, 5min)

For more information about specifying a span, see Specifying time spans in the SPL2 Search Manual.

All functions require parentheses

In SPL, the count function could be specified without parentheses. In SPL2, the parentheses are required when you use the count function. For all other functions, you must specify a field inside the parentheses or BY clause.

Version	Example
SPL	...| stats count
SPL2	...| stats count ()

Field lists must be comma-delimited

If you specify a list of fields in the <by-clause>, the list must be comma-delimited. Otherwise a parsing error is returned.

Version	Example
SPL	...| stats count BY host source
SPL2	...| stats count () BY host, source

Aggregations must be comma-delimited

If you specify multiple aggregations, the aggregations must be comma-delimited. Otherwise a parsing error is returned.

Version	Example
SPL	...| stats avg (fieldX) max (fieldY) BY host
SPL2	...| stats avg (fieldX), max (fieldY) BY host

See also

stats command

stats command overview

stats command syntax details

stats command examples