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.
Exemplo de consultas
ContasNú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;
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;
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;
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
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 = "recipient@example.com")
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 = "recipient@example.com" 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 = "recipient@example.com"
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;
Os 100 eventos de registro do Gmail mais recentes com pelo menos um rótulo de classificação associado à mensagem de e-mail
SELECT
resource_details[OFFSET(0)].id AS MESSAGE_ID,
gmail.message_info.subject AS SUBJECT,
gmail.event_info.mail_event_type AS MAIL_EVENT_TYPE,
gmail.message_info.source.address AS SENDER,
resource_details[OFFSET(0)].applied_labels AS LABELS
FROM workspace_audit_logs.activity
WHERE gmail.event_info.mail_event_type > 0 and ARRAY_LENGTH(resource_details) > 0
ORDER by time_usec desc
LIMIT 100;
Todos os eventos de registro disponíveis para uma mensagem de e-mail específica
SELECT
gmail.event_info,
gmail.message_info,
resource_details
FROM workspace_audit_logs.activity
WHERE gmail.message_info.rfc2822_message_id = "<XYZ>"
ORDER by time_usec desc;
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
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 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;
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;
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;
O esquema pode mudar. Veja a lista completa e atualizada de parâmetros e campos na documentação da API Reports.
É 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.
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.%")
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;