Splunk Search

Can a stats search use if/else functions or something similar?

Path Finder

I am counting the occurrence of uniq keys in the log file and need to do the following count:

<...>key1<....>
<...>key2<...>
<...>key3<...>
<...>key1<...>

If the key only appear once, then:

|stats count by key | where count = 1 | stats sum(count) as key_once

If the key appears more than once, then:

|stats count by key | where count > 1 | stats dc(key) as key_gt_one

Last, I need to find out the ratio of the two:

|eval ratio=round(key_once/key_gt_one, 1)

What would be the best way of doing this? Right now, I am doing two searches and use "appendcols": one search to get the result for "key_once", one for "key_gt_one". It seems very inefficient. Wonder if there is a better way of doing this.

Thanks

Tags (4)
1 Solution

SplunkTrust
SplunkTrust

Try something like this

your base search  | stats count by key | stats count(eval(count=1)) as key_once count(eval(count>1)) as key_gt_one |eval ratio=round(key_once/key_gt_one, 1)

Alternatively

your base search | stats count by key | eval key_once=if(count=1,1,0) | eval key_gt_one=if(count>1,1,0) | stats sum(*) as * |eval ratio=round(key_once/key_gt_one, 1)

View solution in original post

SplunkTrust
SplunkTrust

Try something like this

your base search  | stats count by key | stats count(eval(count=1)) as key_once count(eval(count>1)) as key_gt_one |eval ratio=round(key_once/key_gt_one, 1)

Alternatively

your base search | stats count by key | eval key_once=if(count=1,1,0) | eval key_gt_one=if(count>1,1,0) | stats sum(*) as * |eval ratio=round(key_once/key_gt_one, 1)

View solution in original post

Path Finder

Excellent, I use the first option, and it give me the result I am looking for. Thanks again for your quick solution!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!