topic Re: Work around if sub-search does not return data for a where clause. in Splunk Search

Work around if sub-search does not return data for a where clause.

griffinpair — Tue, 29 Sep 2020 15:18:28 GMT

Search:
source=D:\XSP\importhelper source=IH_Daily\DebugImportHelper End
| eval dayBuffer=strftime(now(), "%d") | eval day=ltrim(tostring(dayBuffer),"0")
| eval todayBuffer=strftime(now(), "%m_"+day+"_%Y") | eval today=ltrim(tostring(todayBuffer),"0") | where like(source,"%".today."%")
| rex field=source "importhelpers\+(?ClientID[^\]+)"
| where (ClientID="WHI") OR (ClientID="IRM")
| where NOT [ search earliest=-24h@h latest=now index="si_errors" sourcetype="si_LateEnd" | dedup ClientID | table ClientID ] | table ClientID, ...data for each ClientID returned, etc.

IF I have results in the sub-search for the "where NOT" clause to compare against then I have no problems and it takes out the ClientIDs I do not want to see. However, if the sub-search is empty (the log files being monitored on sourcetype="si_LateEnd" have not been changed in the last 24 hours) then I get the error:
"Error in 'where' command: The 'not' function is unsupported or undefined"

In this case, based on the above search, I would want to return data for ClientIDs "WHI" and "IRM", rather than get an error. What possible work around is there for this error in my case?

Re: Work around if sub-search does not return data for a where clause.

DalJeanis — Thu, 10 Aug 2017 22:05:12 GMT

[ search earliest=-24h@h latest=now index="si_errors" sourcetype="si_LateEnd" | dedup ClientID
| append [| makeresults | eval ClientID = "NobodyReallyButThereHasToBeSomebody"] 
| table ClientID   ]

Re: Work around if sub-search does not return data for a where clause.

griffinpair — Fri, 11 Aug 2017 13:48:56 GMT

This works perfect! Thank you so much!

Re: Work around if sub-search does not return data for a where clause.

mhergh — Thu, 25 Feb 2021 17:36:29 GMT

Big big kudos buddy for the solution!!!

I tried all kind of tricks to get around the void list for the subsearch - related to a NOT operator, and finally got this one from you.

I find some things in the Splunk SPL pretty dumb, just to put it on the polite side!

Thanks a lot again.