Knowledge Management

## How do you calculate ratios of counts to field totals?

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.

Tags (4)
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
``````
Splunk Employee

@chris94089

If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

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.

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

State of Splunk Careers