Exempel på sökfrågor för Gmail-loggar i BigQuery

Den här artikeln innehåller exempelfrågor för vanliga Gmail-rapporter som du kan få från BigQuery. Dessa exempel på Gmail-loggsökningar använder Standard SQL.

Läs mer om att söka i BigQuery-data.

Metodtips för Gmail med BigQuery

  • Sök enbart efter de data du behöver. I de här exemplen används en gräns på 1 000 matchningar, men du kan ange en egen gräns.
  • Ange en tidsram för sökfrågorna. En dag är en vanlig tidsram.

Exempelsökningar

Ämnesmatchning

Meddelandeöversikt för upp till 1 000 objekt som matchar ett visst ämne:

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

Mottagarmatchning

Antal distinkta meddelanden för en viss mottagare:

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

Matchning av disposition och mottagare

Meddelandeöversikt för upp till 1 000 objekt som matchar båda:

  • En angiven disposition (Modifiera, Avvisa, Karantänplacera)
  • En angiven mottagare
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

Regelbeskrivning aktiverad

Meddelandeöversikt för upp till 1 000 objekt som aktiverar den angivna regelbeskrivningen:

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

Markerad som spam

Meddelandeöversikt för upp till 1 000 objekt: 

  • Markerat som spam
  • För en angiven mottagare
  • Av alla orsaker
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

Krypteringsprotokoll – ej krypterad

Meddelandeöversikt per krypteringsprotokoll – ej krypterad:

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

Krypteringsprotokoll – enbart TLS

Meddelandeöversikt per krypteringsprotokoll – enbart 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

Matchning av meddelande-id

Meddelandedetalj för ett angivet meddelande-id (inkluderar "<>" runt meddelandets 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 a 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 – avvisa meddelande

Avvisa meddelande:

  • Vilken regel orsakade avvisandet?
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 – ändra meddelande

Ändra meddelande: 

  • Vilken regel orsakade modifieringen?
  • Vilken underkategori av modifiering (exempelvis rubriker, ämne)?
     
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

Karantänplacera meddelande: 

Vilken regel gäller för karantänplacerat meddelande?

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

Sammansatta frågor

Räkna alla meddelanden som fångats av en specifik regel (rule description) under de senaste 30 dagarna:

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"

 

Lista alla meddelanden som tagits emot utan TLS-kryptering under det senaste dygnet:

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

 

Lista de 10 toppdomänerna som mitt konto har utbytt e-post med under de senaste 30 dagarna:

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

 

Var det här till hjälp?
Hur kan vi förbättra den?