組織全体のスナップショットの情報を取得して削除優先度をスコアリングしてみた

CREATE EXTERNAL TABLE IF NOT EXISTS `<CUR_DATABASE>`.`<CUR_TABLE>` (
  `line_item_usage_account_id` string,
  `line_item_resource_id`      string,
  `line_item_product_code`     string,
  `line_item_usage_type`       string,
  `line_item_usage_start_date` timestamp,
  `line_item_unblended_cost`   double
)
PARTITIONED BY (`billing_period` string)
STORED AS PARQUET
LOCATION 's3://<CUR_BUCKET>/<CUR_PREFIX>/<CUR_EXPORT_NAME>/data/'
TBLPROPERTIES (
  'projection.enabled'                  = 'true',
  'projection.billing_period.type'      = 'date',
  'projection.billing_period.format'    = 'yyyy-MM',
  'projection.billing_period.range'     = '2025-01,NOW',
  'projection.billing_period.interval'         = '1',
  'projection.billing_period.interval.unit'    = 'MONTHS',
  'storage.location.template' = 's3://<CUR_BUCKET>/<CUR_PREFIX>/<CUR_EXPORT_NAME>/data/BILLING_PERIOD=${billing_period}/'
);

"""
EBS Snapshot Priority Scorer - アカウント横断版（CURベース）

Audit アカウントの Lambda として動作し、以下を実行する:

  1. ログアーカイブアカウントの CUR を Athena でクエリし、
     代表日1日分のスナップショット別コストを全アカウント分取得する（下限フィルタなし）。
  2. 対象を (account_id, region) でグループ化し、各メンバーアカウントへ
     AssumeRole して ec2:DescribeSnapshots/Volumes/Images を実行、
     チェーン構造・ソースボリューム削除有無・AMI参照・取得日を分類する。
  3. CUR コスト × メタデータを Join し、チェーン（=ソースボリューム）単位で
     「合計年間コスト >= MIN_ANNUAL_COST」かつ「最新スナップが MIN_AGE_DAYS 以上前」
     のチェーンを対象に、全メンバーを出力する。各メンバーをティアでスコアリングする。
  4. 1スナップショット=1行の CSV を S3 に出力する。
     QuickSight 読み込み用に latest.csv（固定）、履歴用に archives/result-{scan_date}.csv。

コスト計測に EBS Direct API / KMS 復号は使用しない（CUR で代替）。
"""

import csv
import io
import logging
import os
import time
from collections import defaultdict
from datetime import datetime, timedelta, timezone

import boto3

logger = logging.getLogger()
logger.setLevel("INFO")

# --- 定数 ---
DAYS_PER_YEAR = 365
DAYS_PER_MONTH = 30

# --- スコアリング閾値（月額ベース）---
COST_TIERS = [
    (50.0, 6),
    (20.0, 5),
    (10.0, 4),
    (5.0, 3),
    (1.0, 2),
    (0.50, 1),
    (0.0, 0),
]

AGE_TIERS = [
    (1095, 3),  # 3年
    (365, 2),   # 1年
    (180, 1),   # 半年
    (0, 0),
]

# 環境変数
LOG_ARCHIVE_ACCOUNT_ID = os.environ["LOG_ARCHIVE_ACCOUNT_ID"]
ATHENA_ROLE_NAME = os.environ["ATHENA_ROLE_NAME"]
MEMBER_ROLE_NAME = os.environ["MEMBER_ROLE_NAME"]
CUR_DATABASE = os.environ["CUR_DATABASE"]
CUR_TABLE = os.environ["CUR_TABLE"]
ATHENA_OUTPUT_S3 = os.environ["ATHENA_OUTPUT_S3"]
ATHENA_WORKGROUP = os.getenv("ATHENA_WORKGROUP", "primary")
OUTPUT_BUCKET = os.environ["OUTPUT_BUCKET"]
OUTPUT_PREFIX = os.getenv("OUTPUT_PREFIX", "ebs-snapshot-score")
MIN_AGE_DAYS = int(os.getenv("MIN_AGE_DAYS", "180"))
MIN_ANNUAL_COST = float(os.getenv("MIN_ANNUAL_COST", "60"))
REPRESENTATIVE_DAY_OFFSET = int(os.getenv("REPRESENTATIVE_DAY_OFFSET", "2"))
DENY_LIST_ACCOUNTS = {
    a.strip() for a in os.getenv("DENY_LIST_ACCOUNTS", "").split(",") if a.strip()
}

