【小ネタ】マルチアカウント環境のCost Optimization Hub(EC2サイズ推奨項目)を見やすく加工

# S3からダウンロード(coh.csv.gz)
aws s3 cp s3://amzn-s3-demo-bucket/example-export/data/date=2025-03-27/..略../example-export-00001.csv.gz ./coh.csv.gz

# gzファイルを展開(coh.csv)
gzip -d coh.csv.gz

aws organizations list-accounts --output json \
  --query "Accounts[].{account_name:Name, account_id:Id}" \
| duckdb -csv -c "select * from read_json('/dev/stdin') order by account_name" > accounts.csv

account_name="EXAMPLE"

# EC2インスタンス一覧を取得してCSV化
output=$(
  aws ec2 describe-instances --output json \
  --query 'Reservations[*].Instances[].{
    instance_name: Tags[?Key==`Name`] | [0].Value,
    instance_cmbillinggroup: Tags[?Key==`CmBillingGroup`] | [0].Value,
    instance_id: InstanceId
    }' \
  | duckdb -csv -c "select * from read_json('/dev/stdin')"
)

# EC2インスタンスが存在するもののみ、instances-per-account フォルダに保存
if [ -n "$output" ]; then
  mkdir -p instances-per-account
  echo "$output" > "instances-per-account/${account_name}.csv"
fi

duckdb -csv -c "select * from read_csv('instances-per-account/*.csv')" > instances.csv

head instances.csv
# instance_name,instance_cmbillinggroup,instance_id
# app-test-bastion-01,NULL,i-0abc123def456789a
# app-dev-bastion-02,NULL,i-0def456789abcdef0
# web-prod-server01,WEB-PROD,i-a1b2c3d4
# web-prod-admin,WEB-PROD,i-e5f6g7h8
# shop-prod-web01,SHOP-PROD,i-12345abc
# shop-dev-web01,SHOP-DEV,i-67890def
# shop-prod-web03,SHOP-PROD,i-abcd1234
# shop-prod-web05,SHOP-PROD,i-5678efgh
# shop-prod-cms01,SHOP-PROD,i-ijkl9012

duckdb_sql=$(cat <<EOF
SELECT
    a.account_name,
    a.account_id,
    i.instance_name,
    i.instance_id,
    i.instance_cmbillinggroup AS cmbillinggroup,
    c.current_resource_summary AS current_size,
    c.recommended_resource_summary AS recommended_size,
    round(c.estimated_monthly_savings_after_discount,1) AS monthly_savings
FROM
    'coh.csv' c
JOIN
    'accounts.csv' a ON c.account_id = a.account_id
LEFT JOIN
    'instances.csv' i ON regexp_replace(c.resource_arn, '^arn:aws:.+:\d+:instance/', '') = i.instance_id
WHERE
    c.current_resource_type = 'Ec2Instance'
    AND c.action_type = 'Rightsize'
ORDER BY
    monthly_savings DESC;
EOF
)

duckdb -csv -c "${duckdb_sql}" >| coh_readable.csv

head coh_readable.csv
# account_name,account_id,instance_name,instance_id,cmbillinggroup,current_size,recommended_size,monthly_savings
# Account-A,111111111111,server-app-01,i-0abc123def456789a,APP-PROD,m5.4xlarge,r6i.xlarge,685.2
# Account-A,111111111111,server-db-01,i-0bcd234efg567890b,DB-PROD,m5.2xlarge,t3.large,562.8
# Account-A,111111111111,server-app-09,i-0cde345fgh678901c,APP-PROD,r5.4xlarge,c6i.2xlarge,510.4
# Account-B,222222222222,server-app-08,i-0def456ghi789012d,NULL,m5.8xlarge,r6i.2xlarge,483.7
# Account-B,222222222222,server-service-01,i-0efg567hij890123e,SVC-PROD,r5.4xlarge,m6i.xlarge,442.3
# Account-B,222222222222,server-cache-01,i-0fgh678ijk901234f,NULL,c5.2xlarge,t3.medium,345.9
# Account-C,333333333333,server-file-02,i-0ghi789jkl012345g,STORAGE,m5.2xlarge,t3a.large,319.5
# Account-C,333333333333,server-file-01,i-0hij890klm123456h,STORAGE,r5.2xlarge,c6a.large,332.6
# Account-C,333333333333,bi-prod-tableau,i-0ijk901lmn234567i,BI-PROD,m5.12xlarge,r6i.4xlarge,315.8