【Athena の躓きシリーズ】CloudTrail の構造体データが入った配列に対する Athena のクエリ

タダです.

業務で Athena を使って S3 のデータを分析・解析することはよくあると思うのですが,CloudTrail の中でも構造体のデータを扱いたい時に欲しいデータの取り出しで詰まったのでこの記事でその対処をまとめます.

CloudTrail 用テーブル作成

予めログを取っているアカウントで 下記のドキュメントを参考に CloudTrail ログのテーブル作成を作りました.

docs.aws.amazon.com

テーブルを作成後,SELECT * FROM "テーブル名" limit 10;で以下のようなレコードが取れます.

"eventversion","useridentity","eventtime","eventsource","eventname","awsregion","sourceipaddress","useragent","errorcode","errormessage","requestparameters","responseelements","additionaleventdata","requestid","eventid","resources","eventtype","apiversion","readonly","recipientaccountid","serviceeventdetails","sharedeventid","vpcendpointid"
"1.05","{type=AWSService, principalid=null, arn=null, accountid=null, invokedby=cloudtrail.amazonaws.com, accesskeyid=null, username=null, sessioncontext=null}","2019-06-05T13:25:20Z","sts.amazonaws.com","AssumeRole","eu-west-1","cloudtrail.amazonaws.com","cloudtrail.amazonaws.com",,,"{""roleArn"":""arn:aws:iam::XXXXXXXXXXX:role/CloudTrail_CloudWatchLogs_Role"",""roleSessionName"":""CLOUDWATCH_LOGS_DELIVERY_SESSION""}","{""credentials"":{""accessKeyId"":""XXXX"",""expiration"":""Jun 5, 2019 2:25:20 PM"",""sessionToken"":""XXXX/XXX/XXXX/XXXX/XXXX/XXXX""},""assumedRoleUser"":{""assumedRoleId"":""XXXX"",""arn"":""arn:aws:sts::XXXXXXXXXXX:assumed-role/CloudTrail_CloudWatchLogs_Role/CLOUDWATCH_LOGS_DELIVERY_SESSION""}}",,"XXXX","XXXX","[{arn=arn:aws:iam::XXXXXXXXXXX:role/CloudTrail_CloudWatchLogs_Role, accountid=XXXXXXXXXXX, type=AWS::IAM::Role}]","AwsApiCall",,,"XXXXXXXXXXX",,"XXXX"

構造体のデータを取得する方法

今回取りたいのは,resourcesに入っているデータです.resourcesは「resources ARRAY<STRUCT<ARN:STRING,accountId:STRING,ype:STRING>>」で定義している構造体が入っている配列データ構造になっています.resourcesに対してクエリを投げた結果帰ってくるレコードは [{arn=arn:aws:iam::XXXX:role/CloudTrail_CloudWatchLogs_Role, accountid=XXXX, type=AWS::IAM::Role}]と言った具合です.

構造体データに対するクエリ例

結論としては次のようなクエリでデータを取れました.

SELECT
 resource.[取りたい情報]
FROM "テーブル名"
CROSS JOIN UNNEST(resources) AS t (resource)
LIMIT 10;

例えば,ARN 情報を取りたい場合は次のようなクエリになります.クエリの結果として帰ってくるのが arn:aws:iam::XXXX:role/CloudTrail_CloudWatchLogs_Role と言った情報です.

SELECT
 resource.arn
FROM "テーブル名"
CROSS JOIN UNNEST(resources) AS t (resource)
LIMIT 10;

ポイントになるのはCROSS JOIN と UNNEST句です.2つを組み合わせることで配列のデータを展開した行レコードを作っています.展開後のレコードから必要な情報を抽出しています. docs.aws.amazon.com

まとめ

CloudTrail の構造体データを Athena で扱う対処についてまとめました.Athena で溜まったデータの活かし方をもっと学んでいきたいと思います.