topic Re: What is the best way to exclude 2 or more countries from iplocation src_ip results? in Splunk Search

What is the best way to exclude 2 or more countries from iplocation src_ip results?

packet_hunter — Thu, 08 Dec 2016 22:37:01 GMT

Let's say I want to look up IP location for all IPs by user, but I want to exclude 2 or more countries?

For example:

...| iplocation src_ip | search Country!="India" AND Country!="Canada" AND Country!="United Kingdom" |stats values(Country) values(Region) values(City) values(src_ip) by user

Is there a better way to exclude countries other than Country!= .... ?

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

jkat54 — Thu, 08 Dec 2016 22:53:53 GMT

maybe

  | search NOT (Country=India OR Country=Canada OR Country="United Kingdom")

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

packet_hunter — Thu, 08 Dec 2016 22:56:06 GMT

Thank you for the reply. By writing it this way, is NOT better than != in terms of search performance?

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

somesoni2 — Thu, 08 Dec 2016 23:23:53 GMT

I don't think there is any performance different between those two methods (!= versus NOT). The only difference is when you use NOT to filter, it'll also keep the results where Country=null, whereas != will exclude them.

Do you see any performance issue in your query? The field Country is available after iplocation command and you're filtering right after that so I don't see any improvement there.

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

packet_hunter — Fri, 09 Dec 2016 14:10:20 GMT

That is what I was thinking.
I just wanted to get some opinions on exclusion as I have heard that searching for NOT or != slows down searches.

Thank you

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

packet_hunter — Sat, 10 Dec 2016 15:11:04 GMT

just have to convert to an answer for points, thankx

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

niketn — Sat, 10 Dec 2016 17:08:57 GMT

@packet_hunter ... You can run both searches and compare in job inspector for performance. Only recommendation is that Inclusion is better than Exclusion also filtering upfront in the query is better than filtering later on. However, both in your case are not feasible so just check for performance.

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

packet_hunter — Sat, 10 Dec 2016 18:45:16 GMT

thank you all for the replies! Niketnilay please convert your comment to answer and I will accept.

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

gcusello — Sun, 11 Dec 2016 09:17:32 GMT

Hi packet_hunter,
the better way to dinamically manage exclusions in a search or to manage many exclusions at the same time is to put them in a lookup and exclude results from your search:

 ...| iplocation src_ip | search NOT [ | inputlookup exclusions.csv | fields Country] |stats values(Country) values(Region) values(City) values(src_ip) by user

In this way if you need to modify exclusions list you don't need to modify all you searches, but only lookup, and you search is slimmer.

Bye.
Giuseppe

bye.
Giuseppe

Re: What is the best way to exclude 2 or more countries from iplocation src_ip results?

packet_hunter — Sun, 11 Dec 2016 16:34:00 GMT

Thank you for suggestion, it is very good and I am making a note of it for future use.