topic Re: Splunk search for NOT IN in Getting Data In

Splunk search for NOT IN

asarolkar — Fri, 20 Apr 2012 21:36:31 GMT

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

Re: Splunk search for NOT IN

reed_kelly — Fri, 20 Apr 2012 22:00:03 GMT

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:

Re: Splunk search for NOT IN

Damien_Dallimor — Sat, 21 Apr 2012 00:12:55 GMT

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 ]

Re: Splunk search for NOT IN

joxley — Tue, 14 Apr 2015 14:33:37 GMT

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 ]