Splunk Search

Join Ip with a subnet

kjiwatrakan
Explorer

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

Tags (3)
1 Solution

dwaddle
SplunkTrust
SplunkTrust

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

View solution in original post

kjiwatrakan
Explorer

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,

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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.

0 Karma

dwaddle
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

rusty009
Path Finder

Amazing - thank you !

0 Karma

kjiwatrakan
Explorer

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.

0 Karma

ziegfried
Influencer

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
Influencer

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

To get you started...

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...