Splunk Search
Highlighted

Compare historical hourly average with today's hourly data

New Member

I am trying to show two things in one graph:
1) bar chart of the count of events for last 24 hours in hourly intervals
2) overlay line chart of the average of the counts for the previous 3 weeks at the same day and hour. E.g. for Tuesday, January 28th 1pm-2pm would want to compute the average from 1pm -2pm for the 21st, 14th and 7th.

0 Karma
Highlighted

Re: Compare historical hourly average with today's hourly data

SplunkTrust
SplunkTrust

You should checkout timewrap. You can even format it where one days worth of data is on the left y-axis and another days worth of data is on the right y-axis.

index=... | timechart <blah> | timewrap 1d

Set your timerange picker to 2 days. Note, you must pipe a timechart into timewrap

If you wanted to get more complicated with it, you could use relative_time then push the data into a summary index for blazing fast searches. Otherwise, you would need to use a subsearch to overlay the data

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Timewrap

0 Karma
Highlighted

Re: Compare historical hourly average with today's hourly data

New Member

I started down this path too because I wanted to know if my indexers were behaving correctly as a daily health check.
First, I created a lookup from the results of "| tstats count where index=* by index,datemonth,datemday,datewday,datehour"
that looks back 13 weeks to collect the counts of events every hour in the past 13 weeks for every index. This runs on Sunday
morning. (I could have added index=_* too, but I haven't.)
Then, I created a lookup that calculates the average and standard deviation for each index for each day of the week that also
runs on Sunday morning after the first one, using it's data.
Finally, I joined a search of yesterday's results to the second lookup and can report on the indexes that aren't acting
normally by comparing yesterday's hourly counts using the average and standard deviation. I chose to use a line chart with
two lines (one for the averages and one for yesterday's counts). I created panels on a dashboard with charts for today (so far), yesterday, and this week -vs- average, as well as indexers that have deviated from norms and shown 0 events yesterday.
I may be able to post my dashboard after I've made sure it's working well, if my company allows it.

0 Karma