Exemplos de consultas para registros do Gmail no BigQuery

Edições compatíveis com este recurso: Frontline Standard; Enterprise Standard e Enterprise Plus; Education Standard e Education Plus; Enterprise Essentials Plus.  Comparar sua edição

Este artigo contém exemplos de consultas para relatórios comuns do Gmail disponíveis no BigQuery. Estes exemplos de consultas pressupõe o uso de SQL legado. Substitua api_project_name.dataset_name pelos nomes do seu projeto e do conjunto de dados.

Saiba mais sobre como consultar dados do BigQuery.

Veja os significados dos campos de registro do Gmail no artigo Esquema para registros de atividade do Gmail no BigQuery.

Exemplos de consultas

Contas

Número de contas delegadas e de administradores e número de contas desativadas, bloqueadas e suspensas organizadas por data

SELECT date,
accounts.num_locked_users,
accounts.num_disabled_accounts,
accounts.num_delegated_admins,
accounts.num_super_admins,
accounts.num_suspended_users,
accounts.num_users
FROM api_project_name.dataset_name.usage
WHERE accounts.num_users IS NOT NULL
ORDER BY date ASC;

Administradores

Eventos mais frequentes realizados por um administrador.

SELECT count(*) as admin_actions, event_name
FROM api_project_name.dataset_name.activity
WHERE email IN (
  SELECT user_email
  FROM api_project_name.dataset_name.usage
  WHERE accounts.is_super_admin = TRUE
)
GROUP BY 2
ORDER BY 1 DESC;

Número de superadministradores em um determinado domínio.

SELECT COUNT(DISTINCT user_email) as number_of_super_admins, date
FROM api_project_name.dataset_name.usage
WHERE accounts.is_super_admin = TRUE
GROUP BY 2
ORDER BY 2 DESC;

Agenda

Somente SQL padrão

A proporção de usuários ativos por dia em relação ao total de usuários ativos no Google Agenda por 30 dias.Este exemplo consulta diversas tabelas.

Usuários ativos por dia

SELECT date, calendar.num_1day_active_users
FROM api_project_name.dataset_name.usage
WHERE calendar.num_1day_active_users IS NOT NULL
ORDER BY date DESC

Usuários ativos nos últimos 30 dias

SELECT date, calendar.num_30day_active_users
FROM api_project_name.dataset_name.usage
WHERE calendar.num_30day_active_users IS NOT NULL
ORDER BY date DESC;

Número de eventos da agenda organizados por tipo:

SELECT COUNT(DISTINCT calendar.calendar_id) AS count, event_name
FROM api_project_name.dataset_name.activity
WHERE calendar.calendar_id IS NOT NULL
GROUP BY 2 ORDER BY 1 DESC;

Drive

Número de itens do Google Drive compartilhados ou agrupados por método de compartilhamento.

SELECT COUNT(DISTINCT drive.doc_id) AS count, drive.visibility
FROM api_project_name.dataset_name.activity
WHERE drive.doc_id IS NOT NULL
GROUP BY 2 ORDER BY 1 DESC;

Código, título, proprietário e tipo do arquivo. Arquivos que foram compartilhados externamente em um determinado período.

SELECT TIMESTAMP_MICROS(time_usec) AS date, drive.doc_id, drive.doc_title,
drive.owner, drive.doc_type
FROM api_project_name.dataset_name.activity
WHERE drive.visibility = "shared_externally"
ORDER BY 1 DESC
LIMIT 100;

Alterações nas permissões de compartilhamento e o resultado delas. Ajuda a identificar as alterações de permissão que mudaram a visibilidade dos arquivos.

SELECT TIMESTAMP_MICROS(time_usec) AS date, drive.doc_title,
drive.visibility_change,drive.old_visibility, drive.visibility,
FROM api_project_name.dataset_name.activity
WHERE record_type = "drive"
AND drive.old_visibility IS NOT NULL
AND drive.old_visibility != "unknown";

