Splunk Search

Compare historical hourly average with today's hourly data

splunk2018a
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

edsale2
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,date_month,date_mday,date_wday,date_hour"
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

skoelpin
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
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...