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.
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 ,
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
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.
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