topic Re: how to find percentage? in Splunk Search

how to find percentage?

priyastalin — Tue, 16 Mar 2021 07:53:22 GMT

I Have a doubt in calculating the percentage.

First query:

(index=71412-cli sourcetype=show_interface) OR (index=71412-np sourcetype=device_details)
| stats values(*) as * by deviceId
|search deviceName ="BLV2-TI-SW_WAS18-01"
|dedup interface
| table deviceId interface deviceName adminStatus
| sort interface | stats count(interface) as "Total no of ports"

from the first query I'm fetching the total no of interface

second query
(index=71412-cli sourcetype=show_interface adminStatus="down") OR (index=71412-np sourcetype=device_details)
| stats values(*) as * by deviceId
|search deviceName ="BLV2-TI-SW_WAS18-01"
|search adminStatus=down
|dedup interface
| table deviceId interface deviceName adminStatus
| sort interface | stats count(interface) as "Down ports"

from the second query I'm fetching only the interfaces which are admin down

I want to find the percentage of (Down ports/Total no of ports) * 100.

please help me in finding the percentage by appending these two queries

thanks,

priya

Re: how to find percentage?

javiergn — Tue, 16 Mar 2021 08:11:27 GMT

Hi @priyastalin,

You could do something like this (not tested by the way):

(index=71412-cli sourcetype=show_interface) OR (index=71412-np sourcetype=device_details) | stats values(*) as * by deviceId | search deviceName ="BLV2-TI-SW_WAS18-01" | dedup interface | table deviceId interface deviceName adminStatus | sort interface | stats count(interface) as "Total no of ports" count(eval(if(adminStatus="down", interface, null()))) as "Down ports" | eval percDownPorts = ('Down ports' /' Total no of ports' )* 100

Keep an eye on that previous stats and dedup you are performing though and ensure that is not removing valid events for your totals calculations.

Regards,

Re: how to find percentage?

priyastalin — Tue, 16 Mar 2021 08:18:15 GMT

Hi,

Thanks for you input

Total no of ports Down ports percDownPorts

599 599 100

its giving the correct total no of ports but down ports count is 234 is giving wrong value for down ports..
is there any other way to fetch the output

Re: how to find percentage?

priyastalin — Tue, 16 Mar 2021 08:26:53 GMT

Hi @javiergn

Thanks for you input

Total no of ports Down ports percDownPorts

599 599 100

its giving the correct total no of ports but down ports count is 234 is giving wrong value for down ports..
is there any other way to fetch the output

Re: how to find percentage?

javiergn — Tue, 16 Mar 2021 08:54:21 GMT

Hi, can you post a data sample. I still think the previous lines with that stats and dedup are causing the problem. Just run something like this and post a few lines if possible:

((index=71412-cli sourcetype=show_interface) OR (index=71412-np sourcetype=device_details)) deviceName ="BLV2-TI-SW_WAS18-01" | table deviceId interface deviceName adminStatus

Re: how to find percentage?

dmarling — Tue, 16 Mar 2021 12:22:49 GMT

In order to know the amount of down ports as compared to the total ports and assuming the show_interface sourcetype is a streaming input of the interface's status, you will need to obtain the latest status of each interface before you calculate a rate. Your current query is passing in the values of all fields and then deduping, but values returns it in lexicographical order, not time order. This will produce inaccurate results. This is my suggestion to accomplish what you are looking for in a single query:

(index=71412-cli sourcetype=show_interface) OR (index=71412-np sourcetype=device_details) | eventstats values(deviceName) as deviceName by deviceId | stats latest(adminStatus) as adminStatus values(deviceName) as deviceName by deviceId interface | search deviceName ="BLV2-TI-SW_WAS18-01" | eval rate=if(adminStatus="down", 1, 0) | stats avg(rate) as "Down Rate" count(eval(adminStatus="down")) as "Down ports" count as "Total no of ports" by deviceName deviceId | eval "Down Rate"='Down Rate'*100

Line 2 is just appending in the deviceName from the device_details sourcetype to the show_interface sourcetype events so we can have that information when we use stats on line 3 to provide the latest adminStatus of each interface. This could also be accomplished with a join after the stats line instead, but I tend to avoid joins if at all possible. Once the latest adminStatus is obtained from line three you can apply your deviceName filter and create a "rate" field that is assigning a 1 or a 0 depending on if the latest adminStatus that returns is down or not. Finally on line 6 you can average the "rate" field we crated on line 5 and also count the down and total ports on each device. Line 7 is unnecessary but makes the percentage a bit easier to consume in my opinion.