Splunk Search

How to calculate percentage in a total row generated by addcoltotals?

pwilson
Explorer

I am trying to add a percentage to the total row generated by addcoltotals. I would like to show the total percentage of successes for a search using top. addcoltotals seems to only perform a sum and doesn't calculate total percentage properly, so leaving "%" off the percentage values would result in it becoming 120 in the final cell.

Currently generated table:

user Total Successful Total Failed Total Calls Success Percentage
Maynard 2 3 5 40.00%
Keenan 8 2 10 80.00%
TOTALS 10 5 15  

Ideally the currently empty cell would display 66.67%.

Query:

 

search string
| top 0 countfield=Count percentfield=Percent status by user
| eventstats sum(eval(if(match(status, "2\d{2}"), Count, 0))) as success by user
| eventstats sum(eval(if(match(status,"[45]\d{2}"), Count, 0))) as fail by user 
| eventstats sum(Count) as total by user 
| eval percent_success = round((success)/(total)*100, 2)."%"
| stats values(success) as "Total Successful" values(fail) as "Total Failed" values(Total) as "Total Calls" values(percent_success) as Success Percentage by user 
| addcoltotals labelfield=user label=TOTALS

 

The ."%" prevents the addcoltotals from summing the values, leaving the bottom right cell blank.

 

Is there a way to override the sum functionality of addcoltotals? Or is there a way to manually add a row to a table generated by stats where I can just calculate the values manually?

Would it be possible to overwrite just the empty cell with a percentage calculation I do myself?

 

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Try calculating the percentage after adding column totals.

search string
| top 0 countfield=Count percentfield=Percent status by user
| eventstats sum(eval(if(match(status, "2\d{2}"), Count, 0))) as success by user
| eventstats sum(eval(if(match(status,"[45]\d{2}"), Count, 0))) as fail by user 
| eventstats sum(Count) as total by user 
| stats values(success) as "Total Successful" values(fail) as "Total Failed" values(Total) as "Total Calls"  by user 
| addcoltotals labelfield=user label=TOTALS
| eval "Success Percentage" = round(('Total Successful')/('Total Calls')*100, 2)."%"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Try calculating the percentage after adding column totals.

search string
| top 0 countfield=Count percentfield=Percent status by user
| eventstats sum(eval(if(match(status, "2\d{2}"), Count, 0))) as success by user
| eventstats sum(eval(if(match(status,"[45]\d{2}"), Count, 0))) as fail by user 
| eventstats sum(Count) as total by user 
| stats values(success) as "Total Successful" values(fail) as "Total Failed" values(Total) as "Total Calls"  by user 
| addcoltotals labelfield=user label=TOTALS
| eval "Success Percentage" = round(('Total Successful')/('Total Calls')*100, 2)."%"
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...