turbot/aws_insights

Query: vpc_routes_for_vpc

Usage

powerpipe query aws_insights.query.vpc_routes_for_vpc

Steampipe Tables

SQL

select
route_table_id as "Route Table ID",
tags ->> 'Name' as "Name",
r ->> 'State' as "State",
case
when r ->> 'GatewayId' is not null then r ->> 'GatewayId'
when r ->> 'InstanceId' is not null then r ->> 'InstanceId'
when r ->> 'NatGatewayId' is not null then r ->> 'NatGatewayId'
when r ->> 'LocalGatewayId' is not null then r ->> 'LocalGatewayId'
when r ->> 'CarrierGatewayId' is not null then r ->> 'CarrierGatewayId'
when r ->> 'TransitGatewayId' is not null then r ->> 'TransitGatewayId'
when r ->> 'VpcPeeringConnectionId' is not null then r ->> 'VpcPeeringConnectionId'
when r ->> 'DestinationPrefixListId' is not null then r ->> 'DestinationPrefixListId'
when r ->> 'DestinationIpv6CidrBlock' is not null then r ->> 'DestinationIpv6CidrBlock'
when r ->> 'EgressOnlyInternetGatewayId' is not null then r ->> 'EgressOnlyInternetGatewayId'
when r ->> 'NetworkInterfaceId' is not null then r ->> 'NetworkInterfaceId'
when r ->> 'CoreNetworkArn' is not null then r ->> 'CoreNetworkArn'
when r ->> 'InstanceOwnerId' is not null then r ->> 'InstanceOwnerId'
end as "Gateway",
r ->> 'DestinationCidrBlock' as "Destination CIDR",
case
when a ->> 'Main' = 'true' then vpc_id
when a ->> 'SubnetId' is not null then a->> 'SubnetId'
else '??'
end as "Associated To"
from
aws_vpc_route_table,
jsonb_array_elements(routes) as r,
jsonb_array_elements(associations) as a
where
vpc_id = $1
order by
route_table_id,
"Associated To"

Dashboards

The query is used in the dashboards: