topic Re: Perform stats count based on the value of a field in Splunk Search

Perform stats count based on the value of a field

JeffBothel — Wed, 13 Sep 2017 04:41:08 GMT

What I am looking to do is something of this nature:

| stats count(eval(if(action=success))), count(eval(if(action=failure))) by computer

but it has not been working out as I had hoped. Can anyone fill me in on what I might be able to do in order to get this result in my stats area of my search?

Re: Perform stats count based on the value of a field

woodcock — Wed, 13 Sep 2017 05:38:22 GMT

Too much if, not enough naming:

 ... | stats count(eval(action=="success")) AS successes, count(eval(action=="failure")) AS failures BY computer

Re: Perform stats count based on the value of a field

JeffBothel — Wed, 13 Sep 2017 06:12:20 GMT

Making this correction to the query did not result in the desired outcome. The query returns 0 for each and every value that was specified when there are at least a few successes and failures in the queried items.

Re: Perform stats count based on the value of a field

javiergn — Wed, 13 Sep 2017 07:32:28 GMT

I think your syntax is wrong. What about this:

| stats 
    count(eval(if(action="success", 1, null()))) as success_count
    count(eval(if(action="failure", 1, null()))) as failure_count 
    by computer

Or simply this instead:

| stats 
    count(eval(action="success")) as success_count
    count(eval(action="failure")) as failure_count 
    by computer

Thanks,
J

Re: Perform stats count based on the value of a field

cpetterborg — Wed, 13 Sep 2017 09:25:29 GMT

If you already have action as a field with values that can be "success" or "failure" or something else (or nothing), what about:

... (action=success OR action=failure) 
| stats count by action, computer

where ... is your original base search. If you have already done some processing of the events, then you may have to resort to something like:

 ... | search action=success OR action=failure
 | stats count by action, computer

The if's in your search aren't complete and seem to be unneeded.

Re: Perform stats count based on the value of a field

JeffBothel — Wed, 13 Sep 2017 09:32:43 GMT

Yes you are correct, the syntax is wrong but I was looking to get across what I am essentially trying to do in a clear and concise manner. I do know from having tried it previously that your second code idea does not work having put that into the search from a previous example of a similar type of code and that did not solve the issue. However, testing the first thought you had on the syntax generated the desired result for this case and as such thank you for your suggestion.

Re: Perform stats count based on the value of a field

tsullivan_pfpt — Fri, 09 Feb 2018 14:16:22 GMT

This worked for me however success and failure need to be encapsulated in quotes - "success" & "failure"

Re: Perform stats count based on the value of a field

drodman29 — Wed, 11 Jul 2018 13:39:32 GMT

The count function using an eval seems to require an AS clause. As per the doco: "count(eval(status="404")) AS count_status"
However count(eval(status="404")) without an as clause will cause a job inspector failure, and sometimes you get a useful message:
Error in 'stats' command: You must specify a rename for the aggregation specifier on the dynamically evaluated field 'count(eval(status="404"))'.

Re: Perform stats count based on the value of a field

woodcock — Wed, 11 Jul 2018 15:35:47 GMT

I forgot the double-quotes. It is fixed now.