topic How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5% in Splunk Search

How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

pkumar2 — Wed, 30 Sep 2020 05:23:57 GMT

My Search has the below format data.

A single host has multiple parameters consists of LED 1..to.20 for each TV and there are 24 TV's , The LED power paramerter has value say Max(val) 34.0 which is related to PA (power Amplitude) of Low/High, we only want search for PA = Low

Query :

source="c:\\program files (x86)\\xxxx" "PLogger" TV earliest=-2d@d latest=now PA = Low | stats max(VAL) by host, TV, LED, PA , _time | fields "host" "LED","PA", "TV", "max(VAL)" |

Result :

host LED PA TV Max (Val) _Time
03192610158 0 Low A1 48.863 2019-12-19 22:00:08.177
03192610158 0 Low A1 48.61 2019-12-20 22:00:08.140
031................. 1 Low A1 44.23 2019-12-19 22:00:08.177
031................. 1 Low A1 45.23 2019-12-20 22:00:08.177
|||||
|||||
031................. 19 Low A1 49.23 2019-12-19 22:00:08.177
031................. 19 Low A1 50.23 2019-12-20 22:00:08.177
|||||
|||||
031................. 1 Low A2 52.23 2019-12-19 22:00:08.177
031................. 1 Low A2 53.73 2019-12-20 22:00:08.177

AND Continues for the same host and for each TV and its LED's of 20.

Now I need to calculate the percentage difference of LED 1 2..till..19 for each TV ( A1 ---A24) and raise and Alert for any LED's if they drop by 5%.

This is the Splunk query I use:

source="c:\\program files (x86)\\prysm\\servo\\logs\\vegaservo.log" "PLogger" earliest=-7d@d latest=now TV PA = Low  | stats max(VAL) as max_val by host, TILE, Laser, PA , _time | fields host, TV, LED, PA ,max_val, _time |streamstats current=f  values(max_val) as prev_val by LED TV host| eval perc_diff=((max_val - prev_val)/((max_val + prev_val)/2)*100) | where perc_diff > 5

Output for one host :
Host :::::: TV ::::::: LED:::::::: PA ::::: Max_val ::::: _time :::: Perc_diff ::::: prev_val
DESKTOP-3S2CV0M :::: E1 ::::: 16 :::: Low :::: 30.354 ::::: 2020-05-06 10:00:46.221 :::: 5.136 ::::: 28.834

Cross Checking host data for the week:

11 May 2020 05:00:46,276 [4] INFO  PLogger : TV = E1, Laser = 16, PA = Low, VAL = 31.512 
10 May 2020 05:00:46,211 [11] INFO  PLogger : TV = E1, LED = 16, PA = Low, VAL = 30.124 
09 May 2020 05:00:46,227 [10] INFO  PLogger : TV= E1, LED= 16, PA = Low, VAL = 30.695 
08 May 2020 05:00:46,307 [11] INFO  PLogger : TV = E1, LED = 16, PA = Low, VAL = 28.731 
07 May 2020 05:00:46,666 [5] INFO  PLogger - : TV = E1, LED = 16, PA = Low, VAL = 28.452 
06 May 2020 05:00:46,221 [16] INFO  PLogger -: TV = E1, LED= 16, PA = Low, VAL = **30.354** 
05 May 2020 05:00:47,196 [16] INFO  PLogger : TV= E1, LED = 16, PA = Low, VAL = **28.834**

The problem here is, the value is only getting calculated between last 2 days as you can see the highlighted data above.

I am stuck in how to get the alert correctly. How can I get the correct perc_diff alert for the week?

Re: How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

to4kawa — Fri, 15 May 2020 22:18:37 GMT

 source="c:\\program files (x86)\\prysm\\servo\\logs\\vegaservo.log" "PLogger" earliest=-7d@d latest=now TV PA = Low  | bin _time span=1d | stats max(VAL) as max_val by host, TILE, Laser, PA , _time | fields host, TV, LED, PA ,max_val, _time  |streamstats current=f  last(max_val) as prev_val by LED TV host| eval perc_diff=((max_val - prev_val)/((max_val + prev_val)/2)*100)

