Is your feature request related to a problem? Please describe.
Due to some bad setup by former managed of our AWS infrastructure I found that an AWS EventBus has been setup to snapshot a volume of a machine daily, for 4 years, with NO deletion of old snapshots.
The result was > 400TB of snapshots (88% of all snapshots) for 2 machines. (of which I had setup in AWS Backup a few months back!) but since I had no insight into "orphaned" snapshots I did not notice this was going on until I found powerpipe!
Describe the solution you'd like
I have a query I wrote to "locate" these orphaned snapshots so I can present them in a table (along with a count). and I feel this would be a good addition to AWS Thrifty, since I probably saved us around 1.6K per month with this cleanup.
Describe alternatives you've considered
Initially I just used steampipe to query the list. then setup a powerpipe dashboard.
Additional context
The query I am using.
with amis as (
select tags ->> 'APP' as tag_app, tags ->> 'Name' as tag_name, name, image_id, "state", creation_date, block_device_mappings
from aws_ec2_ami
), snapshots as (
select snapshot_id, volume_size, volume_id, start_time, description, storage_tier, tags->>'Name' as tag_name
from aws_ebs_snapshot
), used_snapshot_ids as (
with t as (
select image_id, jsonb_array_elements(block_device_mappings)->'Ebs'->>'SnapshotId' as snapshot_id
from amis)
select *
from t
where snapshot_id is not null
)
select
S.snapshot_id as "Snapshot ID",
volume_id as "Source Volume ID",
volume_size as "Volume Size",
storage_tier as "Storage Tier",
tag_name as "Name Tag",
description as "Description",
start_time as "Created At"
from snapshots S
left join used_snapshot_ids U ON U.snapshot_id = S.snapshot_id
WHERE U.snapshot_id is null
ORDER BY volume_id, start_time
Is your feature request related to a problem? Please describe.
Due to some bad setup by former managed of our AWS infrastructure I found that an AWS EventBus has been setup to snapshot a volume of a machine daily, for 4 years, with NO deletion of old snapshots.
The result was > 400TB of snapshots (88% of all snapshots) for 2 machines. (of which I had setup in AWS Backup a few months back!) but since I had no insight into "orphaned" snapshots I did not notice this was going on until I found powerpipe!
Describe the solution you'd like
I have a query I wrote to "locate" these orphaned snapshots so I can present them in a table (along with a count). and I feel this would be a good addition to AWS Thrifty, since I probably saved us around 1.6K per month with this cleanup.
Describe alternatives you've considered
Initially I just used steampipe to query the list. then setup a powerpipe dashboard.
Additional context
The query I am using.