Help with conditional event count.

[AFKunc]

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!

[yuanliu]

Two problems with the search.

1. 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.
2. 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

or

| 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 ```

[AFKunc]

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

[ITWhisperer]

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

[AFKunc]

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.

[kiran_panchavat · Accepted solution]

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

[AFKunc]

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