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

[njohnson7]

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" ]

[njohnson7]

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

[martinpu · Accepted solution]

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 🙂