Example queries for Gmail logs in BigQuery

This article contains example queries for common Gmail reports you can get from BigQuery. These Gmail log query examples use Standard SQL.

Learn more about querying BigQuery data.

Best practices for Gmail with BigQuery

Query only for the data you need. These examples have a limit of 1,000 matches but you can set your own limit.
Set a time frame for your queries. One day is a typical time frame.

Example queries

Subject match

Message summary view for up to 1,000 records matching a specified subject:

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

Recipient match

Count number of distinct messages for a specified recipient:

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")

Disposition and recipient match

Message summary view for up to 1,000 records matching both:

A specified disposition (Modify, Reject, Quarantine)
A specified recipient

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

Rule description triggered

Message summary view for up to 1,000 records, which triggered specified rule description:

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

Marked as spam

Message summary view for up to 1,000 records:

Marked as spam
For a specified recipient
For all reasons

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

Encryption protocol—not encrypted

Message summary view by encryption protocol—not encrypted:

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

Encryption protocol—TLS only

Message summary view by encryption protocol—TLS only:

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

Message ID match

Message detail view for a given message ID (include “<>” around the message 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

Disposition—Reject message

Reject message:

Which rule caused rejection?

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

Disposition—Modify message

Modify message:

Which rule caused the modification?
What modification subcategory (for example, headers or subject)?

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

Quarantine message:

Which rule quarantined a message?

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

Compound queries

Count all messages caught by a specific rule (named "rule description") in the last 30 days:

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"

List all messages that were received without TLS encryption in the last day:

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

List the top 10 domains my account exchanged mail within the last 30 days:

SELECT
COUNT(DISTINCT 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

Was this helpful?

How can we improve it?