Splunk Search

How to get a total count and count by specific field displayed in the same stats table?

matthewb4
Path Finder

Let's say I have a base search query that contains the field 'myField'. I want to create a query that results in a table with total count and count per myField value. In addition, I want the percentage of (count per myField / totalCount) for each row. I want it to look like the following...

| myField | count | totalCount| percentage
| a       | 5     | 50        | .1
| b       | 10    | 50        | .2
| c       | 20    | 50        | .4
| d       | 5     | 50        | .1
| e       | 10    | 50        | .2

I'm trying to run something like...

base query | stats count as totalCount | stats count by myField | eval percentage=(count/totalCount)

Obviously this isn't right, but I don't know where to go from here. Any suggestions would be very appreciated. Thanks.

Tags (4)
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

base search | stats count by myfield | eventstats sum(count) as totalCount  | eval percentage=(count/totalCount)

OR

base search | top limit=0 count by myfield showperc=t | eventstats sum(count) as totalCount

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

base search | stats count by myfield | eventstats sum(count) as totalCount  | eval percentage=(count/totalCount)

OR

base search | top limit=0 count by myfield showperc=t | eventstats sum(count) as totalCount

matthewb4
Path Finder

wow, that worked perfectly, thx. I was messing around with eventstats earlier and could not get this work. If you could explain the sum part to me it would be very helpful. As far as I am aware, 'eventstats' works off the original and unaltered set of data as if it was the first pipe command. I am slight confused on how sum(count) produces the correct results in this case.

somesoni2
SplunkTrust
SplunkTrust

The eventstats works on the dataset/result available to it (all result in whatever format available just before eventstats command is invoked), and without altering it, adds new information/column. So the data available before eventstats was the output of "stats count by myfield", which will give you one row per myfield with corresponding count. The eventstats is then summing all those count values and adding it as a new field to each row.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Easiest option to understand is the just run the command before eventstats and then add the eventstats to see the changes.

0 Karma

matthewb4
Path Finder

Ok I guess my question then is why does this work...
base search | stats count by myfield | eventstats sum(count) as totalCount
But this doesn't...
base search | stats count by myfield | stats sum(count) as totalCount

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You're using stats command to calculate the totalCount which will summarize the results before that, so you'll only get a single row single column for totalCount. Your requirement was to keep the myfield and corresponding count, and get an additional field for totalCount (to calculate percentage) in each row, so eventstats is the way to go.

matthewb4
Path Finder

oh ok my understanding of eventstats was slightly off, your last comment makes a lot of sense now, thanks for all the help, this has been troubling me all day. You definitely cleared up my confusion!

Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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