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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...