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

[pwilson]

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?

[richgalloway · Accepted solution]

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