Tipos de eventos organizados por tipo de arquivo. Útil para o relatório de adoção por tipo de arquivo.

SELECT drive.doc_type, event_type, count(*) 
FROM  api_project_name.dataset_name.activity
WHERE record_type = "DRIVE"
GROUP by 1,2 ORDER BY 3 desc;

O tipo de evento e o nome de cada drive compartilhado.

SELECT drive.shared_drive_id, event_type, event_name, record_type,
count(distinct drive.doc_id) AS count
FROM api_project_name.dataset_name.activity
WHERE record_type = "drive"
AND drive.shared_drive_id IS NOT NULL
GROUP BY 1,2,3,4 ORDER BY 5 DESC;

Informações sobre os usuários fora do domínio.

SELECT email, event_name, count(*) AS count
FROM api_project_name.dataset_name.activity
WHERE email != ""
AND email NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

As alterações de permissão que foram concedidas a usuários externos e quando elas ocorreram.

SELECT drive.target_user, event_name, count(*) AS count
FROM api_project_name.dataset_name.activity
WHERE drive.target_user IS NOT NULL
AND drive.target_user NOT LIKE "%mydomain.com%"
GROUP BY 1,2 ORDER BY 3 DESC;

Informações sobre monitoramento de armazenamento

Útil para a criação de relatórios sobre usuários que consomem mais de X de armazenamento no Drive, com um limite definido pela cláusula AND accounts.drive_used_quota_in_mb > 0.

Essa consulta pode ser definida como uma consulta programada ou, por exemplo, pode ser chamada periodicamente usando a API.

SELECT date,
user_email,
accounts.drive_used_quota_in_mb,
FROM api_project_name.dataset_name.usage
WHERE accounts.drive_used_quota_in_mb IS NOT NULL
AND accounts.drive_used_quota_in_mb > 0
AND user_email != ""
AND date = CAST(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AS STRING)
ORDER BY 3,1 DESC;

Observações:

• Esse valor pode ser modificado para corresponder ao filtro que está sendo configurado pelo cliente. Por exemplo, mais de 15 GB: AND accounts.drive_used_quota_in_mb > 15000
• A comparação de datas com CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) permite fazer uma comparação com o formato disponível do valor date.

• Esta consulta também é aplicável ao Gmail, onde podemos encontrar um valor semelhante: accounts.gmail_used_quota_in_mb

Gmail

Práticas recomendadas para o Gmail com o BigQuery

• Consulte apenas os dados necessários. Estes exemplos usam um limite de mil correspondências, mas você pode definir seu próprio limite.
• Defina um intervalo de tempo para suas consultas. "Um dia" é um intervalo de tempo muito usado.

Correspondência de assunto
Visualização do resumo de mensagens de até 1.000 registros que correspondem a um assunto especificado.

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

Correspondência de destinatário
Contagem do número de mensagens distintas para um destinatário especificado

SELECT COUNT(DISTINCT gmail.message_info.rfc2822_message_id)
FROM your_dataset_id.activity d
WHERE
  EXISTS(
   SELECT 1 FROM d.gmail.message_info.destination WHERE destination.address = "[e-mail]")

Correspondência de disposição e destinatário

Visualização do resumo de mensagens com até mil registros que correspondem a:

• uma disposição específica (modificar, rejeitar, colocar em quarentena);
• um destinatário específico.

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE
     destination.address = "[e-mail]" AND
     EXISTS(SELECT 1 FROM d.gmail.message_info.triggered_rule_info ri, ri.consequence
          WHERE consequence.action = 17)
LIMIT 1000

Descrição da regra acionada
Visualização do resumo de mensagens de até 1.000 registros, o que acionou a descrição de regra especificada.

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

Marcado como spam
Visualização do resumo de mensagens com até mil registros: 

• Mensagem marcada como spam
• para um destinatário especificado;
• por todos os motivos.

