Splunk Search

Roll Up Summary Index from Seconds to Hourly

tmurray3
Path Finder

I have created a saved search which runs once an hour and records to a summary index. The search allows me to determine the number of transactions per second by host or collectively.

index=data "Start::" | bucket _time span=1s | sistats count by _time,host

Using the summary index which rolled up counts by into seconds, I can create a report which gives me average, max, min, total count by the hour for by hosts or collectively.

Here is the query I use for reporting totals across all hosts.
index=summary report="summary_by_seconds"| stats count by _time |timechart span=1h avg(count) as avgcount max(count) as maxcount min(count) as mincount, sum(count) as totalcountperhour

This gives me the max, min, avg TPS for the hour along with total count for all hosts.

_time avgcount maxcount mincount totalcountperhour

2/19/14 3:00:00.000 PM 56.225556 110 22 202412

Now, I try to roll the above result up into an hourly summary, so I can store long term. I create the saved search using the query which produced my hourly tps results above, but add the si to the timechart command:

index=summary report="summary_by_seconds"| stats count by orig_host,_time |sitimechart span=1h avg(count) as avgcount max(count) as maxcount min(count) as mincount, sum(count) as totalcountperhour by orig_host

This gets stored in the summary index report="summary_by_hourly"

Now, I try to create the same report using summary_by_hourly, I don't get the same results as above:

index=summary report="summary_by_hourly"| stats count by _time |timechart span=1h avg(count) as avgcount max(count) as maxcount min(count) as mincount, sum(count) as totalcountperhour

_time avgcount maxcount mincount totalcountperhour
2/19/14 3:00:00.000 PM 44198.000000 44198 44198 44198

Am I rolling up from seconds to hourly correctly?

Or is the query running against the hourly summary incorrect?

Thanks in advance for your assistance.

Tags (2)
0 Karma

tmurray3
Path Finder

I see what I was doing wrong. I should not have been adding stats count by _time to the query on the hourly summary. The correct query is:

index=summary report="summary_by_hourly"| timechart span=1h avg(count) as avgcount max(count) as maxcount min(count) as mincount, sum(count) as totalcountperhour

0 Karma