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
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)
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)
Excellent, I use the first option, and it give me the result I am looking for. Thanks again for your quick solution!