How can I find values present in one table but not in other without subsearch?

[bhavyajain]

I have to prepare reporting dashboards in Splunk for which I used this query until now:

field1=GTIN_RECEIVED field2=NREC field3=*1234* field4=SNS  
NOT 
[search field1=MESSAGE_INVALID OR field1=GTIN_INVALID field2=NREC OR field2=PRODUCER field3=*1234* field4=SNS | dedup field5 
| fields field5 ] 

| dedup field5 
| table field5 
| rename field5 as gtin

The data size is huge now and the query takes too long to run which is becoming very difficult for me to generate dashboard.

Can someone pls help and simplify this query so that it takes minimal time.

[bowesmana · Accepted solution]

I believe this should work

field3=*1234* field4=SNS ((field1=GTIN_RECEIVED field2=NREC) OR ((field1=MESSAGE_INVALID OR field1=GTIN_INVALID) (field2=NREC OR field2=PRODUCER)))
| stats values(field1) as field1 by field5
| where isnull(mvfind(field1, "MESSAGE_INVALID")) AND isnull(mvfind(field1, "GTIN_INVALID"))
| fields field5
| rename field5 as gtin

You are searching for both the events you want (GTIN_RECEIVED) as well as those you don't (MESSAGE_INVALID or GTIN_INVALID). 

Use stats to collect all field1 values for all field5 values. If your field1 value has either of the two values you do not want, then the where clause will exclude those.

I hope you are also including index constraints there also, as with that search you will be searching all the indexes you have visibility of.

Make sure you have ALL the constraints you can in the search to minimise the data set you are processing, e.g. include sourcetype constraint to if appropriate

[bhavyajain]

Thanks. This worked!