Knowledge Management
Highlighted

How do you calculate ratios of counts to field totals?

Explorer

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
Highlighted

Re: How do you calculate ratios of counts to field totals?

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
Highlighted

Re: How do you calculate ratios of counts to field totals?

Explorer

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
Highlighted

Re: How do you calculate ratios of counts to field totals?

@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
Highlighted

Re: How do you calculate ratios of counts to field totals?

Explorer

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
Highlighted

Re: How do you calculate ratios of counts to field totals?

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.