Amazon SESメールログをAmazon Athenaで分析してみた

はじめに

こんにちは。

ディーネットの牛山です。

今日は、眠育の日です、皆さま快眠でしたでしょうか。

それはさておき、今回は、Amazon SESメールログをAmazon Athenaを使用し、解析してみます。

本記事では、Amazon Athenaクエリを使用した部分に焦点を当てます。

その他、設定については割愛していますのであらかじめご了承ください。

構成イメージ

Amazon SESのメール送信ログをAmazon Data FirehoseからS3バケットに蓄積し、Amazon AthenaよりS3バケットを解析する流れとなります。

---

---

Amazon Athenaへテーブルを作成

以下、過去記事で紹介しております、S3へのアクセス設定は、SESメールが吐き出されているS3バケットに変更する以外同一ですので、S3アクセス設定解説は割愛します。

• 過去記事

テーブル作成

エディタタブより以下クエリを実行し、テーブルを作成します。

これらのクエリは、実際に吐かれるメールログJSON形式にしたがっています。

詳しい解説は、AWS公式より出ておりますので参考にしてください。

• JSONSerDe によるマッピングを使って、入れ子の JSON から Amazon Athena のテーブルを作成する | Amazon Web Services ブログ

CREATE EXTERNAL TABLE ses_mail (
  eventType string,
  mail struct<`timestamp`:string,
              source:string,
              sourceArn:string,
              sendingAccountId:string,
              messageId:string,
              destination:string,
              headersTruncated:boolean,
              headers:array<struct<name:string,value:string>>,
              commonHeaders:struct<`from`:array<string>,to:array<string>,messageId:string,subject:string>
              > 
  )           
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://ses-20240319/'

※LOCATION箇所は、各自の環境に合わせてください。

---

---

ケース別分析

いくつか、ケース別にSQLコマンドを紹介します。

バウンスメールを検索

以下で、バウンスしたメールのみ抽出可能です。

SELECT *
FROM ses_mail
WHERE eventType = 'Bounce'

日時を指定して特定のメールを検索

このクエリは、eventTypeがSendかつtimestampが2024-03-18T19:14で始まるすべてのレコードを返します。

%は任意の文字列にマッチするワイルドカードです。

SELECT *
FROM ses_mail
WHERE eventType = 'Send' and mail.timestamp like '2024-03-18T19:14%'

クエリ結果として以下例のようなログが抽出されました。

eventtypeとmailに別れており、上記クエリ、WHERE行を変更することで、より融通の利いたログ分析をおこなうことができます。

#   eventtype   mail
1   Send    {timestamp=2024-03-18T19:14:53.483Z, source=from@example, sourcearn=arn:aws:ses:ap-northeast-1:***********:identity/example, sendingaccountid=************, messageid=***-***-***-***-***-***-***, destination=["success@simulator.amazonses.com"], headerstruncated=false, headers=[{name=From, value=from@example}, {name=To, value=success@simulator.amazonses.com}, {name=Subject, value=test mail}, {name=MIME-Version, value=1.0}, {name=Content-Type, value=multipart/alternative;  boundary="----=_Part_***"}], commonheaders={from=[from@example], to=[success@simulator.amazonses.com], messageid=***-***-***-***-***-***-***, subject=test mail}}

おわりに

Amazon SESのメールログ分析をおこなってみました。

SQLクエリのところが難しい印象です。

使いこなせると便利ですね。