topic Re: Join Ip with a subnet in Splunk Search

Join Ip with a subnet

kjiwatrakan — Tue, 28 Aug 2012 22:15:36 GMT

I am trying to search from source A that contains IP and trying to lookup IP location from source B where source B contains location and subnet information.

Example:

source="A" ip="192.168.0.23"
source="B" iprange="192.168.0.0/16" location="building_a"

Example Result: ( table ip, location )
ip location
192.168.0.23 building_a

Re: Join Ip with a subnet

MHibbin — Tue, 28 Aug 2012 22:36:20 GMT

You will probably need to use the cidrmatch function for eval/where.

To get you started...

Re: Join Ip with a subnet

dwaddle — Tue, 28 Aug 2012 22:49:19 GMT

It sounds like your "source B" should be a lookup table instead of indexed data. You can define a lookup with a match_type for a given field of CIDR, which should let you maintain your source B as a simple CSV file that is used by Splunk to update your events.

A good starting point is at http://docs.splunk.com/Documentation/Splunk/latest/User/CreateAndConfigureFieldLookups

Update: A working example

Put into $SPLUNK_HOME/etc/system/lookups/ip_lookup.csv:

ip,location
1.2.3.0/24,site_1
4.0.0.0/8,site_2
11.2.2.0/24,site_3_floor_1_zone_1
11.2.0.0/16,site_3_floor_1
11.0.0.0/8,site_3
0.0.0.0/0,internet

Put into $SPLUNK_HOME/etc/system/default/transforms.conf:

[ip_lookup]
filename = ip_lookup.csv
match_type = CIDR(ip)
max_matches = 1

Using these sample events:

Thu Aug 30 23:31:27 CDT 2012 ip=1.2.3.4
Thu Aug 30 23:31:35 CDT 2012 ip=4.5.6.7
Thu Aug 30 23:31:43 CDT 2012 ip=192.168.1.1
Thu Aug 30 23:31:56 CDT 2012 ip=11.2.2.2

A search of the form:

sourcetype=foo ip=* 
| lookup ip_lookup ip OUTPUT location 
| table ip,location

Produces these results:

    ip            location
----------- ---------------------
11.2.2.2    site_3_floor_1_zone_1
192.168.1.1 internet
4.5.6.7     site_2
1.2.3.4     site_1

Note that Splunk's CIDR matching rules are on the first matching CIDR entry in the lookup table, so I had to put more specific subnets of 11.0.0.0/8 first in the file, and I had to put 0.0.0.0/0 last in the file for it to work right.

UPDATE 09 Sept: Like any other lookup, you can enable this lookup to fire automatically for a sourcetype, source, or host. This is easily done via an update to props.conf. To enable this lookup for a sourcetype of foo, add to props.conf:

[foo]
LOOKUP-iplookup = ip_lookup ip OUTPUTNEW location

Now, I can run the search above without the explicit lookup command:

sourcetype=foo ip=* 
| table ip,location

And I get the same results as before:

    ip            location
----------- ---------------------
11.2.2.2    site_3_floor_1_zone_1
192.168.1.1 internet
4.5.6.7     site_2
1.2.3.4     site_1

Re: Join Ip with a subnet

ziegfried — Wed, 29 Aug 2012 07:18:01 GMT

You can use scheduled searches to maintain such a lookup table from your indexed data (outputlookup). If this information changes over time, you can create and maintain a time-based lookup to get accurate results for events from the past.

Re: Join Ip with a subnet

kjiwatrakan — Wed, 29 Aug 2012 18:17:31 GMT

Can you please provide an example on how to use lookup as part of the search if my lookup source is iprangeLocation.csv. The file contains iprange and location column.

Re: Join Ip with a subnet

kjiwatrakan — Thu, 30 Aug 2012 14:43:52 GMT

Can anyone please help me write a sample query to join data from logs to the lookup table match by cidr to get location?

Thank you,

Re: Join Ip with a subnet

dwaddle — Fri, 31 Aug 2012 04:49:39 GMT

See update above with a complete example. Just as advice, I would not call this a "JOIN" - you are thinking too much in SQL terms.

Re: Join Ip with a subnet

kjiwatrakan — Tue, 04 Sep 2012 16:45:11 GMT

Awesome and double awesome. This is a perfect example. I guess this answer would be ideal for a lot of problems that a lot of people are facing. +1 in my book.

Re: Join Ip with a subnet

rusty009 — Wed, 16 Mar 2016 11:15:02 GMT

Amazing - thank you !