topic Re: Calculating Percentage in Splunk Search

Calculating Percentage

g038123 — Thu, 23 Jun 2016 19:13:24 GMT

Hello,
I'm having trouble finding the correct syntax and function to get the desired end result. I have a search based off of a dbx query. The search is like this:
dbx query base search | chart count over status by country
This returns something similiar to:

I'd like to add a new row to the results, calculating the %NotConnected for each country.
I have tried using the following to calculate the %NotConnected:

stats count(eval(STATUS="NotConnecting")) AS NotConnected, count(eval(STATUS="Monitored")) AS total | eval %NotConnected=((NotConnecting/Monitored)*100

doesn't seem to work and I am unable to figure out, even if it did work, how to add the calculation for each country to the current results as a new row. Hopefully this makes sense and someone can help me out. I'm fairly new to Splunk and really out of my realm on this one.

Thank you

Re: Calculating Percentage

somesoni2 — Thu, 23 Jun 2016 21:47:03 GMT

Give this a try

dbx query base search 
| chart count over country by status
| eval "%NotConnected"=round('NotConnecting'*100/('NotConnecting' + Monitored + 'Alert Failure' + 'Pending Setup'),2)
| untable country status count
| chart values(count) over status by country

dbx query base search 
| chart count over country by status
| addtotal | eval "%NotConnected"=round('NotConnecting'*100/(Total),2)
| untable country status count
| chart values(count) over status by country

Re: Calculating Percentage

g038123 — Fri, 24 Jun 2016 18:58:41 GMT

Thank you somesoni2,

You pointed me in the right direction. I ended up transposing the original data and used the following to get the totals in a new column rather than in a new row.

dbx query base search
| chart count over CTRY by STATUS limit=20
| eval "%NotConnected"=tostring(round(('Not Connecting'/Monitored)*100,1))+"%"
| table CTRY,Monitored,"NotConnecting","Alert Monitoring Failure","Replacement", %NotConnected

That worked perfectly, Thank you for the help!

Re: Calculating Percentage

sloshburch — Mon, 27 Jun 2016 12:27:29 GMT

On a side note, does the eval(STATUS=="NotConnecting") need a double equals sign? I haven't played in a bit so apologies if I'm being too tangential here.