Calculating percentages based on counts

[hduncan7]

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.

[renjith_nair]

@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

[kmorris_splunk · Accepted solution]

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.

[hduncan7]

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

My solution
text = "*TBL1*" | eval "Table Name" = "TBL1"
| append [search text = "*TBL2*" | eval "Table Name" = "TBL2"]
| append [search text = "*TBL3*" | eval "Table Name" = "TBL3"]
| append [search text = "*TBL4*" | eval "Table Name" = "TBL4"]
| append [search text = "*TBL5*" | eval "Table Name" = "TBL5"]
| append [search text = "*" (TBL1 OR TBL2 OR TBL3 OR TBL4 OR TBL5) | eval "Table Name" = "Total"]
| table "Table Name"
| eventstats count as "totalCount" | eventstats count as "tblCount" by "Table Name" | eval percent = round((tblCount/totalCount)*200,2) | stats values(tblCount) as Count, values(percent) as Percentage by "Table Name" | sort - Count

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