How do I count the number of events based on the value of a field?

[andrewtrobec]

Hello,

I am having trouble with a simple search. I have the following data:

OBJECT ID,NEW STATE
1,STATE ONE
1,STATE TWO
1,STATE THREE
2,STATE ONE
2,STATE TWO
2,STATE ONE
3,STATE ONE

...and so forth

I would like to return the number of events in which "NEW STATE" = "STATE ONE". I have the following search:

index = "SAMPLE INDEX" | stats count(eval("NEW STATE" = "STATE ONE")) as "COUNT"

Instead of returning "COUNT"=4, it returns 0 and I can't understand why. Is there something missing?

Any help would be greatly appreciated!

Thank you,

Andrew

[niketn · Accepted solution]

As a optimized search you should include filter upfront rather than later. Following should work with better performance:

index = "SAMPLE INDEX" "NEW STATE"="STATE ONE"| stats count

If you intend to use your own query replace double quotes with single quotes for the 'New State' field. As within eval expression it does not match the field but value on both left and right hand side.

index = "SAMPLE INDEX" | stats count(eval('NEW STATE' = "STATE ONE")) as "COUNT"

[andrewtrobec]

Thanks for the suggestion and the solution to my problem!

[MonkeyK]

First off: you should consider changing your data definition to not include spaces in field names. I am not very experienced, but spaces in field names have already caused problems. Try denoting words by using CamelCase or separating them with underscores "_"

As to your question, if you are really only interested in a single state, you can filter your results before getting a count

index = "SAMPLE INDEX" | search "STATE ONE" | stats count

If you are trying to get counts for everything, you can just count by the field

index = "SAMPLE INDEX" | stats count by "NEW STATE"

But it is possible that Splunk will misinterpret the field "NEW STATE" because of the space in it, so it may just be found as "STATE". So if the above doesn't work, try this:

index = "SAMPLE INDEX" | stats count by "STATE"

[sdaniels]

You can use the Splunk top command. It will automatically give you a percentage as well and i've turned that off in the search below.

http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/Top

index = "SAMPLE INDEX" | top showperc=false "NEW STATE"