In this tutorial, you will find AWS resources that are likely safe to delete. This can be a significant cost savings: one CloudQuery customer saves approximately $30k/month in costs by using queries like this.
Get started
Install CloudQuery, point it at a PostgreSQL instance you control (local Docker/RDS), and grant read-only EC2 permissions (ec2:DescribeVolumes, ec2:DescribeAddresses, ec2:DescribeSecurityGroups, ec2:DescribeNetworkInterfaces). Save the config below as aws-safe-deletes.yaml, export POSTGRES_URL and your AWS creds/region in your shell, then run cq sync. Paste the SQL into PostgreSQL to get a screenshot-ready savings list.
CloudQuery Config and Sync
This config will sync AWS metadata for EC2 and related resources to PostgreSQL.
kind: source spec: name: aws path: cloudquery/source/aws version: 'vX.Y.Z' # pin a recent version # Keep it tight: only the tables we query tables: - aws_ec2_ebs_volumes - aws_ec2_eips - aws_ec2_security_groups - aws_ec2_network_interfaces destinations: [postgres]
--- kind: destination spec: name: postgres path: cloudquery/destination/postgresql version: 'vA.B.C' # whatever your postgres version is spec: connection_string: ${POSTGRES_URL} # e.g. postgres://user:pass@host:5432/db
After you create your config file, all you need to do is run a sync. Make sure to set your environment variables!
cq sync aws-safe-deletes.yaml
Data structure
aws_ec2_ebs_volumes Rows: ~hundreds (depends on account) Columns: - volume_id (utf8) e.g. "vol-xxxxxxx" - state (utf8) e.g. "in-use", "available" - size (int64) e.g. 100 (GB) - volume_type (utf8) e.g. "gp3" - attachments (json) e.g. '[{"InstanceId":"i-0abc123..."}]' - region (utf8) e.g. "us-east-1" - tags (json) e.g. '{"Name":"db-data","Owner":"TeamA"}'
aws_ec2_eips Rows: usually small (<50) Columns: - allocation_id (utf8) e.g. "eipalloc-0123456789abcdef" - public_ip (utf8) e.g. "1.11.22.333" - association_id (utf8) e.g. NULL if unattached - instance_id (utf8) e.g. "i-0abc123def456" - region (utf8) e.g. "us-east-1" - tags (json) e.g. '{"Env":"prod"}'
aws_ec2_security_groups Rows: varies widely (dozens → hundreds) Columns: - group_id (utf8) e.g. "sg-0abc123def456" - group_name (utf8) e.g. "default", "web-sg" - description (utf8) e.g. "Web tier security group" - vpc_id (utf8) e.g. "vpc-0abcd1234ef567" - ip_permissions (json) inbound rules (array of objects) - ip_permissions_egress (json) outbound rules - region (utf8) e.g. "us-east-1" - tags (json) e.g. '{"Owner":"sec-team"}'
Useful queries
Note: these all use PostgreSQL-style syntax
Unattached EBS volumes
This assumes $0.08 per GB - join to a pricing table (which you can sync using CloudQuery!) to get accurate savings estimates
-- Value: Volumes with no attachments (state='available' or empty attachments array) cost $/GB-mo without use. -- Heuristic: gp3 ~ $0.08/GB-mo; adjust if you prefer. SELECT v.volume_id, v.region, v.volume_type, v.state, v.size::int AS size_gb, (v.size * 0.08)::numeric(12,2) AS est_monthly_cost_usd, v.tags FROM aws_ec2_ebs_volumes v WHERE v.state = 'available' OR ( v.attachments IS NOT NULL AND jsonb_typeof(v.attachments) = 'array' AND jsonb_array_length(v.attachments) = 0 ) ORDER BY est_monthly_cost_usd DESC NULLS LAST, v.size DESC;
Unassociated Elastic IPs
You pay for these when idle
-- Value: EIPs with no association_id incur hourly charges (~$3.65/mo at ~$0.005/hr). SELECT e.public_ip, e.allocation_id, e.region, e.association_id, CASE WHEN e.association_id IS NULL THEN 3.65 ELSE 0 END::numeric(10,2) AS est_monthly_cost_usd, e.tags FROM aws_ec2_eips e WHERE e.association_id IS NULL ORDER BY est_monthly_cost_usd DESC, e.public_ip;
Unused security groups
Reduce clutter and audit noise
-- Value: Security groups not referenced by any ENI (excluding the literal 'default') are cleanup candidates. -- Note: aws_ec2_network_interfaces.groups is JSON (array of objects). We extract group_id robustly, handling key variants. WITH attached AS ( SELECT DISTINCT COALESCE( g.elem->>'group_id', g.elem->>'GroupId', g.elem->>'groupId' ) AS group_id FROM aws_ec2_network_interfaces eni CROSS JOIN LATERAL jsonb_array_elements(enit.groups) AS g(elem) WHERE eni.groups IS NOT NULL AND jsonb_typeof(eni.groups) = 'array' ) SELECT sg.group_id, sg.group_name, sg.vpc_id, sg.description, sg.region, sg.tags FROM aws_ec2_security_groups sg LEFT JOIN attached a ON a.group_id = sg.group_id WHERE a.group_id IS NULL AND sg.group_name <> 'default' ORDER BY sg.vpc_id, sg.group_name;
Wrap-up
CloudQuery is lightning-fast data movement that runs on your infrastructure. This is perfect for working with sensitive data (like your key AWS resources!). Schedule a demo to see how CloudQuery can help you realize these cost savings.
Written by
Chris Reuter
VP of Marketing at CloudQuery. I like Maine (the US State), software & analytics, woodworking, hiking, travel, and spending time with my wife & dog.
Try CloudQuery yourself
Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.
Download →
Schedule a demo →
Related posts
AWS
Security
Tutorials
Finding AWS Unrestricted Outbound Access in 2026: EC2, Lambda, and Beyond
Find EC2, Lambda, and ECS Fargate resources with unrestricted outbound access in AWS using SQL. Why egress controls still break attack chains in 2026.
Joe Karlsson
May 5, 2026
AWS
Security
Tutorials
AWS IAM Identity Center with Google Workspace as an IdP: 2026 Step-by-Step Guide
Set up AWS IAM Identity Center with Google Workspace: SAML authentication, SCIM user provisioning, and group sync with CLI or ssosync. Updated for 2026.
Joe Karlsson
May 5, 2026
AWS
Cloud Infrastructure
Security
Tutorials
Integrate AbuseIPDB Threat Intelligence with CloudQuery
Enrich your cloud asset inventory with AbuseIPDB threat intelligence to identify public cloud IPs with known abuse reports.
Joe Karlsson
April 20, 2026
AWS
Cloud Infrastructure
Tutorials
How to Query ipinfo.io Data with CloudQuery
Learn how to enrich your cloud asset inventory with ipinfo.io IP geolocation and threat intelligence data using CloudQuery.
Joe Karlsson
April 20, 2026
AWS
Security
Tutorials
AWS IAM Identity Center (Formerly AWS SSO): The Complete 2026 Guide
AWS IAM Identity Center replaced AWS SSO in 2022. Learn how to set it up, configure permission sets, use the AWS CLI, and audit access with CloudQuery.
Joe Karlsson
April 6, 2026
AWS
Tutorials
AWS Pinpoint Is Being Deprecated: How to Assess Your Migration Scope
AWS Pinpoint reaches end of support October 2026. Query your Pinpoint usage across all accounts to find what needs migrating before the deadline.
Joe Karlsson
March 24, 2026
AWS
Best Practices
Tutorials
CloudQuery Sync Performance: A Practical Troubleshooting Guide
Diagnose why CloudQuery syncs slow down after plugin upgrades, identify expensive tables, and use the CLI command that replaces the tables wildcard.
Joe Karlsson
March 6, 2026
AWS
Security
Tutorials
Set Up AWS CLI with SSO (IAM Identity Center) — Step by Step
Set up AWS CLI v2 with IAM Identity Center in 5 minutes. Covers SSO registration scopes, sso-session config, token refresh, and multi-account profiles.
Yevgeny Pats
February 25, 2026
AWS
Cloud Infrastructure
Tutorials
AWS Lambda Python 3.9 End-of-Support: Find and Upgrade Every Affected Function
AWS Lambda Python 3.9 security patches stopped Dec 15, 2025. Find every affected function across all accounts with SQL queries and upgrade to 3.12 or 3.13.