Versioni supportate per questa funzionalità: Frontline Standard; 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 il 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, consulta lo schema dei log delle attività di Gmail in BigQuery.
Esempi di query
AccountNumero 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;
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;
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 giornalieri attivi
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 un periodo di 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;
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 consumano 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 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 data. -
Questa query è applicabile anche a Gmail, dove possiamo trovare un valore simile:
accounts.gmail_used_quota_in_mb
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
Conta il 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 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 ID messaggio
Visualizzazione dei dettagli del messaggio per un determinato ID messaggio (include "<>" 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 = ""
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
Messaggio in quarantena
Quale regola ha messo in quarantena un 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 per 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;
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 AAAA-MM-GG, il primo elemento SELECT può essere sostituito 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
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 giornalieri attivi
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 un periodo di 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 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;
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;
Lo schema è passibile di modifiche. L'elenco completo aggiornato dei parametri e dei campi è disponibile nella documentazione dell'API Reports.
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.
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.%")
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;
