Splunk Search

Help with conditional event count.

AFKunc
Explorer

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!

Labels (3)
0 Karma
1 Solution

kiran_panchavat
Influencer

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

 

kiran_panchavat_0-1736697625278.png

kiran_panchavat_1-1736697711660.png

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

Did this help? If yes, please consider giving kudos, marking it as the solution, or commenting for clarification — your feedback keeps the community going!

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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

_rawpayload.statusseq
{"seq":1,"payload":{"status":"ok"}}ok1
{"seq":2,"payload":{"status":"degraded"}}degraded2
{"seq":3,"payload":{"status":"ok"}}ok3

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

totalok_count
32

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
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

AFKunc
Explorer

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.

0 Karma

kiran_panchavat
Influencer

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

 

kiran_panchavat_0-1736697625278.png

kiran_panchavat_1-1736697711660.png

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

Did this help? If yes, please consider giving kudos, marking it as the solution, or commenting for clarification — your feedback keeps the community going!

AFKunc
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...