Splunk Search

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

jgcsco
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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)

jgcsco
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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...