Splunk Search

How to calculate percentages with multiple count function?

homer07
Explorer

I'm trying to calculate percentages based on the number of events per vary group. There are actually a lot of events, so can't use method like count(eval(...)). The summary of events is as follows:

 

 

 

color
------
green
red
greed
greed
red

 

 

 

 Here's my search so far:

 

 

 

index="test" sourcetype="csv"
| stats count as numColor by color
| eval total=5 
| eval percent=printf("%.2f", (numColor/total)*100)
| sort num(percent)
| table color numColor percent

 

 

 

How do I replace the hardcore variable value "total" with count() function or other methods?
Any help would be appreciated.

Labels (2)
0 Karma
1 Solution

PickleRick
SplunkTrust
SplunkTrust

Sorry, my mistake. sum(numColor), not sum(count). You renamed the column in your stats.

| makeresults 
| eval _raw="red
green
red
blue
green
red
red
green"
| multikv noheader=t
| table Column_1
| rename Column_1 as color| stats count as numColor by color |eventstats sum(numColor) as totalcolors
| eval percentage=numColor/totalcolors
| fields - totalcolors
| fieldformat percentage=round(percentage*100,1)

 

PickleRick_1-1630579139949.png

 

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

There is probably another way. But I'd do your initial search

index="test" sourcetype="csv"
| stats count as numColor by color

Then I'd add eventstats to have sum of the counts in every row

|eventstats sum(count) as totalcolors

So now you can simply evaluate

| eval percentage=numColor/totalcolors
0 Karma

homer07
Explorer

I tried your method, but it doesn't seem to work to use two count function in the same search.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Sorry, my mistake. sum(numColor), not sum(count). You renamed the column in your stats.

| makeresults 
| eval _raw="red
green
red
blue
green
red
red
green"
| multikv noheader=t
| table Column_1
| rename Column_1 as color| stats count as numColor by color |eventstats sum(numColor) as totalcolors
| eval percentage=numColor/totalcolors
| fields - totalcolors
| fieldformat percentage=round(percentage*100,1)

 

PickleRick_1-1630579139949.png

 

homer07
Explorer

Thanks, it's great!

0 Karma
Get Updates on the Splunk Community!

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...