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