topic loop search for ranges mapping in Dashboards & Visualizations

loop search for ranges mapping

iKate — Thu, 14 Aug 2014 08:27:40 GMT

Hi all,

While mapping ip-addresses to cities using iplocation or geoip commands, they returned just 65% of matched ip+cities. There's another geoip database that have more full information concerning my region.
Ip-addresses there are grouped in ranges that are converted into numeric format (ip address a.b.c.d is converted this way: a*256*256*256+b*256*256+c*256+d) :

city    range                      range_start  range_end   region
city1   2.60.0.0 - 2.60.255.255    37486592     37552127    region1 
city2   2.61.0.0 - 2.61.255.255    37552128     37617663    region2
city3   2.62.0.0 - 2.62.255.255    37617664     37683199    region3

csv file of 38k lines

And file with 700k ip-s that is needed to be matched with cities above, looks like this:

ip_address  ip_converted 
2.60.0.0    37486592
2.61.0.0    37552128
2.62.0.0    37617664

Each ip_converted value shoud be checked against all ranges to find one where this statement is true:

range_start <= ip_converted <= range_end

How can this matching be done in splunk? I've tried map but it's not for this case.

Thanks in advance!

Re: loop search for ranges mapping

martin_mueller — Thu, 14 Aug 2014 09:20:27 GMT

You could put CIDR ranges into your CSV file, e.g. 2.60.0.0/16 for the first line, 2.61.0.0/16 for the second line, and so on. Using that column, Splunk can match individual IPs against that list of CIDR ranges and enrich the events with the other columns from that lookup. See http://answers.splunk.com/answers/5916/using-cidr-in-a-lookup-table/46866 for an example.

Re: loop search for ranges mapping

Richfez — Thu, 14 Aug 2014 19:48:32 GMT

To do that math in Splunk, just add something like ...

...| rex field=clientip "(?<octet1>\d{1,3}).(?<octet2>\d{1,3}).(?<octet3>\d{1,3}).(?<octet4>\d{1,3})" |eval ip_decimal=(octet1*16777216)+(octet2*65536)+(octet3*256)+octet4

to the search. Obviously fix "clientip" to your IP field. This will create a field "ip_decimal" which will match your ranges.