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!
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.
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.