Esempi di query SQL

Questo documento contiene query di esempio sulle voci di log archiviate nei bucket di log di cui viene eseguito l'upgrade per utilizzare l'analisi dei log. In questi bucket puoi eseguire query SQL dalla pagina Analisi dei log nella console Google Cloud. Per altri esempi, consulta i repository GitHub di logging-analytics-samples e security-analytics.

Questo documento non descrive SQL né come eseguire il routing e l'archiviazione delle voci di log. Per informazioni su questi argomenti, consulta la sezione Passaggi successivi.

Suggerimento: non includere un campo timestamp nella clausola WHERE della query. Puoi impostare invece l'intervallo di tempo utilizzando il relativo selettore.

Prima di iniziare

• Per utilizzare le query mostrate nella pagina Analisi dei log in questo documento, sostituisci TABLE con il nome della tabella corrispondente alla vista su cui vuoi eseguire la query. Il nome della tabella ha il formato project_ID.region.bucket_ID.view_ID. Il nome della tabella di una vista è disponibile nella pagina Analisi dei log. La query predefinita per una vista di log elenca il nome della tabella nell'istruzione FROM. Per informazioni su come accedere alla query predefinita, consulta Eseguire una query su una visualizzazione log.

• Per utilizzare le query mostrate in questo documento nella pagina BigQuery Studio, sostituisci TABLE con il percorso alla tabella nel set di dati collegato. Ad esempio, per eseguire una query sulla vista _AllLogs nel set di dati collegato mydataset che si trova nel progetto myproject, imposta questo campo su myproject.mydataset._AllLogs:

  Nel pannello di navigazione della console Google Cloud, seleziona BigQuery:

  Vai a BigQuery

• Per aprire la pagina Analisi dei log:

  1. Nel pannello di navigazione della console Google Cloud, seleziona Logging, quindi Analisi dei log:

     Vai ad Analisi dei log

  2. (Facoltativo) Per identificare lo schema della tabella per la visualizzazione log, trova la vista nell'elenco Visualizzazioni log e selezionane il nome.

  Viene visualizzato lo schema per la tabella. Puoi utilizzare il campo Filtro per individuare campi specifici. Non puoi modificare lo schema.

Filtra log

Le query SQL determinano quali righe della tabella elaborare, poi raggruppano le righe ed eseguono operazioni aggregate. Se non sono elencate operazioni di raggruppamento e aggregazione, il risultato della query include le righe selezionate dall'operazione di filtro. Gli esempi in questa sezione illustrano l'applicazione dei filtri.

Filtra per ora

Per impostare l'intervallo di tempo della query, ti consigliamo di utilizzare il selettore dell'intervallo di tempo. Questo selettore viene utilizzato automaticamente quando una query non specifica un campo timestamp nella clausola WHERE. Ad esempio, per visualizzare i dati della settimana precedente, seleziona Ultimi 7 giorni dal selettore dell'intervallo di tempo. Puoi utilizzare il selettore dell'intervallo di tempo anche per specificare un'ora di inizio e di fine, un orario di visualizzazione e modificare i fusi orari.

Se includi un campo timestamp nella clausola WHERE, l'impostazione del selettore dell'intervallo di tempo non viene utilizzata. L'esempio seguente filtra i dati utilizzando la funzione TIMESTAMP_SUB, che consente di specificare un intervallo di look-back a partire dall'ora attuale:

WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

Per ulteriori informazioni su come filtrare in base all'ora, consulta Funzioni temporali e Funzioni timestamp.

Filtra per risorsa

Per filtrare in base alla risorsa, aggiungi una limitazione resource.type.

Ad esempio, la seguente query legge l'ora più recente dei dati, poi conserva le righe il cui tipo di risorsa corrisponde a gce_instance e poi ordina e visualizza fino a 100 voci:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

Filtra per gravità

Puoi filtrare in base a una gravità specifica con una limitazione come severity = 'ERROR'. Un'altra opzione consiste nell'utilizzare l'istruzione IN e specificare un insieme di valori validi.

Ad esempio, la seguente query legge l'ora più recente dei dati e conserva solo le righe che contengono un campo severity il cui valore è 'INFO' o 'ERROR':

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  severity IS NOT NULL AND
  severity IN ('INFO', 'ERROR')
ORDER BY timestamp ASC
LIMIT 100

