Detect all BigQuery Editions Reservations in a project and display their Fluid Scaling status. Also provides the SQL code to turn on Fluid Scaling for those not using it.

fluid_scaling_by_org.sh

#!/bin/bash

# MIT License
# 
# Copyright (c) 2026 DoiT International
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.

# Purpose:
# This script will report on all reservations in your organization if they have BigQuery Fluid Scaling
# turned on or off and provide the SQL code to enable Fluid Scaling for them.
# Note it will ignore any projects the currently logged in user (via gcloud CLI) does not have access to.

# TODO for users:
# Change these to match your region and organization
# Organization ID can be found on the "Resource Selector" at the top of the GCP Console
# * After clicking it select the "All" tab and it will be next to your org at the top in the right-most column
ORG_ID="YOUR_ORGANIZATION_ID"
REGION="region-us"  # Note that this needs to have the prefix of "region-" in front of the region name

# Fetch the active projects
PROJECTS=$(gcloud projects list --filter="parent.id=$ORG_ID AND lifecycleState=ACTIVE" --format="value(projectId)" 2>/dev/null)

echo "Fetching federated auth token..."
TOKEN=$(gcloud auth print-access-token 2>/dev/null)

echo "Scanning organization $ORG_ID for fluid scaling status..."
echo "================================================================================="

for PROJECT in $PROJECTS; do
  
  # 1. Check API silently
  API_ENABLED=$(gcloud services list \
    --project="$PROJECT" \
    --enabled \
    --filter="name:bigqueryreservation.googleapis.com" \
    --format="value(name)" 2>/dev/null)
  
  if [[ -n "$API_ENABLED" ]]; then
    
    # 2. Check IAM silently
    HAS_ACCESS=$(curl -s -X POST "https://cloudresourcemanager.googleapis.com/v1/projects/$PROJECT:testIamPermissions" \
      -H "Authorization: Bearer $TOKEN" \
      -H "Content-Type: application/json" \
      -d '{"permissions":["bigquery.jobs.create"]}' 2>/dev/null | grep -o "bigquery.jobs.create")

    if [[ -n "$HAS_ACCESS" ]]; then
      echo "--> Access verified. Querying Admin Project: $PROJECT..."
      
      # 3. Run the audit query to show the current state
      bq query --use_legacy_sql=false --format=pretty --project_id="$PROJECT" \
      "SELECT 
        '$PROJECT' AS admin_project,
        reservation_name,
        EXISTS (
          SELECT 1 
          FROM \`$REGION\`.INFORMATION_SCHEMA.PROJECT_OPTIONS,
          UNNEST(SPLIT(TRIM(option_value, '[]'), ',')) AS res_name
          WHERE option_name = 'preflight_fluid_autoscaling_reservations'
            AND TRIM(res_name, ' \"\\'') = reservation_name
        ) AS is_fluid_scaling_enabled
      FROM 
        \`$REGION\`.INFORMATION_SCHEMA.RESERVATIONS
      ORDER BY 
        is_fluid_scaling_enabled DESC, reservation_name ASC;" 2>/dev/null || true

      # 4. Generate the master DDL statement and capture it as a variable
      DDL_STATEMENT=$(bq query --use_legacy_sql=false --format=csv --project_id="$PROJECT" \
      "SELECT 
        CONCAT('ALTER PROJECT \`$PROJECT\` SET OPTIONS ( \`$REGION.preflight_fluid_autoscaling_reservations\` = [', 
        STRING_AGG(CONCAT('\"', reservation_name, '\"'), ', '), 
        ']);') AS master_ddl 
      FROM \`$REGION\`.INFORMATION_SCHEMA.RESERVATIONS
      HAVING COUNT(reservation_name) > 0;" 2>/dev/null | tail -n +2)
      
      # Clean up any trailing carriage returns from the CSV output
      DDL_STATEMENT=$(echo "$DDL_STATEMENT" | tr -d '\r')

      # 5. Only print the DDL if the string actually contains data
      if [[ -n "$DDL_STATEMENT" ]]; then
        echo "    [DDL covering all reservations for $PROJECT]:"
        echo "    $DDL_STATEMENT"
      else
        echo "    [No reservations found in $PROJECT. Skipping DDL.]"
      fi

      echo "---------------------------------------------------------------------------------"
    else
      echo "- Skipping $PROJECT (Insufficient access or boundary restricted)"
    fi
    
  fi
done