topic Re: How to add total percentage to rows and columns in Splunk Search

How to add total percentage to rows and columns

vshakur — Sat, 02 Sep 2017 16:57:58 GMT

I have the following query :

... | search service_name=$service$ | dedup name, jenkins_data.JOB_NAME, jenkins_data.UNIX_TIMESTAMP | stats count AS total, count(eval(status="PASS")) as success by service_name browser_name | eval rate = round(success/total*100,0). " %" |  chart values(rate) as result by service_name browser_name

which produces the following table:

I want to add a "Total" both as a row and as a column at the end of the rows and columns in the table.
Using addtotals didn't achieve my goals since I don't need to calculate the total sum of the percentages (which would obviously produce a number greater than 100%).
I need the total pass rate both for the services and the browsers, i.e: ( (Passed Total) / (Grand Total) * 100 ) %.

Re: How to add total percentage to rows and columns

niketn — Sun, 03 Sep 2017 08:42:48 GMT

@vshakur, I could do Pass percent by each service_name. However, in the same query I could not get to Pass percent by browser_name. Please try out the following (until someone gets you a better query to even perform 😞

 ... 
| search service_name=$service$ 
| dedup name, jenkins_data.JOB_NAME, jenkins_data.UNIX_TIMESTAMP 
| chart count as total count(eval(status="PASS")) as success over service_name by browser_name 
| eval success=0, total=0
| foreach "success: *" "total: *"
    [eval "perc: &lt;&lt;MATCHSTR&gt;&gt;"=round(('success: &lt;&lt;MATCHSTR&gt;&gt;'/ 'total: &lt;&lt;MATCHSTR&gt;&gt;')*100,1)]
| foreach "success: *" 
    [eval success=success + '&lt;&lt;FIELD&gt;&gt;']
| foreach "total: *" 
    [eval total=total + '&lt;&lt;FIELD&gt;&gt;']
| eval perc= round((success/total)*100,1)
| fields - "success*" "total*"
| rename "perc: *" as "*"

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 08:45:28 GMT

Thanks, I'll try that.

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 08:58:07 GMT

I tried the query but got an error. What exactly should I replace <> and <> with?
And what do categoryId and referer_domain represent?

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 09:08:19 GMT

I tried the query but got an error. What exactly should I replace FIELD and MATCHSTR with?
And what do categoryId and referer_domain represent?

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 09:23:59 GMT

OK, now I understand. It worked perfectly as a separate query in Splunk, but I need it to be part of a dashboard, and that's where I get the error message: Unencoded.

Re: How to add total percentage to rows and columns

niketn — Sun, 03 Sep 2017 09:53:02 GMT

@vshakur, sorry I had missed using your field names service_name and browser_name. I have corrected the query.

For adding the query to your dashboard you need to escape < and > characters with < and > respectively. Please find the updated query for dashboard.

<<FIELD>> and <<MATCHSTR>> are parameter for foreach command in Splunk. You can refer to documentation for details: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Foreach

Please confirm if this solves your need and I will convert to answer so that you can accept. You can also up vote if this has helped you.

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 09:57:40 GMT

Yes thank you, I managed.
Indeed it has solved completely half of the problem. I'm still stuck with the other half (calculating total by browser).
I would like to up-vote. Please convert to answer.

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 10:25:30 GMT

I've managed to add the total by browser using addtotals . Now all my problems are solved.
Please convert to answer so I could accept.

Re: How to add total percentage to rows and columns

niketn — Sun, 03 Sep 2017 10:29:00 GMT

@vshakur, you can upvote comments directly. Since your issue is not resolved completely, I will leave it as comment so that it remains flagged as unanswered for other Splunkers.

May I know whether you want to display the final result as table or chart?

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 10:32:03 GMT

As a table. But I've already managed to solve the second part of my problems. You can post as answer and I'll accept.

Re: How to add total percentage to rows and columns

niketn — Sun, 03 Sep 2017 10:36:46 GMT

I thought addtotals would add total of percent which you did not want. But nevertheless, glad it worked and I was able to help. Please accept the answer.

Re: How to add total percentage to rows and columns

vshakur — Sun, 03 Sep 2017 10:44:50 GMT

I added the addtotals before the calculations. This created a new row called "Total" with all the services summed up for each browser. Only then I proceeded with your solution (the for loops). This has produced the right percentage for all the rows (services), including the Total row.
Thanks.

Re: How to add total percentage to rows and columns

niketn — Sun, 03 Sep 2017 11:59:21 GMT

Cheers !!!