Return results where ClientID in first search DOES NOT match any ClientID in second search.

[griffinpair]

Search 1:

source=*D:\\XSP\\importhelpers* source=*IH_Daily\\DebugImportHelper* End
| rex field=source "importhelpers\\\\+(?LateClientID[^\\\\]+)"
| where (LateClientID="WHI")
OR (LateClientID="IRM")

Results:

LateClientID: WHI, IRM

Search 2:

index="si_errors" sourcetype="si_LateEnd"

Results:

ClientID: WHI, ALP, USBI

Based on the results, I would want data from IRM to be returned. This is because any ClientID in the second search that matches a LateClientID returned in the first search I DO NOT want data from.

---

updated to mark code, DMJ

[DalJeanis · Accepted solution]

Try this ...

 source=*D:\\XSP\\importhelpers* source=*IH_Daily\\DebugImportHelper* End
 | rex field=source "importhelpers\\\\+(?LateClientID[^\\\\]+)"
 | where NOT [
      index="si_errors" sourcetype="si_LateEnd" 
     | dedup ClientID | table ClientID | rename ClientID as lateClientID  
     ]

[griffinpair]

This search works perfect thanks!! One more question for this though. How do I only search for records within the last 24 hours on the sub-search?

[kmorris_splunk]

Add the following to your sub-search:

index="si_errors" sourcetype="si_LateEnd" earliest=-24h@h latest=now

[griffinpair]

It works good before I add that text. After I add it I get the error "Error in 'where' command: The 'not' function is unsupported or undefined."

[kmorris_splunk]

You could use a sub-search for this. Try something like this:

source=D:\\XSP\\importhelpers source=IH_Daily\\DebugImportHelper End 
| rex field=source "importhelpers\\\+(?LateClientID[^\\\]+)"
    NOT 
    [ search index="si_errors" sourcetype="si_LateEnd" 
    | eval LateClientID=ClientID]

[griffinpair]

The "NOT" here is throwing an error. It is saying it is an invalid argument.

[DalJeanis]

@griffinpair - run the subsearch all together on one line and try again. kmorris and I both chose to prettify the code - almost exactly the same way by coincidence - and sometimes splunk objects to whitespace in certain locations.

You should add | table LateClientID just inside the last subsearch bracket also.

[DalJeanis]

@kmorris [Splunk] - OOOO - very close... but you left the ClientID field (and possibly others) to hit the implicit format....... 😉