Exemples de requêtes SQL

Restez organisé à l'aide des collections Enregistrez et classez les contenus selon vos préférences.

Ce document contient des exemples de requêtes sur des entrées de journal stockées dans des buckets de journaux mis à niveau pour utiliser Log Analytics. Sur ces buckets, vous pouvez exécuter des requêtes SQL depuis la page Log Analytics de la console Google Cloud. Pour voir d'autres exemples, consultez les dépôts GitHub logging-analytics-samples et security-analytics.

Ce document ne décrit pas SQL ni comment acheminer et stocker des entrées de journal. Pour en savoir plus sur ces sujets, consultez la section Étapes suivantes.

Avant de commencer

  • Pour utiliser les requêtes présentées dans ce document sur la page Analyse de journaux, remplacez TABLE par le nom de la table correspondant à la vue que vous souhaitez interroger. Le nom de la table est au format project_ID.region.bucket_ID.view_ID. Vous pouvez trouver le nom de la table pour une vue sur la page Journaliser les analyses. La requête par défaut pour une vue de journal répertorie le nom de la table dans l'instruction FROM. Pour savoir comment accéder à la requête par défaut, consultez Interroger une vue de journal.

    Accéder à Log Analytics

  • Pour utiliser les requêtes présentées dans ce document sur la page Espace de travail SQL de BigQuery*, remplacez TABLE par le chemin d'accès à la table de l'ensemble de données associé. Par exemple, pour interroger la vue _AllLogs sur l'ensemble de données associé mydataset qui se trouve dans le projet myproject, définissez ce champ sur myproject.mydataset._AllLogs:

    Accéder à l'espace de travail BigQuery SQL

Filtrer les journaux

Les requêtes SQL déterminent les lignes de la table à traiter, puis elles les regroupent et effectuent les opérations d'agrégation. Si aucune opération de regroupement et d'agrégation n'est répertoriée, le résultat de la requête inclut les lignes sélectionnées par l'opération de filtrage. Les exemples de cette section illustrent le filtrage.

Filtrer par heure

Pour filtrer les données en fonction de l'heure, vous pouvez utiliser la fonction TIMESTAMP_SUB. Cette fonction vous permet de spécifier un intervalle d'analyse à partir de l'heure actuelle.

Par exemple, la requête suivante lit l'heure de données la plus récente, trie les données en augmentant l'horodatage, puis affiche les 100 entrées les plus anciennes:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
ORDER BY timestamp ASC
LIMIT 100

Une autre option consiste à utiliser la fonction timestamp. La requête suivante lit tous les journaux reçus sur une période fermée, puis affiche les 100 entrées les plus anciennes:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  timestamp >= TIMESTAMP("2022-08-25 13:00:00", "America/New_York") AND
  timestamp <= TIMESTAMP("2022-08-25 17:00:00", "America/New_York")
ORDER BY timestamp ASC
LIMIT 100

Pour en savoir plus sur le filtrage par heure, consultez les pages Fonctions de temps et Fonctions d'horodatage.

Filtrer par ressource

Pour filtrer par ressource, ajoutez une restriction resource.type.

Par exemple, la requête suivante lit l'heure de données la plus récente, puis conserve les lignes dont le type de ressource correspond à gce_instance, puis trie et affiche jusqu'à 100 entrées:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

Filtrer par gravité

Vous pouvez filtrer par gravité spécifique avec une restriction telle que severity = 'ERROR'. Une autre option consiste à utiliser l'instruction IN et à spécifier un ensemble de valeurs valides.

Par exemple, la requête suivante lit l'heure de données la plus récente, puis ne conserve que les lignes contenant un champ severity dont la valeur est 'INFO' ou 'ERROR' :

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

La requête précédente filtre en fonction de la valeur du champ severity. Toutefois, vous pouvez également écrire des requêtes qui filtrent selon la valeur numérique de la gravité du journal. Par exemple, si vous remplacez les lignes severity par les lignes suivantes, la requête renvoie toutes les entrées de journal dont le niveau de gravité est au moins NOTICE :

  severity_number IS NOT NULL AND
  severity_number > 200

Pour en savoir plus sur les valeurs énumérées, consultez la section LogSeverity.

Filtrer par nom de journal

Pour filtrer par nom de journal, vous pouvez ajouter une restriction sur la valeur du champ log_name ou log_id. Le champ log_name inclut le chemin d'accès à la ressource. Autrement dit, ce champ contient des valeurs telles que projects/myproject/logs/mylog. Le champ log_id ne stocke que le nom du journal, tel que mylog.

Par exemple, la requête suivante lit l'heure de données la plus récente, conserve les lignes où la valeur du champ log_name se termine par data_access, puis trie et affiche les résultats:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  log_name LIKE "%data_access"
ORDER BY timestamp ASC
LIMIT 100

L'exemple précédent spécifie un nom partiel et utilise l'instruction LIKE et le caractère générique %.

Filtrer par libellé de ressource

La plupart des descripteurs de ressources surveillés définissent les libellés utilisés pour identifier la ressource spécifique. Par exemple, le descripteur d'une instance Compute Engine inclut des libellés pour la zone, l'ID du projet et l'ID de l'instance. Lorsque l'entrée de journal est écrite, des valeurs sont attribuées à chaque champ. Voici un exemple:

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

Étant donné que le type de données du champ labels est JSON, l'inclusion d'une restriction telle que resource.labels.zone = "us-centra1-f" dans une requête génère une erreur de syntaxe. Pour obtenir la valeur d'un champ de type JSON, utilisez la fonction JSON_VALUE.

Par exemple, la requête suivante lit les données les plus récentes, puis conserve les lignes où la ressource est une instance Compute Engine située dans la zone us-central1-f:

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

Pour en savoir plus sur toutes les fonctions capables de récupérer et de transformer les données JSON, consultez la page Fonctions JSON.

Filtrer par requête HTTP

Pour filtrer la table de façon à n'inclure que les lignes correspondant à une requête ou une réponse HTTP, ajoutez une restriction http_request IS NOT NULL:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  http_request IS NOT NULL
ORDER BY timestamp
LIMIT 100

La requête suivante n'inclut que les lignes correspondant aux requêtes GET ou POST:

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

Filtrer par état HTTP

Pour filtrer par état HTTP, modifiez la clause WHERE afin que le champ http_request.status soit défini:

SELECT
  timestamp, log_name, http_request.status, http_request, resource, labels
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  http_request IS NOT NULL AND
  http_request.status IS NOT NULL
ORDER BY timestamp ASC
LIMIT 100

Pour déterminer le type de données stockées dans un champ, affichez le schéma ou affichez le champ. Les résultats de la requête précédente montrent que le champ http_request.status stocke des valeurs entières.

Filtrer par un champ de type JSON

Pour extraire une valeur d'une colonne dont le type de données est JSON, utilisez la fonction JSON_VALUE.

Considérez les requêtes suivantes:

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  json_payload.status IS NOT NULL

et

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  JSON_VALUE(json_payload.status) IS NOT NULL

Les requêtes précédentes testent la valeur de la colonne json_payload. Le contenu de cette colonne est déterminé par le contenu d'une entrée de journal. Les deux requêtes suppriment les lignes qui ne contiennent pas de colonne intitulée json_payload. La différence entre ces deux requêtes est la dernière ligne, qui définit ce qui est testé par rapport à NULL. Prenons l'exemple d'une table comportant deux lignes. Sur une ligne, la colonne json_payload a la forme suivante:

{
    status: {
        measureTime: "1661517845"
    }
}

Dans l'autre ligne, la colonne json_payload présente une structure différente:

{
    @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"
}

Les deux lignes précédentes respectent la restriction json_payload.status IS NOT NULL. Autrement dit, le résultat de la requête comprend les deux lignes. Toutefois, lorsque la restriction est JSON_VALUE(json_payload.status) IS NOT NULL, seule la deuxième ligne est incluse dans le résultat.

Filtrer par expression régulière

Pour renvoyer la sous-chaîne correspondant à une expression régulière, utilisez la fonction REGEXP_EXTRACT. Le type renvoyé par cette fonction est STRING ou BYTES.

La requête suivante affiche les entrées de journal les plus récentes reçues, conserve ces entrées avec un champ json_payload.jobName, puis affiche la partie du nom commençant par test:

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

Pour obtenir des exemples supplémentaires, consultez la documentation sur REGEXP_EXTRACT. Pour obtenir des exemples d'autres expressions régulières que vous pouvez utiliser, consultez la page Fonctions, opérateurs et expressions conditionnelles.