# scan_date は S3 パス（scan_date=YYYY-MM-DD/）のパーティションとして持たせるため
# CSV 本体の列には含めない（Glue のパーティションキーと重複させない）
CSV_FIELDS = [
    "account_id",
    "account_name",
    "region",
    "snapshot_id",
    "volume_id",
    "source_volume_deleted",
    "ami_referenced",
    "ami_id",
    "ami_name",
    "chain_type",
    "chain_total",
    "creation_date",
    "age_days",
    "storage_tier",
    "encrypted",
    "name_tag",
    "snapshot_description",
    "daily_cost_usd",
    "monthly_cost_usd",
    "annual_cost_usd",
    "chain_annual_cost_usd",
    "cost_score",
    "age_score",
    "vol_deleted_bonus",
    "score",
    "action_label",
    "est_annual_savings_usd",
    "delete_recommendation",
]

# ============================================================
# スコアリング・分類
# ============================================================

def get_cost_tier(monthly_usd):
    for threshold, score in COST_TIERS:
        if monthly_usd >= threshold:
            return score
    return 0

def get_age_tier(age_days):
    for threshold, score in AGE_TIERS:
        if age_days >= threshold:
            return score
    return 0

def score_label(score):
    if score >= 8:
        return "要対応"
    elif score >= 5:
        return "確認推奨"
    elif score >= 1:
        return "低優先"
    else:
        return "対象外"

def estimate_annual_savings(annual_cost, chain_type, volume_deleted):
    """削除した場合の実効削減見込み（年額 USD）。

    EBS スナップショットは増分課金のため、消してもコストが減らない位置がある。
      - ソースボリューム削除済み: チェーン全削除が前提 → 全額削減
      - sole / last（最新）: 固有ブロックが解放される → 全額削減
      - first / middle: 後続から参照されるブロックは解放されないため保守的に 0 とする
    """
    if volume_deleted:
        return annual_cost
    if chain_type in ("sole", "last"):
        return annual_cost
    return 0.0

def delete_recommendation(chain_type, volume_deleted, ami_referenced):
    """削除可否・推奨アクションのラベルを返す。"""
    if volume_deleted:
        base = "チェーン削除推奨" if chain_type != "sole" else "単独削除可(Vol削除済)"
    elif chain_type == "sole":
        base = "単独削除可"
    elif chain_type == "last":
        base = "最新・要確認"
    else:  # first / middle
        return "削減効果は限定的"
    if ami_referenced:
        return f"{base}(先にAMI登録解除)"
    return base

def get_tag_value(tags, key):
    if not tags:
        return ""
    for tag in tags:
        if tag.get("Key") == key:
            return tag.get("Value", "")
    return ""

def get_ami_snapshot_map(ec2_client):
    """自アカウントの全AMIを取得し、スナップショットID → (AMI ID, AMI名) のマップを返す。"""
    snap_to_ami = {}
    paginator = ec2_client.get_paginator("describe_images")
    for page in paginator.paginate(Owners=["self"]):
        for image in page["Images"]:
            ami_id = image["ImageId"]
            ami_name = image.get("Name", "")
            for bdm in image.get("BlockDeviceMappings", []):
                ebs = bdm.get("Ebs", {})
                snap_id = ebs.get("SnapshotId")
                if snap_id:
                    snap_to_ami[snap_id] = (ami_id, ami_name)
    return snap_to_ami

def classify_snapshots(snapshots, existing_volume_ids, ami_snapshot_map):
    """スナップショットを chain_type / volume_deleted / ami_referenced で分類する。"""
    UNKNOWN_VOLUME_IDS = {"vol-ffffffff", ""}
    volume_groups = defaultdict(list)
    for snap in snapshots:
        vol_id = snap.get("VolumeId", "")
        if vol_id in UNKNOWN_VOLUME_IDS:
            volume_groups[f"{vol_id}_{snap['SnapshotId']}"].append(snap)
        else:
            volume_groups[vol_id].append(snap)

    for vol_id in volume_groups:
        volume_groups[vol_id].sort(key=lambda s: s["StartTime"])

    classified = {}
    for vol_id, snaps in volume_groups.items():
        volume_deleted = vol_id not in existing_volume_ids
        is_sole = len(snaps) == 1

        for i, snap in enumerate(snaps):
            if is_sole:
                chain_type = "sole"
            elif i == 0:
                chain_type = "first"
            elif i == len(snaps) - 1:
                chain_type = "last"
            else:
                chain_type = "middle"

            snap_id = snap["SnapshotId"]
            ami_info = ami_snapshot_map.get(snap_id)
            ami_id, ami_name = ami_info if ami_info else (None, "")
            classified[snap_id] = {
                "snapshot_id": snap_id,
                "volume_id": snap.get("VolumeId", ""),
                # チェーンのグループ化キー（volume_deleted/未知ボリュームでも一意に対応）
                "chain_key": vol_id,
                "volume_size_gib": snap.get("VolumeSize", 0),
                "start_time": snap["StartTime"],
                "state": snap.get("State", ""),
                "storage_tier": snap.get("StorageTier", "standard"),
                "name": get_tag_value(snap.get("Tags"), "Name"),
                "description": snap.get("Description", ""),
                "encrypted": snap.get("Encrypted", False),
                "chain_type": chain_type,
                "chain_total": len(snaps),
                "volume_deleted": volume_deleted,
                "ami_referenced": ami_id is not None,
                "ami_id": ami_id or "",
                "ami_name": ami_name,
            }
    return classified

# ============================================================
# クロスアカウント・組織列挙
# ============================================================

def assume_role(account_id, role_name, session_name):
    sts = boto3.client("sts")
    role_arn = f"arn:aws:iam::{account_id}:role/{role_name}"
    assumed = sts.assume_role(RoleArn=role_arn, RoleSessionName=session_name)
    c = assumed["Credentials"]
    return boto3.Session(
        aws_access_key_id=c["AccessKeyId"],
        aws_secret_access_key=c["SecretAccessKey"],
        aws_session_token=c["SessionToken"],
    )

def get_organization_accounts():
    """委任管理者アカウントで Organizations の全アクティブアカウントを取得"""
    org_client = boto3.client("organizations")
    accounts = {}
    paginator = org_client.get_paginator("list_accounts")
    for page in paginator.paginate():
        for acct in page["Accounts"]:
            if acct["Status"] == "ACTIVE":
                accounts[acct["Id"]] = acct["Name"]
    return accounts

# ============================================================
# Phase 1: CUR を Athena でクエリ
# ============================================================

def representative_date():
    """CUR反映済みの確定日（実行日の REPRESENTATIVE_DAY_OFFSET 日前）"""
    return (datetime.now(timezone.utc) - timedelta(days=REPRESENTATIVE_DAY_OFFSET)).date()

def build_cur_query(rep_date):
    """代表日1日分の全スナップショット別日次コストを取得するクエリ。

    billing_period（CUR 2.0 のパーティション = YYYY-MM）で代表日の月に絞り込み、
    line_item_usage_start_date が代表日に該当する行のみを対象とする。
    コスト下限の絞り込みはここでは行わない。チェーン（=ソースボリューム）単位の
    合計コストで判定するため、ボリューム所属が判明する Describe 後に
    build_records 側で MIN_ANNUAL_COST 判定する。
    """
    date_str = rep_date.isoformat()
    billing_period = rep_date.strftime("%Y-%m")
    return f"""
SELECT line_item_usage_account_id AS account_id,
       line_item_resource_id      AS snapshot_arn,
       SUM(line_item_unblended_cost) AS daily_cost
FROM   "{CUR_DATABASE}"."{CUR_TABLE}"
WHERE  billing_period = '{billing_period}'
  AND  line_item_product_code = 'AmazonEC2'
  AND  line_item_usage_type LIKE '%EBS:SnapshotUsage%'
  AND  line_item_resource_id LIKE '%snapshot/snap-%'
  AND  date(line_item_usage_start_date) = date('{date_str}')
GROUP BY 1, 2
""".strip()

def run_athena_query(athena_client, query):
    """Athena クエリを実行し、全結果行を dict のリストで返す"""
    start = athena_client.start_query_execution(
        QueryString=query,
        QueryExecutionContext={"Database": CUR_DATABASE},
        ResultConfiguration={"OutputLocation": ATHENA_OUTPUT_S3},
        WorkGroup=ATHENA_WORKGROUP,
    )
    qid = start["QueryExecutionId"]

    while True:
        resp = athena_client.get_query_execution(QueryExecutionId=qid)
        status = resp["QueryExecution"]["Status"]["State"]
        if status in ("SUCCEEDED", "FAILED", "CANCELLED"):
            break
        time.sleep(2)

    if status != "SUCCEEDED":
        reason = resp["QueryExecution"]["Status"].get("StateChangeReason", "")
        raise RuntimeError(f"Athena query {status}: {reason}")

    rows = []
    paginator = athena_client.get_paginator("get_query_results")
    header = None
    for page in paginator.paginate(QueryExecutionId=qid):
        for r in page["ResultSet"]["Rows"]:
            values = [c.get("VarCharValue", "") for c in r["Data"]]
            if header is None:
                header = values
                continue
            rows.append(dict(zip(header, values)))
    return rows

def parse_snapshot_arn(arn):
    """arn:aws:ec2:REGION:ACCOUNT:snapshot/snap-xxx → (region, snapshot_id)"""
    try:
        parts = arn.split(":")
        region = parts[3]
        snap_id = parts[5].split("/")[-1]
        return region, snap_id
    except (IndexError, AttributeError):
        return None, None

def fetch_cur_costs(rep_date):
    """ログアーカイブの CUR をクエリし、(account_id, region) -> {snap_id: daily_cost} を返す"""
    session = assume_role(LOG_ARCHIVE_ACCOUNT_ID, ATHENA_ROLE_NAME, "EbsScorerAthena")
    athena = session.client("athena")
    query = build_cur_query(rep_date)
    logger.info("Running CUR query for %s", rep_date.isoformat())
    rows = run_athena_query(athena, query)
    logger.info("CUR returned %d qualifying snapshot rows", len(rows))

    grouped = defaultdict(dict)  # (account_id, region) -> {snap_id: daily_cost}
    for row in rows:
        account_id = row["account_id"]
        if account_id in DENY_LIST_ACCOUNTS:
            continue
        region, snap_id = parse_snapshot_arn(row["snapshot_arn"])
        if not region or not snap_id:
            continue
        grouped[(account_id, region)][snap_id] = float(row["daily_cost"])
    return grouped

# ============================================================
# Phase 2: メンバーアカウントのメタデータ取得
# ============================================================

def fetch_account_metadata(session, region):
    """指定リージョンの全スナップショットを分類した dict を返す。

    チェーン単位で判定するため、CUR の有無に関わらず全メンバーを返す
    （チェーンのコスト合計判定・全メンバー出力に必要）。
    """
    ec2 = session.client("ec2", region_name=region)

    snapshots = []
    paginator = ec2.get_paginator("describe_snapshots")
    for page in paginator.paginate(OwnerIds=["self"]):
        snapshots.extend(page["Snapshots"])

    existing_volume_ids = set()
    vol_paginator = ec2.get_paginator("describe_volumes")
    for page in vol_paginator.paginate():
        for vol in page["Volumes"]:
            existing_volume_ids.add(vol["VolumeId"])

    ami_snapshot_map = get_ami_snapshot_map(ec2)

    return classify_snapshots(snapshots, existing_volume_ids, ami_snapshot_map)

# ============================================================
# Phase 3: Join + チェーン単位フィルタ + スコアリング
# ============================================================

