Azure Quick Wins

•

3 min read

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 PostgreSQL (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!). Schedule a demo to see how CloudQuery can help you realize these cost savings.

CloudOps