Exemples de requêtes pour les journaux de données de rapports dans BigQuery

Éditions compatibles avec cette fonctionnalité : Frontline Standard et Frontline Plus ; 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

Comptes

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

Administrateurs

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

Agenda

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

Migration de données

Les événements de journal de migration de données seront déployés progressivement à partir du 26 mai 2025. Les données des événements de journal antérieures à cette date peuvent être inexactes.

Toutes les actions de configuration et d'installation effectuées par un administrateur

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;

Tous les événements liés à la migration pour un ID d'exécution spécifique

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;

Tous les échecs pour un ID d'exécution spécifique

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;

Tous les événements pour un type de source spécifique, comme 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;

Tous les événements pour un utilisateur source spécifique dans Enterprise Gmail Migration

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;

Tous les événements d'un message spécifique avec un URI source donné, tel qu'un ID 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;

Tous les événements pour un type de données source spécifique, comme le dossier de messagerie 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;

Nombre d'événements regroupés par état et nom d'événement

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;

Nombre quotidien de tous les événements, regroupés par état et type d'objet

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

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.

Gmail

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 une période 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 du 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é 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é

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 = "<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 de modification est concernée (en-têtes ou 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 mon 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;

100 derniers événements de journaux Gmail avec au moins un libellé de classification associé à l'e-mail

SELECT
      resource_details[OFFSET(0)].id AS MESSAGE_ID,
      gmail.message_info.subject AS SUBJECT,
      gmail.event_info.mail_event_type AS MAIL_EVENT_TYPE,
      gmail.message_info.source.address AS SENDER,
      resource_details[OFFSET(0)].applied_labels AS LABELS
    FROM workspace_audit_logs.activity
    WHERE gmail.event_info.mail_event_type > 0 and ARRAY_LENGTH(resource_details) > 0
    ORDER by time_usec desc
    LIMIT 100;

Tous les événements de journaux disponibles pour un e-mail spécifique

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;

Groupes

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

Google Meet

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 sur 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

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;

Jetons

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;

Schéma pour les tableaux de statistiques d'utilisation

Le schéma est susceptible de changer. Vous pouvez consulter la liste complète et à jour des paramètres et des champs dans la documentation sur l'API Reports.

Filtrer par date

Lorsque vous lancez une requête concernant les tableaux activity (activité) ou usage (utilisation), vous pouvez filtrer les données par date. Les deux tableaux présentent des formats distincts de présentation des dates :

Le tableau activity stocke les codes temporels 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 la clause 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.

Filtrer par alias de domaine et sous-domaine : exclure et inclure

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 (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.%")

Journaux d'audit des règles

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;

Article associé

Documentation BigQuery

Google, Google Workspace, ainsi que les marques et logos associés sont des marques appartenant à Google LLC. Tous les autres noms de sociétés et de produits sont des marques des sociétés auxquelles ils sont associés.

Exemples de requêtes pour les journaux de données de rapports dans BigQuery Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.