topic Re: Help with conditional event count. in Splunk Search

Help with conditional event count.

AFKunc — Sun, 12 Jan 2025 15:17:51 GMT

Hi, I have json data structured as follows:

{ "payload": { "status": "ok", # or "degraded" } }

I'm trying to use the stats command to count the "ok" and "degraded" events separately. I am using the following query:

index=whatever | eval is_ok=if(payload.status=="ok", 1, 0) | stats count as total, count(is_ok) as ok_count

I have tried passing it through spath, , with "=" in the if condition, and several other approaches changes. What always happens is that both counts contain all elements, despite there being different numbers of them. Please help!

Re: Help with conditional event count.

kiran_panchavat — Sun, 12 Jan 2025 16:02:23 GMT

@AFKunc

Kindly verify if the JSON data has been onboarded correctly. I tested it using the same data you provided. Could you confirm if this is the data you were expecting?

I hope this helps, if any reply helps you, you could add your upvote/karma points to that reply, thanks.

Re: Help with conditional event count.

ITWhisperer — Sun, 12 Jan 2025 16:28:51 GMT

Try using sum rather than count

index=whatever | eval is_ok=if(payload.status=="ok", 1, 0) | stats count as total, sum(is_ok) as ok_count

Re: Help with conditional event count.

AFKunc — Sun, 12 Jan 2025 19:32:28 GMT

It still fails in that it appears that the if(payload.status==...) always evaluates to false, despite there being both "ok" and "degraded" events, so the sum is equal to the count of all events.

Re: Help with conditional event count.

AFKunc — Sun, 12 Jan 2025 19:43:41 GMT

Worked like a charm. This line seems to be making all the difference: | spath path=payload.status output=status.

Re: Help with conditional event count.

yuanliu — Sun, 12 Jan 2025 19:57:47 GMT

Two problems with the search.

In an evaluation function, deep path payload.status needs to be single quoted (i.e., 'payload.status') to dereference its value. Otherwise bare word payload.status evaluates to null.
If you want to use count(is_ok), you should make the "other" value disappear, i.e., make it be a null, not a "real" value of 0. If you think 0 is a better representation for "other", use sum as @ITWhisperer suggests.

In other words, on mock event sequence

_raw	payload.status	seq
{"seq":1,"payload":{"status":"ok"}}	ok	1
{"seq":2,"payload":{"status":"degraded"}}	degraded	2
{"seq":3,"payload":{"status":"ok"}}	ok	3

either

| eval is_ok=if('payload.status'=="ok", 1, null()) | stats count as total, count(is_ok) as ok_count

| eval is_ok=if('payload.status'=="ok", 1, 0) | stats count as total, sum(is_ok) as ok_count

or even

| eval is_ok=if('payload.status'=="ok", 1, 0) | stats count as total, count(eval(is_ok = 1)) as ok_count

should give you

total	ok_count
3	2

This is an emulation you can play with and compare with real data

| makeresults format=json data="[ { \"seq\": 1, \"payload\": { \"status\": \"ok\", } }, { \"seq\": 2, \"payload\": { \"status\": \"degraded\", } }, { \"seq\": 3, \"payload\": { \"status\": \"ok\", } } ]" | fields - payload, seq, _time | spath ``` data emulation above ```

Re: Help with conditional event count.

AFKunc — Wed, 15 Jan 2025 10:24:55 GMT

Had I not chosen the solution already I would have given it to you for a more comprehensive answer 🙂