topic Re: Calculating percentages based on counts in Splunk Search

Calculating percentages based on counts

hduncan7 — Tue, 04 Jun 2019 13:10:44 GMT

I'm trying to get percentages based on the number of logs per table. I want the results to look like this:

Table Count Percentage
Total 14392 100
TBL1 8302 57.68
TBL2 4293 29.93
TBL3 838 5.82
TBL4 639 4.44
TBL5 320 2.22

Here's my search so far:

text = "\*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | eventstats count AS Total
| append [search PAJYE text = "\*TBL1\*" | stats count | eval "Count Type" = "TBL1 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*TBL2\*" | stats count | eval "Count Type" = "TBL2 Count" | eval "Percentage" = ((count/Total)\*100)] 
| append [search PAJYE text = "\*TBL3\*" | stats count | eval "Count Type" = "TBL3 Count" | eval "Percentage" = ((count/Total)\*100)] 
| append [search PAJYE text = "\*TBL4\*" | stats count | eval "Count Type" = "TBL4 Count" | eval "Percentage" = ((count/Total)\*100)] 
| append [search PAJYE text = "\*TBL5\*" | stats count | eval "Count Type" = "TBL5 Count" | eval "Percentage" = ((count/Total)\*100)]
| append [search PAJYE text = "\*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | stats count | eval "Count Type" = "Total Count" | eval "Percentage" = ((count/Total)\*100)]
| rename count as "Count"
| sort - "Count"
| table "Count Type", "Count", "Percentage"

I've tried so many different methods of trying to get this to work. My results are either a percentage column with no data, the counts get messed up, or pages of empty rows following my data.

Any help would be appreciated.

Re: Calculating percentages based on counts

kmorris_splunk — Tue, 04 Jun 2019 15:15:14 GMT

I would extract the Table name as a field if you haven't already, the try something like the following:

(tablename=TBL1 OR tablename=TBL2 OR tablename=TBL3)
| eventstats count as totalevents 
| eventstats count as tablecount by tablename 
| eval percentage=(tablecount/totalevents)*100 
| stats values(tablecount) as totalevents values(percentage) as table_perc by tablename

I only covered 3 of the tablenames, but you get the idea.

Re: Calculating percentages based on counts

renjith_nair — Tue, 04 Jun 2019 17:03:40 GMT

@hduncan7 ,

Try

| stats count by Table | addcoltotals labelfield="Table"| eval _Total=if(Table=="Total",count,0)
| eventstats sum(_Total) as _Total| eval percentage=(count/_Total)*100

Re: Calculating percentages based on counts

hduncan7 — Tue, 04 Jun 2019 17:14:55 GMT

This is basically what I ended up doing. I found a solution but forgot to update my question.

My percentage is multiplied by 200 to account for the fact that I'm finding the total count as well.

Thanks for your help kmorris