Querying IP Addresses

One of the primary uses of Steampipe is for auditing cloud and network infrastructure. As such, many columns store IP addresses or network addresses in CIDR format.

Steampipe leverages the native Postgres inet and cidr data types for IP addresses and cidr ranges. The essential difference between inet and cidr data types is that inet accepts values with nonzero bits to the right of the netmask, whereas cidr does not; inet columns can either be a single IP address OR a CIDR range, but cidr MUST be a CIDR range.

You can use the standard Postgres network address functions and operators with Steampipe.

You can extract the host, network, netmask, and broadcast addresses from a CIDR:

select
  vpc_id,
  cidr_block,
  host(cidr_block),
  broadcast(cidr_block),
  netmask(cidr_block),
  network(cidr_block)
from
  aws_vpc;

You can find IP addresses that match exactly:

select
  title,
  private_ip_address,
  public_ip_address
from
  aws_ec2_instance
where
  private_ip_address = '172.31.52.163';

or find IPs that are contained within a given CIDR range:

select
  title,
  private_ip_address,
  public_ip_address
from
  aws_ec2_instance
where
  private_ip_address <<= '172.16.0.0/12';

or test whether a CIDR contains an address:

select
  title,
  cidr_block
from
  aws_vpc_subnet
where
  cidr_block >> '172.31.52.163';

Of course you can use 'not' to look for IP addresses that are NOT in a range as well:

select
  vpc_id,
  cidr_block,
  state,
  region
from
  aws_vpc
where
  not cidr_block <<= '10.0.0.0/8'
  and not cidr_block <<= '192.168.0.0/16'
  and not cidr_block <<= '172.16.0.0/12';

You can even join tables where an address from one table is contained in the network of another:

select
  i.title as instance,
  i.private_ip_address,
  s.title as subnet,
  s.cidr_block
from
  aws_ec2_instance as i
  join aws_vpc_subnet as s on i.private_ip_address <<= s.cidr_block;

This works for networks as well - you can test whether one CIDR is contained entirely in another:

select
  title as subnet,
  cidr_block
from
  aws_vpc_subnet
where
  cidr_block <<= '10.0.0.0/8';