Esempi di query per i log dei report in BigQuery

Versioni supportate per questa funzionalità: Frontline Standard e Frontline Plus; Enterprise Standard ed Enterprise Plus; Education Standard ed Education Plus; Enterprise Essentials Plus. Confronta la tua versione

Questo articolo contiene esempi di query per i report comuni che puoi ottenere con BigQuery. Questi esempi di query presuppongono il dialetto SQL precedente. Sostituisci api_project_name.dataset_name con il nome del tuo progetto e quello del set di dati.

Scopri di più sull'esecuzione di query dei dati di BigQuery.

Per i campi dei log di Gmail e i relativi significati, vedi Schema dei log attività di Gmail in BigQuery.

Esempi di query

Account

Numero di account amministratore e account delegati, e numero di account disattivati, bloccati e sospesi ordinati in base alla data

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;

Amministratori

Azioni più frequenti eseguite da un amministratore

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;

Trovare il numero di super amministratori in un determinato dominio

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;

Calendar

Solo SQL standard

Rapporto tra utenti attivi giornalieri e utenti attivi negli ultimi 30 giorni in Google Calendar. La query di questo esempio viene eseguita su più tabelle.

Utenti attivi giornalieri

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

Utenti attivi per 30 giorni

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;

Numero di eventi di calendario in base al 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;

Migrazione dei dati

Gli eventi dei log di Migrazione dei dati verranno implementati gradualmente a partire dal 26 maggio 2025. I dati sugli eventi del log precedenti a questa data potrebbero non essere accurati.

Tutte le azioni di configurazione e configurazione eseguite da un amministratore

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;

Tutti gli eventi correlati alla migrazione per un determinato ID esecuzione

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;

Tutti gli errori per un determinato ID esecuzione

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;

Tutti gli eventi per un determinato tipo di origine, ad esempio 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;

Tutti gli eventi per un determinato utente di origine in 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;

Tutti gli eventi per un determinato messaggio con un URI di origine specificato, ad esempio 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;

Tutti gli eventi per un determinato tipo di dati di origine, ad esempio la cartella di posta di 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;

Numero di eventi raggruppati per stato e nome 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;

Numero giornaliero di tutti gli eventi raggruppati per stato e tipo di oggetto

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

Numero di elementi di Google Drive condivisi, raggruppati in base al metodo di condivisione

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 file, titolo, proprietario e tipo. File che sono stati condivisi esternamente entro un dato periodo di tempo.

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;

Modifiche apportate alle autorizzazioni di condivisione e relativi risultati. La query consente di comprendere quali sono le modifiche delle autorizzazioni che hanno avuto come risultato un cambiamento nella visibilità dei file.

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

Tipi di evento suddivisi per tipo di file. Utile per il report adozione in base al tipo di file.

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 e nome degli eventi per ciascun Drive condiviso

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;

Informazioni sugli utenti esterni 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;

Quali modifiche delle autorizzazioni sono state concesse agli utenti esterni e quando

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;

Informazioni sul monitoraggio dello spazio di archiviazione

Utile per creare report sugli utenti che utilizzano più di X spazio di archiviazione di Drive, con una soglia impostata (definita con la clausola AND accounts.drive_used_quota_in_mb > 0).

Questa query può essere definita come query programmata o, ad esempio, può essere chiamata periodicamente utilizzando 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;

Note:

Questo valore può essere modificato in modo che corrisponda al filtro impostato dal cliente. Ad esempio, più di 15 GB: AND accounts.drive_used_quota_in_mb > 15000
Il confronto delle date con CAST(DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS STRING) consente di confrontare le date con il formato disponibile nel valore date.
Questa query è applicabile anche a Gmail, dove possiamo trovare un valore simile: accounts.gmail_used_quota_in_mb

Gmail

Best practice per Gmail con BigQuery

Esegui query solo per i dati che ti occorrono. In questi esempi viene utilizzato un massimo di 1000 corrispondenze,ma puoi impostare un limite diverso.
Imposta un intervallo di tempo per le query. L'intervallo tipico è di un giorno.

Corrispondenza oggetto
Visualizzazione di riepilogo dei messaggi per un massimo di 1000 record corrispondenti a un oggetto specificato

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

Corrispondenza destinatario
Conteggio del numero di messaggi distinti per un destinatario specificato

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")

Corrispondenza disposizione e destinatario

Visualizzazione di riepilogo del messaggio per un massimo di 1000 record corrispondenti a entrambi i seguenti elementi:

Una disposizione specificata (Modifica, Rifiuta, Quarantena)
Un destinatario specificato

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

Descrizione della regola attivata
Visualizzazione del riepilogo dei messaggi,per un massimo di 1000 record, che hanno attivato la descrizione della regola specificata

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

Contrassegnati come spam
Visualizzazione del riepilogo dei messaggi per un massimo di 1000 record:

Contrassegnato come spam
Per un destinatario specificato
Per tutti i motivi

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

Protocollo di crittografia: non criptato
Visualizzazione di riepilogo del messaggio per il protocollo di crittografia non criptato

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

Protocollo di crittografia: solo TLS
Visualizzazione di riepilogo del messaggio per il protocollo di crittografia di tipo 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

Corrispondenza dell'ID messaggio
Visualizzazione dei dettagli del messaggio per un determinato ID messaggio (includere "<>" attorno all'ID messaggio)

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

Disposizione: rifiuta messaggio
Rifiuta messaggio:

Quale regola ha causato il rifiuto?

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

Disposizione: modifica messaggio
Modifica messaggio:

Quale regola ha causato la modifica?
Quale sottocategoria di modifica (ad esempio, intestazioni o oggetto)?

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

Metti messaggio in quarantena
In base a quale regola è stato messo in quarantena il messaggio?

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

Query composte
Conta tutti i messaggi bloccati da una regola specifica (denominata "rule description", descrizione regola) negli ultimi 30 giorni:

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"

Elenca tutti i messaggi ricevuti senza crittografia TLS nelle ultime 24 ore:

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

Elenca i 10 domini principali con cui l'account ha scambiato posta negli ultimi 30 giorni:

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

Rapporto tra utenti attivi giornalieri e utenti attivi negli ultimi 30 giorni in Gmail

Utenti attivi giornalieri:

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;

Utenti attivi per 7 giorni

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;

Utenti attivi per 30 giorni

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;

I 100 eventi dei log di Gmail più recenti con almeno un'etichetta di classificazione associata al messaggio email

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;

Tutti gli eventi dei log disponibili per un messaggio email specifico

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;

Gruppi

Modifiche apportate all'elenco dei membri di un gruppo Google e attività degli utenti

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

Se vuoi avere un timestamp del tipo AAAA-MM-GG, puoi sostituire il primo elemento dell'istruzione SELECT con:
EXTRACT(DATE FROM TIMESTAMP_MICROS(time_usec)) AS date,

Le date possono essere filtrate nella clausola WHERE in uno dei seguenti modi:

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

Numero di videochiamate e minuti di chiamata totali ordinati in base alla data

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

Utenti attivi giornalieri

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

Utenti attivi per 30 giorni

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

Regole

Regole DLP attivate in base al nome, all'applicazione corrispondente e alle azioni

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;

Token

Numero di volte in cui è stato consentito a un'app di terze parti di accedere 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;

Informazioni dettagliate sugli accessi non riusciti alla Console di amministrazione 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;

Schema delle tabelle relative all'utilizzo

Lo schema può cambiare. L'elenco completo aggiornato dei parametri e dei campi è disponibile nella documentazione dell'API Reports.

Filtrare in base alla data

Puoi filtrare i risultati in base alla data quando esegui query nelle tabelle relative all'attività o all'utilizzo. Entrambi hanno formati distinti per presentare la data:

La tabella activity memorizza i timestamp in microsecondi Unix. Si tratta di un valore intero (un numero) che può essere convertito in una data con la funzione TIMESTAMP_MICROS().
La tabella relativa all'utilizzo mostra i valori della data in un formato di data, pertanto non è necessaria la conversione.

Per entrambe le tabelle, puoi scegliere di filtrare in base a una data specifica (o a un intervallo di date) utilizzando uno dei seguenti metodi.

Tabella attività

Per filtrare in base a una data specifica con la struttura di microsecondi Unix (tabella dell'attività), puoi definire la clausola WHERE e la funzione TIMESTAMP() per eseguire un semplice confronto con gli operatori maggiore di (>) e minore di (<):

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

In questo caso vengono posti dei limiti al valore di input time_usec confrontando il relativo valore restituito dalla funzione TIMESTAMP_MICROS() con il valore restituito dalla funzione TIMESTAMP(), con una data aggiunta come parametro di tipo stringa. Per farlo vengono seguiti gli standard descritti nel documento sulle funzioni di timestamp in SQL standard e si utilizzano semplici operatori di confronto (>) e (<), insieme all'estensione AND della clausola WHERE per chiudere una specifica finestra temporale.

Tabella relativa all'utilizzo

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;

Possiamo trasferire il valore date di tipo stringa della tabella nella funzione TIMESTAMP() e utilizzare gli operatori di confronto (>) e (<) come nel primo esempio.

Filtrare per alias di dominio e sottodomini: escludere e includere

Per escludere o includere determinati domini dai risultati della query, applica un filtro per l'indirizzo email alla clausola WHERE, utilizzando i caratteri jolly (%) per filtrare i domini.

L'utilizzo dell'istruzione AND o dell'istruzione OR varia a seconda che tu stia lasciando fuori (escludendo) o includendo soltanto risultati specifici.

Escludere determinati domini dai risultati

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

Includere soltanto determinati domini nei risultati

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

Log di controllo delle regole

Utilizza questa query per monitorare i tentativi degli utenti di condividere dati sensibili

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;

Argomento correlato

Documentazione di BigQuery

Google, Google Workspace e i marchi e loghi correlati sono marchi di Google LLC. Tutti gli altri nomi di società e prodotti sono marchi delle società a cui sono associati.

Esempi di query per i log dei report in BigQuery Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.