Splunk Search

How to add total percentage to rows and columns

vshakur
Path Finder

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:

alt text

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

0 Karma
1 Solution

niketn
Legend

@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 "*"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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 "*"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

vshakur
Path Finder

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.

0 Karma

niketn
Legend

@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 &lt; and &gt; 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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vshakur
Path Finder

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.

0 Karma

niketn
Legend

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vshakur
Path Finder

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.

0 Karma

niketn
Legend

Cheers !!!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vshakur
Path Finder

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.

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vshakur
Path Finder

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.

0 Karma

vshakur
Path Finder

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?

0 Karma

vshakur
Path Finder

Thanks, I'll try that.

0 Karma

vshakur
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...