実行にあたって必要なパッケージ

・awsのcli
・jq（brew install jqとかでいけるはず）
・bc（brew install bcとかでいけるはず）
基本コピペで動くはず
awsのコマンドラインが実行できて、RDSに参照できる権限があれば、インスタンス名の一覧は取得する
変更するとしたら、UNIT_COSTとかはリージョンに合わせて欲しい
またこれらはserverlessの
#!/bin/bash

set -e

# Set the time range for September 2024
START_TIME="2024-09-01T00:00:00Z"
END_TIME="2024-09-30T23:59:59Z"

# Storage prices
GP2_PRICE=0.138
GP3_PRICE=0.138
IO1_PRICE=0.24
IO2_PRICE=0.24
MAGNETIC_PRICE=0.12


# https://aws.amazon.com/jp/rds/aurora/pricing/#Aurora_MySQL-Compatible_Edition

#Aurora Serverless v1 の料金
#リージョン: アジアパシフィック (東京)
#測定	Aurora Standard (ACU 時間あたり)	Aurora I/O 最適化 (ACU 時間あたり)
#Aurora キャパシティーユニット	USD 0.20	USD 0.26
AURORA_V2_UNIT_COST=0.26

#Aurora Serverless v1 の料金
#リージョン: アジアパシフィック (東京)
#測定	料金
#Aurora キャパシティーユニット	USD 0.10/ACU 時間
AURORA_V1_UNIT_COST=0.1

# I/O prices
IO_PRICE_PER_MILLION=0.12

# Backup storage price
# 2024/10月時点
BACKUP_PRICE=0.012

# Function to update total cost
update_total_cost() {
    local cost_type=$1
    local cost_value=$2

    case $cost_type in
        "v2")
            total_v2_cost=$(echo "scale=2; $total_v2_cost + $cost_value" | bc)
            ;;
        "v1")
            total_v1_cost=$(echo "scale=2; $total_v1_cost + $cost_value" | bc)
            ;;
    esac
}

# Function to get I/O operations
get_io_operations() {
    local identifier=$1
    local start_time=$2
    local end_time=$3
    local dimension_name=$4

    local read_iops=$(aws cloudwatch get-metric-statistics \
        --namespace AWS/RDS \
        --metric-name ReadIOPS \
        --start-time $start_time \
        --end-time $end_time \
        --period 2592000 \
        --statistics Average \
        --dimensions Name=$dimension_name,Value=$identifier \
        --query 'Datapoints[0].Average' \
        --output text)

    local write_iops=$(aws cloudwatch get-metric-statistics \
        --namespace AWS/RDS \
        --metric-name WriteIOPS \
        --start-time $start_time \
        --end-time $end_time \
        --period 2592000 \
        --statistics Average \
        --dimensions Name=$dimension_name,Value=$identifier \
        --query 'Datapoints[0].Average' \
        --output text)

    if [[ $read_iops == "None" ]]; then
        read_iops=0
    fi

    if [[ $write_iops == "None" ]]; then
        write_iops=0
    fi

    # Calculate total I/O operations for the month
    local total_iops=$(echo "($read_iops + $write_iops) * 3600 * 24 * 30" | bc)
    printf "%.0f" $total_iops
}

# Function to get storage details
get_storage_details() {
    local instance=$1
    aws rds describe-db-instances --db-instance-identifier $instance \
        --query 'DBInstances[0].{StorageType:StorageType,AllocatedStorage:AllocatedStorage,Iops:Iops,StorageThroughput:StorageThroughput}'
}

# Function to calculate storage cost
calculate_storage_cost() {
    local storage_type=$1
    local allocated_storage=$2
    local iops=$3
    local throughput=$4

    case $storage_type in
        "gp2")
            echo "$allocated_storage * $GP2_PRICE" | bc
            ;;
        "gp3")
            base_cost=$(echo "$allocated_storage * $GP3_PRICE" | bc)
            iops_cost=$(echo "($iops - 3000) * 0.024" | bc)
            throughput_cost=$(echo "($throughput - 125) * 0.096" | bc)
            echo "$base_cost + $iops_cost + $throughput_cost" | bc
            ;;
        "io1"|"io2")
            storage_cost=$(echo "$allocated_storage * $IO1_PRICE" | bc)
            iops_cost=$(echo "$iops * 0.12" | bc)
            echo "$storage_cost + $iops_cost" | bc
            ;;
        "standard"|"magnetic")
            echo "$allocated_storage * $MAGNETIC_PRICE" | bc
            ;;
        *)
            echo "0"
            ;;
    esac
}

# Function to get manual snapshot size
get_manual_snapshot_size() {
    local identifier=$1
    local snapshot_type=$2
    if [[ "$snapshot_type" == "instance" ]]; then
        local snapshot_size=$(aws rds describe-db-snapshots \
            --db-instance-identifier $identifier \
            --query 'sum(DBSnapshots[].AllocatedStorage)' \
            --output text)
    else
        local snapshot_size=$(aws rds describe-db-cluster-snapshots \
            --db-cluster-identifier $identifier \
            --query 'sum(DBClusterSnapshots[].AllocatedStorage)' \
            --output text)
    fi

    if [[ $snapshot_size == "None" ]]; then
        echo "0"
    else
        echo "$snapshot_size"
    fi
}

total_v2_cost=0
total_v1_cost=0

# Process Aurora MySQL v2 Instances
echo "Processing Aurora MySQL v2 Instances:"
# Fetch RDS Aurora MySQL v2 instance identifiers
INSTANCES=$(aws rds describe-db-instances --query 'DBInstances[?Engine==`aurora-mysql`].DBInstanceIdentifier' --output text)

echo "Detected RDS Aurora MySQL v2 instances: $INSTANCES"

for INSTANCE in $INSTANCES; do
    echo "Collecting data and calculating costs for instance: $INSTANCE"

    # Get instance details
    instance_details=$(aws rds describe-db-instances --db-instance-identifier $INSTANCE)
    engine=$(echo $instance_details | jq -r '.DBInstances[0].Engine')
    engine_version=$(echo $instance_details | jq -r '.DBInstances[0].EngineVersion')
    cluster_id=$(echo $instance_details | jq -r '.DBInstances[0].DBClusterIdentifier')

    echo "  Engine: $engine"
    echo "  Engine Version: $engine_version"
    echo "  Cluster: $cluster_id"

    # Get cluster scaling configuration
    scaling_config=$(aws rds describe-db-clusters --db-cluster-identifier $cluster_id \
        --query 'DBClusters[0].ServerlessV2ScalingConfiguration' \
        --output json)
    min_capacity=$(echo $scaling_config | jq -r '.MinCapacity')
    max_capacity=$(echo $scaling_config | jq -r '.MaxCapacity')

    echo "  Min Capacity: $min_capacity"
    echo "  Max Capacity: $max_capacity"

    if [[ $min_capacity == "null" || $max_capacity == "null" ]]; then
        echo "  Warning: Serverless v2 scaling configuration not found. Using default values."
        min_capacity=0.5
        max_capacity=16
    fi

    # Get ACU price
    acu_price=$AURORA_V2_UNIT_COST

    echo "  ACU Price: $acu_price"

    # ACU Usage and Cost
    acu_usage=$(aws cloudwatch get-metric-statistics \
        --namespace AWS/RDS \
        --metric-name ServerlessDatabaseCapacity \
        --start-time $START_TIME \
        --end-time $END_TIME \
        --period 2592000 \
        --statistics Average \
        --dimensions Name=DBInstanceIdentifier,Value=$INSTANCE \
        --query 'Datapoints[0].Average' \
        --output text)

    if [[ $acu_usage == "None" ]]; then
        echo "  Warning: No ACU usage data found. Using min capacity for estimation."
        acu_usage=$min_capacity
    fi

    acu_cost=$(echo "$acu_usage * $acu_price * 24 * 30" | bc)

    echo "  ACU Usage: $acu_usage ACUs"
    echo "  Estimated ACU Cost: \$${acu_cost}"

    # Storage Usage and Cost
    storage_details=$(get_storage_details $INSTANCE)
    storage_type=$(echo $storage_details | jq -r '.StorageType')
    allocated_storage=$(echo $storage_details | jq -r '.AllocatedStorage')
    iops=$(echo $storage_details | jq -r '.Iops // 0')
    throughput=$(echo $storage_details | jq -r '.StorageThroughput // 0')

    storage_cost=$(calculate_storage_cost $storage_type $allocated_storage $iops $throughput)

    echo "  Storage Usage: ${allocated_storage} GB"
    echo "  Storage Cost: \$${storage_cost}"

    # Manual Snapshot Size and Cost
    manual_snapshot_size=$(get_manual_snapshot_size $INSTANCE)
    manual_snapshot_cost=$(echo "scale=2; $manual_snapshot_size * $BACKUP_PRICE" | bc)

    echo "  Manual Snapshot Size: ${manual_snapshot_size} GB"
    echo "  Manual Snapshot Cost: \$${manual_snapshot_cost}"

    # I/O Operations and Cost
    io_ops=$(get_io_operations $INSTANCE $START_TIME $END_TIME "DBInstanceIdentifier")
    if [[ $io_ops == "None" ]]; then
        io_ops=0
    fi
    io_cost=$(echo "scale=2; ($io_ops / 1000000) * $IO_PRICE_PER_MILLION" | bc)

    echo "  I/O Operations: $io_ops"
    echo "  I/O Cost: \$${io_cost}"

    # Calculate total cost for this instance
    instance_total_cost=$(echo "scale=2; $acu_cost + $storage_cost + $manual_snapshot_cost + $io_cost" | bc)
    echo "  Total Cost for $INSTANCE: \$${instance_total_cost}"

    update_total_cost "v2" $instance_total_cost

    echo "----------------------------------------"
done

echo "Subtotal for Aurora MySQL v2 Instances: \$${total_v2_cost}"
echo "----------------------------------------"

# Process Aurora Serverless v1 Clusters
echo "Processing Aurora Serverless v1 Clusters:"
V1_CLUSTERS=$(aws rds describe-db-clusters \
    --query 'DBClusters[?Engine==`aurora-mysql` && !ServerlessV2ScalingConfiguration].{ClusterId:DBClusterIdentifier,Engine:Engine,EngineVersion:EngineVersion}' \
    --output json)

total_v1_cost=0  # Initialize total_v1_cost

for cluster in $(echo "$V1_CLUSTERS" | jq -c '.[]'); do
    CLUSTER_ID=$(echo $cluster | jq -r '.ClusterId')
    ENGINE=$(echo $cluster | jq -r '.Engine')
    ENGINE_VERSION=$(echo $cluster | jq -r '.EngineVersion')

    echo "Collecting data and calculating costs for cluster: $CLUSTER_ID"
    echo "  Engine: $ENGINE"
    echo "  Engine Version: $ENGINE_VERSION"

    # ACU Usage and Cost
    acu_usage=$(aws cloudwatch get-metric-statistics \
        --namespace AWS/RDS \
        --metric-name ServerlessDatabaseCapacity \
        --start-time $START_TIME \
        --end-time $END_TIME \
        --period 2592000 \
        --statistics Average \
        --dimensions Name=DBClusterIdentifier,Value=$CLUSTER_ID \
        --query 'Datapoints[0].Average' \
        --output text)

    if [[ $acu_usage == "None" ]]; then
        echo "  Warning: No ACU usage data found. Using default value for estimation."
        acu_usage=1
    fi

    acu_price=$AURORA_V1_UNIT_COST  # Example price, adjust as needed
    acu_cost=$(echo "$acu_usage * $acu_price * 24 * 30" | bc)

    echo "  ACU Usage: $acu_usage ACUs"
    echo "  Estimated ACU Cost: \$${acu_cost}"

    # Manual Snapshot Size and Cost
    manual_snapshot_size=$(get_manual_snapshot_size $CLUSTER_ID "cluster")
    manual_snapshot_cost=$(echo "scale=2; $manual_snapshot_size * $BACKUP_PRICE" | bc)

    echo "  Manual Snapshot Size: ${manual_snapshot_size} GB"
    echo "  Manual Snapshot Cost: \$${manual_snapshot_cost}"

    # I/O Operations and Cost
    io_ops=$(get_io_operations $CLUSTER_ID $START_TIME $END_TIME "DBClusterIdentifier")
    if [[ $io_ops == "None" ]]; then
        io_ops=0
    fi
    io_cost=$(echo "scale=2; ($io_ops / 1000000) * $IO_PRICE_PER_MILLION" | bc)

    echo "  I/O Operations: $io_ops"
    echo "  I/O Cost: \$${io_cost}"

    # Calculate total cost for this cluster
    cluster_total_cost=$(echo "scale=2; $acu_cost + $manual_snapshot_cost + $io_cost" | bc)
    echo "  Total Cost for $CLUSTER_ID: \$${cluster_total_cost}"

    # Update total_v1_cost directly
    total_v1_cost=$(echo "scale=2; $total_v1_cost + $cluster_total_cost" | bc)

    echo "----------------------------------------"
done

echo "Subtotal for Aurora Serverless v1 Clusters: \$${total_v1_cost}"
echo "----------------------------------------"

# Calculate and display the grand total
grand_total=$(echo "scale=2; $total_v2_cost + $total_v1_cost" | bc)

echo "Summary:"
echo "Total Cost for Aurora MySQL v2 Instances: \$${total_v2_cost}"
echo "Total Cost for Aurora Serverless v1 Clusters: \$${total_v1_cost}"
echo "Grand Total for all RDS MySQL instances and clusters: \$${grand_total}"

echo "Note: This is an estimate. Actual costs may vary. Please check the AWS Billing and Cost Management console for accurate pricing and to include costs like backup storage."
AIに適当にかかせて調整したやつなんで設定値とか料金とか、間違ってるところは色々ありそう
https://aws.amazon.com/jp/rds/aurora/pricing/
ここ見てコストは調整してほしいリージョンに応じて変わるし
というかその辺りさくっと内訳計算してくれるツールあるのかな
（画面から一つ一つ入力して計算する公式の料金計算ツールはなしで（めんどくさすぎる
あとおそらくAuroraV2のI/O最適化の設定に応じて料金切り替えるようになってないから、このコードをAIとかに投げて料金切り替えてもらうように調整してもらえれば。