mysql external shell command to get GeoIP Information from an IP address

I have geoiplookup installed on Centos 7 and I'd like to select an IP from a mysql table and then get the location of an IP address from the command line.

For example, I can type ! geoiplookup' 123.45.36.35 in mysql and get the city, country, etc.

I could download the data from MaxMind and then upload it to mysql and run a query as mentioned on this site or this site but I do not want to maintain and update another table monthly. Is this the best solution?

This link shows several python examples that I have used in fail2ban logs.

Id like to do something like this:

SELECT udf_geoip_lookup(ip) AS 'City',  
     udf_geoip_lookup(ip) AS 'State'
     udf_geoip_lookup(ip) AS 'Region
FROM table-name
 ;

3 functions calls would be too much.

CREATE FUNCTION `udf_geoip_lookup` (ip varchar(15))
RETURNS varchar(100)
BEGIN

      SET @sql = CONCAT('\! geoiplookup', @ip);
      -- I need to parse the city, region, country etc, somehow.
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   

RETURN @sql;
END;

Thanks very much!

Solution

As you mention, if you want to use the geoiplookup utility and keep up-to-date IP info you would need to periodically (monthly), download and maintain the IP updates from MaxMind (with a cron job).

What you may want to do instead is to call the curl utility, and retrieve data from the super cool ipinfo.io website:

$ curl ipinfo.io/47.144.148.253
{
  "ip": "47.144.148.253",
  "city": "Hermosa Beach",
  "region": "California",
  "country": "US",
  "loc": "33.8622,-118.3990",
  "org": "AS5650 Frontier Communications of America, Inc.",
  "postal": "90254"
}

Then you would need to parse that JSON output. But you can also pass a GET parameter with the ip address and get exactly what you want to retrieve.

For example, I ran curl with these parameters:

$ curl ipinfo.io/47.144.148.253/country
US
$ curl ipinfo.io/47.144.148.253/region
California
$ curl ipinfo.io/47.144.148.253/city
Hermosa Beach

So your SQL functions would be something like this:

CREATE FUNCTION `curl_lookup_country` (ip varchar(15))
RETURNS varchar(100)
BEGIN
      SET @sql = CONCAT('\! curl', 'ipinfo.io/',@ip,'/country');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   // country
RETURN @sql;
END;

CREATE FUNCTION `curl_lookup_region` (ip varchar(15))
RETURNS varchar(100)
BEGIN
      SET @sql = CONCAT('\! curl', 'ipinfo.io/',@ip,'/region');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   // region or state
RETURN @sql;
END;

CREATE FUNCTION `curl_lookup_city` (ip varchar(15))
RETURNS varchar(100)
BEGIN
      SET @sql = CONCAT('\! curl', 'ipinfo.io/',@ip,'/city');
      PREPARE stmt FROM @sql;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;   // city
RETURN @sql;
END;

And your SQL query would be:

SELECT curl_lookup_country(ip) AS 'Country',  
     curl_lookup_region(ip) AS 'State'
     curl_lookup_city(ip) AS 'City
FROM table-name

This way you do not have to parse the JSON output.

But I must warn you: this DOES make 3 HTTP requests for each IP address query. If it is a large table it would be a pretty big performance hit to do 3 HTTP curl requests for each row in the table.

If you need to access this IP data many times you should consider creating country, region and city columns in the original table along with the IP address, then run an UPDATE statement to only update rows where these columns are empty, and fill them with your groovy new curl_lookup_xxx functions.

Something like this:

UPDATE table_name
SET country = curl_lookup_country(ip),
    region = curl_lookup_region(ip),
    city = curl_lookup_city(ip)
WHERE ip IS NOT NULL AND
  (country IS NULL OR
   region IS NULL OR
   city IS NULL)