FinOps-focused queries

Optimize cloud costs effortlessly with CloudQuery’s SQL-powered FinOps queries.

Optimize cloud costs with SQL

Managing cloud costs effectively requires real-time insights into resource usage and spending. CloudQuery allows you to run SQL queries on your cloud asset inventory, helping you identify cost inefficiencies and optimize expenses.

FinOps queries in action

Here are some essential queries to help you cut costs and improve cloud efficiency.

Identify resources without a "cost center" tag (AWS, GCP, Azure)

Why it matters: Without a cost_center tag, it's difficult to allocate cloud expenses correctly.

SELECT
    cloud, account, name, region, resource_type, tags
FROM
    cloud_assets
WHERE
    tags NOT LIKE '%cost_center%';

List unattached (orphaned) storage volumes (AWS, GCP, Azure)

Why it matters: Unused storage volumes drive up cloud costs unnecessarily.

WITH unattached_disks AS (
    SELECT _cq_id FROM gcp_compute_disks WHERE length(users) = 0
    UNION DISTINCT 
    SELECT _cq_id FROM azure_compute_disks WHERE managed_by is null
    UNION DISTINCT 
    SELECT _cq_id FROM aws_ec2_ebs_volumes WHERE attachments='[]'
)
SELECT cloud, account, region, name, resource_type_label, tags 
FROM cloud_assets a
JOIN unattached_disks d ON d._cq_id = a._cq_id
ORDER BY cloud, account, region, resource_type_label, name

Identify expired reserved instances (AWS)

Why it matters: Missing out on renewing reserved instances leads to higher on-demand costs.

SELECT
    arn, fixed_price, instance_count, instance_type
FROM
	aws_ec2_reserved_instances
WHERE
	toDate(end) < now();

Find GCP BigQuery tables without partitioning enabled (GCP)

Why it matters: Tables without partitioning lead to expensive queries due to full table scans.

SELECT
	friendly_name, project_id, location
FROM
	gcp_bigquery_tables
WHERE
	num_partitions = 0;

List EC2 instances that are not using graviton processors (AWS)

Why it matters: Graviton-based instances provide better performance at a lower cost.

SELECT
    arn, instance_type, region
FROM
    aws_ec2_instances
WHERE
    instance_type NOT LIKE 't4g%'
    AND instance_type NOT LIKE 'm6g%'
    AND instance_type NOT LIKE 'c6g%'
    AND instance_type NOT LIKE 'r6g%'; -- List of Graviton-based instance types

Detect unused elastic IPs (AWS)

Why it matters: Unused Elastic IPs incur unnecessary costs.

SELECT
	public_ip, instance_id
FROM
	aws_ec2_eips
WHERE
	instance_id IS NULL;

List old snapshots that can be deleted (AWS)

Why it matters: Keeping old snapshots that are no longer needed increases storage costs.

SELECT
	arn, allocated_storage, engine
FROM
	aws_rds_db_snapshots
WHERE
	instance_create_time  < NOW() - INTERVAL '180 days'; -- Older than 6 months

Want more?

These are just a few examples of how CloudQuery turns your cloud into an instantly queryable database—giving you unparalleled visibility into cost optimization opportunities.

Check out the main query examples page for more ways to audit, secure, and optimize your cloud—all with SQL!