SELECT TIMESTAMP_MICROS(gmail.event_info.timestamp_usec) as timestamp,
     gmail.message_info.subject,
     gmail.message_info.source.address as source,
     destination.address as destination,
     gmail.message_info.rfc2822_message_id
FROM your_dataset_id.activity d, d.gmail.message_info.destination
WHERE gmail.message_info.is_spam AND
          destination.address = "[e-mail]"
LIMIT 1000

Protocolo de criptografia: não criptografado
Visualização do resumo de mensagens por protocolo de criptografia: não criptografado

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

Protocolo de criptografia: somente TLS
Visualização do resumo de mensagens por protocolo de criptografia: somente TLS

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

Correspondência de ID de mensagem
Visualização de detalhes de mensagem de um determinado ID de mensagem (inclua "<>" ao redor do ID da mensagem)

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

Disposição: rejeitar mensagem
Rejeitar mensagem:

• Qual regra causou a rejeição?

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

Descartar: modificar mensagem
Modificar mensagem: 

• Qual regra causou a modificação?
• Qual é a subcategoria da modificação (por exemplo, cabeçalhos ou assunto)?

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

Colocar mensagem em quarentena
Qual regra colocou uma mensagem em quarentena?

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

Consultas compostas
Conta todas as mensagens identificadas por uma regra específica (pela descrição da regra) nos últimos 30 dias:

SELECT
  COUNT(gmail.message_info.rfc2822_message_id) AS message_cnt
FROM
  `your_dataset_id.activity`,
UNNEST (gmail.message_info.triggered_rule_info) AS triggered_rule
WHERE
  _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND triggered_rule.rule_name LIKE "rule description"

Lista todas as mensagens recebidas sem a criptografia TLS no último dia:

SELECT gmail.message_info.subject,
    gmail.message_info.rfc2822_message_id
FROM `your_dataset_id.activity`
WHERE
    _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND
    gmail.message_info.connection_info.smtp_tls_state = 0

Lista os 10 domínios principais que trocaram e-mails com minha conta nos últimos 30 dias:

SELECT
  COUNT(DISTINCT gmail.message_info.rfc2822_message_id) as message_cnt,
  IF(gmail.message_info.is_policy_check_for_sender, 
       REGEXP_EXTRACT(gmail.message_info.source.address , "(@.*)"),
       REGEXP_EXTRACT(destination.address , "(@.*)")) AS domain
FROM `your_dataset_id.activity` d, d.gmail.message_info.destination
WHERE 
    _PARTITIONTIME >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY domain
ORDER BY message_cnt desc
LIMIT 10

Proporção de usuários ativos por dia em relação ao total de usuários ativos no Gmail por 30 dias.

Usuários ativos por dia:

SELECT date,
gmail.num_1day_active_users
FROM api_project_name.dataset_name.usage
WHERE gmail.num_1day_active_users > 0
ORDER BY 1 DESC;

Usuários ativos nos últimos 7 dias:

SELECT date,
gmail.num_7day_active_users
FROM api_project_name.dataset_name.usage
WHERE gmail.num_7day_active_users > 0
ORDER BY 1 DESC;

Usuários ativos nos últimos 30 dias:

SELECT date,
gmail.num_30day_active_users
FROM api_project_name.dataset_name.usage
WHERE gmail.num_30day_active_users > 0
ORDER BY 1 DESC;

Grupos

Alterações de participantes do Grupo do Google e o comportamento dos usuários.

SELECT TIMESTAMP_MICROS(time_usec) AS date,
  event_name,
  admin.group_email,
  event_type,
  email,
  record_type,
  admin.user_email,
  admin.new_value,
  admin.old_value,
  admin.setting_name
FROM project_name.dataset_name.activity
WHERE `admin`.group_email IS NOT NULL
AND CONCAT(TIMESTAMP_MICROS(time_usec)) LIKE "%YYYY-MM-DD%"
ORDER BY 1 DESC
LIMIT
  1000

