Google utiliza tecnología de IA para traducir contenido a tu idioma preferido. Las traducciones realizadas con IA pueden contener errores.

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 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 registro de Gmail y sus significados, consulta Esquema para 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 en comparación con los usuarios activos en 30 días en el 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 los últimos 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 de 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 de forma gradual 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 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' ORDER BY timestamp desc LIMIT 1000;

Todas las fallas de 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 de origen específico en la migración de Gmail para empresas

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 un 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. 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 sus resultados. Te permite comprender qué cambios de 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. Es ú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 los 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 los usuarios externos y cuándo

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 crear informes sobre los usuarios que consumen más de X almacenamiento en 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 de forma periódica 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 realizar una comparación de 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 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 consultas. 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
Cuenta la cantidad de mensajes distintos para un destinatario específico

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 un máximo de 1,000 registros que coincidan con lo siguiente:

Una disposición específica (Modificar, Rechazar, En cuarentena)
Un destinatario especificado

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

Se activó la descripción de la regla
Vista de resumen de mensajes para un máximo de 1,000 registros, que activó 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

Marcados como spam
Vista de resumen de mensajes para un máximo de 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 de mensajes 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 de ID de mensaje
Vista de detalles de 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

Disposition—Reject message
Reject message:

¿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

Disposition—Modify message
Modify message:

¿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 (llamada "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 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 en relación con los usuarios activos por mes 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 los últimos 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 los últimos 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 de los usuarios

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 con el formato AAAA-MM-DD, puedes reemplazar el primer elemento de la sentencia SELECT 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 minutos totales 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

Glossary Term: 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 los últimos 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 de las tablas de uso

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

Filtrar por fecha.

Puedes filtrar por fecha cuando consultas las tablas de actividad o de uso. Ambos tienen formatos distintos para presentar la fecha:

La tabla activity almacena las marcas de tiempo en microsegundos de Unix. Este es un valor de número 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 por una fecha (o un período) específica con uno de los siguientes métodos.

La tabla de actividad

Para filtrar por una fecha específica con la estructura de microsegundos de Unix (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 retorno de la función TIMESTAMP_MICROS() con el valor de retorno de la función TIMESTAMP() con una fecha agregada como un 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 de fecha 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.

Filtra por alias de dominio y subdominios: Excluye e incluye

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

La forma en que uses la sentencia AND o OR dependerá de si excluyes o solo incluyes ciertos resultados.

Cómo excluir ciertos dominios de los resultados

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

Incluye 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 de 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.