Remove results based on subsearch

[RamMur]

I'm looking to get all failed event log based on a field , and then trying to find the success event log for the same field, so that i have a net failed even log to report. 

Basically, i'm trying to an alert that i can run every X hours , looking back X hours, and finds me ONLY the failed logs that havent succeeded yet . The failures are retried at regular intervals. In short, get all failed events and weed out the succeeded ones on later retries , so i only have the ones that is still failed. 

Trying something like below but i think there should be better way than this.. 

index=sample cf_org_name=orgname service=xyz sourceSystem!="aaa" errorCd="*-701" status=FAILED
|where jobNumber NOT IN [search index=sample cf_org_name=orgname service=xyz sourceSystem!="aaa" status=SUCCESS ]
|table _time accountNumber jobNumber letterId errorCd
|sort _time

TIA!!

[bowesmana]

The Splunk way of doing this sort of task is to use stats, so you search both data sets, combine the bits you want based on the common field and then do conditional logic on the results, e.g.

index=sample cf_org_name=orgname service=xyz sourceSystem!="aaa" (errorCd="*-701" status=FAILED) OR status=SUCCESS
| stats min(eval(if(status="FAILED", _time, null()))) as _time values(status) as status count by  accountNumber jobNumber letterId errorCd
| where status="FAILED" AND mvcount(status)=1

which searches both failed and success events, and then combines them with stats, but retaining _time IFF the event is failed and split by the 4 fields. Without knowing your data, I don't know if letterId and errorCd have a 1:1 correlation with jobNumber, so you'll have to work out if that will work for you.

Then the final where condition will only look for events that have ONLY recorded a FAILED status.

Subsearches have their uses, but generally using NOT clauses is inefficient and a single search (no subsearches) is often a better approach.

[ITWhisperer · Accepted solution]

Try something like this

| eventstats latest(status) as latest_status by jobNumber
| where latest_status="FAILED"