# Framework Components
strategy:
  business_objectives:
    - Digital transformation
    - Cost optimization
    - Innovation enablement
    - Global scalability

  success_metrics:
    - Migration velocity
    - Cost reduction percentage
    - Performance improvement
    - User satisfaction

people:
  roles:
    - Cloud Architect
    - Migration Engineer
    - Security Specialist
    - FinOps Analyst

  training:
    - Google Cloud Fundamentals
    - Professional certifications
    - Hands-on labs
    - Architecture workshops

process:
  governance:
    - Resource hierarchy
    - Policy enforcement
    - Cost controls
    - Security standards

  operations:
    - Monitoring strategy
    - Incident management
    - Change control
    - Continuous improvement

technology:
  foundation:
    - Network architecture
    - Identity management
    - Security controls
    - Automation tools

# Install migration assessment tool
gsutil cp gs://solutions-public-assets/bqmigration/BQMigrationAssessment.zip .
unzip BQMigrationAssessment.zip

# Run infrastructure assessment
python assess_infrastructure.py \
  --project-id=my-project \
  --output-dir=./assessment-results

# Python script for application dependency analysis
import networkx as nx
from google.cloud import compute_v1

def map_dependencies(project_id):
    """Map VM dependencies based on network connections"""

    instance_client = compute_v1.InstancesClient()
    network_graph = nx.DiGraph()

    # Get all instances
    for zone in compute_v1.ZonesClient().list(project=project_id):
        for instance in instance_client.list(project=project_id, zone=zone.name):
            network_graph.add_node(instance.name)

            # Analyze network connections
            for interface in instance.network_interfaces:
                # Add edges based on firewall rules and connections
                analyze_connections(network_graph, instance, interface)

    return network_graph

def analyze_connections(graph, instance, interface):
    """Analyze network connections between instances"""
    # Implementation for connection analysis
    pass

// TCO calculation parameters
const tcoParams = {
  onPremise: {
    servers: {
      count: 100,
      averageCost: 5000,
      refreshCycle: 4,
      maintenanceCost: 0.15 // 15% of hardware cost
    },
    dataCenter: {
      rackSpace: 10,
      powerPerRack: 5, // kW
      coolingMultiplier: 1.5,
      costPerKwh: 0.10
    },
    personnel: {
      admins: 5,
      averageSalary: 100000,
      overhead: 1.3
    }
  },
  googleCloud: {
    compute: {
      instanceType: 'n2-standard-4',
      count: 80,
      sustainedUseDiscount: 0.30,
      committedUseDiscount: 0.57
    },
    storage: {
      standardStorage: 1000, // TB
      nearlineStorage: 5000, // TB
      archivalStorage: 10000 // TB
    },
    network: {
      egressTraffic: 10, // TB/month
      loadBalancing: true,
      cdn: true
    }
  }
};

# Create migration manager
gcloud compute instances create migration-manager \
  --zone=us-central1-a \
  --machine-type=n1-standard-4 \
  --image-family=debian-10 \
  --image-project=debian-cloud \
  --scopes=cloud-platform

# Install Migrate for Compute Engine
curl -O https://storage.googleapis.com/migrate-for-compute-engine/v5.0/migrate-installer.sh
chmod +x migrate-installer.sh
./migrate-installer.sh

# migration-wave.yaml
apiVersion: migrate.google.com/v1
kind: MigrationWave
metadata:
  name: wave-1-web-tier
spec:
  sources:
    - type: vmware
      datacenter: dc1
      cluster: web-cluster
  targets:
    - project: my-project
      zone: us-central1-a
      network: projects/my-project/global/networks/prod-vpc
      subnet: projects/my-project/regions/us-central1/subnetworks/web-subnet
  vms:
    - name: web-server-01
      targetInstanceType: n2-standard-4
      targetBootDiskType: pd-ssd
    - name: web-server-02
      targetInstanceType: n2-standard-4
      targetBootDiskType: pd-ssd

-- Pre-migration checks
SELECT 
  @@version AS mysql_version,
  @@innodb_version AS innodb_version,
  @@character_set_server AS charset,
  @@collation_server AS collation;

-- Check for migration blockers
SELECT 
  table_schema,
  table_name,
  engine
FROM information_schema.tables
WHERE engine NOT IN ('InnoDB', 'MyISAM');

# Create Database Migration Service job
gcloud database-migration migration-jobs create mysql-migration \
  --region=us-central1 \
  --type=CONTINUOUS \
  --source=projects/PROJECT_ID/locations/us-central1/sources/mysql-source \
  --destination=projects/PROJECT_ID/locations/us-central1/destinations/cloudsql-destination

from google.cloud import storage_transfer_v1

def create_transfer_job():
    """Create a transfer job from on-premises to GCS"""

    client = storage_transfer_v1.StorageTransferServiceClient()

    transfer_job = {
        'name': 'transferJobs/on-prem-to-gcs',
        'description': 'Migrate on-premises data to Cloud Storage',
        'project_id': 'my-project',
        'transfer_spec': {
            'source_agent_pool_name': 'projects/my-project/agentPools/on-prem-pool',
            'gcs_data_sink': {
                'bucket_name': 'migration-data-bucket',
                'path': 'migrated-data/'
            },
            'transfer_options': {
                'overwrite_objects_already_existing_in_sink': False,
                'delete_objects_from_source_after_transfer': False
            }
        },
        'schedule': {
            'start_time': '2024-01-25T00:00:00Z',
            'repeat_interval': '86400s'  # Daily
        }
    }

    return client.create_transfer_job(
        request={'transfer_job': transfer_job}
    )

# Terraform VPC configuration
resource "google_compute_network" "prod_vpc" {
  name                    = "production-vpc"
  auto_create_subnetworks = false
  routing_mode           = "REGIONAL"
}

resource "google_compute_subnetwork" "web_subnet" {
  name          = "web-tier-subnet"
  ip_cidr_range = "10.1.0.0/24"
  region        = "us-central1"
  network       = google_compute_network.prod_vpc.id

  secondary_ip_range {
    range_name    = "gke-pods"
    ip_cidr_range = "10.10.0.0/20"
  }

  secondary_ip_range {
    range_name    = "gke-services"
    ip_cidr_range = "10.20.0.0/20"
  }

  private_ip_google_access = true
}

resource "google_compute_firewall" "allow_internal" {
  name    = "allow-internal"
  network = google_compute_network.prod_vpc.name

  allow {
    protocol = "tcp"
    ports    = ["0-65535"]
  }

  allow {
    protocol = "udp"
    ports    = ["0-65535"]
  }

  allow {
    protocol = "icmp"
  }

  source_ranges = ["10.0.0.0/8"]
}

# Cloud VPN configuration
apiVersion: compute.cnrm.cloud.google.com/v1beta1
kind: ComputeVPNGateway
metadata:
  name: on-prem-gateway
spec:
  region: us-central1
  networkRef:
    name: production-vpc
---
apiVersion: compute.cnrm.cloud.google.com/v1beta1
kind: ComputeVPNTunnel
metadata:
  name: on-prem-tunnel-1
spec:
  vpnGatewayRef:
    name: on-prem-gateway
  peerIp: "203.0.113.1"
  sharedSecret:
    valueFrom:
      secretKeyRef:
        name: vpn-secret
        key: shared-secret
  ikeVersion: 2

# Global load balancer configuration
from google.cloud import compute_v1

def create_global_load_balancer():
    """Create a global HTTP(S) load balancer"""

    # Backend configuration
    backend_service = {
        'name': 'global-web-backend',
        'protocol': 'HTTP',
        'port_name': 'http',
        'timeout_sec': 30,
        'health_checks': ['global-health-check'],
        'backends': [
            {
                'group': 'us-central1-ig',
                'balancing_mode': 'UTILIZATION',
                'max_utilization': 0.8
            },
            {
                'group': 'europe-west1-ig',
                'balancing_mode': 'UTILIZATION',
                'max_utilization': 0.8
            }
        ],
        'cdn_policy': {
            'cache_mode': 'CACHE_ALL_STATIC',
            'default_ttl': 3600,
            'max_ttl': 86400
        }
    }

    # URL map configuration
    url_map = {
        'name': 'global-web-map',
        'default_service': 'global-web-backend',
        'host_rules': [
            {
                'hosts': ['www.example.com'],
                'path_matcher': 'path-matcher-1'
            }
        ],
        'path_matchers': [
            {
                'name': 'path-matcher-1',
                'default_service': 'global-web-backend',
                'path_rules': [
                    {
                        'paths': ['/api/*'],
                        'service': 'api-backend-service'
                    }
                ]
            }
        ]
    }

    return backend_service, url_map

# GKE cluster configuration
apiVersion: container.cnrm.cloud.google.com/v1beta1
kind: ContainerCluster
metadata:
  name: production-gke
spec:
  location: us-central1
  initialNodeCount: 3
  nodeConfig:
    machineType: n2-standard-4
    diskSizeGb: 100
    diskType: pd-ssd
    oauthScopes:
      - https://www.googleapis.com/auth/cloud-platform
    metadata:
      disable-legacy-endpoints: "true"
  addonsConfig:
    horizontalPodAutoscaling:
      disabled: false
    httpLoadBalancing:
      disabled: false
    networkPolicyConfig:
      disabled: false
  networkPolicy:
    enabled: true
  ipAllocationPolicy:
    useIpAliases: true
    clusterSecondaryRangeName: gke-pods
    servicesSecondaryRangeName: gke-services
  workloadIdentity:
    workloadPool: my-project.svc.id.goog

# Terraform organization policies
resource "google_organization_policy" "require_shielded_vm" {
  org_id     = var.org_id
  constraint = "compute.requireShieldedVm"

  boolean_policy {
    enforced = true
  }
}

resource "google_organization_policy" "allowed_locations" {
  org_id     = var.org_id
  constraint = "gcp.resourceLocations"

  list_policy {
    allow {
      values = ["us-central1", "us-east1", "europe-west1"]
    }
  }
}

from google.oauth2 import service_account
from google.cloud import iam

def create_least_privilege_sa():
    """Create service account with minimal permissions"""

    iam_client = iam.IAMClient()

    # Create service account
    service_account = {
        'account_id': 'app-service-account',
        'display_name': 'Application Service Account',
        'description': 'Service account for application with least privilege'
    }

    sa = iam_client.create_service_account(
        name=f'projects/{project_id}',
        service_account=service_account
    )

    # Grant minimal roles
    policy = {
        'bindings': [
            {
                'role': 'roles/cloudsql.client',
                'members': [f'serviceAccount:{sa.email}']
            },
            {
                'role': 'roles/storage.objectViewer',
                'members': [f'serviceAccount:{sa.email}'],
                'condition': {
                    'title': 'Only specific bucket',
                    'expression': 'resource.name.startsWith("projects/_/buckets/app-data")'
                }
            }
        ]
    }

    return sa, policy

# Customer-Managed Encryption Keys (CMEK)
apiVersion: cloudkms.cnrm.cloud.google.com/v1beta1
kind: CloudKMSKeyRing
metadata:
  name: migration-keyring
spec:
  location: us-central1
---
apiVersion: cloudkms.cnrm.cloud.google.com/v1beta1
kind: CloudKMSCryptoKey
metadata:
  name: disk-encryption-key
spec:
  keyRingRef:
    name: migration-keyring
  purpose: ENCRYPT_DECRYPT
  rotationPeriod: 7776000s # 90 days
  versionTemplate:
    algorithm: GOOGLE_SYMMETRIC_ENCRYPTION

from google.cloud import dlp_v2

def configure_dlp_inspection():
    """Configure DLP for sensitive data inspection"""

    dlp_client = dlp_v2.DlpServiceClient()

    # Define inspection configuration
    inspect_config = {
        'info_types': [
            {'name': 'CREDIT_CARD_NUMBER'},
            {'name': 'US_SOCIAL_SECURITY_NUMBER'},
            {'name': 'EMAIL_ADDRESS'}
        ],
        'min_likelihood': dlp_v2.Likelihood.LIKELY,
        'include_quote': True,
        'limits': {
            'max_findings_per_request': 100
        }
    }

    # Create inspection template
    template = {
        'display_name': 'Migration DLP Template',
        'description': 'Template for scanning migrated data',
        'inspect_config': inspect_config
    }

    parent = f"projects/{project_id}/locations/global"

    return dlp_client.create_inspect_template(
        request={
            'parent': parent,
            'inspect_template': template
        }
    )

# Security Command Center setup
gcloud scc sources create migration-security-source \
  --display-name="Migration Security Scanner" \
  --organization=${ORG_ID}

# Enable compliance scanning
gcloud scc custom-modules create pci-compliance-check \
  --organization=${ORG_ID} \
  --display-name="PCI DSS Compliance Check" \
  --module-type=CUSTOM_MODULE_TYPE_DETECTOR \
  --custom-config-from-file=pci-compliance-rules.yaml

def calculate_cud_savings():
    """Calculate savings from Committed Use Discounts"""

    # Current usage
    current_usage = {
        'n2-standard-4': 50,
        'n2-standard-8': 25,
        'pd-ssd': 10000  # GB
    }

    # Pricing (example rates)
    on_demand_prices = {
        'n2-standard-4': 0.1892,  # per hour
        'n2-standard-8': 0.3784,  # per hour
        'pd-ssd': 0.17  # per GB per month
    }

    # CUD discounts
    cud_discount = 0.57  # 57% discount for 3-year commitment

    # Calculate monthly costs
    monthly_on_demand = 0
    monthly_cud = 0

    for resource, quantity in current_usage.items():
        if resource == 'pd-ssd':
            monthly_on_demand += quantity * on_demand_prices[resource]
            monthly_cud += quantity * on_demand_prices[resource] * (1 - cud_discount)
        else:
            hours_per_month = 730
            monthly_on_demand += quantity * on_demand_prices[resource] * hours_per_month
            monthly_cud += quantity * on_demand_prices[resource] * hours_per_month * (1 - cud_discount)

    monthly_savings = monthly_on_demand - monthly_cud
    annual_savings = monthly_savings * 12

    return {
        'monthly_on_demand': monthly_on_demand,
        'monthly_cud': monthly_cud,
        'monthly_savings': monthly_savings,
        'annual_savings': annual_savings,
        'savings_percentage': (monthly_savings / monthly_on_demand) * 100
    }

# Horizontal Pod Autoscaler
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
  name: web-app-hpa
spec:
  scaleTargetRef:
    apiVersion: apps/v1
    kind: Deployment
    name: web-app
  minReplicas: 2
  maxReplicas: 100
  metrics:
  - type: Resource
    resource:
      name: cpu
      target:
        type: Utilization
        averageUtilization: 70
  - type: Resource
    resource:
      name: memory
      target:
        type: Utilization
        averageUtilization: 80
  - type: External
    external:
      metric:
        name: pubsub_queue_depth
        selector:
          matchLabels:
            queue: work-queue
      target:
        type: Value
        value: 100

-- Create partitioned and clustered table
CREATE TABLE dataset.optimized_logs
PARTITION BY DATE(timestamp)
CLUSTER BY user_id, event_type
AS
SELECT * FROM dataset.raw_logs;

-- Query optimization example
-- Bad: Scans entire table
SELECT COUNT(*) 
FROM dataset.raw_logs
WHERE DATE(timestamp) = '2024-01-25';

-- Good: Uses partition pruning
SELECT COUNT(*)
FROM dataset.optimized_logs
WHERE DATE(timestamp) = '2024-01-25';

-- Set up scheduled queries for cost control
CREATE OR REPLACE SCHEDULED QUERY
  'daily_aggregation'
OPTIONS (
  query = """
    SELECT 
      DATE(timestamp) as date,
      COUNT(*) as events,
      COUNT(DISTINCT user_id) as unique_users
    FROM dataset.optimized_logs
    WHERE DATE(timestamp) = CURRENT_DATE() - 1
    GROUP BY date
  """,
  destination_dataset_id = 'aggregated_data',
  write_disposition = 'WRITE_APPEND',
  schedule = 'every day 02:00'
);

#!/bin/bash
# Migration script for Wave 1

# Set variables
PROJECT_ID="my-project"
SOURCE_ZONE="on-prem-dc1"
TARGET_ZONE="us-central1-a"

# Migrate test environments
echo "Starting Wave 1 migration..."

# Create snapshots
for vm in $(gcloud compute instances list --zones=$SOURCE_ZONE --format="value(name)" --filter="labels.environment=test"); do
  echo "Creating snapshot for $vm"
  gcloud compute disks snapshot $vm --zone=$SOURCE_ZONE --snapshot-names=$vm-migration-snapshot
done

# Create instances from snapshots
for snapshot in $(gcloud compute snapshots list --format="value(name)" --filter="name~migration-snapshot"); do
  vm_name=$(echo $snapshot | sed 's/-migration-snapshot//')
  echo "Creating instance $vm_name in GCP"

  gcloud compute instances create $vm_name \
    --zone=$TARGET_ZONE \
    --machine-type=n2-standard-4 \
    --source-snapshot=$snapshot \
    --boot-disk-size=100GB \
    --boot-disk-type=pd-ssd \
    --network-tier=PREMIUM
done

# Automated migration for business applications
from google.cloud import compute_v1
import time

class MigrationOrchestrator:
    def __init__(self, project_id):
        self.project_id = project_id
        self.compute_client = compute_v1.InstancesClient()

    def migrate_application_stack(self, app_name, vms):
        """Migrate an entire application stack"""

        print(f"Starting migration for {app_name}")

        # Pre-migration checks
        if not self.validate_prerequisites(vms):
            raise Exception("Prerequisites not met")

        # Create migration plan
        migration_plan = self.create_migration_plan(vms)

        # Execute migration
        for step in migration_plan:
            print(f"Executing: {step['description']}")

            if step['type'] == 'database':
                self.migrate_database(step['source'], step['target'])
            elif step['type'] == 'application':
                self.migrate_application(step['source'], step['target'])
            elif step['type'] == 'load_balancer':
                self.update_load_balancer(step['config'])

            # Validate step
            if not self.validate_step(step):
                self.rollback(step)
                raise Exception(f"Migration failed at step: {step['description']}")

        print(f"Migration completed for {app_name}")

    def migrate_database(self, source, target):
        """Migrate database with minimal downtime"""
        # Implementation for database migration
        pass

# Kubernetes blue-green deployment
apiVersion: v1
kind: Service
metadata:
  name: app-service
spec:
  selector:
    app: myapp
    version: green  # Switch between blue/green
  ports:
  - port: 80
    targetPort: 8080
---
# Blue deployment (current)
apiVersion: apps/v1
kind: Deployment
metadata:
  name: myapp-blue
spec:
  replicas: 3
  selector:
    matchLabels:
      app: myapp
      version: blue
  template:
    metadata:
      labels:
        app: myapp
        version: blue
    spec:
      containers:
      - name: app
        image: gcr.io/my-project/myapp:v1.0
        ports:
        - containerPort: 8080
---
# Green deployment (new)
apiVersion: apps/v1
kind: Deployment
metadata:
  name: myapp-green
spec:
  replicas: 3
  selector:
    matchLabels:
      app: myapp
      version: green
  template:
    metadata:
      labels:
        app: myapp
        version: green
    spec:
      containers:
      - name: app
        image: gcr.io/my-project/myapp:v2.0
        ports:
        - containerPort: 8080

from google.cloud import monitoring_v3
from google.cloud.monitoring_dashboard import v1

def create_migration_dashboard():
    """Create comprehensive monitoring dashboard"""

    client = v1.DashboardsServiceClient()
    project_name = f"projects/{project_id}"

    dashboard = {
        "displayName": "Migration Success Dashboard",
        "mosaicLayout": {
            "columns": 12,
            "tiles": [
                {
                    "width": 6,
                    "height": 4,
                    "widget": {
                        "title": "VM CPU Utilization",
                        "xyChart": {
                            "dataSets": [{
                                "timeSeriesQuery": {
                                    "timeSeriesFilter": {
                                        "filter": 'metric.type="compute.googleapis.com/instance/cpu/utilization"',
                                        "aggregation": {
                                            "alignmentPeriod": "60s",
                                            "perSeriesAligner": "ALIGN_MEAN"
                                        }
                                    }
                                }
                            }]
                        }
                    }
                },
                {
                    "xPos": 6,
                    "width": 6,
                    "height": 4,
                    "widget": {
                        "title": "Application Response Time",
                        "xyChart": {
                            "dataSets": [{
                                "timeSeriesQuery": {
                                    "timeSeriesFilter": {
                                        "filter": 'metric.type="loadbalancing.googleapis.com/https/backend_latencies"',
                                        "aggregation": {
                                            "alignmentPeriod": "60s",
                                            "perSeriesAligner": "ALIGN_PERCENTILE_95"
                                        }
                                    }
                                }
                            }]
                        }
                    }
                }
            ]
        }
    }

    return client.create_dashboard(parent=project_name, dashboard=dashboard)

from google.cloud import logging
from google.cloud import bigquery

def analyze_migration_logs():
    """Analyze migration logs for insights"""

    # Set up logging client
    logging_client = logging.Client()

    # Export logs to BigQuery
    sink_name = "migration-logs-sink"
    destination = f"bigquery.googleapis.com/projects/{project_id}/datasets/migration_logs"

    sink = logging_client.sink(
        sink_name,
        filter_='resource.type="gce_instance" OR resource.type="k8s_container"',
        destination=destination
    )

    if not sink.exists():
        sink.create()

    # Query logs for analysis
    bq_client = bigquery.Client()

    query = """
    SELECT
      timestamp,
      resource.labels.instance_id as instance,
      jsonPayload.message as message,
      severity
    FROM `migration_logs.syslog_*`
    WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
      AND severity IN ('ERROR', 'CRITICAL')
    ORDER BY timestamp DESC
    LIMIT 100
    """

    return bq_client.query(query)

# Optimize GKE workloads
apiVersion: v1
kind: ConfigMap
metadata:
  name: app-optimization
data:
  JAVA_OPTS: "-Xmx2g -Xms2g -XX:+UseG1GC -XX:MaxGCPauseMillis=200"
  DB_POOL_SIZE: "20"
  CACHE_ENABLED: "true"
  COMPRESSION_ENABLED: "true"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: optimized-app
spec:
  template:
    spec:
      containers:
      - name: app
        resources:
          requests:
            memory: "2Gi"
            cpu: "1000m"
          limits:
            memory: "4Gi"
            cpu: "2000m"
        envFrom:
        - configMapRef:
            name: app-optimization
        readinessProbe:
          httpGet:
            path: /health
            port: 8080
          initialDelaySeconds: 30
          periodSeconds: 10
        livenessProbe:
          httpGet:
            path: /health
            port: 8080
          initialDelaySeconds: 60
          periodSeconds: 30

class FinOpsAnalyzer:
    def __init__(self, project_id):
        self.project_id = project_id
        self.bq_client = bigquery.Client()

    def analyze_cost_trends(self):
        """Analyze cost trends and provide recommendations"""

        query = """
        WITH daily_costs AS (
          SELECT
            service.description as service,
            sku.description as sku,
            DATE(usage_start_time) as usage_date,
            SUM(cost) as daily_cost
          FROM `{project_id}.billing.gcp_billing_export_v1_{billing_account_id}`
          WHERE DATE(usage_start_time) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
          GROUP BY 1, 2, 3
        ),
        cost_trends AS (
          SELECT
            service,
            sku,
            usage_date,
            daily_cost,
            AVG(daily_cost) OVER (
              PARTITION BY service, sku 
              ORDER BY usage_date 
              ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
            ) as rolling_avg
          FROM daily_costs
        )
        SELECT
          service,
          sku,
          usage_date,
          daily_cost,
          rolling_avg,
          (daily_cost - rolling_avg) / rolling_avg * 100 as pct_change
        FROM cost_trends
        WHERE usage_date = CURRENT_DATE() - 1
          AND ABS((daily_cost - rolling_avg) / rolling_avg) > 0.2
        ORDER BY daily_cost DESC
        """

        results = self.bq_client.query(query.format(
            project_id=self.project_id,
            billing_account_id=self.billing_account_id
        ))

        recommendations = []
        for row in results:
            if row.pct_change > 20:
                recommendations.append({
                    'service': row.service,
                    'sku': row.sku,
                    'action': 'investigate_spike',
                    'daily_cost': row.daily_cost,
                    'change_pct': row.pct_change
                })

        return recommendations

-- BigQuery dashboard queries
-- Migration velocity
SELECT
  DATE(migration_completed) as migration_date,
  COUNT(DISTINCT vm_name) as vms_migrated,
  AVG(migration_duration_hours) as avg_duration,
  SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) / COUNT(*) * 100 as success_rate
FROM `migration_tracking.vm_migrations`
WHERE migration_completed >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY migration_date
ORDER BY migration_date DESC;

-- Cost optimization
SELECT
  DATE(usage_date) as date,
  SUM(on_premise_cost) as on_prem_cost,
  SUM(gcp_cost) as cloud_cost,
  (SUM(on_premise_cost) - SUM(gcp_cost)) / SUM(on_premise_cost) * 100 as savings_pct
FROM `cost_analysis.daily_comparison`
WHERE usage_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY date
ORDER BY date DESC;

-- Performance metrics
SELECT
  application_name,
  AVG(response_time_before) as avg_response_before_ms,
  AVG(response_time_after) as avg_response_after_ms,
  (AVG(response_time_before) - AVG(response_time_after)) / AVG(response_time_before) * 100 as improvement_pct
FROM `performance_metrics.application_comparison`
GROUP BY application_name
HAVING improvement_pct > 0
ORDER BY improvement_pct DESC;