Se você quiser ter um carimbo de data/hora AAAA-MM-DD, substitua o primeiro elemento de instrução SELECT por:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

As datas podem ser filtradas na cláusula WHERE de uma das seguintes maneiras:

SELECT TIMESTAMP_MICROS(time_usec) AS date,
  event_name,
  admin.group_email,
  event_type,
  email,
  record_type,
  admin.user_email,
  admin.new_value,
  admin.old_value,
  admin.setting_name
FROM project_name.dataset_name.activity
WHERE `admin`.group_email IS NOT NULL
AND EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) > "2020-06-30"
AND EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) < "2020-08-31"
ORDER BY 1 DESC
LIMIT
  1000

SELECT TIMESTAMP_MICROS(time_usec) AS date,
  event_name,
  admin.group_email,
  event_type,
  email,
  record_type,
  admin.user_email,
  admin.new_value,
  admin.old_value,
  admin.setting_name
FROM project_name.dataset_name.activity
WHERE `admin`.group_email IS NOT NULL
AND TIMESTAMP_MICROS(time_usec) > TIMESTAMP("2020-07-21")
AND TIMESTAMP_MICROS(time_usec) < TIMESTAMP("2020-07-23")
ORDER BY 1 DESC
LIMIT
  1000

Google Meet

Número de videochamadas e total de minutos de videochamada por data

SELECT date, meet.num_calls, meet.total_call_minutes
FROM `api_project_name.dataset_name.usage`
WHERE meet.num_calls IS NOT NULL
ORDER BY date ASC

Usuários ativos por dia

SELECT date, meet.num_1day_active_users
FROM `api_project_name.dataset_name.usage`
WHERE meet.num_1day_active_users IS NOT NULL
ORDER BY date DESC

Usuários ativos nos últimos 30 dias

SELECT date, meet.num_30day_active_users
FROM `api_project_name.dataset_name.usage`
WHERE meet.num_30day_active_users IS NOT NULL
ORDER BY date DESC

Regras

Regras de DLP acionadas por nome, o app correspondente e as ações.

SELECT TIMESTAMP_MICROS(time_usec) AS date, rules.rule_name, rules.application,
rules.resource_title, rules.actions, rules.resource_owner_email,
rules.data_source, rules.matched_trigger
FROM api_project_name.dataset_name.activity
WHERE rules.rule_name IS NOT NULL
ORDER BY 1 DESC LIMIT 1000;

Tokens

Número de vezes em que um app de terceiros teve permissão para acessar o Google Drive.

SELECT token.client_id, scope, token.app_name, count(*) AS count
FROM api_project_name.dataset_name.activity
LEFT JOIN UNNEST(token.scope) AS scope
WHERE scope LIKE "%drive%"
GROUP BY 1,2,3 ORDER BY 4 DESC;

Tentativas de login no Admin Console

Informações detalhadas sobre logins com falha no Google Admin Console.

SELECT TIMESTAMP_MICROS(time_usec) AS date, email, ip_address,
event_name, login.login_type, login.login_failure_type
FROM api_project_name.dataset_name.activity
WHERE login.login_type IS NOT NULL
AND login.login_failure_type IS NOT NULL
AND event_type = "login"
ORDER BY date DESC;

Esquema para tabelas de uso

O esquema pode mudar. Veja a lista completa e atualizada de parâmetros e campos na documentação da API Reports. 

Filtrar por data

É possível filtrar por data ao consultar as tabelas activity ou usage. As duas têm formatos distintos ao apresentar a data:

• A tabela activity armazena os carimbos de data/hora em microssegundos do Unix. Esse é um valor inteiro (um número) que pode ser convertido em uma data com a função TIMESTAMP_MICROS().
• Como a tabela usage exibe os valores de date com um formato de data, ela não é necessária.

