Splunk® Cloud Services

SPL2 Search Reference

Acrobat logo Download manual as PDF

Acrobat logo Download topic as PDF

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 by multivalue fields

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=5m | stats count (error) BY _time
SPL2 ...| stats count(error) BY _time span=5m

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, 5m) SELECT count(error), _time
| SELECT count(error), _time FROM <spl> GROUP BY span(_time, 5m)

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 22 June, 2021
stats command syntax details
stats command examples

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

Was this documentation topic helpful?

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