Splunk Search

Calculating percentages based on counts

hduncan7
Engager

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.

0 Karma
1 Solution

kmorris_splunk
Splunk Employee
Splunk Employee

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.

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

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
Engager

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...