Amazon AthenaでCloudTrailのログをクエリするときにuserIdentityをフラットにするSELECT文を書いた

こんにちは、臼田です。

みなさん、CloudTrailの調査してますか？(挨拶

今回は私のただの備忘録ですが、AthenaでCloudTrailのログをクエリしてcsvでダウンロードするときに、構造データ(つまりuserIdentity)をフラットにしておきたいと思ったのでそのSELECT文を置いておきます。

ちょっと前置き

テーブル作成は以下を参照しています。パーティションが自動で切れるのでサイコーですよね。

クエリ

以下のようになります。

SELECT eventVersion,
	userIdentity.type AS userIdentity_type,
	userIdentity.principalId AS userIdentity_principalId,
	userIdentity.arn AS userIdentity_arn,
	userIdentity.accountId AS userIdentity_accountId,
	userIdentity.invokedBy AS userIdentity_invokedBy,
	userIdentity.accessKeyId AS userIdentity_accessKeyId,
	userIdentity.userName AS userIdentity_userName,
	userIdentity.sessionContext.attributes.mfaAuthenticated AS userIdentity_sessionContext_attributes_mfaAuthenticated,
	userIdentity.sessionContext.attributes.creationDate AS userIdentity_sessionContext_attributes_creationDate,
	userIdentity.sessionContext.sessionIssuer.type AS userIdentity_sessionContext_sessionIssuer_type,
	userIdentity.sessionContext.sessionIssuer.principalId AS userIdentity_sessionContext_sessionIssuer_principalId,
	userIdentity.sessionContext.sessionIssuer.arn AS userIdentity_sessionContext_sessionIssuer_arn,
	userIdentity.sessionContext.sessionIssuer.accountId AS userIdentity_sessionContext_sessionIssuer_accountId,
	userIdentity.sessionContext.sessionIssuer.userName AS userIdentity_sessionContext_sessionIssuer_userNam,
	eventTime,
	eventSource,
	eventName,
	awsRegion,
	sourceIpAddress,
	userAgent,
	errorCode,
	errorMessage,
	requestParameters,
	responseElements,
	additionalEventData,
	requestId,
	eventId,
	resources,
	eventType,
	apiVersion,
	readOnly,
	recipientAccountId,
	serviceEventDetails,
	sharedEventID,
	vpcEndpointId
FROM cloudtrail_logs_partition_projection;

特別なことは何もしてないです。単純にこれ作るのが面倒なので、残しておこうと思いました。

ASを利用していますが、これがないとtypeなどのカラムが複数出来上がるためです。カラム名は.の代わりに_を利用しています。

これでCSVでダウンロードして簡単にこねこねできます。

なお、これが最適化はわからないので、もっといいやり方を知っている人がいたら教えて下さい。

まとめ

とりあえず自分の役に立つと思うのでまとめました。

使いたい人がいたらどうぞ。