一覧の見方

AWS プラグインのテーブルは580を超えるので、ここでは実務でよく使うものを目的別に絞って載せる。表の「メモ」には、キー列(AWS 側で絞り込まれる列、PART 03)や、使うときの注意を書いた。権限は、ReadOnlyAccess があればほぼすべて動く(コスト系を除く)。

  • 表にないものは、対話モードの .inspect aws か、Hub のテーブル一覧で探す
  • boto3 で同じ情報を取る方法は、boto3 シリーズの付録に API 名と IAM アクションの一覧がある
  • グローバルなサービス(IAM・S3 のバケット一覧・Route 53 など)は、regions の設定に関係なく1回の取得で全件が返る

リソースの棚卸し

サービステーブル取れる情報メモ
EC2aws_ec2_instance種類・状態・IP・タグ・起動日時・停止日時キー列:instance_state・instance_type など
EBSaws_ebs_volume / aws_ebs_snapshotサイズ・種類・アタッチ先・暗号化、スナップショットスナップショットは自アカウント所有のものが対象
AMIaws_ec2_ami自アカウントで作った AMI
VPCaws_vpc / aws_vpc_subnet / aws_vpc_security_group_ruleVPC・サブネット・セキュリティグループのルール(1ルール1行)ルールは aws_vpc_security_group_rule のほうが絞り込みやすい
Elastic IPaws_vpc_eip割り当て済みの IP と関連付け先
ELBaws_ec2_application_load_balancer / aws_ec2_network_load_balancer / aws_ec2_target_groupALB・NLB・ターゲットグループ
RDSaws_rds_db_instance / aws_rds_db_clusterクラス・エンジン・状態・マルチAZ・暗号化Aurora はクラスター単位も見る
DynamoDBaws_dynamodb_table状態・容量モード・項目数(概算)
Lambdaaws_lambda_functionランタイム・メモリ・タイムアウト・状態tags などは関数ごとに追加の API
ECS / EKSaws_ecs_cluster / aws_ecs_service / aws_eks_clusterクラスター・サービス・タスク数・バージョン
S3aws_s3_bucketバケット名・リージョン・暗号化・バージョニング・パブリックアクセスのブロック列ごとにバケット単位の API が呼ばれる。列は絞る
Route 53aws_route53_zone / aws_route53_recordホストゾーンとレコードグローバル
CloudFrontaws_cloudfront_distributionディストリビューションとオリジングローバル
ACMaws_acm_certificateドメイン・状態・有効期限・使用中のリソースCloudFront 用は us-east-1
Systems Manageraws_ssm_managed_instanceSSM に登録されたマシンとエージェントの状態キー列: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 Logsaws_cloudwatch_log_group保持期間・保存量・暗号化
CloudTrail(証跡)aws_cloudtrail_trail証跡の設定(マルチリージョン・ログの検証など)
CloudTrail(イベント)aws_cloudtrail_lookup_event直近90日の管理イベント検索属性の条件は1つまで(後述)
Healthaws_health_eventAWS 側の障害・メンテナンスの予定ビジネス以上のサポートプランが必要

コスト

テーブル取れる情報メモ
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_recommendationCost Optimization Hub の推奨事項事前にサービスの有効化が必要

セキュリティ・監査

サービステーブル取れる情報メモ
IAM ユーザーaws_iam_userMFA・最終ログイン・所属グループ・付与ポリシー
アクセスキー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 Analyzeraws_accessanalyzer_analyzerアナライザーの有無と検出結果
GuardDutyaws_guardduty_finding検出結果と重要度
Security Hubaws_securityhub_finding検出結果件数が多いので条件を付ける
S3(アカウント)aws_s3_account_settingsアカウント全体のパブリックアクセスのブロック
KMS / Secrets Manageraws_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_searchResource Explorer の検索結果集約インデックスの作成が必要。1回1,000件まで

逆引き:こういうことを知りたい

ここからは、boto3 版の逆引きと同じお題を SQL で書く。どれも regions = ["*"](PART 04)にしておけば全リージョンが対象になり、aggregator(PART 06)を使えば全アカウントが対象になる。

停止したまま放置されているEC2

停止中のインスタンスも EBS の料金はかかり続ける。boto3 版では StateTransitionReason の文字列を表示して目で見たが、Steampipe の state_transition_time 列は、その文字列から停止した日時を取り出して日時の型にしてくれている。そのまま日数の計算や比較に使える。

SQL:30日以上停止しているEC2
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つの表にする。

SQL
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 で補う。

SQL
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 になる。保存量の大きい順に並べると、料金への影響が大きいものから対処できる。

SQL
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 を見れば、どのロードバランサーやディストリビューションで使っているかがわかる。

SQL
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)や、使われていないため更新されない証明書だ。

今アラーム状態のもの

SQL
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 などで手元で絞り込む。

SQL:直近7日間のセキュリティグループの変更
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 と突き合わせる。

SQL
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 する。

SQL
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件まで
SQL:Env=prod のリソースを種類ごとに数える
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アカウント、テーブルの裏で複数の APIPART 01
Windows で使うWSL2 に入れ、~/.aws を Windows とリンクで共有。aws.spc に profile と regions を明示PART 02
速く・漏れなく取る列を絞る、キー列で絞り込む、JSONB の演算子、キャッシュは .cache clearPART 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 シリーズが、その両方の手がかりになればうれしい。