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?

Need more help?

Sign in for additional support options to quickly solve your issue

Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
73010
false