自然言語による大規模Gmailログの分析
ClaudeやChatGPTのような生成AIモデルの最近の進歩により、データベースへのクエリが非常に簡単になりました。 特に興味深い点は、**これらのAIモデルは、与えられたDBスキーマ情報に基づいて、自然言語クエリとそれに対応するDBクエリ（SQL）文**を自動的に生成できることだ。 これにより、ユーザーは複雑なクエリを書くことなく、自然言語を使ってデータベースに問い合わせることができる。 したがって、これはRAG(Retrieval-Augmented Generation)技術の部分的な応用と見ることができる。
DBスキーマは、テーブル、フィールド、リレーションシップなどを含むデータベースの構造を定義する。 このスキーマ情報がAIモデルの入力として提供されると、モデルはスキーマを分析し、どのような情報が格納されているかを理解する。 これに基づいて、モデルはユーザーがデータベースについてどのような質問ができるかを推測し、適切な自然言語クエリを生成する。
これにより、ユーザがDBのスキーマを直接分析し、マッチする質問を考えなければならない不便さを大幅に軽減することができます。 また、SQL構文に詳しくないユーザでも、自然言語を使って簡単にDBに問い合わせることができます。
この記事では、BigQueryでGmailのログスキーマを使用して自然言語クエリとSQL文を生成する方法を紹介します。
2023年2月より、GmailのログがWorkspaceのログやレポートと統合され、BigQueryのジョブが簡素化され、すべてのBigQueryデータを一箇所で管理できるようになりました。 すべてのWorkspaceサービスのBigQueryログとレポートは、Google Admin Consoleの同じ場所で管理できます。 GmailのBigQueryビュー、クエリ、スクリプトをWorkspaceのログとレポートに移行することをお勧めします。
BigQuery用GmailログDBスキーマベースの自然言語クエリとBigQuery用SQLの例
重要なポイントは、DBスキーマに関する情報を提供することで、AIがユーザーの視点から意味のある自然言語クエリを生成できることだ。 つまり、直接コードを書かなくても、会話を通じてデータ分析が可能になる。 これは今後さらに発展することが予想され、誰でも簡単にデータベースにアクセスし、洞察を得ることができるようになる。
以下に挙げる自然言語クエリやSQL文は、BigQueryでGmailのログ分析を行う際に、データベース（SQL）の専門家でなくても様々な観点からGmailのログを分析することができます。 これにより、幅広いユーザーが、豊富な技術的知識がなくても、Gmailデータから意味のある洞察を導き出せるようになることが期待されます。
Schema for Gmail logs in BigQuery
Gmail Schema情報（Geminiの場合は画像として、Claude/ChatGPTの場合はPDFとしてアップロードされている）を入力した後、各生成AIに以下の質問をする。
プロンプト Gmailのスキーマ情報に基づいて、10個の自然言語クエリとそれに対応するSQL文を書きました：
自然言語クエリ | SQL statements |
2023年1月1日以降に送信されたスパムメールの送信者メールアドレス、分類タイムスタンプ、およびスパム分類の理由を取得する | SELECT message_info.source.address, message_info.spam_info.classification_timestamp_usec, message_info.spam_info.classification_reason<br>FROM project.dataset.table <br>WHERE message_info.is_spam = true AND event_info.timestamp_usec >= UNIX_MICROS(TIMESTAMP('2023-01-01')) |
Gmail がスパムとして分類した後に解除したメールの RFC2822 Message-ID、スパム分類のタイムスタンプ、アクションの種類、および説明を取得する | SELECT message_info.rfc2822_message_id, message_info.spam_info.classification_timestamp_usec, message_info.action_type, message_info.description<br>FROM project.dataset.table<br>WHERE message_info.is_spam = true AND message_info.action_type = 69 |
過去 30 日間に添付ファイル コンプライアンス ポリシーによって添付ファイルが削除されたメールの送信者、受信者のメールアドレス、削除された添付ファイル名を取得します。 | SELECT message_info.source.address, message_info.destination.address, message_info.triggered_rule_info.string_match.attachment_name<br>FROM project.dataset.table<br>WHERE message_info.triggered_rule_info.rule_type = 16 AND event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 302460601000000 |
S/MIME で暗号化された電子メールの送信者、受信者の電子メール アドレス、および暗号化の成功を取得します。 | SELECT message_info.source.address, message_info.destination.address, message_info.destination.smime_decryption_success<br>FROM project.dataset.table<br>WHERE message_info.smime_content_type = 3 |
悪意のある URL を含むために管理者隔離にルーティングされたメールの件名、時間帯、関連するポリシー名を表示する | SELECT message_info.subject, event_info.timestamp_usec, message_info.triggered_rule_info.rule_name<br>FROM project.dataset.table<br>WHERE message_info.triggered_rule_info.consequence.action = 3 AND message_info.triggered_rule_info.string_match.type = 4 |
30 日以内に TLS を使用せずに送信されたメールの合計サイズと、メールアドレス ドメイン別のメール数を取得します。 | SELECT SUM(message_info.payload_size) as total_size, COUNT() AS num_mails, REGEXP_EXTRACT(message_info.source.address, r'@(.+)') AS domain<br>FROM project.dataset.table<br>WHERE message_info.connection_info.smtp_tls_version IS NULL AND event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 30246060*1000000<br>GROUP BY domain |
メール本文に"@gmail.com "という文字列が含まれ、受信者アドレスに "test "が含まれるメールのMessage-IDおよび受信者を取得する。 | SELECT message_info.rfc2822_message_id, message_info.destination.address<br>FROM project.dataset.table<br>WHERE REGEXP_CONTAINS(message_info.triggered_rule_info.string_match.matched_string, '@gmail\.com') AND REGEXP_CONTAINS(message_info.destination.address, 'test') |
SPF、DKIM、DMARC認証に失敗したメールの送信者メールアドレス、認証結果、アクションタイプを表示します。 | SELECT message_info.source.address, message_info.connection_info.spf_pass, message_info.connection_info.dkim_pass, message_info.connection_info.dmarc_pass, message_info.action_type<br>FROM project.dataset.table<br>WHERE (message_info.connection_info.spf_pass = false OR message_info.connection_info.spf_pass IS NULL)<br>AND (message_info.connection_info.dkim_pass = false OR message_info.connection_info.dkim_pass IS NULL)<br>AND (message_info.connection_info.dmarc_pass = false OR message_info.connection_info.dmarc_pass IS NULL) |
RFC 5322に違反したメールのMessage-ID、送信者、受信者のメールアドレス、件名、説明を取得する。 | SELECT message_info.rfc2822_message_id, message_info.source.address, message_info.destination.address, message_info.subject, message_info.description<br>FROM project.dataset.table<br>WHERE message_info.connection_info.smtp_response_reason = 12 |
過去24時間以内にスパムフィルタのホワイトリストポリシーによって通過したメールの数、送信者、受信者のメールアドレスを取得する | SELECT COUNT() AS num_mails, message_info.source.address, message_info.destination.address<br>FROM project.dataset.table<br>WHERE message_info.spam_info.ip_whitelist_entry IS NOT NULL AND event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 2460601000000<br>GROUP BY message_info.source.address, message_info.destination.address |
過去30日間に処理に1秒以上かかったメールのうち、処理に成功しなかった（success=false）メールのMessage-ID、送信者、受信者、処理時間を取得する。 | SELECT message_info.rfc2822_message_id, message_info.source.address, message_info.destination.address, ROUND(event_info.elapsed_time_usec/1000000, 2) AS elapsed_time_sec<br>FROM project.dataset.table<br>WHERE event_info.success = false AND event_info.elapsed_time_usec >= 1000000 AND event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 302460601000000 |
曜日ごとに処理されたメールの数と平均処理時間を求める。 正常に処理されたメールのみを含む | SELECT EXTRACT(DAYOFWEEK FROM TIMESTAMP_MICROS(event_info.timestamp_usec)) AS day_of_week, COUNT(*) AS num_events, AVG(event_info.elapsed_time_usec)/1000 AS avg_elapsed_time_ms<br>FROM project.dataset.table<br>WHERE event_info.success = true<br>GROUP BY day_of_week<br>ORDER BY day_of_week |
connection_infoで同じclient_ipを持つメールが過去1時間に100回以上処理されたclient_ipのリストと、各IPで処理されたメールの数を取得する。 | SELECT message_info.connection_info.client_ip, COUNT() AS num_mails<br>FROM project.dataset.table<br>WHERE event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 60601000000<br>GROUP BY message_info.connection_info.client_ip<br>HAVING COUNT() >= 100<br>ORDER BY num_mails DESC |
処理に最も時間がかかったメールの上位10%のrfc2822_message_id、期間、送信者の電子メール、受信者の電子メールを取得する(event_info)。 | WITH cte AS (<br> SELECT message_info.rfc2822_message_id, event_info.elapsed_time_usec, message_info.source.address AS sender, message_info.destination.address AS recipient, NTILE(100) OVER (ORDER BY event_info.elapsed_time_usec DESC) AS percentile<br> FROM project.dataset.table<br>)<br>SELECT rfc2822_message_id, ROUND(elapsed_time_usec/1000000,2) AS elapsed_time_sec, sender, recipient<br>FROM cte<br>WHERE percentile <= 10 |
最初のメールが処理された時刻、最後のメールが処理された時刻、その日に処理されたメールの総数を調べる。 | SELECT DATE(TIMESTAMP_MICROS(event_info.timestamp_usec)) AS date, MIN(TIMESTAMP_MICROS(event_info.timestamp_usec)) AS first_mail_time, MAX(TIMESTAMP_MICROS(event_info.timestamp_usec)) AS last_mail_time, COUNT(*) AS num_mails<br>FROM project.dataset.table<br>GROUP BY date |
spam_infoによってスパムと分類されたメールの割合が高い上位10人の送信者のメールアドレスと、過去1週間に処理されたスパムメールの割合を調べる。 | WITH cte AS (<br> SELECT message_info.source.address, COUNT() AS total_mails, COUNTIF(message_info.spam_info.disposition = 2) AS spam_mails<br> FROM project.dataset.table<br> WHERE event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 7246060*1000000<br> GROUP BY message_info.source.address<br>)<br>SELECT address, spam_mails/total_mails AS spam_ratio<br>FROM cte<br>WHERE total_mails >= 10<br>ORDER BY spam_ratio DESC<br>LIMIT 10 |
処理時間が最も長かったメールと最も短かったメールのrfc2822_message_id、payload_size、client_ip、処理時間をそれぞれ取得する。 | WITH cte AS (<br> SELECT message_info.rfc2822_message_id, message_info.payload_size, message_info.connection_info.client_ip, event_info.elapsed_time_usec, ROW_NUMBER() OVER (ORDER BY event_info.elapsed_time_usec DESC) AS rn_desc, ROW_NUMBER() OVER (ORDER BY event_info.elapsed_time_usec ASC) AS rn_asc<br> FROM project.dataset.table<br>)<br>SELECT rfc2822_message_id, payload_size, client_ip, elapsed_time_usec/1000000 AS elapsed_time_sec<br>FROM cte<br>WHERE rn_desc = 1 OR rn_asc = 1 |
平均処理時間が最も長かったconnection_info.ip_geo_cityの上位10都市と、それらの都市で処理されたメールの数を求める | SELECT message_info.connection_info.ip_geo_city, COUNT(*) AS num_mails, AVG(event_info.elapsed_time_usec)/1000 AS avg_elapsed_time_ms<br>FROM project.dataset.table<br>GROUP BY message_info.connection_info.ip_geo_city<br>ORDER BY avg_elapsed_time_ms DESC<br>LIMIT 10 |
過去1ヶ月の曜日別に、スパムに分類されたメールの数と、そのメールの総数に占める割合を調べる | WITH cte AS (<br> SELECT EXTRACT(DAYOFWEEK FROM TIMESTAMP_MICROS(event_info.timestamp_usec)) AS day_of_week, COUNT() AS total_mails, COUNTIF(message_info.spam_info.disposition = 2) AS spam_mails<br> FROM project.dataset.table<br> WHERE event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 30246060*1000000<br> GROUP BY day_of_week<br>)<br>SELECT day_of_week, spam_mails, total_mails, spam_mails/total_mails AS spam_ratio<br>FROM cte<br>ORDER BY day_of_week |
過去1ヶ月で最もメール処理が混雑した日上位10日と、各日の平均処理時間および正常に処理されたメールの割合を調べる。 | WITH cte AS (<br> SELECT DATE(TIMESTAMP_MICROS(event_info.timestamp_usec)) AS date, COUNT() AS num_mails, AVG(event_info.elapsed_time_usec)/1000 AS avg_elapsed_time_ms, COUNTIF(event_info.success=true)/COUNT() AS success_ratio<br> FROM project.dataset.table<br> WHERE event_info.timestamp_usec >= UNIX_MICROS(CURRENT_TIMESTAMP()) - 302460601000000<br> GROUP BY date<br>)<br>SELECT date, num_mails, avg_elapsed_time_ms, success_ratio<br>FROM cte<br>ORDER BY num_mails DESC<br>LIMIT 10 |
(** 注：これらの SQL 文は Claude3 AI を使用して生成されたものです。 SQL文に誤りがある可能性があります。 参考にしてください。）
'project.dataset.gmail_logs'は、Gmailのログデータが格納されていると仮定した場合の、仮想的なBigQueryテーブル名です。 実際には、この名前のテーブルは存在しないかもしれません。
GmailスキーマはGmailログデータの構造と形式を記述したドキュメントですが、実際のBigQueryテーブル名やデータが格納されている場所に関する情報は提供していません。 したがって、このスキーマを使用してクエリを記述する場合は、データが格納されている実際のBigQueryテーブル名に置き換える必要があります。
例えば、Gmailのログデータが'my_project.gmail_logs.data_2023'テーブルに格納されている場合、上記のSQL文では'project.dataset.gmail_logs'を'my_project.gmail_logs.data_2023'に変更する必要があります。
---------------------------------
"Claude 3 AIの活用:文章作成よ びビジネス業務" https://bit.ly/3wAY3mX
コメント
コメントを投稿