NOT Subsearch

[praveenvemuri]

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.

[DavidHourani]

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

[MSimon]

There is a typo in the final search

rex mode=sed field=search ...

[rtadams89]

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.

[rschutt]

This should be your search:

Index=test event=initiated | dedup ip-address | table ip-address | search NOT [search Index=test event=closed | dedup ip-address | table ip-address]

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

[praveenvemuri]

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.

[DavidHourani]

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