Splunk search for NOT IN

[asarolkar]

I have two sourcetypes A and B with column names Serial and SN respectively

To find where there is like a column name match in both A and B ->
(sourcetype=A Serial=) OR (sourcetype=B SN=) | search where Serial=SN

How do I write a query such that -> For every Serial (in A) - there are no matches to SN

[Damien_Dallimor]

You can achieve this with a NOT on a subsearch , equivalent to SQL "NOT IN".

Follow this link and scroll down to the "Use subsearch to correlate data" section:

sourcetype=A NOT [search sourcetype=B | rename SN as Serial | fields Serial ]

[joxley]

Be aware that if your subsearch starts with a generating command, you must exclude the search keyword:

e.g.

sourcetype=A NOT [ inputlookup my_lookup | rename SN as Serial | fields Serial ]

[reed_kelly]

One way to do it is to use a join on Serial and SN and then count the unique sourcetypes and look for results with 1 sourcetype of the kind you want. This may not be the most efficient way, but here goes:

sourcetype=A Serial=*|rename Serial as SN|join SN [search sourcetype=B SN=*]|stats first(_time) by SN,sourcetype|stats dc(sourcetype) as numst,min(sourcetype) as minst by SN|where numst==1 AND minst==A