ENGINEER BLOG ENGINEER BLOG
  • 公開日
  • 最終更新日

CloudTrailログをAthenaでクエリしよう

この記事を共有する

目次

皆さんこんにちは！
サービスGの山内です！
お客様先にて、CloudTrailの料金が急増し、原因となるAPI操作を特定するためにログに対してクエリを行いました。
このようなケースはよくあるかと思うため、方法について紹介します。

準備

私が以前投稿したAthenaのブログを参考に、データベースの作成まで実施します。

テーブル作成

マネジメントコンソールにて「Amazon Athena > クエリエディタ」へ遷移し、クエリを実行して、テーブルを作成します。
AWS公式ガイドに記載されているコマンドで、パーティション射影を使用したテーブルを作成します。
パーティション射影については、上記の私のブログに概要を記載しています。

※CloudTrailのマネジメントコンソールからAthenaテーブルの作成ができますが、パーティション射影を使用したテーブルではないため、公式ガイドに紹介されているコマンドを使用するのがおすすめです。

01.png

公式ガイドのコマンドを修正します。
分かりやすくするため、★で置換箇所を示しています。

ここを押すと展開します

CREATE EXTERNAL TABLE ★テーブル名★(
    eventversion STRING,
    useridentity STRUCT<
        type: STRING,
        principalid: STRING,
        arn: STRING,
        accountid: STRING,
        invokedby: STRING,
        accesskeyid: STRING,
        username: STRING,
        onbehalfof: STRUCT<
             userid: STRING,
             identitystorearn: STRING>,
        sessioncontext: STRUCT<
            attributes: STRUCT<
                mfaauthenticated: STRING,
                creationdate: STRING>,
            sessionissuer: STRUCT<
                type: STRING,
                principalid: STRING,
                arn: STRING,
                accountid: STRING,
                username: STRING>,
            ec2roledelivery:string,
            webidfederationdata: STRUCT<
                federatedprovider: STRING,
                attributes: map>
        >
    >,
    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>,
    eventtype STRING,
    apiversion STRING,
    recipientaccountid STRING,
    serviceeventdetails STRING,
    sharedeventid STRING,
    vpcendpointid STRING,
    vpcendpointaccountid STRING,
    eventcategory STRING,
    addendum STRUCT<
      reason:STRING,
      updatedfields:STRING,
      originalrequestid:STRING,
      originaleventid:STRING>,
    sessioncredentialfromconsole STRING,
    edgedevicedetails STRING,
    tlsdetails STRUCT<
      tlsversion:STRING,
      ciphersuite:STRING,
      clientprovidedhostheader:STRING>
  )
PARTITIONED BY (
   `timestamp` 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
  '★CloudTrailログ保管先URI★'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='★クエリを開始したい日★,NOW', 
  'projection.timestamp.type'='date', 
  'storage.location.template'='★CloudTrailログ保管先URI★${timestamp}')

今回は以下のように修正しました。
アカウントIDはマスキングしています。
「aws-cloudtrail-logs-xxxxxxx」の部分を、適切なCloudTrailログ保管先バケットに修正してください。

ここを押すと展開します

CREATE EXTERNAL TABLE cloudtrail_table (
    eventversion STRING,
    useridentity STRUCT<
        type: STRING,
        principalid: STRING,
        arn: STRING,
        accountid: STRING,
        invokedby: STRING,
        accesskeyid: STRING,
        username: STRING,
        onbehalfof: STRUCT<
             userid: STRING,
             identitystorearn: STRING>,
        sessioncontext: STRUCT<
            attributes: STRUCT<
                mfaauthenticated: STRING,
                creationdate: STRING>,
            sessionissuer: STRUCT<
                type: STRING,
                principalid: STRING,
                arn: STRING,
                accountid: STRING,
                username: STRING>,
            ec2roledelivery:string,
            webidfederationdata: STRUCT<
                federatedprovider: STRING,
                attributes: map>
        >
    >,
    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>,
    eventtype STRING,
    apiversion STRING,
    recipientaccountid STRING,
    serviceeventdetails STRING,
    sharedeventid STRING,
    vpcendpointid STRING,
    vpcendpointaccountid STRING,
    eventcategory STRING,
    addendum STRUCT<
      reason:STRING,
      updatedfields:STRING,
      originalrequestid:STRING,
      originaleventid:STRING>,
    sessioncredentialfromconsole STRING,
    edgedevicedetails STRING,
    tlsdetails STRUCT<
      tlsversion:STRING,
      ciphersuite:STRING,
      clientprovidedhostheader:STRING>
  )
