Cette page a été traduite par l'API Cloud Translation.

Exemples de requêtes SQL

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

Ce document ne décrit pas SQL et ne explique pas comment acheminer et stocker des entrées de journal. Pour 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, procédez comme suit : remplacez TABLE par le nom de la table qui correspond à la vue que vous souhaitez interroger. Le nom de la table a le format project_ID.region.bucket_ID.view_ID Vous pouvez trouver le nom de la table sur la page Analyse de journaux. la requête par défaut d'un journal "view" liste le nom de la table dans l'instruction FROM. Pour des informations sur la façon d'accéder à la requête par défaut, consultez la section Interroger une vue de journal.
Pour utiliser les requêtes présentées dans ce document sur la BigQuery Studio, remplacez TABLE par le chemin d'accès à la table ; dans 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:

Dans la console Google Cloud, accédez à la page BigQuery.
Accéder à BigQuery Studio

Vous pouvez également accéder à cette page à l'aide de la barre de recherche.
Pour ouvrir la page Analyse de journaux, procédez comme suit:
1. Dans la console Google Cloud, accédez à la page Analyse de journaux:
  Accéder à l'Analyse de journaux
  
  Si vous utilisez la barre de recherche pour trouver cette page, sélectionnez le résultat dont le sous-titre est Logging.
2. Facultatif: Pour identifier le schéma de la table pour la vue de journal, Dans la liste Vues de journaux, recherchez la vue, puis sélectionnez la nom de la vue.
Le schéma de la table s'affiche. Vous pouvez utiliser le champ Filtrer pour localiser des champs spécifiques. Vous ne pouvez pas modifier le schéma.

Filtrer les journaux

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

Filtrer par heure

Pour définir la période de votre requête, nous vous recommandons utilisez le sélecteur de période. Ce sélecteur est utilisé automatiquement lorsqu'une requête ne spécifie pas de champ timestamp dans la clause WHERE. Par exemple, pour afficher les données de la semaine passée, sélectionnez 7 derniers jours dans le sélecteur de période. Vous pouvez également utiliser la période pour spécifier une heure de début et de fin, indiquer l'heure à afficher changer de fuseau horaire.

Si vous incluez un champ timestamp dans la clause WHERE, la période paramètre de sélecteur n'est pas utilisé. L'exemple suivant filtre les données selon à l'aide de la fonction TIMESTAMP_SUB, qui vous permet de spécifier une période d'analyse ; intervalle à partir de l'heure actuelle:

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

Pour en savoir plus sur le filtrage par heure, consultez Fonctions d'heure 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
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

Filtrer par gravité

Vous pouvez filtrer par gravité spécifique et appliquer une restriction telle que severity = 'ERROR' Vous pouvez également 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 et ne conserve ensuite 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
  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 du niveau de 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 de 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 log_name ou log_id. Le champ log_name inclut la ressource chemin d'accès. Autrement dit, ce champ comporte des valeurs telles que projects/myproject/logs/mylog. Le champ log_id ne stocke que le nom de journal tel que mylog.

Par exemple, la requête suivante lit l'heure de données la plus récente, puis conserve les lignes dans lesquelles la valeur du champ log_id est cloudaudit.googleapis.com/data_access, puis trie et affiche les résultats:

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

Filtrer par étiquette de ressource

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

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

Comme le type de données du champ labels est JSON, y compris une restriction comme resource.labels.zone = "us-centra1-f" dans une requête, cela donne une syntaxe . Pour obtenir la valeur d'un champ avec un type de données JSON, utilisez la fonction JSON_VALUE

Par exemple, la requête suivante lit les données les plus récentes, puis conserve aux 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
  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 pouvant récupérer et transformer des fichiers JSON consultez la section Fonctions JSON.

Filtrer par requête HTTP

À filtrer le tableau pour n'inclure que les lignes correspondant à une requête HTTP ou répondez, ajoutez une restriction 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 requête suivante n'inclut que les lignes correspondant à GET ou POST. requêtes:

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

Filtrer par état HTTP

Pour filtrer par état HTTP, modifiez la clause WHERE pour exiger que le Le champ http_request.status doit être défini:

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

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

Filtrer selon 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

Examinez les requêtes suivantes:

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

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  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 supprime les lignes qui ne contiennent pas de colonne intitulée json_payload. La différence entre ces deux requêtes se trouve dans la dernière ligne, qui définit ce qui est testé par rapport à NULL. Maintenant, considérons un tableau qui comporte deux lignes. En un la colonne json_payload se présente comme suit:

{
    status: {
        measureTime: "1661517845"
    }
}

Sur l'autre ligne, la colonne json_payload a 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 inclut 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 qui correspond à 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 les une partie du nom commençant par 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

Pour obtenir d'autres exemples, consultez les 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 instructions conditionnelles.

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

Regrouper et agréger des entrées de journal

Cette section s'appuie sur les exemples précédents et montre comment regrouper et agréger des lignes de tableau. Si vous ne spécifiez pas de regroupement, spécifier une agrégation, un seul résultat est imprimé, car SQL traite toutes lignes qui répondent à la clause WHERE en tant que groupe unique.

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

Grouper par heure

Pour regrouper des données par date, utilisez la fonction TIMESTAMP_TRUNC. qui tronque un horodatage selon un niveau de précision spécifié, tel que MINUTE. Pour Exemple d'horodatage de 15:30:11, au format suivant : hours:minutes:seconds devient 15:30:00 lorsque le niveau de précision est défini sur MINUTE

La requête suivante lit les données reçues dans l'intervalle spécifié par dans l'outil de sélection de période, pour les lignes dont la valeur du champ json_payload.status n'est pas NULL. La requête tronque l'horodatage de chaque ligne par heure, puis regroupe les lignes par horodatage et état tronqués:

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

Pour voir d'autres exemples, consultez les Documentation sur TIMESTAMP_TRUNC Pour en savoir plus sur les autres fonctions temporelles, consultez la section 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 des lignes par type de ressource. Il compte ensuite le nombre de lignes pour chaque type, et renvoie un tableau à deux colonnes. La première colonne indique tandis que la deuxième colonne indique le nombre de lignes pour ce type de ressource:

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

Grouper par gravité

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

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  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 à deux colonnes. Le premier la colonne répertorie les noms des journaux et la seconde indique le nombre de qui ont été écrites dans le journal. La trie les résultats en fonction du nombre d'entrées:

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

Calculer la latence moyenne pour une requête HTTP

La requête suivante illustre le regroupement sur plusieurs colonnes et le calcul une valeur moyenne. La requête regroupe les lignes en fonction de l'URL contenue dans le requête et par la valeur du champ labels.checker_location. Après regroupant les lignes, la requête calcule la latence moyenne pour chaque groupe:

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

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 labels est au format JSON. Cependant, vous n'utilisez pas cette fonction pour extraire la valeur du http_request.latency.seconds. Ce dernier champ a un type de données entier.

Calculer le nombre moyen d'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 les données de l'heure la plus récente, puis ne conserve que ces lignes dont la colonne de type de ressource est gce_subnetwork et dont la colonne json_payload la colonne n'est pas NULL. Ensuite, la requête regroupe les lignes en fonction de l'emplacement du ressource. Contrairement à l'exemple précédent, où les données sont stockées sous forme la valeur du champ bytes_sent est une chaîne. Vous devez donc convertissez 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
  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 pouvant récupérer et transformer des fichiers JSON consultez la section Fonctions JSON.

Pour en savoir plus sur CAST et les autres fonctions de conversion, consultez Fonctions de conversion :

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

Pour renvoyer la sous-chaîne qui correspond à 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. Ensuite, il regroupe les entrées par le suffixe de nom qui commence avec 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
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

Recherche dans plusieurs colonnes

Cette section décrit deux approches différentes que vous pouvez utiliser pour effectuer des recherches plusieurs colonnes d'un tableau.

Recherche par jeton

Pour rechercher dans un tableau les entrées qui correspondent à un ensemble de termes de recherche, utilisez la fonction SEARCH. Cette fonction nécessite deux paramètres: où rechercher et la requête de recherche. Comme la fonction SEARCH applique des règles spécifiques sur la façon dont les données sont recherchées, nous vous recommandons de lire la documentation SEARCH.

La requête suivante ne conserve que les lignes comportant un champ qui correspond exactement à "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

Dans la requête précédente, les accents graves encapsulent la valeur à rechercher. Ce garantit que la fonction SEARCH recherche une correspondance exacte entre une valeur de champ et la valeur entre les guillemets obliques.

Lorsque les accents graves sont omis dans la chaîne de requête, celle-ci est divisée en fonction de règles définies dans la documentation 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 requête précédente, vous pouvez remplacer le filtre sur l'ID de journal par un comme suit:

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

L'instruction précédente effectue une recherche dans l'ensemble du tableau, tandis que l'instruction d'origine ne recherche que la colonne log_id.

Pour effectuer plusieurs recherches sur 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 rechercher des colonnes spécifiques d'un tableau au lieu d'effectuer une recherche l'ensemble du tableau. Par exemple, l'instruction suivante recherche uniquement les colonnes nommées text_payload et json_payload:

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

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

Rechercher des sous-chaînes

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

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

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

Combiner des données provenant de plusieurs sources

Les instructions de requête analysent une ou plusieurs tables ou expressions et renvoient le les lignes de résultats calculés. Par exemple, vous pouvez utiliser des instructions de requête pour fusionner les résultats des instructions SELECT sur différents tableaux ou ensembles de données dans une différentes manières, puis sélectionner les colonnes à partir des données combinées.

Combiner les données de deux tables avec des jointures

Pour combiner les informations de deux tables, utilisez l'une des méthodes de jointure les opérateurs. Le type de jointure et la clause conditionnelle que vous utilisez déterminent comment les lignes sont combinées et supprimées.

La requête suivante vous donne les champs json_payload des lignes dans deux tables différentes écrites par le même délai de trace. La requête effectue une JOIN interne sur deux tables pour les lignes où les valeurs de les colonnes span_id et trace des deux tableaux correspondent. À partir de ce résultat, la requête sélectionne ensuite les champs timestamp, severity et json_payload provenant de TABLE_1, le champ json_payload TABLE_2, et les valeurs de span_id et trace sur lesquels les deux tables ont été jointes, et renvoie jusqu'à 100 lignes:

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

Combiner plusieurs sélections avec des unions

Combiner les résultats d'au moins deux instructions SELECT et les supprimer pour les lignes en double, utilisez l'opérateur UNION. Pour conserver les doublons lignes, utilisez l'opérateur UNION ALL.

La requête suivante lit l'heure de données la plus récente de TABLE_1 fusionne le résultat avec l'heure la plus récente de données de TABLE_2, trie les données fusionnées en augmentant du code temporel, puis affiche les 100 entrées les plus anciennes:

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

Étape suivante

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

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