DuckDB で CloudTrail 証跡をクエリする

AWS CloudTrail 証跡をアドホックにサクッと分析したいな〜という場面があって DuckDB を使ってみた🦆

もちろん AWS CloudTrail Lake や Amazon Athena を使う選択肢もあるけど，せっかく Amazon S3 にデータ (.json.gz) があるんだし，もっと気軽にクエリできても良いのでは❗️と思って DuckDB を選んだ．

$ duckdb --version
v1.3.2 (Ossivalis) 0b83e5d2f6

シークレットを登録する

まずは DuckDB を起動して，CREATE SECRET コマンド（もしくは CREATE OR REPLACE SECRET コマンド）で Amazon S3 にアクセスするためのシークレットを登録しておく．

⚫◗ CREATE OR REPLACE SECRET secret (
    TYPE s3,
    PROVIDER credential_chain,
    CHAIN config,
    PROFILE 'xxxxx'
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘

duckdb.org

テーブルを作る

次に CREATE TABLE AS SELECT (CTAS) コマンドで AWS CloudTrail 証跡テーブル events を作る．今回はプレフィックスで範囲を限定して「2025年7月」のみにした📅 取得するフィールドはドキュメントを参考に代表的なものを選んだ．

docs.aws.amazon.com

⚫◗ CREATE TABLE events AS
    SELECT
        record.eventTime::TIMESTAMP AS eventTime,
        record.eventSource,
        record.eventName,
        record.awsRegion,
        record.userIdentity,
        record.eventType,
        record.requestParameters
    FROM (
        SELECT unnest(Records) as record
        FROM read_json('s3://aws-cloudtrail-logs-000000000000-xxxxxxxx/AWSLogs/000000000000/CloudTrail/ap-northeast-1/2025/07/*/000000000000_CloudTrail_ap-northeast-1_*.json.gz', ignore_errors=true)
    );

duckdb.org

クエリを実行する

あとは自由にクエリを実行する❗️

eventSource 別カウント

ちょうど7月に ECS Blue/Green デプロイの検証や ecspresso x ECS Blue/Green デプロイの検証をしていて elasticloadbalancing.amazonaws.com と ecs.amazonaws.com のカウントが増えている📈

⚫◗ SELECT eventSource, awsRegion, COUNT(*) AS COUNT
    FROM events
    GROUP BY eventSource, awsRegion
    ORDER BY COUNT DESC;
┌──────────────────────────────────────────┬────────────────┬───────┐
│               eventSource                │   awsRegion    │ COUNT │
│                 varchar                  │    varchar     │ int64 │
├──────────────────────────────────────────┼────────────────┼───────┤
│ kms.amazonaws.com                        │ ap-northeast-1 │ 14847 │
│ elasticloadbalancing.amazonaws.com       │ ap-northeast-1 │ 11297 │
│ ecs.amazonaws.com                        │ ap-northeast-1 │  5109 │
│ ec2.amazonaws.com                        │ ap-northeast-1 │  3013 │
│ sts.amazonaws.com                        │ ap-northeast-1 │  2559 │
│ arc-zonal-shift.amazonaws.com            │ ap-northeast-1 │  2398 │
│ s3.amazonaws.com                         │ ap-northeast-1 │  2235 │
│ lambda.amazonaws.com                     │ ap-northeast-1 │  1488 │
│ autoscaling.amazonaws.com                │ ap-northeast-1 │   407 │
│ cloudformation.amazonaws.com             │ ap-northeast-1 │   396 │
│ logs.amazonaws.com                       │ ap-northeast-1 │   382 │
│ notifications.amazonaws.com              │ ap-northeast-1 │   279 │
│ tagging.amazonaws.com                    │ ap-northeast-1 │   154 │
│ apigateway.amazonaws.com                 │ ap-northeast-1 │    59 │
│ schemas.amazonaws.com                    │ ap-northeast-1 │    33 │
│ iotsecuredtunneling.amazonaws.com        │ ap-northeast-1 │    24 │
│ cloudtrail.amazonaws.com                 │ ap-northeast-1 │    18 │
│ servicecatalog-appregistry.amazonaws.com │ ap-northeast-1 │    16 │
│ monitoring.amazonaws.com                 │ ap-northeast-1 │    16 │
│ sns.amazonaws.com                        │ ap-northeast-1 │    12 │
│ ecr.amazonaws.com                        │ ap-northeast-1 │    12 │
│ securityhub.amazonaws.com                │ ap-northeast-1 │    11 │
│ resource-explorer-2.amazonaws.com        │ ap-northeast-1 │    10 │
│ events.amazonaws.com                     │ ap-northeast-1 │     9 │
│ servicediscovery.amazonaws.com           │ ap-northeast-1 │     7 │
│ compute-optimizer.amazonaws.com          │ ap-northeast-1 │     6 │
│ bedrock.amazonaws.com                    │ ap-northeast-1 │     6 │
│ codepipeline.amazonaws.com               │ ap-northeast-1 │     5 │
│ acm.amazonaws.com                        │ ap-northeast-1 │     5 │
│ config.amazonaws.com                     │ ap-northeast-1 │     5 │
│ signin.amazonaws.com                     │ ap-northeast-1 │     4 │
│ acm-pca.amazonaws.com                    │ ap-northeast-1 │     2 │
│ sqs.amazonaws.com                        │ ap-northeast-1 │     2 │
│ access-analyzer.amazonaws.com            │ ap-northeast-1 │     1 │
│ route53resolver.amazonaws.com            │ ap-northeast-1 │     1 │
├──────────────────────────────────────────┴────────────────┴───────┤
│ 35 rows                                                 3 columns │
└───────────────────────────────────────────────────────────────────┘

Amazon ECS 関連の eventName 別カウント

eventSource を ecs.amazonaws.com に限定した eventName のカウントも確認できた📈

⚫◗ SELECT eventName, awsRegion, COUNT(*) AS COUNT
    FROM events
    WHERE eventSource = 'ecs.amazonaws.com'
    GROUP BY eventName, awsRegion
    ORDER BY COUNT DESC;
┌────────────────────────────┬────────────────┬───────┐
│         eventName          │   awsRegion    │ COUNT │
│          varchar           │    varchar     │ int64 │
├────────────────────────────┼────────────────┼───────┤
│ DescribeServices           │ ap-northeast-1 │  1653 │
│ DescribeClusters           │ ap-northeast-1 │   704 │
│ DescribeServiceRevisions   │ ap-northeast-1 │   421 │
│ ListContainerInstances     │ ap-northeast-1 │   383 │
│ DescribeTaskDefinition     │ ap-northeast-1 │   373 │
│ DescribeServiceDeployments │ ap-northeast-1 │   299 │
│ ListTasks                  │ ap-northeast-1 │   292 │
│ ListServiceDeployments     │ ap-northeast-1 │   288 │
│ DescribeCapacityProviders  │ ap-northeast-1 │   183 │
│ DescribeTasks              │ ap-northeast-1 │   146 │
│ ListServices               │ ap-northeast-1 │    63 │
│ ListTaskDefinitions        │ ap-northeast-1 │    60 │
│ UpdateService              │ ap-northeast-1 │    56 │
│ ListTaskDefinitionFamilies │ ap-northeast-1 │    45 │
│ GetTaskProtection          │ ap-northeast-1 │    42 │
│ ListAccountSettings        │ ap-northeast-1 │    34 │
│ RegisterTaskDefinition     │ ap-northeast-1 │    23 │
│ ListClusters               │ ap-northeast-1 │    12 │
│ DeregisterTaskDefinition   │ ap-northeast-1 │     9 │
│ DeleteService              │ ap-northeast-1 │     9 │
│ CreateService              │ ap-northeast-1 │     6 │
│ DeleteCluster              │ ap-northeast-1 │     3 │
│ ListTagsForResource        │ ap-northeast-1 │     3 │
│ CreateCluster              │ ap-northeast-1 │     2 │
├────────────────────────────┴────────────────┴───────┤
│ 24 rows                                   3 columns │
└─────────────────────────────────────────────────────┘

Amazon S3 関連の eventName 別カウント

同じように eventSource を s3.amazonaws.com に限定した eventName のカウントも確認できた📈

⚫◗ SELECT eventName, awsRegion, COUNT(*) AS COUNT
    FROM events
    WHERE eventSource = 's3.amazonaws.com'
    GROUP BY eventName, awsRegion
    ORDER BY COUNT DESC;
┌──────────────────────────────────┬────────────────┬───────┐
│            eventName             │   awsRegion    │ COUNT │
│             varchar              │    varchar     │ int64 │
├──────────────────────────────────┼────────────────┼───────┤
│ GetBucketAcl                     │ ap-northeast-1 │  2085 │
│ ListObjectVersions               │ ap-northeast-1 │    68 │
│ HeadBucket                       │ ap-northeast-1 │    21 │
│ GetBucketVersioning              │ ap-northeast-1 │    17 │
│ ListObjects                      │ ap-northeast-1 │    11 │
│ GetBucketOwnershipControls       │ ap-northeast-1 │     7 │
│ GetBucketObjectLockConfiguration │ ap-northeast-1 │     6 │
│ GetBucketLifecycle               │ ap-northeast-1 │     4 │
│ DeleteBucketLifecycle            │ ap-northeast-1 │     2 │
│ GetBucketPublicAccessBlock       │ ap-northeast-1 │     2 │
│ GetBucketReplication             │ ap-northeast-1 │     1 │
│ GetAccelerateConfiguration       │ ap-northeast-1 │     1 │
│ GetBucketTagging                 │ ap-northeast-1 │     1 │
│ DeleteBucket                     │ ap-northeast-1 │     1 │
│ GetBucketWebsite                 │ ap-northeast-1 │     1 │
│ GetBucketEncryption              │ ap-northeast-1 │     1 │
│ GetBucketPolicy                  │ ap-northeast-1 │     1 │
│ PutBucketVersioning              │ ap-northeast-1 │     1 │
│ GetBucketCors                    │ ap-northeast-1 │     1 │
│ GetBucketLogging                 │ ap-northeast-1 │     1 │
│ DeleteBucketPublicAccessBlock    │ ap-northeast-1 │     1 │
│ GetBucketRequestPayment          │ ap-northeast-1 │     1 │
├──────────────────────────────────┴────────────────┴───────┤
│ 22 rows                                         3 columns │
└───────────────────────────────────────────────────────────┘

AWS Lambda 関連の eventName 別カウント

eventSource を lambda.amazonaws.com に限定した eventName のカウントも確認できた📈

GetFunction20150331v2 や GetFunctionConfiguration20150331v2 など AWS Lambda 関連の eventName には日付やバージョン表記が含まれているんだな〜という発見もあった👀

⚫◗ SELECT eventName, awsRegion, COUNT(*) AS COUNT
    FROM events
    WHERE eventSource = 'lambda.amazonaws.com'
    GROUP BY eventName, awsRegion
    ORDER BY COUNT DESC;
┌───────────────────────────────────────┬────────────────┬───────┐
│               eventName               │   awsRegion    │ COUNT │
│                varchar                │    varchar     │ int64 │
├───────────────────────────────────────┼────────────────┼───────┤
│ GetFunction20150331v2                 │ ap-northeast-1 │   488 │
│ GetFunctionConfiguration20150331v2    │ ap-northeast-1 │   157 │
│ GetAccountSettings20160819            │ ap-northeast-1 │   145 │
│ ListEventSourceMappings20150331       │ ap-northeast-1 │   104 │
│ ListVersionsByFunction20150331        │ ap-northeast-1 │    72 │
│ ListTags20170331                      │ ap-northeast-1 │    71 │
│ ListFunctions20150331                 │ ap-northeast-1 │    42 │
│ GetRuntimeManagementConfig            │ ap-northeast-1 │    38 │
│ GetFunctionCodeSigningConfig          │ ap-northeast-1 │    38 │
│ ListFunctionUrlConfigs                │ ap-northeast-1 │    35 │
│ ListLayers20181031                    │ ap-northeast-1 │    35 │
│ GetFunctionEventInvokeConfig          │ ap-northeast-1 │    35 │
│ GetPolicy20150331                     │ ap-northeast-1 │    35 │
│ ListAliases20150331                   │ ap-northeast-1 │    35 │
│ ListProvisionedConcurrencyConfigs     │ ap-northeast-1 │    34 │
│ GetFunctionRecursionConfig            │ ap-northeast-1 │    34 │
│ UpdateFunctionConfiguration20150331v2 │ ap-northeast-1 │    24 │
│ GetEventSourceMapping20150331         │ ap-northeast-1 │    17 │
│ DeleteFunction20150331                │ ap-northeast-1 │    13 │
│ PublishVersion20150331                │ ap-northeast-1 │    12 │
│ GetPolicy20150331v2                   │ ap-northeast-1 │     8 │
│ CreateFunction20150331                │ ap-northeast-1 │     5 │
│ UpdateFunctionCode20150331v2          │ ap-northeast-1 │     3 │
│ RemovePermission20150331v2            │ ap-northeast-1 │     2 │
│ AddPermission20150331v2               │ ap-northeast-1 │     2 │
│ GetLayerVersionByArn20181031          │ ap-northeast-1 │     2 │
│ DeleteEventSourceMapping20150331      │ ap-northeast-1 │     1 │
│ CreateEventSourceMapping20150331      │ ap-northeast-1 │     1 │
├───────────────────────────────────────┴────────────────┴───────┤
│ 28 rows                                              3 columns │
└────────────────────────────────────────────────────────────────┘