turbot/aws_insights

Query: ec2_instance_block_device_mapping

Usage

powerpipe query aws_insights.query.ec2_instance_block_device_mapping

SQL

with volume_details as (
select
p -> 'Ebs' ->> 'VolumeId' as "Volume ID",
p ->> 'DeviceName' as "Device Name",
p -> 'Ebs' ->> 'AttachTime' as "Attach Time",
p -> 'Ebs' ->> 'DeleteOnTermination' as "Delete On Termination",
p -> 'Ebs' ->> 'Status' as "Status",
arn
from
aws_ec2_instance,
jsonb_array_elements(block_device_mappings) as p
where
arn = $1
)
select
"Volume ID",
"Device Name",
"Attach Time",
"Delete On Termination",
"Status",
v.arn as "Volume ARN"
from
volume_details as vd
left join aws_ebs_volume v on v.volume_id = vd."Volume ID"
where
v.volume_id in (select "Volume ID" from volume_details)

Dashboards

The query is used in the dashboards: