How to get the count of a specific value?

[apignata]

How would you return the count of only the Reachable devices?
In the picture above you would return 8.

When using the query below I receive a result of 0

| stats count("SWITCHES_AND_HUBS{}.reachability_status"=REACHABLE)

[gcusello]

Hi @apignata,

I'd rename the field and use quotes in the eval:

| rename "SWITCHES_AND_HUBS{}.reachability_status" AS reachability_status
| stats count(eval(reachability_status="REACHABLE")) AS reachable

Ciao.

Giuseppe

[kamlesh_vaghela]

@apignata 

Try eval in count function.

YOUR_SEARCH
| stats count(eval('SWITCHES_AND_HUBS{}.reachability_status'=="REACHABLE")) as Count

Please refer below link for more.

https://docs.splunk.com/Documentation/SplunkCloud/latest/Search/Usestatswithevalexpressionsandfuncti...

Thanks
KV

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[apignata]

Thanks for providing the info on eval.

When running the query I noticed the count is showing 413 instead of the expected 3,312.
Do you have any insight?

Here is an example of the data:
- The data is in JSON format
- Example of one event below
- Run through a script to create multiple events in a day about the updated status

"SWITCHES_AND_HUBS": [
{
"name": "test1",
"reachability_status": "REACHABLE"
},
{
"name": "test2",
"reachability_status": "UNREACHABLE"
},

To get the info on the latest event, can I use below to filter the data? Or would stats latest/last be better?

| head 1 

Thank you!

[kamlesh_vaghela · Accepted solution]

@apignata 

| stats count(eval('SWITCHES_AND_HUBS{}.reachability_status'=="REACHABLE"))

The above count command consider an event as one count if  eval condition get passed. As you have multivalued filed, means multiple reachability_status values in single events, this command is showing you 413 count from 1239 events.  As you have 3312 REACHABLE status then we should consider all values as separate. 

Can you please try below search for your case?

YOUR_SEARCH 
| stats count(eval('SWITCHES_AND_HUBS{}.reachability_status'=="REACHABLE")) as REACHABLE, count(eval('SWITCHES_AND_HUBS{}.reachability_status'=="UNREACHABLE")) as UNREACHABLE

My Sample Search :

| makeresults 
| eval _raw="{\"SWITCHES_AND_HUBS\": [{\"name\": \"test1\",\"reachability_status\": \"REACHABLE\"},{\"name\": \"test2\",\"reachability_status\": \"UNREACHABLE\"},{\"name\": \"test3\",\"reachability_status\": \"UNREACHABLE\"},{\"name\": \"test4\",\"reachability_status\": \"UNREACHABLE\"}]}" 
| kv
|mvexpand SWITCHES_AND_HUBS{}.reachability_status
| stats count(eval('SWITCHES_AND_HUBS{}.reachability_status'=="REACHABLE")) as REACHABLE, count(eval('SWITCHES_AND_HUBS{}.reachability_status'=="UNREACHABLE")) as UNREACHABLE

Thanks
KV


If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[apignata]

@kamlesh_vaghela Thank you for the reply. The sample search helped me understand how I could use mvexpand to expand the values of SWITCHES_AND_HUBS{}.reachability_status field into separate events. Then use the count(eval() to get the result

[VatsalJagani]

@apignata - use the solution given by @kamlesh_vaghela 

Use single quotes instead of double quotes when you want to specify column/field names. (except with rename command)

The bouble quote is for value/string.