How to calculate the percentage and create an alert condition that triggers for a drop of 5%.

[pkumar2]

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%.

I am stuck in how to get the alert correctly. Please help me in getting correct answer to get my alert condition right.

[to4kawa · Accepted solution]

source="c:\\program files (x86)\\xxxx" "PLogger" TV earliest=-2d@d latest=now PA = Low 
| stats max(VAL) as max_val by host, TV, LED, PA , _time 
| fields host, TV, LED, PA , max_val, _time 
| streamstats current=f  values(max_val) as prev_val by LED TV
| eval perc_diff=if(isnotnull(prev_val),abs((max_val - prev_val)/((max_val + prev_val)/2)*100),NULL)
| where perc_diff > 5

cf. percentage difference calculator

Hi, @pkumar2
The formula was borrowed from the link. How about this?

Hi, @dindu
delta is useful. but in your query,
When the type of LED changes, it calculates the value immediately before another LED.

[pkumar2]

Great it works, Thank you.

How can i now show it to display the result with specific LED (1--20) on each TV (1...24) deployed at multiple hosts .

Basically i need an alert messge with Host - Led no - Tv no. so its easy to identify if any location has a issue with LED dropping by 5% and for the TV number .

[to4kawa]

 ....
| where perc_diff > 5
| eval equipments=TV.":".LED
| stats values(equipments) as equipments by host
| eval equipments=mvjoin(equipments,",")
| table host equipments

try $result.host$ and $result.equipments$

[pkumar2]

Thank you, Works perfectly.

[to4kawa]

you're welcome. happy splunking.

[pkumar2]

Hi, I am relooking at this answer as we decide to go live. What i see is that for the max and Min value is not correctly applied.

-- Each TV has 20 LED , So Max and Min value for the duration of -7d@d is not giving correct value. The calculation takes the latest day value as min and not sorting for max and min to give the correct delta.

What could be the change in query to reflect the correct output.

[pkumar2]

@to4kawa Any suggestion to correct the query. I am stuck on this.

[dindu]

Hi,

You could use the delta command to achieve to calculate the difference.

Please use the below query and let us know whether this helped.
The difference between the adjacent records is evaluated using the delta command.

    |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)" 
    |rename max(VAL) as max_value
    |delta max_value as power_delta p=1
    |eval delta_perc=round(power_delta*100/(max_value-power_delta),2)
    |where delta_perc>5

[pkumar2]

Thank you, the query works partially wherin for the next set of LED the values are not listed.