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?
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
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
Huzzah! That solved it! Thanks, sir.