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

[bryhoffman]

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.

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

[Cottonball]

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.

[yuanliu]

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

hour	variance
00	15664.5
01	72200
02	15488
03	63368
04	14792
05	51842
06	31752
07	69192
08	41123380.5
09	66612.5
10	127296968
11	51842
12	2380.5
13	36414578
14	3120.5
15	12.5
16	0.5
17	138095580.5
18	561694644.5
19	542027812.5
20	565084962
21	531966962
22	558916178
23	563304612.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

[Cottonball]

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.

[bryhoffman]

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

[ITWhisperer]

https://docs.splunk.com/Documentation/Splunk/9.1.0/SearchReference/Timewrap 

[PickleRick]

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