Splunk® Cloud Services

SPL2 Search Reference

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 purchase DC-SG-G02
2024/01/20 11:58 addtochart MB-AG-G07
2024/01/20 11:58 purchase WC-SH-A01
2024/01/20 11:56 changequantity PZ-SG-G05
2024/01/20 11:51 purchase SF-BVS-01
2024/01/20 11:47 purchase SF-BVS-G01
2024/01/20 11:42 purchase WC-SH-T02
2024/01/20 11:39 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
Last modified on 12 March, 2024
stats command syntax details   stats command examples

This documentation applies to the following versions of Splunk® Cloud Services: current

Was this topic useful?

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters