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 di attività di Gmail in BigQuery.

Esempi di query

Account

Numero di account amministratore e account con delega, 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;

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

Il lancio degli eventi dei log di Migrazione dei dati avverrà gradualmente a partire dal 26 maggio 2025. I dati degli eventi dei log precedenti a questa data potrebbero non essere accurati.

Tutte le azioni di 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 relativi alla migrazione per un determinato ID di 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 di 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 Migrazione di Gmail Enterprise

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 email 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 in base allo stato e al nome dell'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 in base allo stato e al 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, con una soglia impostata (definita con la AND accounts.drive_used_quota_in_mb > 0 clausola).

Questa query può essere definita come query programmata o, ad esempio, può essere richiamata 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 a partire dal 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

Contrassegnato 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 del riepilogo dei messaggi in base al 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 del riepilogo dei messaggi in base al protocollo di crittografia 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 all'iscrizione al gruppo Google e comportamento 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 in 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;

Tentativi di accesso alla Console di amministrazione

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 è passibile di modifiche. L'elenco completo aggiornato dei parametri e dei campi è disponibile nella documentazione dell'API Reports.

Filtrare per 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 relativa all'attività 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 relativa all'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, puoi applicare 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;


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