Athena で CloudTrail ログを調査してみた

先日 CloudWatch Logs Insights を利用して CloudTrail ログを調査してみたので、今度は Athena で調査してみます。

CloudTrail イベント履歴のコンソールから Athena のテーブルを簡単に作成できますが、パーティションの設定がないので手動で作成します。

Athenaのテーブル作成
クエリ
1. 拒否された API の特定
2. 特定日のエラー
料金
さいごに

Athenaのテーブル作成

テーブルは以下の内容です。

CREATE EXTERNAL TABLE cloudtrail_logs_pp(
    eventVersion STRING,
    userIdentity STRUCT<
        type: STRING,
        principalId: STRING,
        arn: STRING,
        accountId: STRING,
        invokedBy: STRING,
        accessKeyId: STRING,
        userName: STRING,
        sessionContext: STRUCT<
            attributes: STRUCT<
                mfaAuthenticated: STRING,
                creationDate: STRING>,
            sessionIssuer: STRUCT<
                type: STRING,
                principalId: STRING,
                arn: STRING,
                accountId: STRING,
                userName: STRING>,
            ec2RoleDelivery:string,
            webIdFederationData:map<string,string>
        >
    >,
    eventTime STRING,
    eventSource STRING,
    eventName STRING,
    awsRegion STRING,
    sourceIpAddress STRING,
    userAgent STRING,
    errorCode STRING,
    errorMessage STRING,
    requestparameters STRING,
    responseelements STRING,
    additionaleventdata STRING,
    requestId STRING,
    eventId STRING,
    readOnly STRING,
    resources ARRAY<STRUCT<
        arn: STRING,
        accountId: STRING,
        type: STRING>>,
    eventType STRING,
    apiVersion STRING,
    recipientAccountId STRING,
    serviceEventDetails STRING,
    sharedEventID STRING,
    vpcendpointid STRING,
    tlsDetails struct<
        tlsVersion:string,
        cipherSuite:string,
        clientProvidedHostHeader:string>
  )
PARTITIONED BY (
   `timestamp` string,
   `region` string)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://バケット名/AWSLogs/アカウントID/CloudTrail/'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2021/10/01,NOW', 
  'projection.timestamp.type'='date', 
  'projection.region.type'='enum',
  'projection.region.values'='us-east-1,us-east-2,us-west-1,us-west-2,af-south-1,ap-east-1,ap-south-1,ap-northeast-2,ap-southeast-1,ap-southeast-2,ap-northeast-1,ca-central-1,eu-central-1,eu-west-1,eu-west-2,eu-south-1,eu-west-3,eu-north-1,me-south-1,sa-east-1',
  'storage.location.template'='s3://バケット名/AWSLogs/アカウントID/CloudTrail/${region}/${timestamp}')

PARTITIONED BYでパーティションをしたい対象を指定しています。
パーティションすることで、クエリによるスキャン量を制限でき、パフォーマンス向上・コスト削減が期待できます。

データをパーティション分割することで、各クエリによってスキャンされるデータの量を制限できるようになるため、パフォーマンスが向上し、コストが削減されます。任意のキーでデータをパーティションに分割することができます。一般的な方法では、時間に基づいてデータをパーティション分割します。これにより、通常、複数レベルのパーティション構成となります。たとえば、1 時間ごとに配信されるデータを年、月、日、時間でパーティション分割できます。別の例として、データが配信されるソースが多数に分かれているものの、それらのロードは 1 日 1 回だけ行われる場合には、データソースと日付によるパーティション分割を行います。
パーティション分割されたテーブルをクエリし、WHERE 句でパーティションを指定する場合、Athena はそのパーティションからのデータしかスキャンしません。詳細については、「テーブルの場所とパーティション」(テーブルの場所とパーティション) を参照してください。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partitions.html

また、TBLPROPERTIES でパーティション射影（プロジェクション）の設定をすることで、手動でパーティションを追加することなくクエリできます。

パーティション射影が役立つシナリオには、次のようなものがあります。
高度にパーティション化されたテーブルに対するクエリが、思ったほどすぐに完了しない。
データに新しい日付または時刻パーティションが作成されたとき、定期的にパーティションをテーブルに追加する。パーティション射影で、新しいデータが到着したときに使用できる相対日付範囲を設定している。
Amazon S3 に高度にパーティション分割されたデータがある。データは、AWS Glue Data Catalog または Hive メタストア内のモデルに対して実用的ではなく、クエリがそのごく一部のみを読み取る。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection.html

テーブル作成は、以下 AWS の記事とクラスメソッドさんのブログを参考にというかほぼそのまま利用しました。

Athena で CloudTrail データをクエリするときのタイムアウトに関する問題のトラブルシューティング

【全リージョン対応】CloudTrailのログをAthenaのPartition Projectionなテーブルで作る | DevelopersIO

CloudTrailのログを分析するためのAthenaテーブルを作る機会がありましたので、AthenaのPartition Projectionという機能を用いてリージョンごと・時系列ごとでパーティションを分割するように …

クエリ

拒否された API の特定

SELECT count (*) as TotalEvents, useridentity.arn, eventsource, eventname, errorCode, errorMessage
FROM cloudtrail_logs_pp
WHERE (errorcode like '%Denied%' or errorcode like '%Unauthorized%')
AND timestamp >= '2022/06/01'
AND timestamp < '2022/12/30'
GROUP by eventsource, eventname, errorCode, errorMessage, useridentity.arn
ORDER by eventsource, eventname

実行結果（以下のように結果が出ました。スキャンデータ485MB、実行時間9.839秒）

#, TotalEvents, arn, eventsource, eventname, errorCode, errorMessage
1, 1, ${実行したIAMのARN}, autoscaling.amazonaws.com, DeletePolicy, AccessDenied, User: xxx is not authorized to perform: autoscaling:DeletePolicy on resource: xxx because no identity-based policy allows the autoscaling:DeletePolicy action
2 ...f
.
.
.

特定日のエラー

SELECT count (*) as TotalEvents, useridentity.arn, eventsource, eventname, errorCode, errorMessage
FROM cloudtrail_logs_pp
WHERE errorcode like '%'
AND timestamp = '2022/12/24'
GROUP by eventsource, eventname, errorCode, errorMessage, useridentity.arn
ORDER by eventsource, eventname

スキャンデータ：2.57MB、実行時間 1.576秒