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
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 ]
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 ]
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