topic Re: Find variance between current hour value and avg value of same hour over last 2 weeks in Splunk Search

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

bryhoffman — Mon, 04 Mar 2024 05:59:53 GMT

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:

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

PickleRick — Mon, 17 Jul 2023 20:43:54 GMT

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

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

bryhoffman — Mon, 17 Jul 2023 21:07:58 GMT

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

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

ITWhisperer — Tue, 18 Jul 2023 05:46:14 GMT

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

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

Cottonball — Fri, 01 Mar 2024 23:47:58 GMT

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.

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

yuanliu — Sat, 02 Mar 2024 01:18:03 GMT

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

(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

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

Cottonball — Mon, 04 Mar 2024 02:21:14 GMT

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.