Dashboards & Visualizations

loop search for ranges mapping

iKate
Builder

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!

Tags (2)
0 Karma

Richfez
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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.

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>