How to add total percentage to rows and columns

[vshakur]

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

[niketn · Accepted solution]

@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: <<MATCHSTR>>"=round(('success: <<MATCHSTR>>'/ 'total: <<MATCHSTR>>')*100,1)]
| foreach "success: *" 
    [eval success=success + '<<FIELD>>']
| foreach "total: *" 
    [eval total=total + '<<FIELD>>']
| eval perc= round((success/total)*100,1)
| fields - "success*" "total*"
| rename "perc: *" as "*"

[vshakur]

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.

[niketn]

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

[vshakur]

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.

[niketn]

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.

[vshakur]

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.

[niketn]

Cheers !!!

[vshakur]

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.

[niketn]

@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?

[vshakur]

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.

[vshakur]

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?

[vshakur]

Thanks, I'll try that.

[vshakur]

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