topic Re: Why is the subsearch excluding inputlookup results? in Splunk Search

Why is the subsearch excluding inputlookup results?

swengroeneveld — Thu, 20 Feb 2020 06:07:56 GMT

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,

Re: Why is the subsearch excluding inputlookup results?

morethanyell — Thu, 20 Feb 2020 14:06:10 GMT

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] ?

Re: Why is the subsearch excluding inputlookup results?

manjunathmeti — Thu, 20 Feb 2020 14:44:26 GMT

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)

Re: Why is the subsearch excluding inputlookup results?

swengroeneveld — Fri, 21 Feb 2020 09:52:56 GMT

This seems to be working perfectly! Thanks a lot!

Re: Why is the subsearch excluding inputlookup results?

swengroeneveld — Fri, 21 Feb 2020 09:54:32 GMT

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.