Ejemplos de consultas de registros de informes en BigQuery

Ediciones compatibles con esta función: Frontline Standard y Frontline Plus; Enterprise Standard y Enterprise Plus; Education Standard y Education Plus; Enterprise Essentials Plus. Comparar tu edición

En este artículo, se incluyen ejemplos de consultas para los informes comunes que puedes obtener de BigQuery. En estos ejemplos de consultas, se supone que se usa SQL heredado. Reemplaza api_project_name.dataset_name por el nombre de tu proyecto y el nombre del conjunto de datos.

Obtén más información para consultar datos de BigQuery.

Para conocer los campos de los registros de Gmail y sus significados, consulta Esquema de los registros de actividad de Gmail en BigQuery.

Ejemplos de consultas

Cuentas

Cantidad de cuentas de administrador y delegadas, y cantidad de cuentas inhabilitadas, bloqueadas y suspendidas por fecha

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 más frecuentes que realiza un 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;

Cómo encontrar la cantidad de administradores avanzados en un dominio determinado

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;

Calendario

Solo SQL estándar

Es la proporción de usuarios activos por día con respecto a los usuarios activos en 30 días en Calendario de Google. En este ejemplo, se realizan consultas en varias tablas.

Usuarios activos por día

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

Usuarios activos durante 30 días

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;

Cantidad de eventos del calendario 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;

Migración de datos

Los eventos de registro de migración de datos se lanzarán gradualmente a partir del 26 de mayo de 2025. Es posible que los datos de eventos de registro anteriores a esa fecha no sean precisos.

Todas las acciones de configuración que realiza un 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 los eventos relacionados con la migración para un ID de ejecución en particular

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 las fallas para un ID de ejecución en particular

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 los eventos de un tipo de fuente en particular, como 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 los eventos de un usuario fuente específico en la migración de Gmail empresarial

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 los eventos de un mensaje en particular con el URI de origen determinado, como un 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 los eventos de un tipo de datos de origen en particular, como la carpeta de correo electrónico de 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;

Cantidad de eventos agrupados por estado y nombre del evento

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;

Cantidad diaria de todos los eventos agrupados por estado y 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

Cantidad de elementos de Google Drive compartidos, agrupados por método de uso compartido

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, propietario y tipo de archivo. Son los archivos que se compartieron de forma externa dentro del 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;

Cambios en los permisos de uso compartido y su resultado. Te permite comprender qué cambios en los permisos generaron el cambio en la visibilidad del archivo.

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 desglosados por tipo de archivo. Útil para el informe de adopción por tipo de archivo.

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;

Tipo y nombre del evento para cada unidad compartida

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;

Información sobre usuarios que no pertenecen a tu dominio

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;

Qué cambios de permisos se otorgaron a usuarios externos y cuándo se otorgaron

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;

Información sobre la supervisión del almacenamiento

Es útil para generar informes sobre los usuarios que consumen más de X almacenamiento de Drive, con un umbral establecido (definido con la cláusula AND accounts.drive_used_quota_in_mb > 0).

Esta consulta se puede definir como una consulta programada o, por ejemplo, se puede llamar periódicamente con la 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;

Notas:

Este valor se puede modificar para que coincida con el filtro que establece el cliente. Por ejemplo, más de 15 GB: AND accounts.drive_used_quota_in_mb > 15000
La comparación de fechas con CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) permite comparar fechas con el formato disponible del valor date.
Esta consulta también se aplica a Gmail, donde podemos encontrar un valor similar: accounts.gmail_used_quota_in_mb

Gmail

Prácticas recomendadas para usar Gmail con BigQuery

Consulta solo los datos que necesitas. Estos ejemplos tienen un límite de 1,000 coincidencias, pero puedes establecer tu propio límite.
Establece un período para tus búsquedas. Un día es un período típico.

Coincidencia de asunto
Vista de resumen de mensajes para hasta 1,000 registros que coinciden con un asunto 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

Coincidencia de destinatario
Recuento de la cantidad de mensajes distintos para un destinatario 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")

Coincidencia de disposición y destinatario

Vista de resumen de mensajes para hasta 1,000 registros que coincidan con ambos criterios:

Una disposición específica (Modificar, Rechazar, Cuarentena)
Un destinatario 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

Descripción de la regla activada
Vista de resumen de mensajes para hasta 1,000 registros, que activaron la descripción de la regla 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
Vista de resumen de mensajes para hasta 1,000 registros:

Marcado como spam
Para un destinatario específico
Por todos los 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 encriptación: Sin encriptar
Vista de resumen del mensaje por protocolo de encriptación: Sin encriptar

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 encriptación: Solo TLS
Vista de resumen de mensajes por protocolo de encriptación: Solo 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

Coincidencia del ID de mensaje
Vista de detalles del mensaje para un ID de mensaje determinado (incluye "<>" alrededor del ID de mensaje)

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

Disposición: Rechazar mensaje
Rechazar mensaje:

¿Qué regla causó el rechazo?

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

Disposición: Modificar mensaje
Modificar mensaje:

¿Qué regla causó la modificación?
¿Qué subcategoría de modificación (por ejemplo, encabezados o asunto)?

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

Mensaje en cuarentena
¿Qué regla puso un mensaje en cuarentena?

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 compuestas
Cuenta todos los mensajes detectados por una regla específica (denominada "descripción de la regla") en los últimos 30 días:

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"

Enumera todos los mensajes que se recibieron sin encriptación TLS en el último día:

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

Enumera los 10 dominios principales con los que mi cuenta intercambió correos electrónicos en los últimos 30 días:

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

Proporción de usuarios activos por día con respecto a los usuarios activos en 30 días en Gmail

Usuarios activos por día:

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;

Usuarios activos durante 7 días:

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;

Usuarios activos durante 30 días:

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;

Los 100 eventos de registro de Gmail más recientes con al menos una etiqueta de clasificación asociada al mensaje de correo electrónico

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 los eventos de registro disponibles para un mensaje de correo electrónico específico

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

Cambios en la membresía de Grupos de Google y comportamiento del usuario

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

Si deseas tener una marca de tiempo en formato AAAA-MM-DD, el primer elemento de la sentencia SELECT se puede reemplazar por lo siguiente:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Las fechas se pueden filtrar en la cláusula WHERE de cualquiera de las siguientes maneras:

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

Cantidad de videollamadas y total de minutos de llamadas por fecha

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

Usuarios activos por día

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

Usuarios activos durante 30 días

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

Reglas

Reglas de DLP activadas por nombre, aplicación coincidente y acciones

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

Cantidad de veces que se habilitó una app de terceros para acceder a 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;

Información detallada sobre los accesos fallidos a la Consola del administrador de Google

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 las tablas de uso

El esquema puede cambiar. Puedes ver una lista actualizada y completa de los parámetros y campos en la documentación de la API de Informes.

Filtrar por fecha

Puedes filtrar por fecha cuando consultes las tablas de actividad o uso. Ambos tienen formatos distintos cuando presentan la fecha:

La tabla activity almacena las marcas de tiempo en microsegundos de Unix. Es un valor entero (un número) que se puede convertir en una fecha con la función TIMESTAMP_MICROS().
La tabla usage muestra sus valores de date con un formato de fecha, por lo que esta conversión no es necesaria.

En cualquiera de las tablas, puedes filtrar los datos por una fecha específica (o un período) con uno de los siguientes métodos.

La tabla de actividad

Para filtrar por una fecha específica con la estructura de Unix Micros (tabla activity), puedes definir la cláusula WHERE y la función TIMESTAMP() para realizar una comparación simple con los operadores mayor que (>) y 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

El concepto aquí es colocar límites en el valor de entrada time_usec comparando su valor de devolución de la función TIMESTAMP_MICROS() con el valor de devolución de la función TIMESTAMP() con una fecha agregada como parámetro de tipo de cadena. Esto sigue los estándares de las funciones de marca de tiempo en SQL estándar y usa operadores de comparación simples (>) y (<), junto con la extensión AND de la cláusula WHERE para cerrar un período en particular.

La tabla 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;

Podemos pasar el valor date de tipo cadena presente en la tabla a la función TIMESTAMP() y usar los operadores de comparación (>) y (<) de la misma manera que en el primer ejemplo.

Filtrar por alias de dominio y subdominios: Excluir e incluir

Para excluir o incluir ciertos dominios en los resultados de tu búsqueda, aplica un filtro para la dirección de correo electrónico en la cláusula WHERE, usando comodines (%) para filtrar los dominios.

La forma en que uses las instrucciones AND o OR dependerá de si filtras (excluyes) o solo incluyes ciertos resultados.

Cómo excluir ciertos dominios de los resultados

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

Cómo incluir solo ciertos dominios en los resultados

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

Registros de auditoría de reglas

Usa esta consulta para hacer un seguimiento de los intentos de los usuarios por compartir datos sensibles

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

Documentación de BigQuery

Google, Google Workspace y las marcas y los logotipos relacionados son marcas comerciales de Google LLC. Todos los demás nombres de productos y empresas son marcas comerciales de las empresas con las que se encuentran asociados.

Ejemplos de consultas de registros de informes en BigQuery Organiza tus páginas con colecciones Guarda y categoriza el contenido según tus preferencias.