check this result

Re: How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

pkumar2 — Sat, 16 May 2020 07:06:57 GMT

I tried it , It is taking only max and min of last 1 day and computing it.

What is needed is Max and Min value reported for the week for each LED. and perform the perc_diff calculation.

Re: How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

to4kawa — Sat, 16 May 2020 07:21:37 GMT

use eventstats

Re: How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

pkumar2 — Wed, 30 Sep 2020 05:25:46 GMT

Just tried this , source="c:\program files (x86)\prysm\servo\logs\vegaservo.log" "PLogger `

" earliest=-7d@d latest=now TV PA = Low | eventstats max(VAL) as max_val min(VAL) as min_val by host, TILE, Laser, PA , _time

16 May 2020 05:01:32,798 [13] INFO  PcalLogger - LaserNits: TV = C3, LED= 17, PA = Low, VAL = 49.138 
16 May 2020 05:01:33,825 [30] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 40.388 
16 May 2020 07:31:29,563 [13] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 42.771 
16 May 2020 05:01:26,504 [18] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 40.617 
16 May 2020 06:01:17,954 [28] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 56.235 
16 May 2020 05:02:48,667 [13] INFO  PcalLogger - LaserNits: TV = C3, LED= 17, PA = Low, VAL = 49.563 
16 May 2020 05:01:55,969 [13] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 49.563 
15 May 2020 05:02:13,664 [11] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 56.425 
15 May 2020 05:02:01,916 [8] INFO  PcalLogger - LaserNits: TV= C3, LED = 17, PA = Low, VAL = 54.894 
15 May 2020 05:01:42,815 [15] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 58.274 
15 May 2020 05:01:33,853 [21] INFO  PcalLogger - LaserNits: TV = C3, LED= 17, PA = Low, VAL = 56.425 
15 May 2020 05:01:47,114 [4] INFO  PcalLogger - LaserNits: TV = C3, LED= 17, PA = Low, VAL = 49.393 
15 May 2020 05:01:39,954 [11] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 47.566 
15 May 2020 05:01:32,599 [17] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 36.822 
15 May 2020 05:01:37,341 [20] INFO  PcalLogger - LaserNits: TV = C3, LED= 17, PA = Low, VAL = 49.028 
15 May 2020 05:01:31,575 [13] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 49.627 
15 May 2020 10:11:12,863 [10] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 36.742 
15 May 2020 05:01:33,639 [21] INFO  PcalLogger - LaserNits: TV = C3, LED= 17, PA = Low, VAL = 40.457 
15 May 2020 07:31:30,474 [12] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 42.566 
15 May 2020 05:01:26,318 [13] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 40.594 
15 May 2020 06:01:18,128 [29] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 56.145 
15 May 2020 05:01:43,558 [18] INFO  PcalLogger - LaserNits: TV= C3, LED = 17, PA = Low, VAL = 53.515 
14 May 2020 05:02:22,486 [26] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 48.056 
14 May 2020 05:01:43,976 [4] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 58.351 
14 May 2020 05:01:41,404 [26] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 48.056 
14 May 2020 05:01:33,721 [11] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 57.254 
14 May 2020 05:01:48,204 [23] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 49.906 
14 May 2020 05:01:38,857 [15] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 47.89 
14 May 2020 05:01:28,036 [19] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 36.776 
14 May 2020 05:01:38,073 [19] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 49.041 
14 May 2020 05:01:26,628 [11] INFO  PcalLogger - LaserNits: TV = C3, LED = 17, PA = Low, VAL = 49.871

I tried |stats values(max_val) as max_val, values(min_val) as min_val -- The Max and Min value for each LED for the week as a stats not able to get it. What am i doing wrong here.

Re: How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

to4kawa — Sat, 16 May 2020 10:28:11 GMT

by _time is each day, Don't you want a week's maximum?

Re: How to calculate the delta for same event for time difference of 7 days and alert if delta is more than 5%

pkumar2 — Sat, 16 May 2020 10:48:44 GMT

yes, want the week maximum and minimum per LED and get the percentage difference.