Generate Rolling Summation

[David]

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?

[sideview · Accepted solution]

Here's your search:

<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

[David]

Huzzah! That solved it! Thanks, sir.