CIDR Ranges in Looker

data
tablecalcs

(Justin Nicoara) #1

Hey guys, I’m new to Looker and trying to learn a lot! I wanted to query one of my Explore data sets and be able to search for all IP Address connections within a CIDR range. Is this possible to do or will I need to convert my CIDR range to all of the individual decimal IP addresses that make it up? Thanks!


(lloyd tabb) #2

There is nothing builtin to Looker to do this, but it really isn’t too hard to code up. Assuming you are backed by BigQuery, the code to do this with simple truncating function might look like the example below. You could also do something more sophisticated with the net.mask function instead of the net.trunc function.

connection: "bigquery_publicdata_standard_sql"

explore: geoip  {}
view: geoip {
  sql_table_name: `fh-bigquery.geocode.201806_geolite2_city_ipv4` ;;
  
  
  dimension: ip {
    sql: REGEXP_REPLACE(${TABLE}.network, r'/\d+$','') ;;
  }
  
  parameter: ip_mask_length {
    type: number
  }
  
  dimension: masked_ip {
    sql:
      NET.IP_TO_STRING (
         NET.IP_TRUNC(
           NET.IP_FROM_STRING(${ip}), {%parameter ip_mask_length%} 
         )
      );;
  }
  
  measure: count {}
  
}

For reference:

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#netip_trunc


(manny.bhatia) #3

@justr00t I had created a derived table solution for looking up CIDR range for another use case. The derived table would look like this

view: cidr_lookup {
  derived_table: {
    sql: SELECT
    addr_str,
    substr(addr_str, 1, strpos(addr_str, '/') - 1) start_ip_addr,
    NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(substr(addr_str, 1, strpos(addr_str, '/') - 1))) AS start_ipv4_to_int64,
    NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(substr(addr_str, 1, strpos(addr_str, '/') - 1))) + POW(2, 32 - cast(substr(addr_str, strpos(addr_str, '/') + 1) as INT64)) - 2 end_ipv4_int64,
    NET.IP_TO_STRING(NET.IPV4_FROM_INT64(
    CAST(
    NET.IPV4_TO_INT64(
    NET.SAFE_IP_FROM_STRING(
    substr(addr_str, 1, strpos(addr_str, '/') - 1)
    )
    ) + POW (2, 32 - cast(substr(addr_str, strpos(addr_str, '/') + 1) as INT64)) - 2  AS INT64)
    )) End_Ipv4
    FROM UNNEST(
      SPLIT(
         REPLACE({% parameter cidr_pattern %}, ' ', ''), ',')
    ) AS addr_str
    ;;
  }

  parameter: cidr_pattern {
    type: string
    description: "Provide comma separated list of CIDR Patterns (aaaa.bbbb.cccc.dddd/xx) "
  }

  dimension: addr_str {
    description: "Original Input String"
    type: string
    sql: ${TABLE}.addr_str ;;
  }

  dimension: start_ip_addr {
    label: "Starting IP Address"
    description: "Starting IP Address from CIDR String"
    type: string
    sql: ${TABLE}.Start_ip_addr ;;
  }

  dimension: start_ipv4_to_int64 {
    label: "Starting IP Address (Numeric)"
    description: "Numeric representation of starting IP Address from CIDR String"
    type: number
    sql: ${TABLE}.start_ipv4_to_int64 ;;
  }

  dimension: end_ipv4_int64 {
    label: "Ending IP Address (Numeric)"
    description: "Numeric representation of ending IP Address from CIDR String"
    type: number
    sql: ${TABLE}.End_ipv4_int64 ;;
  }

  dimension: end_ipv4 {
    label: "Ending IP Address"
    description: "Ending IP Address from CIDR String"
    type: string
    sql: ${TABLE}.End_Ipv4 ;;
  }
}

The derived table will return the starting and ending IP address range in both IP4 and numeric values which can then be used to join with explores.

explore: some_firewall_log_file {
    join: cidr_lookup {
    type: inner
    relationship: many_to_one
    sql_on:                 NET.IPV4_TO_INT64(NET.SAFE_IP_FROM_STRING(${some_firewall_log_file.ip_address}))
              BETWEEN ${cidr_lookup.start_ipv4_to_int64} AND ${cidr_lookup.end_ipv4_int64}
           ;;
  }
}