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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...