def build_records(grouped_costs, accounts):
    """CUR コスト × メタデータ を Join し、チェーン単位で絞り込んだレコードを返す。

    フィルタ単位はチェーン（=ソースボリューム）:
      - チェーン合計の年間コスト >= MIN_ANNUAL_COST
      - チェーン内の最新スナップショットが MIN_AGE_DAYS 以上前
        （最近のスナップがある = ライフサイクル稼働中とみなして対象外）
    条件を満たしたチェーンは全メンバーを出力する（chain_total と出力行数が一致）。
    """
    now = datetime.now(timezone.utc)
    records = []

    for (account_id, region), snap_costs in grouped_costs.items():
        account_name = accounts.get(account_id, "")
        try:
            session = assume_role(account_id, MEMBER_ROLE_NAME, "EbsScorerDescribe")
            metadata = fetch_account_metadata(session, region)
        except Exception as e:
            logger.error("Failed metadata for %s/%s: %s", account_id, region, e)
            continue

        # チェーン（ソースボリューム）単位にまとめる
        chains = defaultdict(list)
        for meta in metadata.values():
            chains[meta["chain_key"]].append(meta)

        for members in chains.values():
            chain_daily = sum(
                snap_costs.get(m["snapshot_id"], 0.0) for m in members
            )
            chain_annual = chain_daily * DAYS_PER_YEAR
            if chain_annual < MIN_ANNUAL_COST:
                continue

            # チェーン内最新スナップの経過日数（最近のものがあれば稼働中→対象外）
            newest_age = min(
                max((now - m["start_time"]).days, 1) for m in members
            )
            if newest_age < MIN_AGE_DAYS:
                continue

            chain_annual_rounded = round(chain_annual, 2)
            for meta in members:
                daily_cost = snap_costs.get(meta["snapshot_id"], 0.0)
                age_days = max((now - meta["start_time"]).days, 1)
                monthly_cost = daily_cost * DAYS_PER_MONTH
                annual_cost = daily_cost * DAYS_PER_YEAR

                cost_score = get_cost_tier(monthly_cost)
                age_score = get_age_tier(age_days)
                vol_deleted_bonus = 1 if meta["volume_deleted"] else 0
                score = cost_score + age_score + vol_deleted_bonus

                est_savings = estimate_annual_savings(
                    annual_cost, meta["chain_type"], meta["volume_deleted"]
                )
                recommendation = delete_recommendation(
                    meta["chain_type"], meta["volume_deleted"], meta["ami_referenced"]
                )

                records.append({
                    "account_id": account_id,
                    "account_name": account_name,
                    "region": region,
                    "snapshot_id": meta["snapshot_id"],
                    "volume_id": meta["volume_id"],
                    "source_volume_deleted": meta["volume_deleted"],
                    "ami_referenced": meta["ami_referenced"],
                    "ami_id": meta["ami_id"],
                    "ami_name": meta["ami_name"],
                    "chain_type": meta["chain_type"],
                    "chain_total": meta["chain_total"],
                    "creation_date": meta["start_time"].strftime("%Y-%m-%d"),
                    "age_days": age_days,
                    "storage_tier": meta["storage_tier"],
                    "encrypted": meta["encrypted"],
                    "name_tag": meta["name"],
                    "snapshot_description": meta["description"],
                    "daily_cost_usd": round(daily_cost, 4),
                    "monthly_cost_usd": round(monthly_cost, 2),
                    "annual_cost_usd": round(annual_cost, 2),
                    "chain_annual_cost_usd": chain_annual_rounded,
                    "cost_score": cost_score,
                    "age_score": age_score,
                    "vol_deleted_bonus": vol_deleted_bonus,
                    "score": score,
                    "action_label": score_label(score),
                    "est_annual_savings_usd": round(est_savings, 2),
                    "delete_recommendation": recommendation,
                })

    # チェーン合計額の降順。同一チェーンは取得日昇順でまとまるように並べる
    records.sort(
        key=lambda r: (
            -r["chain_annual_cost_usd"],
            r["account_id"],
            r["volume_id"],
            r["creation_date"],
        )
    )
    return records

# ============================================================
# Phase 4: CSV 出力
# ============================================================

def write_csv_to_s3(records, scan_date):
    buf = io.StringIO()
    writer = csv.DictWriter(buf, fieldnames=CSV_FIELDS)
    writer.writeheader()
    for r in records:
        writer.writerow(r)
    body = buf.getvalue().encode("utf-8")

    s3 = boto3.client("s3")
    # QuickSight が常に読み込む最新ファイル（固定パス）
    latest_key = f"{OUTPUT_PREFIX}/latest.csv"
    # 実行履歴（scan_date ごとに残す）
    archive_key = f"{OUTPUT_PREFIX}/archives/result-{scan_date}.csv"
    for key in (latest_key, archive_key):
        s3.put_object(
            Bucket=OUTPUT_BUCKET,
            Key=key,
            Body=body,
            ContentType="text/csv; charset=utf-8",
        )

    latest_uri = f"s3://{OUTPUT_BUCKET}/{latest_key}"
    archive_uri = f"s3://{OUTPUT_BUCKET}/{archive_key}"
    logger.info("Wrote %d records to %s and %s", len(records), latest_uri, archive_uri)
    return latest_uri

# ============================================================
# Lambda ハンドラ
# ============================================================

def lambda_handler(event, context):
    rep_date = representative_date()
    scan_date = datetime.now(timezone.utc).date().isoformat()

    try:
        accounts = get_organization_accounts()
        logger.info("Found %d active accounts", len(accounts))

        grouped_costs = fetch_cur_costs(rep_date)
        target_account_count = len({a for (a, _r) in grouped_costs.keys()})
        logger.info(
            "Qualifying: %d (account,region) groups across %d accounts",
            len(grouped_costs), target_account_count,
        )

        records = build_records(grouped_costs, accounts)
        s3_uri = write_csv_to_s3(records, scan_date)

        return {
            "statusCode": 200,
            "body": {
                "scan_date": scan_date,
                "representative_date": rep_date.isoformat(),
                "qualifying_groups": len(grouped_costs),
                "target_accounts": target_account_count,
                "output": s3_uri,
                "output_records": len(records),
            },
        }
    except Exception as e:
        logger.error("Error in main process: %s", e)
        return {"statusCode": 500, "body": str(e)}

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "ListOrganizationAccounts",
            "Effect": "Allow",
            "Action": [
                "organizations:ListAccounts"
            ],
            "Resource": "*"
        },
        {
            "Sid": "AssumeAthenaRoleInLogArchive",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::<LOG_ARCHIVE_ACCOUNT_ID>:role/<ATHENA_ROLE_NAME>"
        },
        {
            "Sid": "AssumeReadRoleInMemberAccounts",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::*:role/<MEMBER_ROLE_NAME>"
        },
        {
            "Sid": "WriteResultCsv",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject"
            ],
            "Resource": "arn:aws:s3:::<OUTPUT_BUCKET>/<OUTPUT_PREFIX>/*"
        },
        {
            "Sid": "CloudWatchLogs",
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": "arn:aws:logs:*:*:*"
        }
    ]
}

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "AthenaQueryExecution",
			"Effect": "Allow",
			"Action": [
				"athena:StartQueryExecution",
				"athena:GetQueryExecution",
				"athena:GetQueryResults",
				"athena:StopQueryExecution",
				"athena:GetWorkGroup"
			],
			"Resource": [
				"arn:aws:athena:ap-northeast-1:<LOG_ARCHIVE_ACCOUNT_ID>:workgroup/<ATHENA_WORKGROUP>"
			]
		},
		{
			"Sid": "GlueCatalogAccess",
			"Effect": "Allow",
			"Action": [
				"glue:GetDatabase",
				"glue:GetTable",
				"glue:GetPartitions"
			],
			"Resource": [
				"arn:aws:glue:ap-northeast-1:<LOG_ARCHIVE_ACCOUNT_ID>:catalog",
				"arn:aws:glue:ap-northeast-1:<LOG_ARCHIVE_ACCOUNT_ID>:database/<CUR_DATABASE>",
				"arn:aws:glue:ap-northeast-1:<LOG_ARCHIVE_ACCOUNT_ID>:table/<CUR_DATABASE>/<CUR_TABLE>"
			]
		},
		{
			"Sid": "S3CurReadAccess",
			"Effect": "Allow",
			"Action": [
				"s3:GetObject",
				"s3:ListBucket"
			],
			"Resource": [
				"arn:aws:s3:::<CUR_BUCKET>",
				"arn:aws:s3:::<CUR_BUCKET>/*"
			]
		},
		{
			"Sid": "S3AthenaOutputAccess",
			"Effect": "Allow",
			"Action": [
				"s3:GetBucketLocation",
				"s3:GetObject",
				"s3:ListBucket",
				"s3:PutObject"
			],
			"Resource": [
				"arn:aws:s3:::<ATHENA_RESULT_BUCKET>",
				"arn:aws:s3:::<ATHENA_RESULT_BUCKET>/*"
			]
		}
	]
}

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": {
				"AWS": "<Lambda実行用ロールARN>"
			},
			"Action": "sts:AssumeRole"
		}
	]
}

