Why is the subsearch excluding inputlookup results?

[swengroeneveld]

Good morning,

Hope someone can help me out here. I am trying to get a list of IPs where hits are > 100, but I want to exclude an external list that is saved as an inputlookup file.

index=server site=login
| stats count AS Hits BY ip
| search Hits > 100
NOT [| inputlookup savedfile | fields test_ip | rename test_ip AS ip]

The problem I am facing here is that in both cases (so with removing the last line of the code "NOT [|..") I am getting the same number as with the line while I manually reviewed the result and a few IPs are in the input file as well as on the "base" query.

Also the following did not provide the desired results:

    index=server site=login 
    | stats count AS Hits BY ip
    | search Hits > 100
    | search NOT [ | inputlookup savedfile | fields test_ip | rename test_ip AS ip ]

Thanks for the feedback and thinking in advance,

[manjunathmeti · Accepted solution]

Use lookup command and exclude IPs that are matched. Try this query.

index=server site=login
| stats count AS Hits BY ip
| search Hits > 100
| lookup savedfile test_ip AS ip OUTPUT test_ip 
| where isnull(test_ip)

[swengroeneveld]

This seems to be working perfectly! Thanks a lot!

[morethanyell]

Not an answer but just curious as to why wouldn't you perform there filtering in the first level search, such as index=server site=login NOT [| inputlookup savedfile | fields test_ip | rename test_ip AS ip] ?

[swengroeneveld]

Good question, my thinking, and maybe wrongly, is that scripts execute left --> right, top to bottom.

Meaning that if I narrow the search first, the lookup goes quicker.