Skip to content

ip_range2ip_networks

ip_range2ip_networks(first_ip, last_ip)

Description

Convert an IP range into a json list of IP networks in CIDR notation (the list can have only one element)

Usage

Call or Deploy ip_range2ip_networks ?
Call ip_range2ip_networks directly

The easiest way to use bigfunctions

  • ip_range2ip_networks function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy ip_range2ip_networks in your project

Why deploy?

  • You may prefer to deploy ip_range2ip_networks in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

ip_range2ip_networks function can be deployed with:

pip install bigfunctions
bigfun get ip_range2ip_networks
bigfun deploy ip_range2ip_networks

Examples

select bigfunctions.eu.ip_range2ip_networks("1.0.0.0", "1.0.0.255")

select bigfunctions.us.ip_range2ip_networks("1.0.0.0", "1.0.0.255")

select bigfunctions.europe_west1.ip_range2ip_networks("1.0.0.0", "1.0.0.255")

+--------------+
| ip_networks  |
+--------------+
| [1.0.0.0/24] |
+--------------+


select bigfunctions.eu.ip_range2ip_networks("192.0.2.1", "192.0.2.15")

select bigfunctions.us.ip_range2ip_networks("192.0.2.1", "192.0.2.15")

select bigfunctions.europe_west1.ip_range2ip_networks("192.0.2.1", "192.0.2.15")

+----------------------------------------------------------+
| ip_networks                                              |
+----------------------------------------------------------+
| [192.0.2.1/32, 192.0.2.2/31, 192.0.2.4/30, 192.0.2.8/29] |
+----------------------------------------------------------+

Use cases

A security analyst wants to identify all traffic originating from a specific range of IP addresses. They have logs containing source IP addresses and want to categorize these logs based on whether the source IP falls within a predefined range. The ip_range2ip_networks function can be used to convert the IP range into a list of CIDR blocks. This list can then be used in a WHERE clause with the IN operator or a JOIN operation to filter or categorize the log data efficiently.

Example Scenario:

The security analyst wants to flag all traffic from the IP range 192.168.1.100 to 192.168.1.110.

  1. Convert the IP range to CIDR notation using the function:
SELECT bigfunctions.us.ip_range2ip_networks('192.168.1.100', '192.168.1.110');

This will return:

+---------------------------------------------------------------------------------+
| ip_networks                                                                     |
+---------------------------------------------------------------------------------+
| [192.168.1.100/32, 192.168.1.102/31, 192.168.1.104/30, 192.168.1.108/30]       |
+---------------------------------------------------------------------------------+
  1. Use the result to filter the logs:

Let's assume the logs are stored in a table named traffic_logs with a column source_ip.

SELECT *
FROM traffic_logs
WHERE source_ip IN (
    SELECT ip
    FROM UNNEST(bigfunctions.us.ip_range2ip_networks('192.168.1.100', '192.168.1.110')) AS ip
);

This query effectively filters the traffic_logs table to only show entries where the source_ip falls within the specified IP range. This allows the analyst to easily isolate and analyze traffic from the range of interest.

This use case demonstrates how ip_range2ip_networks simplifies working with IP ranges in BigQuery by converting them into a more manageable and query-friendly CIDR representation. This is especially useful when dealing with large datasets and complex filtering requirements.

Need help or Found a bug?
Get help using ip_range2ip_networks

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about ip_range2ip_networks

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on