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!

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...