Client IP to Network Range Lookups & Reporting

[rturk]

Hi All 🙂

I may have had too much coffee, because I'm having some trouble getting my head around this one.

I have a some web logs in which I have used Splunk to extract out a list of unique source IP addresses for incoming customer requests, and I would like to find out what ISP the requests originated from.

I have created a csv lookup table from the output of a 'show ip bgp' and a scripted 'whois' that is structured as follows:

<network-range>,<as-number>,<isp-name>

eg. 223.165.96.0/19,9268,Over The Wire Pty Ltd

So as far as my caffeine-riddled bloodsystem can tell I need to:

• Compare the IP address to the network-range in the lookup table

eg. The IP address 223.165.96.2 will be a unique match, as will 223.165.96.78.

• Lookup the as-number or isp-name as my report sees fit
• Do a count on the number of entries per unique ISP

eg. Two from the example listed above.

• Make a purdy pie graph showing what percentage of users came from where

...and I'm stuck on... step 1.

Is it possible to evaluate to see if a client_ip is present in a network range that is in an external lookup table, or am I going to need to script this up?

I'm running 4.2 and I have seen the cidrmatch command as well as the Splunk>Answers "How to best determine IP range membership?", however when dealing with 100,000+ routes I'm not sure this is the correct tool or way to do this.

As always, any help or suggestions gratefully received 🙂

[ziegfried · Accepted solution]

As of Splunk 4.2 you can specify a match_type for a lookup in transforms.conf. See http://www.splunk.com/base/Documentation/latest/Admin/Transformsconf

Setting the match_type to CIDR for your IP range field is probably what you are looking for.

transforms.conf:

[mylookup]
filename = isp.csv
match_type = CIDR(network-range)

[rturk]

Yep, that worked a treat! Interesting sidenote... the change to use:

match_type = CIDR(network-range)

Can't be done via the GUI which is fine, however if you make any changes in the GUI after doing this (eg. max_matches = 1), then the transforms.conf is reset to the values that may ONLY be set via the GUI.

That is to say, I lost my match_type command.

Thanks again 🙂

[rturk]

Thanks Ziegfried, I'll give that a try and let you know 🙂