Splunk Search

Handle 0 Count, No results found & when no data is being indexed

njohnson7
Path Finder

I was displaying the count of certain type of locks using the query below.

index=A sourcetype="source" LOCK_MODE!="" SYSTEM_ID=SYSTEM1 
| dedup CLIENT_ID ENTRY_TIME USER_ID LOCK_TEXT LOCK_MODE 
| eval SYSTEM=SYSTEM_ID."-".CLIENT_ID 
| eval DURATION=strptime(DATETIME, "%d/%m/%Y %H:%M:%S") - strptime(ENTRY_TIME, "%d/%m/%Y %H:%M:%S") 
| eval LOCK_DURATION=round(DURATION/3600) 
| eval LOCK_STATUS=case(LOCK_DURATION > 15, "RED", LOCK_DURATION > 8, "AMBER" , 1=1, "GREEN") 
| search LOCK_STATUS=RED 
| stats count 
| rangemap field=count low=0-1 default=severe

Recently, we faced a situation where our SYSTEM_ID was down and no data was being indexed - So obviously my count was being displayed as 0 and in GREEN colour. I tried just the stats count in line 8 and the results will always be 0 even if

So now I have to handle two situations, 1) the count should be 0 and in GREEN in case there are no lock types I am looking for.

2) the count should be 0 or "No events are indexed" and in RED only incase no data is flowing to index=A sourcetype="source" LOCK_MODE!="" SYSTEM_ID=SYSTEM1 for the specified timeperiod.

I tried the following but it doesn't handle the two situations i need together. Either even if the count is zero although data is flowing , it is being changed to "no events found". I tried just the stats command in line number 8 , and my count will always be 0 irrespective of whether data is flowing or not.

index=A sourcetype="source" LOCK_MODE!="" SYSTEM_ID=SYSTEM1 
| dedup CLIENT_ID ENTRY_TIME USER_ID LOCK_TEXT LOCK_MODE 
| eval SYSTEM=SYSTEM_ID."-".CLIENT_ID 
| eval DURATION=strptime(DATETIME, "%d/%m/%Y %H:%M:%S") - strptime(ENTRY_TIME, "%d/%m/%Y %H:%M:%S") 
| eval LOCK_DURATION=round(DURATION/3600) 
| eval LOCK_STATUS=case(LOCK_DURATION > 15, "RED", LOCK_DURATION > 8, "AMBER" , 1=1, "GREEN") 
| search LOCK_STATUS=RED 
| stats count AS "Event Status" by LOCK_STATUS 
| table "Event Status" 
| appendpipe 
    [| stats count 
    | eval "Event Status"="No events indexed for the time range" 
    | where count==0 
    | fields - count ] 
| rangemap field="Event Status" low=0-1 default=severe

In the appendpipe I tried using eventstats command to evaluate if there are no events, but because of | stats count AS "Event Status" by LOCK_STATUS --> it is showing no results found , and if I just do stats , the result will always be 0.

| appendpipe   [| eventstats count as
   "Number of Events" 
       | eval "Event Status"="No events indexed for the time range" 
       | where 'Number of Events'==0 
       | fields - "Number of Events" ]
0 Karma
1 Solution

martinpu
Communicator

Try this:

index=A sourcetype="source" LOCK_MODE!="" SYSTEM_ID=SYSTEM1 
| dedup CLIENT_ID ENTRY_TIME USER_ID LOCK_TEXT LOCK_MODE 
| eval SYSTEM=SYSTEM_ID."-".CLIENT_ID 
| eval DURATION=strptime(DATETIME, "%d/%m/%Y %H:%M:%S") - strptime(ENTRY_TIME, "%d/%m/%Y %H:%M:%S") 
| eval LOCK_DURATION=round(DURATION/3600) 
| eval LOCK_STATUS=case(LOCK_DURATION > 15, "RED", LOCK_DURATION > 8, "AMBER" , 1=1, "GREEN") 
| stats count(eval(if(LOCK_STATUS="RED", LOCK_STATUS, null))) as lock_status_counted, count(LOCK_STATUS) as total 
| table total lock_status_counted 
| eval result=case(total="0", "No events indexed for the time range" , lock_status_counted>1, "Severe", 1=1, "Low") 
| table result

Using evals inside of stats commands allows for some nifty Splunking 🙂

View solution in original post

0 Karma

njohnson7
Path Finder

@martinpu

Thanks a lot - just modified it to suit my need.

| eval result=case(total="0", "No events indexed for the time range" , lock_status_counted>=1, 'lock_status_counted', lock_status_counted=0, 0) 
0 Karma

martinpu
Communicator

Try this:

index=A sourcetype="source" LOCK_MODE!="" SYSTEM_ID=SYSTEM1 
| dedup CLIENT_ID ENTRY_TIME USER_ID LOCK_TEXT LOCK_MODE 
| eval SYSTEM=SYSTEM_ID."-".CLIENT_ID 
| eval DURATION=strptime(DATETIME, "%d/%m/%Y %H:%M:%S") - strptime(ENTRY_TIME, "%d/%m/%Y %H:%M:%S") 
| eval LOCK_DURATION=round(DURATION/3600) 
| eval LOCK_STATUS=case(LOCK_DURATION > 15, "RED", LOCK_DURATION > 8, "AMBER" , 1=1, "GREEN") 
| stats count(eval(if(LOCK_STATUS="RED", LOCK_STATUS, null))) as lock_status_counted, count(LOCK_STATUS) as total 
| table total lock_status_counted 
| eval result=case(total="0", "No events indexed for the time range" , lock_status_counted>1, "Severe", 1=1, "Low") 
| table result

Using evals inside of stats commands allows for some nifty Splunking 🙂

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...