La requête présentée dans cet exemple n'est pas efficace. Pour une correspondance de sous-chaîne, comme celle illustrée, utilisez la fonction CONTAINS_SUBSTR.

Regrouper et saisir des entrées de journal

Cette section s'appuie sur les exemples précédents et montre comment vous pouvez regrouper et agréger des lignes de table. Si vous ne spécifiez pas de regroupement, mais que vous spécifiez une agrégation, un seul résultat est affiché, car SQL traite toutes les lignes qui respectent la clause WHERE comme un seul groupe.

Chaque expression SELECT doit être incluse dans les champs du groupe ou agrégée.

Grouper par heure

Pour regrouper des données par date, utilisez la fonction TIMESTAMP_TRUNC, qui tronque un horodatage à un niveau de précision spécifié tel que MINUTE. Par exemple, l'horodatage 15:30:11, au format hours:minutes:seconds, devient 15:30:00 lorsque la granularité est définie sur MINUTE.

La requête suivante lit les 10 heures de données les plus récentes, puis conserve les lignes dans lesquelles la valeur du champ json.payload.status n'est pas nulle. La requête tronque l'horodatage de chaque ligne par heure, puis regroupe les lignes par horodatage et par état tronqués:

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

Pour voir d'autres exemples, consultez la documentation sur TIMESTAMP_TRUNC. Pour en savoir plus sur les autres fonctions basées sur l'heure, consultez la page Fonctions de date et heure.

Grouper par ressource

La requête suivante lit l'heure de données la plus récente, puis regroupe les lignes par type de ressource. Il compte ensuite le nombre de lignes pour chaque type et renvoie une table avec deux colonnes. La première colonne indique le type de ressource, tandis que la deuxième indique le nombre de lignes pour ce type de ressource:

SELECT
   resource.type, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY resource.type
LIMIT 100

Regrouper par gravité

La requête suivante lit l'heure de données la plus récente, puis conserve les lignes ayant un champ de gravité. La requête regroupe ensuite les lignes par niveau de gravité et compte le nombre de lignes pour chaque groupe:

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  severity IS NOT NULL
GROUP BY severity
ORDER BY severity
LIMIT 100

Grouper par log_id

Le résultat de la requête suivante est une table avec deux colonnes. La première colonne répertorie les noms des journaux, tandis que la seconde indique le nombre d'entrées de journal écrites dans ce journal au cours de l'heure la plus récente. La requête trie les résultats en fonction du nombre d'entrées:

SELECT
  log_id, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY log_id
ORDER BY count DESC
LIMIT 100

Calculer la latence moyenne des requêtes HTTP

La requête suivante illustre le regroupement de plusieurs colonnes et le calcul d'une valeur moyenne. La requête regroupe les lignes par URL contenue dans la requête HTTP et par la valeur du champ labels.checker_location. Une fois les lignes regroupées, la requête calcule la latence moyenne de chaque groupe:

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

Dans l'expression précédente, JSON_VALUE est requis pour extraire la valeur du champ labels.checker_location, car le type de données de labels est JSON. Cependant, vous n'utilisez pas cette fonction pour extraire la valeur du champ http_request.latency.seconds. Ce dernier champ contient un type de données entier.

Calculer la moyenne des octets envoyés pour un test de sous-réseau

La requête suivante montre comment afficher le nombre moyen d'octets envoyés par emplacement.

La requête lit l'heure de données la plus récente, puis ne conserve que les lignes dont la colonne de type de ressource est gce_subnetwork et dont la colonne json_payload n'est pas NULL. Ensuite, la requête regroupe les lignes en fonction de l'emplacement de la ressource. Contrairement à l'exemple précédent où les données sont stockées sous forme de valeur numérique, la valeur du champ bytes_sent est une chaîne. Vous devez donc convertir la valeur en FLOAT64 avant de calculer la moyenne:

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

Le résultat de la requête précédente est une table dans laquelle chaque ligne indique un emplacement et le nombre moyen d'octets envoyés pour cet emplacement.

Pour en savoir plus sur toutes les fonctions capables de récupérer et de transformer les données JSON, consultez la page Fonctions JSON.

Pour en savoir plus sur CAST et sur les autres fonctions de conversion, consultez la page Fonctions de conversion.

Comptez les entrées de journal avec un champ correspondant à un modèle

