FinOps-focused 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!

Compliance-focused Queries CloudQuery MCP Server