Exemplos de consultas para registros de relatórios no BigQuery

Edições compatíveis com este recurso: Frontline Standard e Frontline Plus, 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 pelo nome do projeto e do conjunto de dados.

Saiba mais sobre como consultar dados do BigQuery.

Confira os significados dos campos de registro do Gmail em 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;

Encontrar o 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 por 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 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;

Migração de dados

Os eventos de registro da migração de dados serão lançados gradualmente a partir de 26 de maio de 2025. Os dados de eventos registrados antes dessa data podem não ser precisos.

Todas as ações de configuração realizadas por um administrador

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, status.*, event_type, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND event_type = 'MIGRATION_SETUP' AND email = 'admin@example.com' ORDER BY timestamp desc LIMIT 1000;

Todos os eventos relacionados à migração para um determinado ID de execução

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND event_type = 'MIGRATION' AND data_migration.execution_id =  'execution_id' ORDER BY timestamp desc LIMIT 1000;

Todas as falhas de um ID de execução específico

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND event_type = 'MIGRATION' AND data_migration.execution_id =  'execution_id' AND status.event_status = 'FAILED' ORDER BY timestamp desc LIMIT 1000;

Todos os eventos de um tipo de origem específico, como o Microsoft Exchange Online

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.migration_type = 'Exchange Online Migration' ORDER BY timestamp desc LIMIT 1000;

Todos os eventos de um usuário de origem específico na migração empresarial do Gmail

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.migration_type = 'Enterprise Gmail Migration' AND data_migration.source_identifier like '%user@example.com%' ORDER BY timestamp desc LIMIT 1000;

Todos os eventos de uma mensagem específica com o URI de origem fornecido, como um ID de RFC

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.source_uri = 'rfc822Id' ORDER BY timestamp desc LIMIT 1000;

Todos os eventos de um determinado tipo de dados de origem, como a pasta de e-mails do Exchange Online

SELECT TIMESTAMP_MICROS(time_usec) as timestamp, email, event_type, status.*, event_name, data_migration.* FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' AND data_migration.source_type = 'Exchange Online Email Folder' ORDER BY timestamp desc LIMIT 1000;

Número de eventos agrupados por status e nome

SELECT status.event_status as status, event_name, count(*) AS count FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' GROUP BY 1,2 ORDER BY 1,2 limit 1000;

Número diário de todos os eventos agrupados por status e tipo de objeto

SELECT EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date, status.event_status as status, data_migration.source_type, count(*) AS count FROM api_project_name.dataset_name.activity WHERE record_type = 'data_migration' GROUP BY 1,2,3 ORDER BY 1,2,3 limit 1000;

Drive

Número de itens do Google Drive compartilhados, 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;

ID, 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;

Quais alterações de permissão 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, acima 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.
Essa 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 período para suas consultas. "Um dia" é um intervalo de tempo muito usado.

Correspondência de assunto
Visualização do resumo de mensagens com até mil 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:

Marcado 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 = "<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

Disposição: 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;

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 por 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;

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. Confira 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 AND da 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

Google, Google Workspace e marcas e logotipos relacionados são marcas registradas da Google LLC. Todos os outros nomes de empresas e produtos são marcas registradas das empresas a que estão associados.

Exemplos de consultas para registros de relatórios no BigQuery Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.