Pour renvoyer la sous-chaîne correspondant à une expression régulière, utilisez la fonction REGEXP_EXTRACT. Le type renvoyé par cette fonction est STRING ou BYTES.

La requête suivante conserve les entrées de journal pour lesquelles la valeur du champ json_payload.jobName n'est pas NULL. Il regroupe ensuite les entrées par le suffixe de nom commençant par test. Enfin, la requête compte le nombre d'entrées dans chaque groupe:

SELECT
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

Pour obtenir des exemples supplémentaires, consultez la documentation sur REGEXP_EXTRACT. Pour obtenir des exemples d'autres expressions régulières que vous pouvez utiliser, consultez la page Fonctions, opérateurs et expressions conditionnelles.

Cette section décrit deux approches différentes que vous pouvez utiliser pour rechercher plusieurs colonnes d'une table.

Pour rechercher dans une table des entrées correspondant à un ensemble de termes de recherche, utilisez la fonction SEARCH. Cette fonction requiert deux paramètres : l'emplacement de recherche et la requête de recherche. Étant donné que la fonction SEARCH applique des règles spécifiques sur la recherche des données, nous vous recommandons de lire la documentation SEARCH.

La requête suivante ne conserve que les lignes dont le champ correspond exactement à "35.193.12.15" :

SELECT
  timestamp, log_name, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  proto_payload IS NOT NULL AND
  log_name LIKE "%cloudaudit.googleapis.com%" AND
  SEARCH(t,"`35.193.12.15`")
ORDER BY timestamp ASC
LIMIT 20

Dans la requête précédente, les accents graves englobent la valeur à rechercher. Cela permet de s'assurer que la fonction SEARCH recherche une correspondance exacte entre une valeur de champ et la valeur entre les accents graves.

Lorsque les accents graves sont omis dans la chaîne de requête, celle-ci est divisée en fonction des règles définies dans la documentation de SEARCH. Par exemple, lorsque l'instruction suivante est exécutée, la chaîne de requête est divisée en quatre jetons : "35", "193", "12" et "15" :

  SEARCH(t,"35.193.12.15")

L'instruction SEARCH précédente correspond à une ligne lorsqu'un seul champ correspond aux quatre jetons. L'ordre des jetons n'a pas d'importance.

Vous pouvez inclure plusieurs instructions SEARCH dans une requête. Par exemple, dans la requête précédente, vous pouvez remplacer le filtre sur le nom du journal par une instruction comme celle-ci:

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

L'instruction précédente recherche dans la table entière, tandis que l'instruction d'origine ne recherche que dans la colonne log_name.

Pour effectuer plusieurs recherches dans une colonne, séparez les chaînes individuelles par un espace. Par exemple, l'instruction suivante correspond aux lignes où un champ contient "Hello World", "happy" et "days" :

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

Enfin, vous pouvez effectuer une recherche dans des colonnes spécifiques d'une table plutôt que dans une table entière. Par exemple, l'instruction suivante ne recherche que les colonnes nommées text_payload et json_payload:

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

Pour plus d'informations sur le traitement des paramètres de la fonction SEARCH, consultez la page de référence Fonctions de recherche de BigQuery.

Pour effectuer un test non sensible à la casse afin de déterminer si une valeur existe dans une expression, utilisez la fonction CONTAINS_SUBSTR. Cette fonction renvoie TRUE lorsque la valeur existe et FALSE dans le cas contraire. La valeur de recherche doit être un littéral STRING, mais pas le littéral littéral NULL.

Par exemple, la requête suivante récupère toutes les entrées du journal d'audit ayant une adresse IP spécifique dont les horodatages se trouvent dans une période spécifique. Enfin, la requête trie les résultats, puis affiche les 20 résultats les plus anciens:

SELECT
  timestamp, log_name, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  proto_payload IS NOT NULL AND
  log_name LIKE "%cloudaudit.googleapis.com%" AND
  CONTAINS_SUBSTR(t,"35.193.12.15")
ORDER BY timestamp ASC
LIMIT 20

La requête précédente effectue un test de sous-chaîne. Par conséquent, une ligne contenant "35.193.12.152" correspond à l'instruction CONTAINS_SUBSTR.

Étapes suivantes

Pour savoir comment acheminer et stocker des entrées de journal, consultez les documents suivants:

Pour la documentation de référence SQL, consultez les documents suivants: