topic Re: How do I count the number of events based on the value of a field? in Splunk Search

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

andrewtrobec — Wed, 09 Nov 2016 15:34:13 GMT

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

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

sdaniels — Wed, 09 Nov 2016 19:07:23 GMT

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"

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

MonkeyK — Wed, 09 Nov 2016 20:32:34 GMT

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"

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

niketn — Wed, 09 Nov 2016 21:59:09 GMT

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"

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

andrewtrobec — Fri, 11 Nov 2016 09:55:26 GMT

Thanks for the suggestion and the solution to my problem!