Get the country for an IP address in a log table using the free location_lite table:
select a.*, b.country from log a
join location_lite b on PARSE_IP(a.ip, 'INET'):ipv4
between b.start_ip_int AND b.end_ip_int
+--------------+------------------+---------+
| IP | LOG | COUNTRY |
|--------------+------------------+---------|
| 44.83.90.126 | Third log entry | US |
| 73.83.90.116 | First log entry | US |
| 73.83.90.126 | Second log entry | US |
+--------------+------------------+---------+Get full geolocation details:
select a.ip, b.city, b.region, b.country, b.postal, b.lat, b.lng, b.timezone from log a
join location b on PARSE_IP(a.ip, 'INET'):ipv4
between b.start_ip_int AND b.end_ip_int
+--------------+---------------+------------+---------+--------+----------+------------+---------------------+
| IP | CITY | REGION | COUNTRY | POSTAL | LAT | LNG | TIMEZONE |
|--------------+---------------+------------+---------+--------+----------+------------+---------------------|
| 73.83.90.116 | Seattle | Washington | US | 98111 | 47.60621 | -122.33207 | America/Los_Angeles |
| 73.83.90.126 | Seattle | Washington | US | 98111 | 47.60621 | -122.33207 | America/Los_Angeles |
| 44.83.90.126 | New York City | New York | US | 10004 | 40.71427 | -74.00597 | America/New_York |
+--------------+---------------+------------+---------+--------+----------+------------+---------------------+Find some IP ranges near a known position, and calculate the distance from it:
select HAVERSINE(47.5815, -122.4057, lat, lng) as distance, start_ip, end_ip, city, region, country, postal, timezone from location order by 1 limit 10;
+-------------+----------------+----------------+---------+------------+---------+--------+---------------------+
| DISTANCE | START_IP | END_IP | CITY | REGION | COUNTRY | POSTAL | TIMEZONE |
|-------------+----------------+----------------+---------+------------+---------+--------+---------------------|
| 1.199961845 | 74.114.168.128 | 74.114.168.191 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 74.121.152.128 | 74.121.152.191 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 74.121.153.128 | 74.121.153.255 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 69.174.50.96 | 69.174.50.103 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 74.121.152.64 | 74.121.152.127 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 173.240.58.208 | 173.240.58.223 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 74.121.154.128 | 74.121.154.255 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 216.160.67.96 | 216.160.67.103 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 74.121.153.64 | 74.121.153.127 | Seattle | Washington | US | 98116 | America/Los_Angeles |
| 1.199961845 | 74.114.168.192 | 74.114.168.255 | Seattle | Washington | US | 98116 | America/Los_Angeles |
+-------------+----------------+----------------+---------+------------+---------+--------+---------------------+
Get the number of IP ranges per country:
select country, count(1) from location group by 1 order by 2 desc limit 10;
+---------+----------+
| COUNTRY | COUNT(1) |
|---------+----------|
| CN | 30492920 |
| US | 23504306 |
| JP | 9242004 |
| GB | 8063837 |
| FR | 6813337 |
| DE | 5854617 |
| IT | 5470829 |
| BR | 3216123 |
| ES | 2012012 |
| NL | 1719514 |
+---------+----------+
Get the country for an IP address in a log table using the free location_lite table:
select a.*, COALESCE(b.is_privacy, false) as is_privacy from log a
join privacy_lite b on PARSE_IP(a.ip, 'INET'):ipv4
between b.start_ip_int AND b.end_ip_int
+--------------+------------------+------------+
| IP | LOG | IS_PRIVACY |
|--------------+------------------+------------|
| 73.83.90.116 | First log entry | False |
| 73.83.90.126 | Second log entry | False |
| 44.83.90.126 | Third log entry | False |
+--------------+------------------+------------+Get full prviacy detection details for a specific IP address:
select hosting, proxy, tor, vpn
from privacy where parse_ip('5.2.72.113', 'inet'):ipv4
between START_IP_INT and END_IP_INT;
+---------+-------+------+------+
| HOSTING | PROXY | TOR | VPN |
|---------+-------+------+------|
| True | NULL | True | NULL |
+---------+-------+------+------+Get the ASN and name for an IP address in a log table using the free asn_lite table:
select a.*, b.asn, b.name from log a
join asn_lite b on PARSE_IP(a.ip, 'INET'):ipv4
between b.start_ip_int AND b.end_ip_int;
+--------------+------------------+--------+-------------------------------------+
| IP | LOG | ASN | NAME |
|--------------+------------------+--------+-------------------------------------|
| 44.83.90.126 | Third log entry | AS7377 | University of California, San Diego |
| 73.83.90.116 | First log entry | AS7922 | Comcast Cable Communications, LLC |
| 73.83.90.126 | Second log entry | AS7922 | Comcast Cable Communications, LLC |
+--------------+------------------+--------+-------------------------------------+Get additional ASN details:
select a.*, b.asn, b.name, b.domain, b.type from log a
join asn b on PARSE_IP(a.ip, 'INET'):ipv4
between b.start_ip_int AND b.end_ip_int;
+--------------+------------------+--------+-------------------------------------+-------------+-----------+
| IP | LOG | ASN | NAME | DOMAIN | TYPE |
|--------------+------------------+--------+-------------------------------------+-------------+-----------|
| 44.83.90.126 | Third log entry | AS7377 | University of California, San Diego | ucsd.edu | education |
| 73.83.90.116 | First log entry | AS7922 | Comcast Cable Communications, LLC | comcast.com | isp |
| 73.83.90.126 | Second log entry | AS7922 | Comcast Cable Communications, LLC | comcast.com | isp |
+--------------+------------------+--------+-------------------------------------+-------------+-----------+Determine if an IP address belongs to a mobile carrier, using the free carrier_lite table:
select a.ip, COALESCE(b.is_carrier, false) AS is_carrier from log a
LEFT JOIN carrier_lite b on PARSE_IP(a.ip, 'INET'):ipv4
between b.start_ip_int AND b.end_ip_int;
+--------------+------------+
| IP | IS_CARRIER |
|--------------+------------|
| 73.83.90.116 | False |
| 73.83.90.126 | False |
| 44.83.90.126 | False |
+--------------+------------+Get full carrier details for a specific IP using the carrier table:
select name, country, mcc, mnc from carrier where parse_ip('8.44.128.0', 'inet'):ipv4 BETWEEN start_ip_int AND end_ip_int;
+-----------------+---------+-----+-----+
| NAME | COUNTRY | MCC | MNC |
|-----------------+---------+-----+-----|
| United Wireless | US | 311 | 650 |
+-----------------+---------+-----+-----+Number of carriers per country:
select country, count(distinct name) from carrier group by 1 order by 2 desc limit 10;
+---------+----------------------+
| COUNTRY | COUNT(DISTINCT NAME) |
|---------+----------------------|
| IN | 30 |
| US | 25 |
| ES | 11 |
| BR | 8 |
| CA | 8 |
| GB | 8 |
| IL | 7 |
| RU | 7 |
| NL | 7 |
| IE | 6 |
+---------+----------------------+
@coderholic: Where do these tables come from? (
location_lite,location,privacy_lite,privacy,asn_lite,asn,carrier_lite,carrier)