Azure Quick Wins

Azure

Template

Tutorials

•

In this tutorial, you will find Azure 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. Beyond cost, some of these resources are a compliance and security risk if they remain as-is.

Get started #

Install CloudQuery, point it at Postgres (local Docker or Azure PG), and use a read-only role (e.g., Reader at the subscription). Save the config as azure-safe-deletes.yaml, export POSTGRES_URL, ensure your Azure credentials are set (CLI login/service principal), and then run a single CloudQuery sync. The SQL below gives a screenshot-ready “top wins” list.

CloudQuery config and sync #

kind: source
spec:
  name: azure
  path: cloudquery/source/azure
  version: "vX.Y.Z"  # pin a recent version
  tables:
    - azure_compute_disks
    - azure_compute_virtual_machines
    - azure_network_public_ip_addresses
    - azure_network_interfaces
  # (Optional) Fetch VM runtime status for better power-state signals:
  # table_options:
  #   azure_compute_virtual_machines:
  #     VirtualMachinesOptions:
  #       - StatusOnly: "true"        # or Expand: "instanceView" if supported by your setup
  destinations: [postgres]

---
kind: destination
spec:
  name: postgres
  path: cloudquery/destination/postgresql
  version: "vA.B.C"
  spec:
    connection_string: ${POSTGRES_URL}  # e.g. postgres://user:pass@host:5432/db?sslmode=disable

Then, run your sync:

cq sync azure-safe-deletes.yaml

Useful queries #

Unattached managed disks #

Unattached disks accrue $/GB per month

-- Disks with no VM attachment (managed_by is NULL or empty).
-- If present, we also surface size via properties->>'diskSizeGB'.
SELECT
  d.subscription_id,
  d.location,
  d.name,
  d.id,
  COALESCE(NULLIF(d.managed_by, ''), '—') AS managed_by,
  NULLIF(d.properties->>'diskState', '')   AS disk_state,
  (d.properties->>'diskSizeGB')::int       AS disk_size_gb,
  d.tags
FROM azure_compute_disks d
WHERE COALESCE(NULLIF(d.managed_by, ''), '') = ''
ORDER BY disk_size_gb DESC NULLS LAST, d.location, d.name;

Unassociated Public IPs --> you pay when idle #

Unattached public IPs are billable (especially static)

-- Public IPs without an ipConfiguration (not associated to a NIC/LB).
-- Azure writes the association under properties.ipConfiguration.
SELECT
  p.subscription_id,
  p.location,
  p.name,
  p.id,
  (p.properties->>'ipAddress') AS current_ip,
  p.properties->>'publicIPAllocationMethod' AS allocation_method,
  p.properties->>'ipVersion'                AS ip_version,
  p.sku,
  p.tags
FROM azure_network_public_ip_addresses p
WHERE (p.properties ? 'ipConfiguration') IS FALSE
   OR p.properties->'ipConfiguration' IS NULL
ORDER BY p.location, p.name;

Stopped/Deallocated VMs #

-- VMs whose power state looks 'stopped' or 'deallocated' (when instance_view is available).
-- This relies on runtime status being fetched (see optional table_options in YAML).
WITH power AS (
  SELECT
    vm.subscription_id,
    vm.location,
    vm.name,
    vm.id,
    -- Look inside instance_view.statuses[*].code for "PowerState/..."
    (
      SELECT s->>'code'
      FROM jsonb_path_query(vm.instance_view, '$.statuses[*] ? (@.code like_regex "PowerState/.*")') AS s
      LIMIT 1
    ) AS power_code,
    vm.tags
  FROM azure_compute_virtual_machines vm
)
SELECT
  subscription_id,
  location,
  name,
  id,
  power_code,
  tags
FROM power
WHERE power_code ~* 'PowerState/(stopped|deallocated)'
ORDER BY location, 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 Azure resources!). Get started free here to realize these cost savings. We’ve got a 14 day free trial, with no credit card required!

CloudQuery