How do I filter field values not present in the successful events in the super search

[rafiqul]

I wanted to extract MAC address from events that were never succeeded within a time boundary. I am dealing with events having both SUCCESS and FAILURES for the same MAC address. I want to exclude those MAC address, and only to find those were failed only. I have Device_Mac field filtered from sub search which returns 1000s of MAC address. I then wanted to run each of these MAC address in the main search and filter out those mac address found in the successful events. I was using NOT Device_Mac="*" to filter those mac address, but it's not returning anything in the result. Can you please help how to exclude those MAC address from the main search results, and find the MAC address that were never succeeded within the time boundary ? I will eventually use the extracted MAC address to run in another super search with latest="04/10/2019:00:00:00" (which is not shown in my following search). Any better search you can recommend ? Thanks for your help.

Here is my search:

index=blah sourcetype=blahblah success earliest="04/10/2019:00:00:00" NOT Device_Mac="*" | search [search index=blah sourcetype=blahblah "500 Internal Error" earliest="04/10/2019:00:00:00" | stats count by Device_Mac | table Device_Mac ] | stats count by Device_Mac

[grittonc]

I think you could create two new fields, one to indicate whether an event is a failure, and one to indicate whether it is a success. Then you can count both of those columns and eliminate the MAC addresses that only had failures.

index=blah sourcetype=blahblah earliest="04/10/2019:00:00:00"
| eval failure=if(match(_raw,".*500 Internal Error.*"), 1, 0), success=if(failure=1, 0, 1)
| stats sum(failure) as failure, sum(success) as success by Device_Mac 
| search failure>0 AND success =0

This also eliminates the potential problem of your results exceeding subsearch limits.