topic Re: NOT Subsearch in Splunk Search

NOT Subsearch

praveenvemuri — Thu, 05 Jan 2012 00:09:06 GMT

Hi
1) Index=test event=initiated | dedup ip-address | table ip-address gives me the initiated transactions.
2) Index=test event=closed | dedup ip-address | table ip-address gives the closed transactions.

I need to display active transactions. so i need to remove the ip-address's of sub search from main search. I tried NOT and it didnt work. also is there any limit for sub search. the above searches return millions of records.

please let me know the better approach for it.

Re: NOT Subsearch

rschutt — Fri, 06 Jan 2012 15:03:40 GMT

This should be your search:

I don't think that a sub-search has more limitation rather than the main-search.

Re: NOT Subsearch

praveenvemuri — Fri, 06 Jan 2012 20:50:05 GMT

Hi rschutt, Thanks fro responding. for some reason it is not working. it is displaying all the values same as Index=test event=initiated | dedup ip-address | table ip-address.

Re: NOT Subsearch

rtadams89 — Fri, 06 Jan 2012 21:02:24 GMT

You'll need to correlate the open and closed events somehow. You could do this with the transaction command, then return only events that aren't closed (that is, where the 'closed_txn' field the transaction command creates is equal to 0). You could also do a join, or selfjoin and then add " | where event!=closed " to your search.

Re: NOT Subsearch

DavidHourani — Mon, 16 Feb 2015 14:04:12 GMT

Has anyone answered this question ?? Because I noticed that the NOT that precedes a subsearch only gets applied to the first result in the subsearch.. I think using rex mode sed could be a useful here

Re: NOT Subsearch

DavidHourani — Tue, 17 Feb 2015 09:30:55 GMT

Hello praveenvemuri,

I had the same problem and I fixed it using return and rex mode=sed.

To do so, in your subsearch first start by returning the ip-address. That will output the result as (ip-address=X.X.X.X.X) OR (ip-address=Y.Y.Y.Y)..... Once you have that, add a regex to take off the OR and replace it with NOT :

| rex mode=sed field=search "s/OR/NOT/g"

That would leave you with a subsearch result that looks like this:
(ip-address=X.X.X.X.X) NOT (ip-address=Y.Y.Y.Y) NOT.....

Then all you have to do is add that subsearch to your search and add a NOT in front of it because there is no NOT in front of the first ip-address in the generated list.

Your final search should look like that:

Index=test event=initiated | dedup ip-address | table ip-address | search NOT [search Index=test event=closed | dedup ip-address  |return 100000 ip-address| rex mode=sed field=ip-address "s/OR/NOT/g"]

Let me know how that work out for you even if its a 2 years later answer lol

Regards,
David

Re: NOT Subsearch

MSimon — Thu, 01 Dec 2016 09:55:17 GMT

There is a typo in the final search

rex mode=sed field=search ...