Ediciones compatibles con esta función: Frontline Standard, Enterprise Standard y Enterprise Plus, Education Standard y Education Plus y Enterprise Essentials Plus. Comparar ediciones
En este artículo se incluyen consultas de ejemplo que permiten generar informes habituales con datos de BigQuery. En estas consultas se utiliza el lenguaje SQL antiguo. Cambia api_project_name.dataset_name por el nombre de tu proyecto y del conjunto de datos.
Más información sobre cómo consultar datos de BigQuery
Para obtener información sobre los campos de registro de Gmail y sus significados, consulta el artículo Esquema de los registros de actividad de Gmail en BigQuery.
Consultas de ejemplo
CuentasNúmero de cuentas de administrador y delegadas y número de cuentas inhabilitadas, bloqueadas y suspendidas, ordenadas 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;
Acciones más frecuentes realizadas por administradores
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 de un dominio concreto
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;
Solo SQL estándar
Proporción de usuarios activos cada día respecto a los usuarios activos en los últimos 30 días en Google Calendar; el código de este ejemplo hace consultas en varias tablas.
Usuarios activos diarios
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 (ú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;
Número de eventos de calendario, agrupados según su 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 elementos de Google Drive compartidos, agrupados según el método con el que se han 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 archivos que se han compartido con usuarios externos en el periodo
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 de permisos de uso compartido y sus resultados; así puedes saber qué cambios de permisos han causado que cambie la visibilidad de archivos
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 evento desglosados por tipo de archivo; útil en informes 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 de evento de 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 ajenos al 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 han concedido a usuarios externos y cuándo se han producido
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 monitorización de almacenamiento
Resulta útil para generar informes sobre usuarios que consumen más de una cantidad fijada de almacenamiento en Drive, con un umbral definido con la cláusula AND accounts.drive_used_quota_in_mb > 0 .
Esta consulta se puede programar o, por ejemplo, se puede activar 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 puede modificarse para que coincida con el filtro que quiera usar el cliente. Por ejemplo, con un tamaño superior a 15 GB:
AND accounts.drive_used_quota_in_mb > 15000 - Como las fechas se comparan con
CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING)se puede hacer la comparación con el formato disponible en el valor date. -
Esta consulta también se aplica a Gmail, donde podemos encontrar un valor similar:
accounts.gmail_used_quota_in_mb
Prácticas recomendadas para obtener datos de Gmail con BigQuery
- Crea consultas para obtener solo los datos que necesitas. Los ejemplos de este artículo tienen un límite de 1000 coincidencias, pero puedes definir el límite que quieras.
- Delimita tus consultas a un periodo determinado; por ejemplo, un día.
Coincidencia de asunto
Vista de resumen de mensajes de hasta 1000 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
Número de mensajes distintos de un destinatario concreto
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
Consulta para obtener una vista resumida de mensajes con hasta 1000 registros que coinciden con los siguientes criterios:
- Una disposición concreta (Modificar, Rechazar, Cuarentena)
- Un destinatario en particular
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 los mensajes de hasta 1000 registros que hayan activado la descripción de la regla indicada.
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 1000 registros:
- Marcado como spam
- Para un destinatario concreto
- Por todas las razones
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 cifrado: no cifrado
Vista de resumen de mensajes no cifrados
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 cifrado: solo TLS
Vista de resumen de mensajes cifrados por protocolo 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 detallada 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 = ""
LIMIT 1000
Disposición: rechazar mensaje:
Rechazar mensaje:
- ¿Qué regla ha provocado 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 ha provocado que se modifique?
- ¿Qué se ha modificado (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
Poner mensaje en cuarentena
¿Qué regla ha puesto en cuarentena un mensaje?
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 capturados por una regla concreta (denominada "descripción de regla") durante 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 recibidos sin cifrado TLS desde el día anterior:
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 ha intercambiado correos mi cuenta durante 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 cada día respecto a los usuarios activos en los últimos 30 días en Gmail
Usuarios activos diarios
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 en 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 en 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;
Cambios de miembros y comportamiento de usuarios de Grupos de Google
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 quieres usar una marca de tiempo con formato AAAA-MM-DD, puedes sustituir el primer elemento de la cláusula SELECT por:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,
Las fechas se pueden filtrar mediante 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
Número de videollamadas y minutos de llamada totales, ordenados 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 diarios
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 (ú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 de DLP activadas agrupadas por nombre, aplicación que las ha activado y acción
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 veces que se ha permitido que una aplicación de terceros acceda 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 intentos fallidos de iniciar sesión en la consola de administración 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;
El esquema puede cambiar. Puedes consultar una lista actualizada y completa de los parámetros y los campos en la documentación de la API de informes.
Cuando consultas las tablas de actividad o de uso, puedes filtrar por fecha, pero ten en cuenta que cada tabla tiene un formato de fecha distinto.
- Las marcas de tiempo de la tabla de actividad se expresan en microsegundos de Unix. Es decir, están representadas por un valor entero (un número) que puede convertirse en una fecha con la función TIMESTAMP_MICROS().
- En la tabla de uso, los valores date se muestran con un formato de fecha, por lo que no es necesario hacer esta conversión.
Puedes filtrar cualquiera de estas tablas por una fecha concreta o por un intervalo de fechas mediante uno de los siguientes métodos.
Tabla de actividad
Para filtrar por una fecha concreta la tabla de actividad (que utiliza la estructura de microsegundos de Unix), puedes añadir la cláusula WHERE e incluir la función TIMESTAMP() para hacer una comparación sencilla 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
La idea aquí es fijar límites en el valor de entrada time_usec comparando el valor que tiene después de pasarlo por la función TIMESTAMP_MICROS() con el valor que devuelve la función TIMESTAMP() con una fecha como parámetro de tipo cadena. De este modo, se siguen los estándares definidos en Funciones de marca de tiempo en SQL estándar y se puede definir un periodo concreto utilizando los operadores de comparación simples (>) y (<), y la extensión AND en la cláusula WHERE.
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 incluir el valor de date de tipo cadena de la tabla como parámetro de la función TIMESTAMP() y usar los operadores de comparación (>) y (<) del mismo modo que en el primer ejemplo.
Para incluir determinados dominios en los resultados de tu consulta (o excluirlos), puedes añadir un filtro de dirección de correo electrónico a la cláusula WHERE y filtrar los dominios que te interesan mediante comodines (%).
El modo en que usas los operadores AND u OR depende de si quieres excluir determinados resultados o de si solo quieres incluir esos resultados.
Excluir ciertos dominios de los resultados
WHERE email NOT LIKE ("%@sub.%")
AND email NOT LIKE ("%@test.%")
Incluir solo ciertos dominios en los resultados
WHERE email LIKE ("%@sub.%")
OR email LIKE ("%@test.%")
Para monitorizar las ocasiones en que los usuarios han intentado compartir datos sensibles, utiliza esta consulta:
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;
