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
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:
You can find IP addresses that match exactly:
selecttitle,private_ip_address,public_ip_addressfromaws_ec2_instancewhereprivate_ip_address = '172.31.52.163';
or find IPs that are contained within a given CIDR range:
selecttitle,private_ip_address,public_ip_addressfromaws_ec2_instancewhereprivate_ip_address <<= '172.16.0.0/12';
or test whether a CIDR contains an address:
selecttitle,cidr_blockfromaws_vpc_subnetwherecidr_block >> '172.31.52.163';
Of course you can use 'not' to look for IP addresses that are NOT in a range as well:
selectvpc_id,cidr_block,state,regionfromaws_vpcwherenot 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:
selecti.title as instance,i.private_ip_address,s.title as subnet,s.cidr_blockfromaws_ec2_instance as ijoin 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:
selecttitle as subnet,cidr_blockfromaws_vpc_subnetwherecidr_block <<= '10.0.0.0/8';