Join Ip with a subnet

[kjiwatrakan]

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

[kjiwatrakan]

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,

[dwaddle]

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.

[dwaddle · Accepted solution]

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

[kjiwatrakan]

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.

[rusty009]

Amazing - thank you !

[kjiwatrakan]

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.

[ziegfried]

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.

[MHibbin]

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

To get you started...