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

[jgcsco]

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

[somesoni2 · Accepted solution]

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]

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