Display results of search that are not in result of subsearch

[JARFB]

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?

[Nisha18789 · Accepted solution]

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.

[JARFB]

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

[Nisha18789]

thats great @JARFB , sorry for the typo.