How to lookup ip address in database with begin and end decimal?

[gwong3]

I am trying to search from source A that contains IP and trying to lookup IP location from a database that contains the subnet information with begin and end decimal. I'm trying to use Splunk DB Connect v2 to extract the database information to spreadsheet and that works but not sure how to use the ip information from source A to lookup the location information in database that only contains begin and end decimal information.

Example:

source="A" ip="192.168.0.23"
database contains = IP address begin = "3232235520" , IP address end = "3232235775" , location="building_a"

Example Result: ( table ip, location )
ip location
192.168.0.23 building_a

[vasildavid]

You could convert your IP to IP decimal format and check if it is between the address begin/end range.

source="A" ip="192.168.0.23" 
      | rex field=ip "(?<oct1>\d+)\.(?<oct2>\d+)\.(?<oct3>\d+)\.(?<oct4>\d+)"
      | eval ip_decimal=(oct1 * 256 * 256 * 256) + (oct2 * 256 * 256) + (oct3 * 256) + (oct4)

Then use your ip_decimal to filter out your dataset from your database.

  | where (ip_decimal >= ip_address_begin) OR (ip_decimal <= ip_address_end)

edit: missed a quotation mark.

[gwong3]

Hi Vasildavid,

That makes sense. Would I use Splunk DB Connect v2 "DB Lookups" or the "DB Inputs"? I'm thinking it'll be DB Inputs to create the csv file and then use your command to filter it? I tried "DB Lookups" but couldn't map the splunk fields to outputs fields in the database because the database only contains begin address and end address.

I have a list of ip addresses and ultimately I would like to chart the list of ip addresses to locations.

Thanks!