[AWS環境] 
↓ （AWS CLI / aws_resources.sh）
[JSONファイル群]
↓ （Python / aws_resources_to_excel.py）
[パラメータシート]
　（Excel / aws_resources.xlsx）

#!/bin/bash

#============================
# AWS リソース取得
# 取得対象: EC2 / VPC
#============================

# リージョン指定（デフォルトap-northeast-1）
REGION=${1:-ap-northeast-1}

# スクリプトのあるディレクトリ
SCRIPT_DIR=$(cd "$(dirname "$0")" && pwd)

# 出力ディレクトリ
OUTDIR="${SCRIPT_DIR}/output_$(date +%Y%m%d%H%M%S)"
mkdir -p "$OUTDIR"

echo "AWS リージョン: $REGION"
echo "出力ディレクトリ: $OUTDIR"

#################################
# 1. EC2
#################################
aws ec2 describe-instances --region "$REGION" --output json > "$OUTDIR/ec2_instances.json"

#################################
# 2. VPC
#################################
aws ec2 describe-vpcs --region "$REGION" --output json > "$OUTDIR/vpcs.json"
aws ec2 describe-subnets --region "$REGION" --output json > "$OUTDIR/subnets.json"
aws ec2 describe-route-tables --region "$REGION" --output json > "$OUTDIR/route_tables.json"
aws ec2 describe-security-groups --region "$REGION" --output json > "$OUTDIR/security_groups.json"

#################################
# ZIP 圧縮
#################################
ZIPFILE="${SCRIPT_DIR}/aws_resources_${REGION}_$(date +%Y%m%d%H%M%S).zip"
cd "$SCRIPT_DIR" || exit 1
zip -r "$ZIPFILE" "$(basename "$OUTDIR")" > /dev/null

echo "取得完了: $OUTDIR"
echo "ZIPファイル作成: $ZIPFILE"

import json
import pandas as pd
from pathlib import Path

def get_name_tag(tags):
"""Nameタグの値を取得"""
return next((t["Value"] for t in tags if t["Key"] == "Name"), "")

# ==== JSONファイル読み込み ====
with open("ec2_instances.json", "r", encoding="utf-8") as f:
ec2_data = json.load(f)
with open("vpcs.json", "r", encoding="utf-8") as f:
vpcs_data = json.load(f)
with open("subnets.json", "r", encoding="utf-8") as f:
subnets_data = json.load(f)
with open("route_tables.json", "r", encoding="utf-8") as f:
rtb_data = json.load(f)
with open("security_groups.json", "r", encoding="utf-8") as f:
sg_data = json.load(f)

# ==== EC2 Instances ====
ec2_records = []
for reservation in ec2_data["Reservations"]:
for instance in reservation["Instances"]:
name_tag = get_name_tag(instance.get("Tags", []))
subnet_id = instance.get("SubnetId", "")
sg_list = [sg["GroupId"] for sg in instance.get("SecurityGroups", [])]
sg_names = [sg["GroupName"] for sg in instance.get("SecurityGroups", [])]

ec2_records.append({
"InstanceId": instance.get("InstanceId", ""),
"Name": name_tag,
"InstanceType": instance.get("InstanceType", ""),
"AvailabilityZone": instance.get("Placement", {}).get("AvailabilityZone", ""),
"State": instance.get("State", {}).get("Name", ""),
"PrivateIpAddress": instance.get("PrivateIpAddress", ""),
"PublicIpAddress": instance.get("PublicIpAddress", ""),
"SubnetId": subnet_id,
"SecurityGroupIds": ", ".join(sg_list),
"SecurityGroupNames": ", ".join(sg_names)
})
df_ec2 = pd.DataFrame(ec2_records)

# ==== VPCs ====
vpc_records = []
for vpc in vpcs_data["Vpcs"]:
vpc_records.append({
"VpcId": vpc.get("VpcId", ""),
"Name": get_name_tag(vpc.get("Tags", [])),
"CidrBlock": vpc.get("CidrBlock", ""),
"State": vpc.get("State", ""),
"IsDefault": vpc.get("IsDefault", False)
})
df_vpcs = pd.DataFrame(vpc_records)

# ==== Subnets ====
subnet_records = []
for sn in subnets_data["Subnets"]:
subnet_records.append({
"SubnetId": sn.get("SubnetId", ""),
"Name": get_name_tag(sn.get("Tags", [])),
"VpcId": sn.get("VpcId", ""),
"CidrBlock": sn.get("CidrBlock", ""),
"AvailabilityZone": sn.get("AvailabilityZone", "")
})
df_subnets = pd.DataFrame(subnet_records)

# ==== Route Tables ====
rtb_records = []
for rtb in rtb_data["RouteTables"]:
routes_str = "; ".join(
[f"{r.get('DestinationCidrBlock', r.get('DestinationIpv6CidrBlock', ''))} -> {r.get('GatewayId', r.get('NatGatewayId', r.get('TransitGatewayId', '')))}"
for r in rtb.get("Routes", [])]
)
rtb_records.append({
"RouteTableId": rtb.get("RouteTableId", ""),
"Name": get_name_tag(rtb.get("Tags", [])),
"VpcId": rtb.get("VpcId", ""),
"Routes": routes_str
})
df_rtb = pd.DataFrame(rtb_records)

# ==== Security Groups ====
sg_records = []
for sg in sg_data["SecurityGroups"]:
ingress_rules = []
for perm in sg.get("IpPermissions", []):
proto = perm.get("IpProtocol", "")
if "FromPort" in perm and "ToPort" in perm:
ports = f"{perm.get('FromPort')} - {perm.get('ToPort')}"
else:
ports = "ALL"
cidrs = ",".join([r.get("CidrIp", "") for r in perm.get("IpRanges", [])])
ingress_rules.append(f"{proto} {ports} {cidrs}")

sg_records.append({
"GroupId": sg.get("GroupId", ""),
"Name": get_name_tag(sg.get("Tags", [])),
"GroupName": sg.get("GroupName", ""),
"VpcId": sg.get("VpcId", ""),
"Description": sg.get("Description", ""),
"Ingress": "; ".join(ingress_rules)
})
df_sg = pd.DataFrame(sg_records)

# ==== Excel 出力 ====
out_path = Path("aws_resources.xlsx")
with pd.ExcelWriter(out_path, engine="openpyxl") as writer:
df_ec2.to_excel(writer, sheet_name="EC2 Instances", index=False)
df_vpcs.to_excel(writer, sheet_name="VPCs", index=False)
df_subnets.to_excel(writer, sheet_name="Subnets", index=False)
df_rtb.to_excel(writer, sheet_name="RouteTables", index=False)
df_sg.to_excel(writer, sheet_name="SecurityGroups", index=False)

print(f"Excelファイルを出力しました: {out_path.resolve()}")