Calculating by a Country Field from IP Address


(Justin Nicoara) #1

I am trying to create a query that shows me all the network traffic from a specific country. Is there a way that Looker can automatically geolocate based on IP Address and then I could search on a Country field? Thanks.

(lloyd tabb) #2

BigQuery provides a nice mapping table so this is pretty easy.

Using the Query described above here is a model that maps IPs to country and does an appropriate join.

connection: "bigquery_publicdata_standard_sql"

explore: geoip  {
  join: geo_city {
    sql_on: ${geoip.class_b} = ${geo_city.class_b} ;;
    relationship: many_to_one
view: geoip {
  sql_table_name: `fh-bigquery.geocode.201806_geolite2_city_ipv4` ;;

  dimension: ip {
    sql: REGEXP_REPLACE(${TABLE}.network, r'/\d+$','') ;;
  dimension: class_b {
     sql: NET.IPV4_TO_INT64(NET.IP_FROM_STRING(${ip}))/(256*256);;

  measure: count {
    type: count

view: geo_city {
  sql_table_name: `fh-bigquery.geocode.geolite_city_bq_b2b` ;;
  dimension: class_b {sql: ${TABLE}.classb;;}
  dimension: city {}
  dimension: country {sql: ${TABLE}.countryLabel;;}

And some example output: