https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489752#M136795 <P>Great it works, Thank you. </P> <P>How can i now show it to display the result with specific LED (1--20) on each TV (1...24) deployed at multiple hosts .</P> <P>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 .</P> Wed, 22 Jan 2020 12:11:31 GMT pkumar2 2020-01-22T12:11:31Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489749#M136792 <P>My Search has the below format data.</P> <P>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</P> <P>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)" | </P> <P>Result :<BR /> host LED PA TV Max (Val) _Time<BR /> 03192610158 0 Low A1 48.863 2019-12-19 22:00:08.177<BR /> 03192610158 0 Low A1 48.61 2019-12-20 22:00:08.140<BR /> 031................. 1 Low A1 44.23 2019-12-19 22:00:08.177<BR /> 031................. 1 Low A1 45.23 2019-12-20 22:00:08.177<BR /> |||||<BR /> |||||<BR /> 031................. 19 Low A1 49.23 2019-12-19 22:00:08.177<BR /> 031................. 19 Low A1 50.23 2019-12-20 22:00:08.177<BR /> |||||<BR /> |||||<BR /> 031................. 1 Low A2 52.23 2019-12-19 22:00:08.177<BR /> 031................. 1 Low A2 53.73 2019-12-20 22:00:08.177<BR /> AND Continues for the same host and for each TV and its LED's of 20.</P> <P>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%.</P> <P>I am stuck in how to get the alert correctly. Please help me in getting correct answer to get my alert condition right.</P> Tue, 21 Jan 2020 18:05:00 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489749#M136792 pkumar2 2020-01-21T18:05:00Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489750#M136793 <P>Hi,</P> <P>You could use the delta command to achieve to calculate the difference.</P> <P>Please use the below query and let us know whether this helped.<BR /> The difference between the adjacent records is evaluated using the delta command.</P> <PRE><CODE> |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&gt;5 </CODE></PRE> Tue, 21 Jan 2020 19:34:48 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489750#M136793 dindu 2020-01-21T19:34:48Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489751#M136794 <PRE><CODE>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 &gt; 5 </CODE></PRE> <P>cf. <A href="https://www.calculatorsoup.com/calculators/algebra/percent-difference-calculator.php">percentage difference calculator</A></P> <P>Hi, @pkumar2 <BR /> The formula was borrowed from the link. How about this?</P> <P>Hi, @dindu <BR /> <CODE>delta</CODE> is useful. but in your query,<BR /> When the type of LED changes, it calculates the value immediately before another LED.</P> Tue, 21 Jan 2020 21:01:25 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489751#M136794 to4kawa 2020-01-21T21:01:25Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489752#M136795 <P>Great it works, Thank you. </P> <P>How can i now show it to display the result with specific LED (1--20) on each TV (1...24) deployed at multiple hosts .</P> <P>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 .</P> Wed, 22 Jan 2020 12:11:31 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489752#M136795 pkumar2 2020-01-22T12:11:31Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489753#M136796 <P>Thank you, the query works partially wherin for the next set of LED the values are not listed.</P> Wed, 22 Jan 2020 12:14:09 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489753#M136796 pkumar2 2020-01-22T12:14:09Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489754#M136797 <PRE><CODE> .... | where perc_diff &gt; 5 | eval equipments=TV.":".LED | stats values(equipments) as equipments by host | eval equipments=mvjoin(equipments,",") | table host equipments </CODE></PRE> <P>try <CODE>$result.host$</CODE> and <CODE>$result.equipments$</CODE></P> Wed, 22 Jan 2020 21:11:51 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489754#M136797 to4kawa 2020-01-22T21:11:51Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489755#M136798 <P>Thank you, Works perfectly. </P> Thu, 23 Jan 2020 12:00:32 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489755#M136798 pkumar2 2020-01-23T12:00:32Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489756#M136799 <P>you're welcome. happy splunking.</P> Thu, 23 Jan 2020 12:08:08 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489756#M136799 to4kawa 2020-01-23T12:08:08Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489757#M136800 <P>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. </P> <P>-- 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.</P> <P>What could be the change in query to reflect the correct output.</P> Wed, 13 May 2020 16:22:16 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489757#M136800 pkumar2 2020-05-13T16:22:16Z https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489758#M136801 <P>@to4kawa Any suggestion to correct the query. I am stuck on this.</P> Fri, 15 May 2020 17:12:36 GMT https://community.splunk.com/t5/Splunk-Search/How-to-calculate-the-percentage-and-create-an-alert-condition/m-p/489758#M136801 pkumar2 2020-05-15T17:12:36Z