【Athena の躓きシリーズ】CloudTrail ログの中から API コール数を集計する Athena のクエリ

タダです.

業務で Athena を使ったログ解析で詰まったことがあったら都度記事にクエリの結果とその対処をまとめるシリーズをやってます.今回は CloudTrail ログの中から特定の API コール数がどこから発信されているかを確認する必要があった時の学びをこの記事で書きます.

API コール数の集計の対象
- テーブル作成用クエリ例
API コール数を集計するクエリ例
- ハマりポイント
まとめ
- 関連記事

API コール数の集計の対象

今回は API コール数の集計の対象として CloudTrail のログの中で eventTime (いつ),eventSource(どのサービスにリクエストが行われたか),userAgent(どこから)のデータを確認しました.

docs.aws.amazon.com

さて,データを確認するためにテーブルを作ったのですが,当初CloudTrail のログデータが1年以上溜まっているシステムだったので,全検検索すると読み取り処理がタイムアウトしました.そのため,ログで確認したい期間が2020年のログだったので2020年のフォルダを検索するようLOCATIONの指定を変更し作り直して対処しました.

docs.aws.amazon.com

テーブル作成用クエリ例

CREATE EXTERNAL TABLE [テーブル名] (
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>>>,
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,
resources ARRAY<STRUCT<
               ARN:STRING,
               accountId:STRING,
               type:STRING>>,
eventtype STRING,
apiversion STRING,
readonly STRING,
recipientaccountid STRING,
serviceeventdetails STRING,
sharedeventid STRING,
vpcendpointid STRING
)
PARTITIONED BY (region string, year string, month string, day string)
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://CloudTrail_bucket_name/AWSLogs/Account_ID/CloudTrail/ap-northeast-1/2020/'; <= 変更箇所

API コール数を集計するクエリ例

テーブルができたのでクエリを投げていくのですが,結論としては次のようなクエリでデータを取れました.

SELECT DATE_FORMAT(from_iso8601_timestamp(eventTime), '%Y-%m-%d') AS EventDate, eventSource, userAgent, COUNT(*) as APICount
FROM "DB名"."テーブル名"
WHERE eventName='CreateBucket(検索したいAPI名)'
AND eventTime > '2020-07-01T00:00:00Z(検索したい期間.左は7/1以降が対象期間の例)'
GROUP BY DATE_FORMAT(from_iso8601_timestamp(eventTime), '%Y-%m-%d'),eventSource, userAgent
ORDER BY EventDate ASC;

クエリのレスポンスとして以下のような結果が返ってきます.

EventDate|eventSource|userAgent|APICount|
2020/7/7|s3.amazonaws.com|athena.amazonaws.com|1|

ハマりポイント

今回ハマったのは,eventTimeのフォーマット変更して集計したかったのですが,その際に CloudTrail のタイムフォーマットがiso8601形式(2014-03-06T21:22:54Z形式)であると知らずに苦戦しました.Athena のクエリエンジンは Presto になっていますが,Presto の関数に iso8601 用の from_iso8601_timestamp 関数があったのでこちらを使って対応しました.

prestodb.io