Gmail のログに対する BigQuery のクエリ例

ここでは、BigQuery から取得できる一般的な Gmail レポートのクエリ例をご紹介します。Gmail ログのクエリ例では、標準 SQL が使用されています。

詳しくは、BigQuery データのクエリについてのページをご覧ください。

Gmail に対する BigQuery のおすすめの方法

  • 必要なデータに対してのみクエリを実行します。以下の例では一致件数の上限を 1,000 件としていますが、上限は独自に設定できます。
  • クエリの対象となる期間を指定します。通常は 1 日で設定します。

クエリの例

件名の一致

指定された件名と一致する最多 1,000 件のレコードについて、メールの概要を表示します。

SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address,
       message_info.rfc2822_message_id
FROM your_dataset_id.daily_YYYYMMDD
WHERE message_info.subject LIKE "%test%"
LIMIT 1000

受信者の一致

明らかに指定された受信者宛てのメールの件数を返します。

SELECT COUNT(DISTINCT message_info.rfc2822_message_id)
FROM your_dataset_id.daily_YYYYMMDD d
WHERE
  EXISTS(
   SELECT 1 FROM d.message_info.destination WHERE destination.address = "recipient@example.com")

処理および受信者の一致

次の両方と一致する最多 1,000 件のレコードについて、メールの概要を表示します。

  • 指定された処理(変更、拒否、検疫)
  • 指定された受信者
SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE
      destination.address = "recipient@example.com" AND
      EXISTS(SELECT 1 FROM d.message_info.triggered_rule_info ri, ri.consequence
                     WHERE consequence.action = 17)
LIMIT 1000

ルールの説明のトリガー

指定されたルールの説明をトリガーした最多 1,000 件のレコードについて、メールの概要を表示します。

SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE
    EXISTS(SELECT 1 FROM d.message_info.triggered_rule_info ri, ri.consequence
                   WHERE consequence.reason LIKE '%description%')
LIMIT 1000

迷惑メールとして振り分け済み

以下の最大 1,000 件のレコードについて、メールの概要を表示します。

  • 迷惑メールとして振り分け済み
  • 指定された受信者宛て
  • すべての理由
SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.is_spam AND
               destination.address = "recipient@example.com"
LIMIT 1000

暗号化プロトコル - 暗号化なし

暗号化プロトコル(暗号化なし)の条件に一致するメールの概要を表示します。

SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,       
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.connection_info.smtp_tls_state = 0
LIMIT 1000

暗号化プロトコル - TLS のみ

暗号化プロトコル(TLS のみ)の条件に一致するメールの概要を表示します。

SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.connection_info.smtp_tls_state = 1
LIMIT 1000

メッセージ ID の一致

指定されたメッセージ ID のメールの概要を表示します(メッセージ ID は "<>" で囲みます)。

SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
      event_info.success,
      event_info.elapsed_time_usec,
      message_info.subject,
      message_info.source.address as source,
      message_info.source.service as source_service,
      message_info.source.selector as source_selector,
      destination.address as destination,
      destination.service,
      destination.selector as destination_selector,
      message_info.rfc2822_message_id,
      message_info.payload_size,
      message_info.num_message_attachments,
      message_info.connection_info.smtp_tls_state,
      message_info.description
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.rfc2822_message_id = "<message id>"
LIMIT 1000

処理 - メールの拒否

メールの拒否:

  • メール拒否の原因となったルール
SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id,
     (SELECT ARRAY_AGG(consequence.reason)
     FROM d.message_info.triggered_rule_info ri, ri.consequence)
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.rfc2822_message_id = "<message id>" AND
     EXISTS(SELECT 1 FROM d.message_info.triggered_rule_info ri, ri.consequence
                     WHERE consequence.action = 17)
LIMIT 1000

処理 - メールの変更

メールの変更:

  • 変更の原因となったルール
  • 変更のサブカテゴリ(ヘッダー、件名など)
     
SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id,
       (SELECT ARRAY_AGG((consequence.action, consequence.reason))
       FROM d.message_info.triggered_rule_info ri, ri.consequence)
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.rfc2822_message_id = "<message id>" AND
   EXISTS(SELECT 1 FROM d.message_info.triggered_rule_info ri, ri.consequence
                 WHERE consequence.action NOT IN (0, 17, 3))
LIMIT 1000

メールの検疫

メールの検疫の原因となったルール

SELECT TIMESTAMP_MICROS(event_info.timestamp_usec) as timestamp,
       message_info.subject,
       message_info.source.address as source,
       destination.address as destination,
       message_info.rfc2822_message_id,
       (SELECT ARRAY_AGG(consequence.reason)
       FROM d.message_info.triggered_rule_info ri, ri.consequence)
FROM your_dataset_id.daily_YYYYMMDD d, d.message_info.destination
WHERE message_info.rfc2822_message_id = "<message id>" AND
       EXISTS(SELECT 1 FROM d.message_info.triggered_rule_info ri, ri.consequence
                  WHERE consequence.action = 3)
LIMIT 1000

複合クエリ

特定のルール(「ルールの説明」)によって取得された、過去 30 日間のすべてのメールの件数を返します。

SELECT
  COUNT(message_info.rfc2822_message_id) AS message_cnt
FROM
  `your_dataset_id.daily_*`,
UNNEST (message_info.triggered_rule_info) AS triggered_rule
WHERE
  _TABLE_SUFFIX >= FORMAT_DATE("%E4Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
  AND triggered_rule.rule_name LIKE "rule description"

 

過去 1 日に受信した、TLS 暗号化が適用されていないすべてのメールのリストを表示します。

SELECT message_info.subject,
       message_info.rfc2822_message_id
FROM `your_dataset_id.daily_*`
WHERE
    _TABLE_SUFFIX >=
    FORMAT_DATE("%E4Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AND
    message_info.connection_info.smtp_tls_state = 0

 

過去 30 日間にアカウントでメールをやり取りした、上位 10 個のドメインのリストを表示します。

SELECT
  COUNT(message_info.rfc2822_message_id) as message_cnt,
  IF(message_info.is_policy_check_for_sender, 
       REGEXP_EXTRACT(message_info.source.address , "(@.*)"),
       REGEXP_EXTRACT(destination.address , "(@.*)")) AS domain
FROM `your_dataset_id.daily_*` d, d.message_info.destination
WHERE 
    _TABLE_SUFFIX >=
    FORMAT_DATE("%E4Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))
GROUP BY domain
ORDER BY message_cnt desc
LIMIT 10

 

この情報は役に立ちましたか?
改善できる点がありましたらお聞かせください。