É possível filtrar por uma data específica (ou período) nas duas tabelas usando um dos métodos a seguir.

Tabela de atividades

Para filtrar por uma data específica com a estrutura Unix Micros (tabela activity), é possível definir a cláusula WHERE e a função TIMESTAMP() para fazer uma comparação simples com os operadores maior que (>) e menor que (<):

SELECT TIMESTAMP_MICROS(time_usec) as date, record_type
FROM api_project_name.dataset_name.activity
WHERE TIMESTAMP_MICROS(time_usec) > TIMESTAMP("2020-07-01")
AND TIMESTAMP_MICROS(time_usec) < TIMESTAMP("2020-07-07")
ORDER BY 1 DESC LIMIT 1000

O conceito aqui é colocar limites no valor de entrada time_usec comparando o valor de retorno da função TIMESTAMP_MICROS() ao valor de retorno da função TIMESTAMP() com uma data adicionada como parâmetro de tipo string. Isso segue os padrões em Funções de carimbo de data/hora no SQL padrão e usa operadores de comparação simples (>) e (<) com a extensão ANDda cláusula WHERE para fechar uma janela de tempo em particular.

Tabela de uso

SELECT date, meet.num_calls,
FROM api_project_name.dataset_name.usage
WHERE meet.num_calls IS NOT NULL
AND TIMESTAMP(date) > TIMESTAMP("2020-07-01")
AND TIMESTAMP(date) < TIMESTAMP("2020-07-07")
ORDER BY date DESC;

É possível transferir o valor date do tipo string presente na tabela para a função TIMESTAMP() e usar os operadores de comparação (>) e (<) como no primeiro exemplo.

 

Filtrar por aliases de domínio e subdomínios: excluir e incluir

Para incluir ou excluir determinados domínios dos resultados da consulta, aplique um filtro ao endereço de e-mail na cláusula WHERE usando caracteres curinga (%) para filtrar os domínios.

A forma de usar as instruções AND ou OR depende se você quer fazer a filtragem (excluir) ou apenas incluir determinados resultados.

Excluir determinados domínios dos resultados

WHERE email NOT LIKE ("%@sub.%")
AND email NOT LIKE ("%@test.%")

Apenas incluir determinados domínios nos resultados

WHERE email LIKE ("%@sub.%")
OR email LIKE ("%@test.%")

Registros de auditoria de regras

Use esta consulta para rastrear tentativas dos usuários de compartilhar dados confidenciais.

SELECT TIMESTAMP_MICROS(time_usec) AS Date,
rules.resource_owner_email AS User,
rules.rule_name AS ruleName,
rules.rule_type AS ruleType,
rules.rule_resource_name AS ruleResourceName,
rules.resource_id AS resourceId,
rules.resource_title AS resourceTitle,
rules.resource_type AS resourceType,
rules.resource_owner_email AS resourceOwner,
CAST(recipients AS STRING) AS Recipients,
rules.data_source AS dataSource,
rules.actor_ip_address AS actorIpAddress,
rules.severity AS severity,
rules.scan_type AS scanType,
rules.matched_trigger AS matchedTriggers,
detect.display_name AS matchedDetectorsName,
detect.detector_id AS matchedDetectorsId,
detect.detector_type AS matchedDetectorsType,
triggers.action_type AS triggeredActions,
suppressors.action_type AS suppressedActions,
FROM api_project_name.dataset_name.activity
LEFT JOIN UNNEST(rules.resource_recipients) as recipients
LEFT JOIN UNNEST(rules.matched_detectors) as detect
LEFT JOIN UNNEST(rules.triggered_actions) as triggers
LEFT JOIN UNNEST(rules.suppressed_actions) as suppressors
WHERE rules.rule_name IS NOT NULL
AND triggers.action_type != "ALERT"
ORDER BY 1 DESC
LIMIT 1000;

Tema relacionado

• Documentação do BigQuery