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!

Security Highlights | November 2022 Newsletter

 November 2022 2022 Gartner Magic Quadrant for SIEM: Splunk Named a Leader for the 9th Year in a RowSplunk is ...

Platform Highlights | November 2022 Newsletter

 November 2022 Skill Up on Splunk with our New Builder Tech Talk SeriesCan you build it? Yes you can! *play ...

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...