Created
May 29, 2024 05:12
-
-
Save yashafromrussia/f9e39c47cb35d5048f0153bc6b1f10d9 to your computer and use it in GitHub Desktop.
steampipe: find used AWS snapshots across all accounts and regions
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with amis as ( | |
| select | |
| name, | |
| title, | |
| image_id, | |
| creation_date, | |
| state, | |
| region, | |
| jsonb_array_elements(block_device_mappings) -> 'Ebs' ->> 'SnapshotId' as snapshot_ids, | |
| owner_id | |
| from | |
| aws_all.aws_ec2_ami | |
| ), | |
| vol_snapshots as ( | |
| select | |
| amis.name as ami_name, | |
| snapshot.title as snapshot_title, | |
| snapshot.snapshot_id as snapshot_id, | |
| snapshot.region as snapshot_region, | |
| snapshot.description as snapshot_description, | |
| amis.image_id as image_id, | |
| snapshot.owner_id as snapshot_owner_id, | |
| snapshot.owner_alias as snapshot_owner_alias | |
| from | |
| aws_all.aws_ebs_snapshot as snapshot | |
| join amis on snapshot.snapshot_id = amis.snapshot_ids | |
| ) | |
| select | |
| vol_snapshots.ami_name, | |
| vol_snapshots.snapshot_id, | |
| vol_snapshots.snapshot_region, | |
| instance.image_id, | |
| instance.instance_id, | |
| instance.title, | |
| vol_snapshots.snapshot_owner_alias, | |
| vol_snapshots.snapshot_owner_id | |
| from vol_snapshots | |
| join aws_all.aws_ec2_instance as instance on instance.image_id = vol_snapshots.image_id | |
| where vol_snapshots.snapshot_owner_alias != 'amazon'; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment