Éditions compatibles avec cette fonctionnalité: Frontline Standard; Enterprise Standard et Enterprise Plus; Education Standard et Education Plus; Enterprise Essentials Plus. Comparer votre édition
Cet article contient des exemples de requêtes pour les rapports courants que vous pouvez obtenir dans BigQuery. Ces exemples de requêtes sont compatibles avec l'ancien SQL. Remplacez api_project_name.dataset_name par le nom de votre projet et celui de votre ensemble de données.
En savoir plus sur l'interrogation des données BigQuery
Pour connaître les champs des journaux Gmail et leur signification, consultez Schéma des journaux d'activité Gmail dans BigQuery.
Exemples de requêtes
ComptesNombre total de comptes administrateur et délégués, et nombre de comptes désactivés, bloqués et suspendus triés selon la date
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;
Événements les plus fréquemment effectués par un administrateur
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;
Rechercher le nombre de super-administrateurs dans un domaine donné
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;
SQL standard uniquement
Ratio entre le nombre d'utilisateurs actifs par jour et le nombre d'utilisateurs actifs sur 30 jours dans Google Agenda. Dans cet exemple, des requêtes sont effectuées dans différents tableaux.
Utilisateurs actifs par jour
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
Utilisateurs actifs (30 jours)
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;
Nombre d'événements d'agenda par type
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;
Nombre d'éléments partagés dans Google Drive, regroupés par mode de partage
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 de fichier, titre, propriétaire et type. Fichiers partagés en externe pendant le créneau sélectionné
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;
Partage des modifications d'autorisations et résultats. Ce partage vous permet de savoir quelles modifications d'autorisations ont changé la visibilité des fichiers
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";
Répartition des types d'événements par type de fichier. Utile pour trier des rapports d'adoption par type de fichier
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;
Type et nom d'événement pour chaque Drive partagé
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;
Informations sur les utilisateurs n'appartenant pas à votre domaine
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;
Quelles modifications d'autorisations ont été accordées aux utilisateurs externes, et quand
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;
Informations sur la surveillance du stockage
Utile pour créer des rapports sur les utilisateurs qui occupent plus de X d'espace de stockage Drive, avec un seuil défini (dans la clause AND accounts.drive_used_quota_in_mb > 0).
Cette requête peut être définie en tant que requête programmée ou, par exemple, être appelée périodiquement à l'aide de l'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;
Remarques :
- Cette valeur peut être modifiée pour correspondre au filtre défini par le client, Par exemple, plus de 15 Go :
AND accounts.drive_used_quota_in_mb > 15000 - La comparaison de dates avec
CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING)permet d'effectuer une comparaison de dates avec le format de la valeur date. -
Cette requête s'applique également à Gmail, où une valeur similaire est disponible :
accounts.gmail_used_quota_in_mb.
Bonnes pratiques à suivre pour utiliser Gmail avec BigQuery
- Interrogez uniquement les données dont vous avez besoin. Ces exemples limitent les résultats à 1 000 correspondances, mais vous pouvez modifier cette limite.
- Définissez un délai pour vos requêtes. Les délais d'un jour sont courants.
Correspondance d'objet
Vue récapitulative des messages limitée à 1 000 enregistrements correspondant à l'objet spécifié
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
Correspondance de destinataire
Nombre de messages distincts pour un destinataire donné
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")
Correspondance de disposition et de destinataire
Affichage du résumé du message limité à 1 000 enregistrements correspondant à la fois à :
- une disposition donnée (modification, rejet, mise en quarantaine) ;
- un destinataire donné.
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
Description de la règle déclenchée
Vue récapitulative des messages limitée à 1 000 enregistrements ayant déclenché la description de la règle spécifiée
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
Marqué comme spam
Vue récapitulative des messages limitée à 1 000 enregistrements :
- Marqués comme spam
- pour un destinataire donné ;
- pour toutes les raisons.
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
Protocole de chiffrement – non chiffré
Vue récapitulative des messages par protocole de chiffrement (non chiffrés)
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
Protocole de chiffrement – TLS uniquement
Vue récapitulative des messages par protocole de chiffrement (TLS uniquement)
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
Correspondance de l'ID du message
Vue détaillée du message pour un ID de message donné (incluez "<>" autour de l'ID du message)
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
Disposition – Rejeter le message
Rejeter le message :
- Quel est le motif du rejet ?
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 – Modifier le message
Modifier le message :
- Quelle règle a entraîné la modification ?
- Quelle sous-catégorie est concernée (en-têtes, objet, par exemple) ?
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
Mettre le message en quarantaine
Quelle règle a mis un message en quarantaine ?
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
Requêtes composées
Total de tous les messages détectés par une règle spécifique (par description) au cours des 30 derniers jours :
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"
Liste de tous les messages reçus sans chiffrement TLS le jour précédent :
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
Liste des 10 principaux domaines avec lesquels notre compte a échangé des messages au cours des 30 derniers jours :
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
Ratio entre le nombre d'utilisateurs actifs par jour et le nombre d'utilisateurs actifs sur 30 jours dans Gmail
Utilisateurs actifs par jour :
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;
Utilisateurs actifs (7 jours) :
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;
Utilisateurs actifs (30 jours) :
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;
Changements d'appartenance et comportement des utilisateurs dans Google Groupes
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 vous souhaitez utiliser l'horodatage AAAA-MM-JJ, le premier élément de l'instruction SELECT peut être remplacé par :
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,
Vous pouvez filtrer les dates dans la clause WHERE de l'une des manières suivantes :
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
Nombre d'appels vidéo et nombre total de minutes d'appel par date
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
Utilisateurs actifs par jour
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
Utilisateurs actifs (30 jours)
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
Règles de protection contre la perte de données déclenchées, triées par nom, application associée et actions
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;
Nombre d'accès autorisés à Google Drive par une application tierce
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;
Informations détaillées sur les échecs de connexion à la console d'administration 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;
Le schéma est sujet à des modifications. Vous pouvez consulter la liste complète et à jour des paramètres et des champs dans la documentation sur l'API Reports.
Lorsque vous lancez une requête concernant les tableaux de statistiques d'activité ou d'utilisation, vous pouvez filtrer les données par date. Les deux tableaux présentent des formats distincts de présentation des dates :
- Le tableau des statistiques d'activité présente les horodatages en microsecondes Unix, qui correspondent à une valeur entière (nombre) pouvant être convertie en date à l'aide de la fonction TIMESTAMP_MICROS().
- Les valeurs de date sont affichées dans un format de date dans le tableau des statistiques d'utilisation. La conversion n'est donc pas nécessaire.
Vous pouvez choisir de filtrer les données de ces tableaux par date (ou période) à l'aide de l'une des méthodes ci-dessous.
Tableau des statistiques d'activité
Pour filtrer les données en fonction d'une date spécifique avec la structure de microsecondes Unix (tableau des statistiques d'activité), vous pouvez définir l'expression WHERE et la fonction TIMESTAMP() pour effectuer une comparaison simple avec les opérateurs supérieur à (>) et inférieur à (<) :
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
Ici, le concept consiste à placer des limites dans la valeur time_usec d'entrée en comparant sa valeur renvoyée à partir de la fonction TIMESTAMP_MICROS() avec la valeur renvoyée de la fonction TIMESTAMP(), avec une date ajoutée en tant que paramètre de type chaîne. Ce concept respecte les normes sur les fonctions d'horodatage en langage SQL standard et utilise des opérateurs de comparaison simples (>) et (<), ainsi que l'extension AND dans la clause WHERE pour fermer une fenêtre de temps.
Tableau des statistiques d'utilisation
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;
La valeur date de type chaîne présente dans le tableau peut être transférée dans la fonction TIMESTAMP(), et les opérateurs de comparaison (>) et (<) peuvent être employés de la même manière que dans le premier exemple.
Pour exclure ou inclure certains domaines des résultats de votre requête, appliquez un filtre pour l'adresse e-mail à la clause WHERE à l'aide des caractères génériques (%) pour filtrer les domaines.
La façon dont vous utilisez les opérateurs AND ou OR varie selon que vous filtrez les données (exclusion) ou que vous incluez seulement certains résultats.
Exclure certains domaines des résultats
WHERE email NOT LIKE ("%@sub.%")
AND email NOT LIKE ("%@test.%")
Inclure uniquement certains domaines dans les résultats
WHERE email LIKE ("%@sub.%")
OR email LIKE ("%@test.%")
Effectuer le suivi des tentatives de partage de données sensibles par les utilisateurs à l'aide de cette requête
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;
