topic Display results of search that are not in result of subsearch in Splunk Search

Display results of search that are not in result of subsearch

JARFB — Mon, 24 Aug 2020 04:15:09 GMT

I have events sent from a configuration management tool that may either contain a status of 'Job Started', or 'Job Completed'. My goal is to write a search that shows me events that are still in progress. My way of doing this is to have a search that looks for events by job ID, where there is a 'Job Started' event for that ID, but no 'Job Completed' event.

Job started search is simple, and I can successfully return a list of job ID's that have an event with the status "Job Started":

index=cm_tool event_status="Job Started" | table job_id

Similar to the job started search, the job completed search is just as easy:

index=cm_tool event_status="Job Completed" | table job_id

What I would like to do now, is show in a table only the job_ids that have results returned from the first search, but do not have a completed event as returned in the second search. Effectively, I'd like to see a list of unique job_id's with a started event, but no completed event. I've played around with sub-searches, however I am not having a ton of luck.

How might I go about doing this?

Re: Display results of search that are not in result of subsearch

Nisha18789 — Mon, 24 Aug 2020 05:30:19 GMT

Hi @JARFB , this should help to get that,

Index=cm_tool event_status= "Job Started" OR event_status= "Job Completed"

|stats values(event_status) as event_status by job_id

| eval matches = if(match(event_status,"*Job Completed*"), 1, 0)
|where match=0

Please upvote my response if this helps.

Re: Display results of search that are not in result of subsearch

JARFB — Mon, 24 Aug 2020 17:29:11 GMT

Hi @Nisha18789 - With a minor correction to the last line (match -> matches), this helped get me going. Thanks!

Re: Display results of search that are not in result of subsearch

Nisha18789 — Mon, 24 Aug 2020 17:45:31 GMT

thats great @JARFB , sorry for the typo.