account_id,account_name,region,snapshot_id,volume_id,source_volume_deleted,ami_referenced,ami_id,ami_name,chain_type,chain_total,creation_date,age_days,storage_tier,encrypted,name_tag,snapshot_description,daily_cost_usd,monthly_cost_usd,annual_cost_usd,chain_annual_cost_usd,cost_score,age_score,vol_deleted_bonus,score,action_label,est_annual_savings_usd,delete_recommendation
111122223333,example-prod,ap-northeast-1,snap-0a11111111111111,vol-0aaaaaaaaaaaaaaaa,True,False,,,first,4,2019-03-14,2727,standard,True,,,0.482,14.46,175.93,598.96,4,3,1,8,要対応,175.93,チェーン削除推奨
111122223333,example-prod,ap-northeast-1,snap-0a22222222222222,vol-0aaaaaaaaaaaaaaaa,True,False,,,middle,4,2019-08-02,2586,standard,True,,,0.391,11.73,142.72,598.96,4,3,1,8,要対応,142.72,チェーン削除推奨
111122223333,example-prod,ap-northeast-1,snap-0a33333333333333,vol-0aaaaaaaaaaaaaaaa,True,False,,,middle,4,2020-01-21,2414,standard,True,,,0.355,10.65,129.57,598.96,4,3,1,8,要対応,129.57,チェーン削除推奨
111122223333,example-prod,ap-northeast-1,snap-0a44444444444444,vol-0aaaaaaaaaaaaaaaa,True,False,,,last,4,2020-06-09,2276,standard,True,,,0.413,12.39,150.75,598.96,4,3,1,8,要対応,150.75,チェーン削除推奨
111122223333,example-prod,ap-northeast-1,snap-0b11111111111111,vol-0bbbbbbbbbbbbbbbb,True,True,ami-0b11111111111111,example-app-base-image,sole,1,2021-05-18,1930,standard,True,,,0.241,7.23,87.97,87.97,3,3,1,7,確認推奨,87.97,単独削除可(Vol削除済)(先にAMI登録解除)
444455556666,example-stg,ap-northeast-1,snap-0c11111111111111,vol-0cccccccccccccccc,False,False,,,first,3,2022-02-07,1665,standard,True,,,0.198,5.94,72.27,150.75,3,3,0,6,確認推奨,0.0,削減効果は限定的
444455556666,example-stg,ap-northeast-1,snap-0c22222222222222,vol-0cccccccccccccccc,False,False,,,middle,3,2022-09-15,1475,standard,True,,,0.124,3.72,45.26,150.75,2,3,0,5,確認推奨,0.0,削減効果は限定的
444455556666,example-stg,ap-northeast-1,snap-0c33333333333333,vol-0cccccccccccccccc,False,False,,,last,3,2023-04-26,1252,standard,True,,,0.091,2.73,33.21,150.75,2,3,0,5,確認推奨,33.21,最新・要確認

cron(0 1 1 * ? *)