How to display a log based off of fields from multiple logs

[Toshbar]

I'm not 100% sure how to title this question so please let me know if you have a suggestion on how to re-title it and i'll edit it.

I have tens of logs showing daily for each JOB
Here is an example of two logs for one of them:

 DATETIME:   2017-08-11 13:00:13.85 -0700   
 JOBNAME:    CIMR801D   
 MSGTXT:     CODE=ENDED - TIME=13.00.13 

 DATETIME:   2017-08-10 20:44:19.21 -0700   
 JOBNAME:    CIMR801D   
 MSGTXT:    CODE=JOB FAILED=S000 U1536 REASON=*

I only want to show jobs that have ended but haven't had any fails. So i'm trying to only show JOBNAMES that have a log containing the text ENDED TIME within the MSGTXT field but not FAILED within MSGTXT of another log within the same day.

Any ideas?

[richgalloway]

Assuming the JOBNAME and MSGTXT fields are already indexed, this query should get you started.

index=foo MSGTXT="*ENDED*" | table JOBNAME

[woodcock · Accepted solution]

Like this:

... | stats values(MSGTXT) AS MSGTXTs BY JOBNAME
| where match(MSGTXTs, "CODE=ENDED") AND NOT match(MSGTXTs, "CODE=JOB FAILED")

If you need to keep the raw events, then change stats to eventstats.

[Toshbar]

This is perfect. I wasn't familiar with the match command and was trying to do an overly complicated join/append.