
S3に配信したAmazon SESのメールログをDuckDBにて簡単分析
こんにちは、こーへいです。
今回は、S3に配信したAmazon SESのメールログをDuckDBにて簡単に分析する方法を紹介します。
DuckDBとは
最近社内でDuckDBの話題がちょくちょく上がっているので気になっていました。
基本的にS3上でログを分析する際は、Amazon Athenaを使用することが多いのですが、個人的にはテーブル作成などの設定の手間や何より料金がかかることが、Athenaを使用する心理的ハードルとなっていました。
そこでDuckDBを使用するとローカルからS3に対して、簡易的にそして無料でログ分析できると聞き、こりゃ使わなイカンということで、今回はAmazon SESのメールログを対象にDuckDBで分析していきます。
やってみた
事前準備
DuckDBのインストール
上記を参考にインストールしてください。
curl https://install.duckdb.org | sh
自分はMacを使用していたので、上記コマンドでインストールしました。
インストールされていますね、コマンド1つで完了して本当に助かります。
Amazon SESのメールログを配信する環境を準備する
Amazon SESでは標準でログを取得することができません。
ログ取得する場合は、SESの設定セットによりメールイベントをData Firehose経由にてS3にログ配信することが多いです。
※CloudWatch Logsに配信したい場合は、Amazon SNSとLambdaを利用するパターンもありますが、個人的にあまりLambdaは保守が必要なので使いたくありません。
上記記事等を参考に、S3にログを配信する環境を準備してください。
テスト配信してログを貯める
を参考に、色んなイベントタイプのメールをテスト配信することが可能です。
今回は検証用のログをこちらの機能を使用して貯めていきます。
※設定セットの設定を忘れないように注意
大体メールログを確認する場合は、バウンスや苦情のイベントタイプを調べたい時が多いかと思いますので、それらのイベントタイプのテスト配信を行なっておきます。
とりあえず以下のログを貯めました。今回はログの時間帯をある程度絞りつつも複数ファイルに跨ってログ分析をするケースを想定しています。
そのため、2025年6月19日の16時帯と17時帯の2ファイル用意しました。
s3://[bucket名]/2025/06/19/16/PUT-S3-yzbU3-1-2025-06-19-16-21-30-760a70a3-8875-425b-809f-c0e375536ffc
{"eventType":"Delivery","mail":{"timestamp":"2025-06-19T07:21:29.360Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["success@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"success@simulator.amazonses.com"},{"name":"Subject","value":"配信の成功"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["success@simulator.amazonses.com"],"messageId":"----","subject":"配信の成功"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:outgoing-tls-version":["TLSv1.3"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."],"ses:outgoing-ip":["..."]}},"delivery":{"timestamp":"2025-06-19T07:21:30.033Z","processingTimeMillis":673,"recipients":["success@simulator.amazonses.com"],"smtpResponse":"250 Ok","remoteMtaIp":"...","reportingMTA":"-.smtp-out.ap-northeast-1.amazonses.com"}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T07:21:40.657Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T07:21:29.360Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["success@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"success@simulator.amazonses.com"},{"name":"Subject","value":"配信の成功"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["success@simulator.amazonses.com"],"messageId":"----","subject":"配信の成功"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Bounce","bounce":{"feedbackId":"----","bounceType":"Permanent","bounceSubType":"General","bouncedRecipients":[{"emailAddress":"bounce@simulator.amazonses.com","action":"failed","status":"5.1.1","diagnosticCode":"smtp; 550 5.1.1 As requested: user unknown bounce@simulator.amazonses.com"}],"timestamp":"2025-06-19T07:21:41.717Z","remoteMtaIp":"...","reportingMTA":"dns; ***-.smtp-out.ap-northeast-1.amazonses.com"},"mail":{"timestamp":"2025-06-19T07:21:40.657Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Complaint","complaint":{"feedbackId":"----","complaintSubType":null,"complainedRecipients":[{"emailAddress":"complaint@simulator.amazonses.com"}],"timestamp":"2025-06-19T07:21:55.483Z","userAgent":"Amazon SES Mailbox Simulator","complaintFeedbackType":"abuse","arrivalDate":"2025-06-19T07:21:55.483Z"},"mail":{"timestamp":"2025-06-19T07:21:52.166Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Delivery","mail":{"timestamp":"2025-06-19T07:21:52.166Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:outgoing-tls-version":["TLSv1.3"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-."],"ses:outgoing-ip":["..."]}},"delivery":{"timestamp":"2025-06-19T07:21:55.936Z","processingTimeMillis":3770,"recipients":["complaint@simulator.amazonses.com"],"smtpResponse":"250 Ok","remoteMtaIp":"...","reportingMTA":"-.smtp-out.ap-northeast-1.amazonses.com"}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T07:21:52.166Z","source":"@.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".jp"],"ses:sender-identity":["@.jp"],"ses:caller-identity":["-.****"]}},"send":{}}
s3://[bucket名]/2025/06/19/17/PUT-S3-yzbU3-1-2025-06-19-17-05-12-6af6b705-fa9e-4d68-a0d7-29b69a525c28
{"eventType":"Send","mail":{"timestamp":"2025-06-19T08:05:11.104Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Complaint","complaint":{"feedbackId":"----","complaintSubType":null,"complainedRecipients":[{"emailAddress":"complaint@simulator.amazonses.com"}],"timestamp":"2025-06-19T08:05:14.812Z","userAgent":"Amazon SES Mailbox Simulator","complaintFeedbackType":"abuse","arrivalDate":"2025-06-19T08:05:14.812Z"},"mail":{"timestamp":"2025-06-19T08:05:11.104Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Bounce","bounce":{"feedbackId":"----","bounceType":"Permanent","bounceSubType":"General","bouncedRecipients":[{"emailAddress":"bounce@simulator.amazonses.com","action":"failed","status":"5.1.1","diagnosticCode":"smtp; 550 5.1.1 As requested: user unknown bounce@simulator.amazonses.com"}],"timestamp":"2025-06-19T08:05:26.616Z","remoteMtaIp":"...","reportingMTA":"dns; ***-.smtp-out.ap-northeast-1.amazonses.com"},"mail":{"timestamp":"2025-06-19T08:05:25.593Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}}}
{"eventType":"Send","mail":{"timestamp":"2025-06-19T08:05:25.593Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["bounce@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"bounce@simulator.amazonses.com"},{"name":"Subject","value":"バウンス3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["bounce@simulator.amazonses.com"],"messageId":"----","subject":"バウンス3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."]}},"send":{}}
{"eventType":"Delivery","mail":{"timestamp":"2025-06-19T08:05:11.104Z","source":"@.co.jp","sourceArn":"arn:aws:ses:ap-northeast-1::identity/@.co.jp","sendingAccountId":"","messageId":"----","destination":["complaint@simulator.amazonses.com"],"headersTruncated":false,"headers":[{"name":"From","value":"@.co.jp"},{"name":"To","value":"complaint@simulator.amazonses.com"},{"name":"Subject","value":"苦情3"},{"name":"MIME-Version","value":"1.0"},{"name":"Content-Type","value":"multipart/alternative; boundary="----=Part_.""}],"commonHeaders":{"from":["@.co.jp"],"to":["complaint@simulator.amazonses.com"],"messageId":"----","subject":"苦情3"},"tags":{"ses:source-tls-version":["TLSv1.3"],"ses:operation":["SendEmail"],"ses:configuration-set":["ses-bounce-log"],"ses:recipient-isp":["AmazonSES"],"ses:outgoing-tls-version":["TLSv1.3"],"ses:source-ip":["..."],"ses:from-domain":[".co.jp"],"ses:sender-identity":["@.co.jp"],"ses:caller-identity":["-."],"ses:outgoing-ip":["..."]}},"delivery":{"timestamp":"2025-06-19T08:05:15.288Z","processingTimeMillis":4184,"recipients":["complaint@simulator.amazonses.com"],"smtpResponse":"250 Ok","remoteMtaIp":"...","reportingMTA":"*-.smtp-out.ap-northeast-1.amazonses.com"}}
DuckDBで分析する
上記を参考にS3と連携するための拡張機能「httpfs」をインストールしてロードします。
D INSTALL httpfs;
D LOAD httpfs;
credential_chainプロバイダーを使用して、認証情報を取得します(これを使用することでaws sts get-caller-identity
で表示される認証情報でログ分析できるようになります)。
D CREATE SECRET secret2 (
TYPE s3,
PROVIDER credential_chain
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘
では、ログ分析していきましょう。
基本的にはeventType
がBounce
やComplaint
に対するログの確認が多いと思いますのでそれをメインで確認しましょう。
eventType
がComplaint
のものをタイムスタンプの降順で検索します(必要な情報をSELECTなりして下さい)。
FROM read_json_auto('s3://[bucket名]/2025/06/19/*/*')
WHERE eventType = 'Complaint'
ORDER BY complaint.timestamp DESC;
eventType
がBounce
のものをタイムスタンプの降順で検索します(必要な情報をSELECTなりして下さい)。
FROM read_json_auto('s3://[bucket名]/2025/06/19/*/*')
WHERE eventType = 'Bounce'
ORDER BY complaint.timestamp DESC;
サクッと分析できて最高ですね。
まとめ
DuckDBを使用することで手軽にS3のデータを検索することができましたね。
他にもVPCフローログやWAFのアクセスログで試している記事がありますので、ぜひご参照ください。
[おまけ]MCPサーバーを活用して自然言語でクエリする
今ならMCPサーバーを試さないわけにはいかないでしょう。
こちらの記事でMCPサーバーをセットアップして自然言語でクエリしてみます。
s3://[bucket名]/2025/06/19/のログにて、eventType`が`Bounce`のものをタイムスタンプの降順で検索してください
認証情報のエラーや、FROMのエラーなど紆余曲折を経て検索できました!
※ファイル名がs3://[bucket名]/2025/06/19/17/PUT-S3-yzbU3-1-2025-06-19-17-05-12-6af6b705-fa9e-4d68-a0d7-29b69a525c28
という構造なので、s3://[bucket名]/2025/06/19/*
ではなくs3://[bucket名]/2025/06/19/*/*
で検索しないとエラーが出る。
.
.
.
流石にこれくらいなら普通にクエリ検索した方が楽か?まあいい感じにMCPサーバーを活用していきましょう。
終わり!