Knowledge Management

How do you calculate ratios of counts to field totals?

chris94089
Path Finder

Here's what I have:

base search| stats count as spamtotal by spam

This gives me:

(13 events)
spam / spamtotal

===== =====
original / 5
crispy / 8

===== =====

What I want is:

(13 events)
spam / eggs / count / spamtotal / ratio

==========================

original / AAA / 2 / 5 / 0.4
original / BBB / 1 / 5 ** / 0.2
crispy / CCC / 2 / **8
/ 0.25
crispy / DDD / 2 / 8 / 0.25
etc...

==========================

Basically it's a ratio of count to the spamtotal, or a dynamic impact percentage. I feel like this should be easy. But stats and eventstats isn't working for me so far. Thank you.

0 Karma

bbialek
Path Finder

As suggested in another thread, using eval in a count instead of 'by' clause worked for me:

search msgType=*| timechart span=1h count(eval(msgType=="IN")) as IN, count(eval(msgType="OUT")) as OUT | eval ratio=OUT/IN
0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

@chris94089

Did the answer below solve your problem? If so, please resolve this post by approving it!
If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

0 Karma

chris94089
Path Finder

Yeah, I'm afraid I didn't ask my question correctly! I've updated my question below, or I can start a new post.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Try something like this...

 base search
| stats count as spamsubtotal by spam eggs
| eventstats sum(spamsubtotal) as spamtotal by spam
| eval ratio = round(spamsubtotal/spamtotal,2)
| rename spamsubtotal as count 
0 Karma

chris94089
Path Finder

Hmm, this table is doing what I described. Unfortunately, I did not ask my question correctly. Sorry!

I'm really hoping to use tstats with something like fillnull so I can compare counts of one field with the total counts of a different field. Right now splunk is only taking the fields that have counts in common and I don't know if there's an easy argument to choose a field to get totals from.

0 Karma
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...