一覧の見方
AWS プラグインのテーブルは580を超えるので、ここでは実務でよく使うものを目的別に絞って載せる。表の「メモ」には、キー列(AWS 側で絞り込まれる列、PART 03)や、使うときの注意を書いた。権限は、ReadOnlyAccess があればほぼすべて動く(コスト系を除く)。
- 表にないものは、対話モードの
.inspect awsか、Hub のテーブル一覧で探す - boto3 で同じ情報を取る方法は、boto3 シリーズの付録に API 名と IAM アクションの一覧がある
- グローバルなサービス(IAM・S3 のバケット一覧・Route 53 など)は、
regionsの設定に関係なく1回の取得で全件が返る
リソースの棚卸し
| サービス | テーブル | 取れる情報 | メモ |
|---|---|---|---|
| EC2 | aws_ec2_instance | 種類・状態・IP・タグ・起動日時・停止日時 | キー列:instance_state・instance_type など |
| EBS | aws_ebs_volume / aws_ebs_snapshot | サイズ・種類・アタッチ先・暗号化、スナップショット | スナップショットは自アカウント所有のものが対象 |
| AMI | aws_ec2_ami | 自アカウントで作った AMI | |
| VPC | aws_vpc / aws_vpc_subnet / aws_vpc_security_group_rule | VPC・サブネット・セキュリティグループのルール(1ルール1行) | ルールは aws_vpc_security_group_rule のほうが絞り込みやすい |
| Elastic IP | aws_vpc_eip | 割り当て済みの IP と関連付け先 | |
| ELB | aws_ec2_application_load_balancer / aws_ec2_network_load_balancer / aws_ec2_target_group | ALB・NLB・ターゲットグループ | |
| RDS | aws_rds_db_instance / aws_rds_db_cluster | クラス・エンジン・状態・マルチAZ・暗号化 | Aurora はクラスター単位も見る |
| DynamoDB | aws_dynamodb_table | 状態・容量モード・項目数(概算) | |
| Lambda | aws_lambda_function | ランタイム・メモリ・タイムアウト・状態 | tags などは関数ごとに追加の API |
| ECS / EKS | aws_ecs_cluster / aws_ecs_service / aws_eks_cluster | クラスター・サービス・タスク数・バージョン | |
| S3 | aws_s3_bucket | バケット名・リージョン・暗号化・バージョニング・パブリックアクセスのブロック | 列ごとにバケット単位の API が呼ばれる。列は絞る |
| Route 53 | aws_route53_zone / aws_route53_record | ホストゾーンとレコード | グローバル |
| CloudFront | aws_cloudfront_distribution | ディストリビューションとオリジン | グローバル |
| ACM | aws_acm_certificate | ドメイン・状態・有効期限・使用中のリソース | CloudFront 用は us-east-1 |
| Systems Manager | aws_ssm_managed_instance | SSM に登録されたマシンとエージェントの状態 | キー列:ping_status など |
監視・ログ
| サービス | テーブル | 取れる情報 | メモ |
|---|---|---|---|
| CloudWatch アラーム | aws_cloudwatch_alarm | 状態・しきい値・通知先 | キー列:state_value |
| メトリクス(専用) | aws_ec2_instance_metric_cpu_utilization_daily など | 代表的なメトリクスの5分・1時間・1日の統計 | 期間は固定(PART 05) |
| メトリクス(汎用) | aws_cloudwatch_metric_statistic_data_point / aws_cloudwatch_metric_data_point | 任意のメトリクス | 名前空間などの指定が必須 |
| CloudWatch Logs | aws_cloudwatch_log_group | 保持期間・保存量・暗号化 | |
| CloudTrail(証跡) | aws_cloudtrail_trail | 証跡の設定(マルチリージョン・ログの検証など) | |
| CloudTrail(イベント) | aws_cloudtrail_lookup_event | 直近90日の管理イベント | 検索属性の条件は1つまで(後述) |
| Health | aws_health_event | AWS 側の障害・メンテナンスの予定 | ビジネス以上のサポートプランが必要 |
コスト
| テーブル | 取れる情報 | メモ |
|---|---|---|
aws_cost_by_service_monthly / _daily | サービス別のコスト | Cost Explorer API は1リクエスト 0.01 USD |
aws_cost_by_account_monthly | アカウント別のコスト | 管理アカウントで使う |
aws_cost_by_service_usage_type_monthly | サービス×使用タイプ別 | 増えた原因の深掘りに |
aws_cost_usage | 任意の2軸で集計 | granularity などの指定が必須 |
aws_cost_forecast_monthly | コストの予測 | ce:GetCostForecast |
aws_costoptimizationhub_recommendation | Cost Optimization Hub の推奨事項 | 事前にサービスの有効化が必要 |
セキュリティ・監査
| サービス | テーブル | 取れる情報 | メモ |
|---|---|---|---|
| IAM ユーザー | aws_iam_user | MFA・最終ログイン・所属グループ・付与ポリシー | |
| アクセスキー | aws_iam_access_key | 状態・作成日・最終使用日 | キー列:user_name |
| 認証情報レポート | aws_iam_credential_report | 全ユーザーのパスワード・キー・MFA の状況を1表で | 事前に aws iam generate-credential-report が必要 |
| IAM ロール・ポリシー | aws_iam_role / aws_iam_policy | 信頼ポリシー・付与ポリシー・最終使用 | select * は遅い(PART 03) |
| パスワードポリシー | aws_iam_account_password_policy | 最小文字数・再利用の禁止など | |
| Access Analyzer | aws_accessanalyzer_analyzer | アナライザーの有無と検出結果 | |
| GuardDuty | aws_guardduty_finding | 検出結果と重要度 | |
| Security Hub | aws_securityhub_finding | 検出結果 | 件数が多いので条件を付ける |
| S3(アカウント) | aws_s3_account_settings | アカウント全体のパブリックアクセスのブロック | |
| KMS / Secrets Manager | aws_kms_key / aws_secretsmanager_secret | キーのローテーション、シークレットの最終更新 |
構成・アカウント管理
| テーブル | 取れる情報 | メモ |
|---|---|---|
aws_sts_caller_identity | 今の認証情報のアカウント・ARN | 接続確認に(PART 02) |
aws_account | アカウントのエイリアス・所属する組織 | |
aws_organizations_account | 組織内のアカウント一覧 | 管理アカウントで使う(PART 06) |
aws_region | リージョンとオプトインの状態 | |
aws_servicequotas_service_quota | サービスクォータの値 | |
aws_ec2_instance_type | インスタンスタイプの vCPU・メモリなど | 棚卸しの表に JOIN して使う |
aws_tagging_resource | タグが付いたリソースを横断して一覧 | 後述 |
aws_resource_explorer_search | Resource Explorer の検索結果 | 集約インデックスの作成が必要。1回1,000件まで |
逆引き:こういうことを知りたい
ここからは、boto3 版の逆引きと同じお題を SQL で書く。どれも regions = ["*"](PART 04)にしておけば全リージョンが対象になり、aggregator(PART 06)を使えば全アカウントが対象になる。
停止したまま放置されているEC2
停止中のインスタンスも EBS の料金はかかり続ける。boto3 版では StateTransitionReason の文字列を表示して目で見たが、Steampipe の state_transition_time 列は、その文字列から停止した日時を取り出して日時の型にしてくれている。そのまま日数の計算や比較に使える。
select
account_id,
region,
instance_id,
title,
instance_type,
to_char(state_transition_time at time zone 'Asia/Tokyo', 'YYYY-MM-DD') as stopped_on,
date_part('day', now() - state_transition_time) as days
from
aws_ec2_instance
where
instance_state = 'stopped'
and state_transition_time < now() - interval '30 days'
order by
state_transition_time;アタッチされていないEBS・使われていないElastic IP
EBS の状態 available は、どのインスタンスにも付いていないことを表す。Elastic IP は関連付け(association_id)がないものが未使用だ。種類の違う2つを union all で1つの表にする。
select
'EBS' as kind, account_id, region,
volume_id as id,
size || 'GiB ' || volume_type as detail,
to_char(create_time at time zone 'Asia/Tokyo', 'YYYY-MM-DD') as created
from aws_ebs_volume
where state = 'available'
union all
select
'EIP', account_id, region,
allocation_id,
host(public_ip),
null
from aws_vpc_eip
where association_id is null
order by kind, region;public_ip は PostgreSQL の IP アドレス型(inet)なので、host() で文字列にしてから他の列とそろえている。
90日以上使われていないアクセスキー
一度も使われていないキーは、最終使用日が空になる。その場合は作成日で判断するよう、coalesce で補う。
select
account_id,
user_name,
access_key_id,
to_char(create_date, 'YYYY-MM-DD') as created,
coalesce(to_char(access_key_last_used_date, 'YYYY-MM-DD'), '未使用') as last_used,
access_key_last_used_service as last_service
from
aws_iam_access_key
where
status = 'Active'
and coalesce(access_key_last_used_date, create_date) < now() - interval '90 days'
order by
coalesce(access_key_last_used_date, create_date);保持期間が「無期限」のロググループ
保持期間を設定していないロググループは retention_in_days が NULL になる。保存量の大きい順に並べると、料金への影響が大きいものから対処できる。
select
account_id,
region,
name,
round(stored_bytes / 1024.0 / 1024 / 1024, 2) as stored_gib
from
aws_cloudwatch_log_group
where
retention_in_days is null
order by
stored_bytes desc nulls last;30日以内に期限が切れる証明書
CloudFront で使う証明書は us-east-1 にあるので、regions に us-east-1 が含まれていることを確認しておく。in_use_by を見れば、どのロードバランサーやディストリビューションで使っているかがわかる。
select
account_id,
region,
domain_name,
status,
type,
to_char(not_after at time zone 'Asia/Tokyo', 'YYYY-MM-DD') as expires_on,
jsonb_array_length(coalesce(in_use_by, '[]'::jsonb)) as used_by_count
from
aws_acm_certificate
where
not_after < now() + interval '30 days'
order by
not_after;ACM が発行した証明書(type = 'AMAZON_ISSUED')で DNS 検証が通っていれば、通常は自動で更新される。注意が必要なのは、インポートした証明書(IMPORTED)や、使われていないため更新されない証明書だ。
今アラーム状態のもの
select
account_id,
region,
name,
to_char(state_updated_timestamp at time zone 'Asia/Tokyo', 'YYYY-MM-DD HH24:MI') as since,
left(state_reason, 80) as reason
from
aws_cloudwatch_alarm
where
state_value = 'ALARM'
order by
state_updated_timestamp;state_value はキー列なので、アラームの多いアカウントでも AWS 側で絞り込まれる。
セキュリティグループを変更したのは誰か
CloudTrail のイベント履歴は aws_cloudtrail_lookup_event で検索する。boto3 版でも触れたとおり、LookupEvents API で使える検索属性は1つだけだ。Steampipe でも、event_name・event_source・username などの検索属性の列に = の条件を2つ以上書くと、API がエラーを返し、そのエラーは無視されて結果が空になる。属性の条件は1つにし、残りは like などで手元で絞り込む。
select
region,
to_char(event_time at time zone 'Asia/Tokyo', 'YYYY-MM-DD HH24:MI') as jst,
event_name,
username,
cloud_trail_event ->> 'sourceIPAddress' as source_ip,
cloud_trail_event -> 'requestParameters' ->> 'groupId' as group_id,
cloud_trail_event ->> 'errorCode' as error_code
from
aws_cloudtrail_lookup_event
where
event_source = 'ec2.amazonaws.com' -- 検索属性はこの1つだけ
and start_time = now() - interval '7 days'
and end_time = now()
and event_name like '%SecurityGroup%' -- 手元で絞り込む
order by
event_time desc;start_time と end_time は検索の期間を API に渡すための列で、= で指定する(>= ではない点に注意)。イベントの詳細は cloud_trail_event 列に JSONB で入っているので、PART 03 の演算子で取り出せる。イベント履歴はリージョンごとに記録されるので、全リージョンを対象にしていれば、各リージョンの結果がまとめて返る。
パブリックアクセスのブロックが未設定のバケット
boto3 版では、ブロックの設定がないバケットで返るエラーを「未設定」として扱う処理を書いた。Steampipe はそのエラーを受けて4項目を false として返すので、普通の条件で探せる。アカウント全体でブロックしている場合はバケットごとの設定がなくても守られているので、aws_s3_account_settings と突き合わせる。
select
b.account_id,
b.region,
b.name,
b.block_public_acls,
b.block_public_policy,
b.ignore_public_acls,
b.restrict_public_buckets
from
aws_s3_bucket as b
join aws_s3_account_settings as a
on a.account_id = b.account_id
where
not (b.block_public_acls and b.block_public_policy
and b.ignore_public_acls and b.restrict_public_buckets)
and not (a.block_public_acls and a.block_public_policy
and a.ignore_public_acls and a.restrict_public_buckets)
order by
b.name;ポートフォワードに使えるEC2(SSM登録済み)
ポートフォワード PART 02 で中継に使う EC2 が、SSM にオンラインで登録されているかを確認する。EC2 の Name タグも見たいので、EC2 の一覧と JOIN する。
select
s.region,
s.instance_id,
i.title as name,
s.ping_status,
s.agent_version,
s.platform_name,
to_char(s.last_ping_date_time at time zone 'Asia/Tokyo', 'YYYY-MM-DD HH24:MI') as last_ping
from
aws_ssm_managed_instance as s
left join aws_ec2_instance as i
on i.instance_id = s.instance_id
and i.region = s.region
order by
s.ping_status, s.region, name;逆に「動いているのに SSM に出てこない EC2」を探すクエリは、PART 03 の JOIN の例で紹介した。
サービスごとに書かず、まとめて取る方法
サービスの数が増えると、PART 04 のように union all を並べるのも大変になる。AWS のリソースを横断して取るテーブルが2つある。
| テーブル | 得意なこと | 注意点 |
|---|---|---|
aws_tagging_resource | タグでの横断検索(「Env=prod のリソースすべて」)。返るのは ARN とタグ | タグが一度も付いたことのないリソースは返らない |
aws_resource_explorer_search | キーワード・タグ・リソースの種類で全リージョンを横断 | Resource Explorer の集約インデックスが必要。1回の検索で1,000件まで |
select
split_part(arn, ':', 3) as service,
region,
count(*) as resources
from
aws_tagging_resource
where
tags ->> 'Env' = 'prod'
group by
1, 2
order by
1, 2;ARN は arn:aws:サービス:リージョン:アカウント:リソース の形なので、split_part(arn, ':', 3) でサービス名を取り出している。
なお、Steampipe のテーブルは、Steampipe の CLI を使わずに利用する方法もある。既存の PostgreSQL に組み込む FDW 版、SQLite の拡張機能、データベースを使わずに CSV などへ書き出す単体のエクスポート用プログラムが、プラグインごとに提供されている。定期的に結果を別のデータベースにためたい場合などは、こちらも選択肢になる(詳しくは AWS プラグインのページを参照)。
シリーズのまとめ
| やりたいこと | ポイント | 記事 |
|---|---|---|
| 仕組みを知る | SQL のたびに API を呼ぶ。1接続=1アカウント、テーブルの裏で複数の API | PART 01 |
| Windows で使う | WSL2 に入れ、~/.aws を Windows とリンクで共有。aws.spc に profile と regions を明示 | PART 02 |
| 速く・漏れなく取る | 列を絞る、キー列で絞り込む、JSONB の演算子、キャッシュは .cache clear | PART 03 |
| 全リージョンの棚卸し | regions = ["*"]、union all で共通の列に、BOM 付き CSV で Excel へ | PART 04 |
| 使用率とコストを見る | 専用テーブルと JOIN、汎用テーブルは条件必須、Cost Explorer は有料 | PART 05 |
| 複数アカウント・エラー対処 | aggregator と search_path、エラーなしで結果が足りないケースに注意 | PART 06 |
| チェックを自動化する | Powerpipe で CIS ベンチマーク、独自のルールも SQL で | PART 07 |
boto3 と Steampipe は、どちらかを選ぶものではない。条件を変えながら調べる段階は Steampipe の SQL で、手順が固まって他の処理とつなげる段階は boto3 で、と使い分けると、それぞれの良さが生きる。このシリーズと boto3 シリーズが、その両方の手がかりになればうれしい。