topic Re: Subsearch NOT in in Splunk Search

Subsearch NOT in

asarolkar — Mon, 28 Sep 2020 11:38:37 GMT

I have two sourcetypes A and B - each has a column SERIAL_NUMBER

Sourcetype A has over 1000,000 records
Sourcetype B has over 15,000 records

I need every SERIAL_NUMBER in sourcetype A that is NOT present in sourcetype B - SO - I write a subsearch and insert a NOT in there - like SO :

sourcetype="A" SERIAL_NUMBER= * | search NOT [ search sourcetype="B" SERIAL_NUMBER= * | fields + SERIAL_NUMBER] | dedup SERIAL_NUMBER | table SERIAL_NUMBER

Is there a better way to create this query ?

I tried doing an outer join but that did not work out well

Re: Subsearch NOT in

sideview — Sun, 08 Apr 2012 19:03:03 GMT

You don't need a subsearch. Just use stats.

sourcetype=A OR sourcetype=B | stats values(sourcetype) as sourcetypes by SERIAL_NUMBER | search sourcetypes!="B" | table SERIAL_NUMBER

It's very common for people to gravitate to complex joins and subsearches and overlook a simpler way to do the same thing with stats.

In some cases subsearches offer the advantage of getting far fewer events off disk than a stats approach. However in your case you need every one of these events off disk anyway so you can perform the comparison. Thus the stats search is not only simpler but also a little faster because it can do all the work in a single search pipeline.

Furthermore you wont smack into the limits that subsearches have. Despite looking like such an attractive general tool to new Splunk users, subsearches are designed to be used only when the searches are relatively fast, and the results returned are numbered in dozens or hundreds... --

long-running subsearches will get finalized at the 60 second mark, and subsearches that generate more than 10,500 rows will get truncated there. Both limits can obviously result in the final results being off.

some links:

Functions for stats, chart and timechart (if you're going to memorize just one page in the Splunk documentation, make it this one)
Subsearches, when to use them, when not. pay careful attention to 'maxout' and 'maxtime'. Other limits on subsearches show up in append/join, such that in several cases there can be more than one limit applying. Again, subsearches are for cases where searches are fast and when they return only dozens or hundreds of results..

Re: Subsearch NOT in

asarolkar — Mon, 09 Apr 2012 00:41:33 GMT

Hi nick !

Thanks for your response

Would the search be the same if the other was being written to just an index

Meaning can I do >> sourcetype="A" OR index="B" and have the same desired outcome ?

Thanks

Re: Subsearch NOT in

sideview — Mon, 28 Sep 2020 11:39:00 GMT

assuming that sourcetype="A" events never appear in index="B", then yes it's about the same. Although note there's an extra values clause:

sourcetype="A" OR index="B" | stats values(sourcetype) as sourcetype values(index) as index by SERIAL_NUMBER | search index!="B" | table SERIAL_NUMBER

Re: Subsearch NOT in

asarolkar — Mon, 09 Apr 2012 21:30:25 GMT

Yea the events dont appear in B.

Although, as is described in the problem statement - there are tens of thousands of SERIAL_NUMBER that are present in sourectype A - that are also present in index B and the goal is to filter out ALL THESE SERIAL NUMBERS that appear in index B - OUT OF A

THe search described here does not work

Re: Subsearch NOT in

sideview — Mon, 28 Sep 2020 11:39:11 GMT

Sorry I had a typo in there. The field I was creating was called 'indexes', and then I was searching using 'index!="B"'. (Note indexes!=index). I have corrected the search in the comment. the correct search is
sourcetype="A" OR index="B" | stats values(sourcetype) as sourcetype values(index) as index by SERIAL_NUMBER | search index!="B" | table SERIAL_NUMBER

Re: Subsearch NOT in

lzhang_soliton — Sun, 14 Apr 2013 23:40:38 GMT

You should try

| set diff

to return results that are not common to both.
Maybe you also need use

| set union

to get results in sourcetypes A.