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
Explorer

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?

0 Karma

to4kawa
Ultra Champion
 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

0 Karma

pkumar2
Explorer

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.

0 Karma

to4kawa
Ultra Champion

use eventstats

0 Karma

pkumar2
Explorer

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.

0 Karma

to4kawa
Ultra Champion

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

0 Karma

pkumar2
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...