Calculating with the result of stats count.

[ndcl]

Hi Base,

I tried to calculate a ratio of the occurrence of a value in a field. F.e. the field is Rvals and the values are 1,3,4,4,3,10,5,8,9,10.

I want to calculate the occurrence of “4” so my approach is first get the total:

…Rvals=* | stats count as r01

Now I need the amount of 4. So I thought append is (maybe) a good idea:

…Rvals=* | stats count as r01 | append [search Rvals=4 | stats count as r02]

That produce 10 (r01) and 2 (r02) and then calculate:

…Rvals=* | stats count as r01 | append [search Rvals=4 | stats count as r02] | eval r=(r02 * 100)/r01

And now the surprise: r is empty. For a short control:

…Rvals=* | stats count as r01 | append [search Rvals=4 | stats count as r02] | eval

r=(r02*100)/r01 | stats values(r01),values(r02),values(r)

I got results for r01,r02 but r shows no results. The question is why?

One more: I do not looking for an alternative way. I want to know why splunk cannot calculate the result of count?

Thanks!

[bmacias84]

Ok, I think I know what you are trying to do. I beleive you should use eventstats which will add a new colum to your events. This example uses web server status codes. Keep in mind if cardinality of your Rvals is high this search will perform poorly as cardinality increases.

Step by step:

# only distilling to field require for final output will increase performance

...| fields sc_status, _time

Next

# eventstats will now add a new field called total which is a count of all events returned

...| fields sc_status, _time |eventstats count as total

Next

# events stats will now added a new field called rvals which is a count of unique rvals

...| fields _time, sc_status | eventstats count as total|  eventstats count as rvals by sc_status

Next

# all the totalling has been preformed for each event by unquiue rvals let dedup because the records are identical

... | fields _time, sc_status | eventstats count as total|  eventstats count as rvals by sc_status | dedup sc_status, rvals, total 

Next

# Math time

...|fields _time, sc_status | eventstats count as total|  eventstats count as rvals by sc_status | dedup sc_status, rvals, total | eval ratio=((rvals/total)*100)

Next

# output or formating time

...| fields _time, sc_status | eventstats count as total|  eventstats count as rvals by sc_status | dedup sc_status, rvals, total | eval ratio=((rvals/total)*100) | table sc_status, ratio

Hope this helps or gets you started. Dont forget to vote and accept answers that Help.

Cheers

[ndcl]

Hi,
thanks for that cool solution but this was not my question. I want to know why splunk cannot calculate “eval r=(r02 * 100)/r01”? If you cast “r=r01” or “r=r02” than r will have the values of r01 or r02.

Btw: if you simply replace the “append” with “appendcols” than it works! 🙂