Cloud SQL Inventory Script for Google Cloud Projects

README.md

Cloud SQL Inventory Script for Google Cloud Projects

This Bash script lists all accessible GCP projects and inspects if they contain Cloud SQL instances. For each SQL instance, it extracts detailed metadata including:

• Project ID
• Instance name
• Database version
• Region
• Machine tier
• Disk size & type
• Private & Public IPs
• default_time_zone flag
• All other flags

Requirements

• gcloud CLI authenticated
• jq installed for JSON parsing
• Sufficient IAM permissions to view project services and Cloud SQL

Usage

chmod +x cloudsql_inventory.sh

# Basic usage (prints to screen)
./cloudsql_inventory.sh

# Save to CSV without enabling APIs
./cloudsql_inventory.sh output.csv

# Save to CSV and auto-enable Cloud SQL Admin API if needed
./cloudsql_inventory.sh output.csv true

cloudsql_inventory.sh

#!/bin/bash

set +e

# Parameters
csv_file="$1"
enable_api="${2:-false}" # Optional: pass "true" to enable the Cloud SQL Admin API automatically if not enabled
log_file="cloudsql_check.log"
: > "$log_file"

# CSV header
if [ -n "$csv_file" ]; then
  echo "project,instance,version,region,tier,privateIp,publicIp,diskSizeGb,diskType,defaultTimeZone,flags,status" > "$csv_file"
  echo "📄 Saving results to $csv_file"
else
  echo "📄 No CSV file specified. Outputting results to the terminal only."
fi

# Counters
total_projects=0
total_with_sql=0
total_api_disabled=0
total_timeouts=0
total_empty=0
total_api_enabled_now=0

# Retrieve all accessible projects
projects=$(gcloud projects list --format="value(projectId)")

for project in $projects; do
  ((total_projects++))
  echo "🔍 Checking project: $project"
  gcloud config set project "$project" --quiet > /dev/null

  # Check if the Cloud SQL Admin API is enabled
  api_enabled=$(gcloud services list --enabled --format="value(config.name)" 2>/dev/null | grep -c "sqladmin.googleapis.com")

  if [ "$api_enabled" -eq 0 ]; then
    if [ "$enable_api" = "true" ]; then
      echo "⚙️  Enabling Cloud SQL Admin API for project $project..." | tee -a "$log_file"
      gcloud services enable sqladmin.googleapis.com --quiet >> "$log_file" 2>&1
      ((total_api_enabled_now++))
      sleep 10
    else
      echo "🚫 Cloud SQL Admin API is NOT enabled in project $project" | tee -a "$log_file"
      [ -n "$csv_file" ] && echo "$project,,,,,,,,,,,API_DISABLED" >> "$csv_file"
      ((total_api_disabled++))
      echo
      continue
    fi
  fi

  # Temporary file for instance names
  tmpfile=$(mktemp)
  trap "rm -f $tmpfile" EXIT

  (gcloud sql instances list --format="value(name)" > "$tmpfile" 2>>"$log_file") &
  pid=$!

  sleep_time=30
  timed_out=false

  # Timeout management
  for ((i=0; i<sleep_time; i++)); do
    if ! kill -0 $pid 2>/dev/null; then break; fi
    sleep 1
  done

  if kill -0 $pid 2>/dev/null; then
    echo "⏱️  Timeout while listing instances in project $project" | tee -a "$log_file"
    kill -9 $pid 2>/dev/null
    wait $pid 2>/dev/null || true
    timed_out=true
  else
    wait $pid 2>/dev/null || true
  fi

  if [ "$timed_out" = true ]; then
    [ -n "$csv_file" ] && echo "$project,,,,,,,,,,,TIMEOUT" >> "$csv_file"
    ((total_timeouts++))
    echo
    continue
  fi

  [ -f "$tmpfile" ] && instances=$(cat "$tmpfile" 2>/dev/null)
  rm -f "$tmpfile"
  trap - EXIT

  if [ -z "$instances" ]; then
    echo "⚠️  No Cloud SQL instances found in project $project"
    [ -n "$csv_file" ] && echo "$project,,,,,,,,,,,NO_INSTANCES" >> "$csv_file"
    ((total_empty++))
  else
    for instance in $instances; do
      describe=$(gcloud sql instances describe "$instance" --format=json 2>>"$log_file")

      version=$(echo "$describe" | jq -r .databaseVersion)
      region=$(echo "$describe" | jq -r .region)
      tier=$(echo "$describe" | jq -r .settings.tier)
      privateIp=$(echo "$describe" | jq -r '.ipAddresses[]? | select(.type=="PRIVATE") | .ipAddress' | head -n 1)
      publicIp=$(echo "$describe" | jq -r '.ipAddresses[]? | select(.type=="PRIMARY") | .ipAddress' | head -n 1)
      diskSizeGb=$(echo "$describe" | jq -r .settings.dataDiskSizeGb)
      diskType=$(echo "$describe" | jq -r .settings.dataDiskType)
      flags=$(echo "$describe" | jq -r '[.settings.databaseFlags[]? | "\(.name)=\(.value)"] | join(",")')
      defaultTimeZone=$(echo "$describe" | jq -r '[.settings.databaseFlags[]? | select(.name == "default_time_zone") | .value][0] // ""')

      [ -z "$version" ] && version="ERROR"
      [ "$version" != "ERROR" ] && ((total_with_sql++))

      echo "✅ Project: $project | Instance: $instance | Version: $version | Region: $region | Tier: $tier | Disk: $diskSizeGb GB $diskType | TimeZone: $defaultTimeZone | Flags: $flags"
      [ -n "$csv_file" ] && echo "$project,$instance,$version,$region,$tier,$privateIp,$publicIp,$diskSizeGb,$diskType,$defaultTimeZone,"$flags",OK" >> "$csv_file"
    done
  fi

  echo
done

# Totals summary
echo "🔢 Summary:"
echo "Projects checked: $total_projects" | tee -a "$log_file"
echo "With SQL instances: $total_with_sql" | tee -a "$log_file"
echo "API not enabled: $total_api_disabled" | tee -a "$log_file"
echo "APIs enabled by script: $total_api_enabled_now" | tee -a "$log_file"
echo "Timeouts: $total_timeouts" | tee -a "$log_file"
echo "Projects with no instances: $total_empty" | tee -a "$log_file"