PARTITIONED BY (
   `timestamp` 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://aws-cloudtrail-logs-xxxxxxx/AWSLogs/xxxxxxxxx/CloudTrail/ap-northeast-1/'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2026/01/01,NOW', 
  'projection.timestamp.type'='date', 
  'storage.location.template'='s3://aws-cloudtrail-logs-xxxxxx/AWSLogs/xxxxxx/CloudTrail/ap-northeast-1/${timestamp}')

コマンドを実行すると、テーブルを作成できました。 02.png

テーブルの確認

テーブルの三点リーダーから「テーブルをプレビュー」を実行すると、作成したテーブルにデータがあるかを確認することができます。
プレビューを実行し、テーブル作成が成功しているかを確認します。

03.png

問題なく結果が返ってきたため、テーブル作成はうまくいっているようです。

04.png

クエリの実行

指定期間のAPIのうち一番多かったAPI上位10個のクエリ

以下のコマンドを実行すると、 2026年1月1日から2026年3月11日までの期間で、多く実行されたAPIのうち上位10個のAPIを特定できます。

SELECT
  eventname AS api_name,
  COUNT(*) AS call_count
FROM cloudtrail_table
WHERE
   date(from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo') BETWEEN DATE '2026-01-01' AND DATE '2026-03-11'
GROUP BY from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo'
ORDER BY call_count DESC
LIMIT 10;

「from_ iso8601 _timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo'」は以下の順番で、
元の日付データをJSTに変換しています。

  1. 「 from_ iso8601 _timestamp(eventtime) 」で文字列をタイムスタンプに変換
  2. 「from_ iso8601 _timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo'」でJSTに変換
変換前：2026-01-12T08:36:04Z
変換後：2026-01-12 17:36:04

私の実行結果は以下の通りです。 05.png

ユーザの操作履歴

以下のコマンドを実行すると、3月11日9:00から18:00までの期間でユーザー操作履歴がクエリできます。

SELECT
from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' AS event_ts_jst,
eventsource,
eventname,
awsregion,
sourceipaddress
FROM cloudtrail_table
WHERE
   from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' 
   BETWEEN TIMESTAMP '2026-03-11 09:00' 
   AND TIMESTAMP '2026-03-11 18:00'
ORDER BY from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' DESC

クエリ結果はこちらです。 06.png

失敗したAPI操作

以下のコマンドを実行すると、3月11日9:00から18:00までの期間で失敗したAPI操作がクエリできます。

SELECT
from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' AS event_ts_jst,
useridentity.arn,
eventsource,
eventname,
errorcode,
errormessage
FROM cloudtrail_table
WHERE 
errorcode IS NOT NULL
AND from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' 
    BETWEEN TIMESTAMP '2026-03-11 09:00' 
    AND TIMESTAMP '2026-03-11 18:00'
ORDER BY from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' DESC

クエリ結果がこちらです。 07.png

特定ユーザーの操作履歴

以下のコマンドを実行すると、3月11日9:00から18:00までの期間で特定ユーザーの操作履歴がクエリできます。

SELECT
from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' AS event_ts_jst,
eventsource,
eventname,
sourceipaddress
FROM cloudtrail_table
WHERE
useridentity.arn LIKE '%hiroki%'
AND from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' 
    BETWEEN TIMESTAMP '2026-03-11 09:00' 
    AND TIMESTAMP '2026-03-11 18:00'
ORDER BY from_iso8601_timestamp(eventtime) AT TIME ZONE 'Asia/Tokyo' DESC;

クエリ結果がこちらです。 08.png

まとめ

今回はCloudTrailログをクエリしてみました。
今後は、クエリの高速化や実践的なクエリについて学んでいきたいと思います。

この記事は私が書きました

山内 宏紀

記事一覧

CloudFormationが好きです。 使っているギターはSGです。

山内 宏紀

この記事を共有する

クラウドのご相談

CONTACT

クラウド導入や運用でお悩みの方は、お気軽にご相談ください。
専門家がサポートします。

お問い合わせ

サービス資料ダウンロード

DOWNLOAD

ビジネスをクラウドで加速させる準備はできていますか？
今すぐサービス資料をダウンロードして、詳細をご確認ください。

資料請求