Splunk Search

## Generate Rolling Summation

Splunk Employee

I have a set of data that has one event for ever second, with a field for the number of simultaneous phone calls going on each second. (Here is more background.) E.g.:

Time                       Concurrency
10/25/2010 00:00:00    0
10/25/2010 00:00:01    0
10/25/2010 00:00:02    0
10/25/2010 00:00:03    0
....
10/25/2010 13:01:01    7
10/25/2010 13:01:02    8
10/25/2010 13:01:03    8

What I want to get out of the data is what is the 60 minute period of time with the highest sum(Concurrency). Looking simply, I could do a `| stats sum(Concurrency) as HourlySum by date_hour | stats max(HourlySum)`. The only problem there is that it cuts the window directly along 00:00-59:59 minute periods. If the busiest period of time is 13:22:00-14:21:59, it wouldn't be properly represented.

Is there any way to do a rolling average, so I could get the data I need out of it?

Tags (1)
1 Solution
SplunkTrust

``````<your search> | bucket _time span=1min | streamstats sum(Concurrency) as rollingSum window=60 | sort - rollingSum
``````

The bucket will normalize all the data to minute boundaries. Essentially it just throws away the seconds and subseconds values.

Then the streamstats command just keeps a rolling sum of the past 60 rows (including the current row).

Then just sort descending on rollingSum and that's your busiest 60 minute period.

some references:

http://www.splunk.com/base/Documentation/latest/SearchReference/Streamstats

http://www.splunk.com/base/Documentation/latest/SearchReference/Bucket

SplunkTrust

``````<your search> | bucket _time span=1min | streamstats sum(Concurrency) as rollingSum window=60 | sort - rollingSum
``````

The bucket will normalize all the data to minute boundaries. Essentially it just throws away the seconds and subseconds values.

Then the streamstats command just keeps a rolling sum of the past 60 rows (including the current row).

Then just sort descending on rollingSum and that's your busiest 60 minute period.

some references:

http://www.splunk.com/base/Documentation/latest/SearchReference/Streamstats

http://www.splunk.com/base/Documentation/latest/SearchReference/Bucket

Splunk Employee

Huzzah! That solved it! Thanks, sir.