La query precedente filtra in base al valore del campo severity. Tuttavia, puoi anche scrivere query che filtrano in base al valore numerico della gravità del log. Ad esempio, se sostituisci le righe severity con le seguenti, la query restituisce tutte le voci di log il cui livello di gravità è almeno NOTICE:

  severity_number IS NOT NULL AND
  severity_number > 200

Per informazioni sui valori enumerati, consulta LogSeverity.

Filtra per nome log

Per filtrare in base al nome log, puoi aggiungere una limitazione al valore dei campi log_name o log_id. Il campo log_name include il percorso della risorsa. Ciò significa che questo campo contiene valori come projects/myproject/logs/mylog. Il campo log_id memorizza solo il nome log, ad esempio mylog.

Ad esempio, la seguente query legge l'ora più recente dei dati, poi conserva le righe in cui il valore nel campo log_id è cloudaudit.googleapis.com/data_access e poi ordina e visualizza i risultati:

SELECT
  timestamp, log_id, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  log_id = "cloudaudit.googleapis.com/data_access"
ORDER BY timestamp ASC
LIMIT 100

Filtra per etichetta della risorsa

La maggior parte dei descrittori risorsa monitorata definisce le etichette utilizzate per identificare la risorsa specifica. Ad esempio, il descrittore per un'istanza Compute Engine include etichette per la zona, l'ID progetto e l'ID istanza. Quando la voce di log viene scritta, i valori vengono assegnati a ogni campo. Ecco un esempio:

{
   type: "gce_instance"
   labels: {
      instance_id: "1234512345123451"
      project_id: "my-project"
      zone: "us-central1-f"
   }
}

Poiché il tipo di dati del campo labels è JSON, una restrizione come resource.labels.zone = "us-centra1-f" in una query genera un errore di sintassi. Per ottenere il valore di un campo con un tipo di dati JSON, utilizza la funzione JSON_VALUE.

Ad esempio, la seguente query legge i dati più recenti e poi conserva le righe in cui la risorsa è un'istanza di Compute Engine che si trova nella zona us-central1-f:

SELECT
  timestamp, log_name, severity, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  resource.type = "gce_instance" AND
  JSON_VALUE(resource.labels.zone) = "us-central1-f"
ORDER BY timestamp ASC
LIMIT 100

Per informazioni su tutte le funzioni in grado di recuperare e trasformare i dati JSON, consulta le funzioni JSON.

Filtra per richiesta HTTP

Per filtrare la tabella in modo da includere solo le righe che corrispondono a una richiesta o una risposta HTTP, aggiungi una limitazione http_request IS NOT NULL:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL
ORDER BY timestamp
LIMIT 100

La seguente query include solo le righe che corrispondono alle richieste GET o POST:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET', 'POST')
ORDER BY timestamp ASC
LIMIT 100

Filtra per stato HTTP

Per filtrare in base allo stato HTTP, modifica la clausola WHERE in modo da richiedere la definizione del campo http_request.status:

SELECT
  timestamp, log_name, http_request.status, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.status IS NOT NULL
ORDER BY timestamp ASC
LIMIT 100

Per determinare il tipo di dati archiviati in un campo, visualizza lo schema o visualizza il campo. I risultati della query precedente mostrano che nel campo http_request.status sono memorizzati valori interi.

Filtrare in base a un campo con un tipo JSON

Per estrarre un valore da una colonna il cui tipo di dati è JSON, utilizza la funzione JSON_VALUE.

Considera le seguenti query:

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  json_payload.status IS NOT NULL

e

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  JSON_VALUE(json_payload.status) IS NOT NULL

Le query precedenti verificano il valore della colonna json_payload; il contenuto di questa colonna è determinato dai contenuti di una voce di log. Entrambe le query eliminano le righe che non contengono una colonna denominata json_payload. La differenza tra queste due query è la riga finale, che definisce ciò che viene testato rispetto a NULL. Ora consideriamo una tabella con due righe. In una riga, la colonna json_payload ha il seguente formato:

{
    status: {
        measureTime: "1661517845"
    }
}

Nell'altra riga, la colonna json_payload ha una struttura diversa:

{
    @type: "type.googleapis.com/google.cloud.scheduler.logging.AttemptFinished"
    jobName: "projects/my-project/locations/us-central1/jobs/test1"
    relativeUrl: "/food=cake"
    status: "NOT_FOUND"
    targetType: "APP_ENGINE_HTTP"
}

Entrambe le righe precedenti soddisfano la limitazione json_payload.status IS NOT NULL. In altre parole, il risultato della query include entrambe le righe. Tuttavia, quando la limitazione è JSON_VALUE(json_payload.status) IS NOT NULL, nel risultato è inclusa solo la seconda riga.

Filtra per espressione regolare

Per restituire la sottostringa corrispondente a un'espressione regolare, utilizza la funzione REGEXP_EXTRACT. Il tipo restituito di questa funzione è STRING o BYTES.

La seguente query visualizza le voci di log più recenti ricevute, conserva queste voci con un campo json_payload.jobName e poi mostra la porzione del nome che inizia con test:

SELECT
  timestamp, REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
FROM
  `TABLE`
WHERE
  json_payload.jobName IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20

Per ulteriori esempi, consulta la documentazione di REGEXP_EXTRACT. Per esempi di altre espressioni regolari che puoi utilizzare, consulta Funzioni, operatori e condizionali.

La query mostrata in questo esempio non è efficiente. Per una corrispondenza di sottostringhe, come quella illustrata, utilizza la funzione CONTAINS_SUBSTR.

Raggruppa e aggrega voci di log

Questa sezione si basa sugli esempi precedenti e illustra come raggruppare e aggregare le righe di una tabella. Se non specifichi un raggruppamento, ma specifichi un'aggregazione, viene stampato un singolo risultato perché tutte le righe che soddisfano la clausola WHERE vengono trattate come un singolo gruppo da parte di SQL.

Ogni espressione SELECT deve essere inclusa nei campi del gruppo o deve essere aggregata.

Raggruppa per ora

Per raggruppare i dati in base all'ora, utilizza la funzione TIMESTAMP_TRUNC, che tronca un timestamp in base a una granularità specificata come MINUTE. Ad esempio, un timestamp di 15:30:11, nel formato hours:minutes:seconds, diventa 15:30:00 quando la granularità è impostata su MINUTE.

Nota: se specifichi un timestamp nella tua query, questo sostituisce l'intervallo di tempo selezionato nel selettore dell'intervallo di tempo. Per utilizzare il selettore dell'intervallo di tempo, rimuovi le espressioni timestamp dalla query.

La seguente query legge i dati ricevuti nell'intervallo specificato dal selettore dell'intervallo di tempo e poi conserva le righe in cui il valore del campo json_payload.status non è NULL. La query tronca il timestamp in ogni riga per ora, quindi raggruppa le righe in base al timestamp troncato e allo stato:

SELECT
  TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,
  JSON_VALUE(json_payload.status) AS status,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  json_payload IS NOT NULL AND
  JSON_VALUE(json_payload.status) IS NOT NULL
GROUP BY hour,status
ORDER BY hour ASC

Per ulteriori esempi, consulta la documentazione di TIMESTAMP_TRUNC. Per informazioni su altre funzioni basate sul tempo, consulta Funzioni di data/ora.

Raggruppa per risorsa

La seguente query legge l'ora di dati più recente e poi raggruppa le righe per tipo di risorsa. Conta il numero di righe per ogni tipo e restituisce una tabella con due colonne. La prima colonna elenca il tipo di risorsa, mentre la seconda colonna indica il numero di righe per quel tipo di risorsa:

SELECT
   resource.type, COUNT(*) AS count
FROM
  `TABLE`
GROUP BY resource.type
LIMIT 100

Raggruppa per gravità

La seguente query legge l'ora di dati più recente e poi conserva le righe con un campo di gravità. La query raggruppa quindi le righe in base alla gravità e conteggia il numero di righe per ogni gruppo:

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  severity IS NOT NULL
GROUP BY severity
ORDER BY severity
LIMIT 100

Raggruppa per log_id

Il risultato della seguente query è una tabella con due colonne. La prima colonna elenca i nomi dei log, mentre la seconda colonna elenca il numero di voci di log che sono state scritte in quel log nell'ora più recente. La query ordina i risultati in base al numero di voci:

SELECT
  log_id, COUNT(*) AS count
FROM
  `TABLE`
GROUP BY log_id
ORDER BY count DESC
LIMIT 100

Latenza media di calcolo per la richiesta HTTP

La seguente query illustra il raggruppamento in base a più colonne e il calcolo di un valore medio. La query raggruppa le righe in base all'URL contenuto nella richiesta HTTP e al valore del campo labels.checker_location. Dopo aver raggruppamento le righe, la query calcola la latenza media per ogni gruppo:

SELECT
  JSON_VALUE(labels.checker_location) AS location,
  AVG(http_request.latency.seconds) AS secs, http_request.request_url
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET')
GROUP BY http_request.request_url, location
ORDER BY location
LIMIT 100

Nell'espressione precedente, JSON_VALUE è necessario per estrarre il valore del campo labels.checker_location perché il tipo di dati per labels è JSON. Tuttavia, non utilizzerai questa funzione per estrarre il valore dal campo http_request.latency.seconds. Quest'ultimo campo ha un tipo di dati intero.

Calcola la media dei byte inviati per un test della subnet

La seguente query illustra come potresti visualizzare il numero medio di byte inviati per località.

La query legge l'ora più recente di dati e poi conserva solo le righe la cui colonna del tipo di risorsa è gce_subnetwork e la cui colonna json_payload non è NULL. Poi, la query raggruppa le righe in base alla località della risorsa. A differenza dell'esempio precedente in cui i dati sono archiviati come valore numerico, il valore del campo bytes_sent è una stringa, perciò devi convertire il valore in FLOAT64 prima di calcolare la media:

SELECT JSON_VALUE(resource.labels.location) AS location,
   AVG(CAST(JSON_VALUE(json_payload.bytes_sent) AS FLOAT64)) AS bytes
FROM
  `TABLE`
WHERE
  resource.type = "gce_subnetwork" AND
  json_payload IS NOT NULL
GROUP BY location
LIMIT 100

Il risultato della query precedente è una tabella in cui ogni riga elenca una località e la media di byte inviati per quella località.

Per informazioni su tutte le funzioni in grado di recuperare e trasformare i dati JSON, consulta le funzioni JSON.

Per informazioni su CAST e altre funzioni di conversione, consulta Funzioni di conversione.

Conta le voci di log con un campo corrispondente a un pattern

Per restituire la sottostringa corrispondente a un'espressione regolare, utilizza la funzione REGEXP_EXTRACT. Il tipo restituito di questa funzione è STRING o BYTES.

La seguente query conserva le voci di log per le quali il valore del campo json_payload.jobName non è NULL. Quindi, raggruppa le voci in base al suffisso del nome che inizia con test. Infine, la query conta il numero di voci in ogni gruppo:

SELECT
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

Per ulteriori esempi, consulta la documentazione di REGEXP_EXTRACT. Per esempi di altre espressioni regolari che puoi utilizzare, consulta Funzioni, operatori e condizionali.

Ricerca in più colonne

Questa sezione descrive due diversi approcci che puoi utilizzare per eseguire ricerche in più colonne di una tabella.

Ricerca basata su token

Per cercare in una tabella le voci che corrispondono a un insieme di termini di ricerca, utilizza la funzione SEARCH. Questa funzione richiede due parametri: dove eseguire la ricerca e la query di ricerca. Poiché la funzione SEARCH prevede regole specifiche sulla modalità di ricerca nei dati, ti consigliamo di leggere la documentazione di SEARCH.

La seguente query conserva solo le righe che hanno un campo che corrisponde esattamente a "35.193.12.15":

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  proto_payload IS NOT NULL AND
  log_id = "cloudaudit.googleapis.com/data_access" AND
  SEARCH(t,"`35.193.12.15`")
ORDER BY timestamp ASC
LIMIT 20

Nella query precedente, gli apici invecchiati presentano il valore da cercare. Ciò garantisce che la funzione SEARCH cerchi una corrispondenza esatta tra il valore di un campo e il valore tra l'accento grave.

Se gli accenti gravi vengono omessi nella stringa di query, quest'ultima viene suddivisa in base alle regole definite nella documentazione di SEARCH. Ad esempio, quando viene eseguita la seguente istruzione, la stringa di query viene suddivisa in quattro token: "35", "193", "12" e "15":

  SEARCH(t,"35.193.12.15")

L'istruzione SEARCH precedente corrisponde a una riga quando un singolo campo corrisponde a tutti e quattro i token. L'ordine dei token non è importante.

Puoi includere più istruzioni SEARCH in una query. Ad esempio, nella query precedente potresti sostituire il filtro nell'ID log con un'istruzione simile alla seguente:

  SEARCH(t,"`cloudaudit.googleapis.com/data_access`")

L'istruzione precedente cerca nell'intera tabella, mentre l'istruzione originale cerca solo nella colonna log_id.

Per eseguire più ricerche in una colonna, separa le singole stringhe con uno spazio. Ad esempio, la seguente istruzione corrisponde alle righe in cui un campo contiene "Hello World", "happy" e "days":

  SEARCH(t,"`Hello World` happy days")

Infine, puoi eseguire ricerche in colonne specifiche di una tabella anziché in un'intera tabella. Ad esempio, la seguente istruzione cerca solo nelle colonne denominate text_payload e json_payload:

   SEARCH((text_payload, json_payload) ,"`35.222.132.245`")

Per informazioni su come vengono elaborati i parametri della funzione SEARCH, consulta la pagina di riferimento di BigQuery Funzioni di ricerca.

Ricerca di sottostringhe

Per eseguire un test senza distinzione tra maiuscole e minuscole per determinare se un valore esiste in un'espressione, utilizza la funzione CONTAINS_SUBSTR. Questa funzione restituisce TRUE se il valore esiste e FALSE in caso contrario. Il valore di ricerca deve essere un valore letterale STRING, ma non il valore letterale NULL.

Ad esempio, la seguente query recupera tutte le voci degli audit log di accesso ai dati con un indirizzo IP specifico i cui timestamp si trovano in un intervallo di tempo specifico. Infine, la query ordina i risultati e mostra i 20 risultati meno recenti:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  proto_payload IS NOT NULL AND
  log_id = "cloudaudit.googleapis.com/data_access" AND
  CONTAINS_SUBSTR(t,"35.193.12.15")
ORDER BY timestamp ASC
LIMIT 20

La query precedente esegue un test delle sottostringhe. Di conseguenza, una riga che contiene "35.193.12.152" corrisponde all'istruzione CONTAINS_SUBSTR.

Combinare i dati di più origini

Le istruzioni di query analizzano una o più tabelle o espressioni e restituiscono le righe dei risultati calcolati. Ad esempio, puoi utilizzare le istruzioni di query per unire i risultati delle istruzioni SELECT su tabelle o set di dati diversi in vari modi e poi selezionare le colonne dai dati combinati.

Combinare i dati di due tabelle con join

Per combinare le informazioni di due tabelle, utilizza quello degli operatori join. Il tipo di join e la clausola condizionale che utilizzi determinano il modo in cui le righe vengono combinate ed eliminate.

La seguente query ti fornisce i campi json_payload di righe in due tabelle diverse scritte dallo stesso intervallo di traccia. La query esegue un JOIN interno su due tabelle per le righe in cui i valori delle colonne span_id e trace in entrambe le tabelle corrispondono. Da questo risultato, la query seleziona quindi i campi timestamp, severity e json_payload provenienti da TABLE_1, il campo json_payload da TABLE_2 e i valori dei campi span_id e trace in cui le due tabelle sono state unite, e restituisce fino a 100 righe:

SELECT
  a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace
FROM `TABLE_1` a
JOIN `TABLE_2` b
ON
  a.span_id = b.span_id AND
  a.trace = b.trace
LIMIT 100

Combinare più selezioni con unioni

Per combinare i risultati di due o più istruzioni SELECT ed eliminare le righe duplicate, utilizza l'operatore UNION. Per mantenere le righe duplicate, utilizza l'operatore UNION ALL.

La seguente query legge l'ora più recente dei dati da TABLE_1, unisce il risultato con l'ora più recente dei dati di TABLE_2, ordina i dati uniti aumentando il timestamp e mostra le 100 voci meno recenti:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM(
  SELECT * FROM `TABLE_1`
  UNION ALL
  SELECT * FROM `TABLE_2`
)
ORDER BY timestamp ASC
LIMIT 100

Passaggi successivi

Per informazioni su come eseguire il routing e l'archiviazione delle voci di log, consulta i seguenti documenti:

• Crea un bucket di log
• Eseguire l'upgrade di un bucket per utilizzare Analisi dei log
• Collegare un bucket di log a un set di dati BigQuery
• Configura e gestisci i sink

Per la documentazione di riferimento SQL, consulta i seguenti documenti:

• Funzioni, operatori e condizionali
• Funzioni di ricerca
• Sintassi delle query