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
Path Finder

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
Path Finder

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...