topic Re: How to get the count of a specific value? in Splunk Search

How to get the count of a specific value?

apignata — Wed, 06 Apr 2022 05:11:35 GMT

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)

Re: How to get the count of a specific value?

kamlesh_vaghela — Wed, 06 Apr 2022 05:39:09 GMT

@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/Usestatswithevalexpressionsandfunctions

Thanks
KV

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

Re: How to get the count of a specific value?

VatsalJagani — Wed, 06 Apr 2022 05:51:34 GMT

@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.

Re: How to get the count of a specific value?

gcusello — Wed, 06 Apr 2022 08:19:20 GMT

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

Re: How to get the count of a specific value?

apignata — Wed, 06 Apr 2022 22:00:07 GMT

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!

Re: How to get the count of a specific value?

kamlesh_vaghela — Thu, 07 Apr 2022 04:41:34 GMT

@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.

Re: How to get the count of a specific value?

apignata — Thu, 07 Apr 2022 19:47:19 GMT

@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