Splunk Search

Find variance between current hour value and avg value of same hour over last 2 weeks

bryhoffman
Explorer

Hi,

I have an search that is used on a dashboard that I would like tweaked.

Currently this search/panel displays the variance of current hour over the same hour the week before. for example: The value at hour 10 on Wed 7/19/23 will be compared to the value at hour 10 on Wed 7/12/23 and give variance.

Instead, I would like to compare current hour value to the value of the AVG of that same hour over the last 2 weeks (instead of compared to 1 day). For example I would like hour 10 on Wed 7/19/23 to be compared to the avg of hour 10 each day from Tues 7/18/23 to Wed 7/5/23.

bryhoffman_0-1689622678784.png

Current search:

| tstats count where index=msexchange host=SMEXCH13* earliest=-14d@d latest=-13d@d by _time span=1h
| eval hour=strftime(_time,"%H")
| eval ReportKey="2weekprior"
| stats values(count) as count by hour, ReportKey
| append
[| tstats count where index=msexchange host=SMEXCH13* earliest=-7d@d latest=-6d@d by _time span=1h
| eval hour=strftime(_time,"%H")
| eval ReportKey="1weekprior"
| stats values(count) as count by hour, ReportKey ]
| append
[| tstats count where index=msexchange host=SMEXCH13* earliest=-0d@d latest=-0h@h by _time span=1h
| eval hour=strftime(_time,"%H")
| eval ReportKey="currentweek"
| stats values(count) as count by hour, ReportKey ]
| eval currenthour=strftime(_time,"%H")
| xyseries hour, ReportKey, count
| eval nowhour = strftime(now(),"%H")
| eval comparehour = nowhour-1
|where hour<=comparehour
|sort by -hour
| table hour,nowhour,comparehour, currentweek,1weekprior,2weekprior
|eval 1weekvar = currentweek/'1weekprior'
|eval 2weekvar = currentweek/'2weekprior'
|eval variance=round(((('1weekvar'+'2weekvar')/2)*100)-100,2)
|table hour,variance
|head 5
Labels (4)

Cottonball
Engager

Bump.  I am working the exact same scenario.

Transaction volume is a daily bell curve, so comparing volume from 16:00 to 15:00 is useless.  Tran volumes will always be increasing or decreasing hour by hour.

Transaction volume is a daily bell curve, so comparing today at 16:00 to the average of the last 24 hours is useless.  24 hour average might be 1M per hour, but 24 hour deviation might be + or - 50%.

Transaction volume is also a weekly bell curve, so comparing today's 16:00 traffic to JUST yesterday's 16:00 traffic is okay, but still not great.

Best case is exact scenario OP described:

Compare today's 16:00-17:00 traffic to the AVERAGE of the last 7 or 14 days' 16:00-17:00 traffic and then alert based on a variance.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

I know it's half year later, but the answer is still timewrap as @PickleRick and @ITWhisperer indicated.  Using OP's sample search,

 

| tstats count where index=msexchange host=SMEXCH13* earliest=-14d@d latest=-0d@d by _time span=1h 
| timechart span=1h values(count) as count
| timewrap 1w@w
| eval _time = strftime(_time, "%H")
| transpose 0 column_name=week header_field=_time
| search week != _*
| stats var(*) as *
| transpose column_name=hour
| rename "row 1" AS variance

 

(Note you also don't need to manually calculate variance from scratch.  Splunk stats has var function.) Obviously I do not have the same exchange data, but this can easily be simulated with index=_internal.  My results are

hourvariance
0015664.5
0172200
0215488
0363368
0414792
0551842
0631752
0769192
0841123380.5
0966612.5
10127296968
1151842
122380.5
1336414578
143120.5
1512.5
160.5
17138095580.5
18561694644.5
19542027812.5
20565084962
21531966962
22558916178
23563304612.5

Simulation code is simply

 

| tstats count where index=_internal earliest=-14d@d latest=-0d@d by _time span=1h@h
| timechart span=1h values(count) as count
| timewrap 1w@w
| eval _time = strftime(_time, "%H")
| transpose 0 column_name=week header_field=_time
| search week != _*
| stats var(*) as *
| transpose column_name=hour
| rename "row 1" AS variance

 

 

Tags (2)
0 Karma

Cottonball
Engager

Being somewhat of a journeyman myself, the proper way to use timewrap was a bit of a mystery to me.  So, while the answer may be apparent to many, I was not sure how to wield the information.

Thank you for the response.  I will give it a go on Monday.

0 Karma

bryhoffman
Explorer

Could you show me what you mean? Thanks for the quick reply

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Instead of using multiple virtually identical (apart from time range) appended searches, just do one longer search and use timewrap.

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

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

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...