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 l'Analyse de journaux.
Sur ces buckets, vous pouvez exécuter des requêtes SQL depuis la page Analyse de journaux de la console Google Cloud. Pour plus d'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 plus d'informations sur ces sujets, consultez la section Étapes suivantes.
Avant de commencer
Pour utiliser les requêtes affiché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 trouverez le nom de table d'une vue sur la page Analyse de journaux. La requête par défaut d'une vue de journal indique le nom de table dans l'instructionFROM
. Pour en savoir plus sur l'accès à 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 page 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 projetmyproject
, définissez ce champ surmyproject.mydataset._AllLogs
:Dans la console Google Cloud, accédez à la page BigQuery.
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:
-
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.
Facultatif: Pour identifier le schéma de la table dans la vue de journal, recherchez la vue dans la liste Vues de journaux, puis sélectionnez son nom.
Le schéma de la table s'affiche. Vous pouvez utiliser le champ Filtre 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 effectuent des 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 définir la période de votre requête, nous vous recommandons d'utiliser 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 le sélecteur de période pour spécifier une heure de début et de fin, une heure à consulter et modifier les fuseaux horaires.
Si vous incluez un champ timestamp
dans la clause WHERE
, le paramètre du sélecteur de période n'est pas utilisé. L'exemple suivant filtre les données à l'aide de la fonction TIMESTAMP_SUB
, qui vous permet de spécifier un intervalle d'analyse à 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 temporelles et Fonctions d'horodatage.
Filtrer par ressource
Pour filtrer par ressource, ajoutez une restriction resource.type
.
Par exemple, la requête suivante lit les données de l'heure 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 niveau de 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 les données de l'heure 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
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 les données en fonction de 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 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 comporte des valeurs telles que projects/myproject/logs/mylog
.
Le champ log_id
ne stocke que le nom du journal, par exemple mylog
.
Par exemple, la requête suivante lit les données de l'heure la plus récente, conserve les lignes pour 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 libellés permettant d'identifier une 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 l'entrée de journal est écrite, des valeurs sont attribuées à chaque champ. En 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 entraîne une erreur de 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 les lignes dans lesquelles 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 données JSON, consultez la section Fonctions JSON.
Filtrer par requête HTTP
Pour filtrer le tableau afin de 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
http_request IS NOT NULL
ORDER BY timestamp
LIMIT 100
La requête suivante ne comprend que les lignes correspondant aux requêtes GET
ou 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
Filtrer par état HTTP
Pour filtrer par état HTTP, modifiez la clause WHERE
pour exiger que le champ http_request.status
soit 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 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 en fonction d'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
et
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 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
. Maintenant, prenons un tableau avec deux lignes. Sur une ligne, 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
.
C'est-à-dire que 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 la 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 des exemples supplémentaires, consultez la documentation sur REGEXP_EXTRACT
.
Pour obtenir des exemples d'expressions régulières que vous pouvez utiliser, consultez la section Fonctions, opérateurs et conditions.
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 agréger 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 d'une table. Si vous ne spécifiez pas de regroupement, mais que vous spécifiez une agrégation, un seul résultat est imprimé, car SQL traite toutes les lignes qui répondent à la clause WHERE
comme un seul groupe.
Chaque expression SELECT
doit être incluse dans les champs du groupe ou être agrégée.
Grouper par heure
Pour regrouper des données par heure, utilisez la fonction TIMESTAMP_TRUNC
, qui tronque un horodatage à un niveau de précision spécifié tel que MINUTE
. Par exemple, un horodatage de 15:30:11
, au format hours:minutes:seconds
, devient 15:30:00
lorsque la précision est définie sur MINUTE
.
La requête suivante lit les données reçues dans l'intervalle spécifié par le sélecteur de période, puis conserve 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 en fonction du code temporel et de l'é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 obtenir des exemples supplémentaires, consultez la documentation sur TIMESTAMP_TRUNC
.
Pour en savoir plus sur les autres fonctions temporelles, consultez la page Fonctions de date et heure.
Grouper par ressource
La requête suivante lit les données de l'heure la plus récente, puis regroupe les lignes par type de ressource. Elle compte ensuite le nombre de lignes pour chaque type et renvoie une table à deux colonnes. La première colonne indique le type de ressource, 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 les données de l'heure 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. La première colonne répertorie les noms des journaux et la deuxième le nombre d'entrées de journal écrites dans le journal. La requête 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 d'une requête HTTP
La requête suivante illustre le regroupement par plusieurs colonnes et le calcul d'une valeur moyenne. La requête regroupe les lignes en fonction de l'URL contenue dans la requête HTTP et de la valeur du champ labels.checker_location
. Après avoir regroupé 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 nécessaire 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
. Dans ce dernier champ, le type de données est "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 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
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 données JSON, consultez la section Fonctions JSON.
Pour en savoir plus sur CAST
et les autres fonctions de conversion, consultez la section 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 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
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'expressions régulières que vous pouvez utiliser, consultez la section Fonctions, opérateurs et conditions.
Recherche sur plusieurs colonnes
Cette section décrit deux approches différentes que vous pouvez utiliser pour effectuer une recherche dans plusieurs colonnes d'une table.
Recherche basée sur des jetons
Pour rechercher dans une table les entrées correspondant à un ensemble de termes de recherche, utilisez la fonction SEARCH
. Cette fonction nécessite deux paramètres : où effectuer la recherche et la requête de recherche.
Étant donné que la fonction SEARCH
applique des règles spécifiques sur la façon dont les données sont recherchées, nous vous recommandons de consulter la documentation SEARCH
.
La requête suivante ne conserve que les lignes dont le champ 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. Cela garantit que la fonction SEARCH
recherche une correspondance exacte entre une valeur de champ et la valeur entre les guillemets obliques.
Lorsque des 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 dans laquelle un champ unique 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 l'ID de journal par une instruction semblable à celle-ci:
SEARCH(t,"`cloudaudit.googleapis.com/data_access`")
L'instruction précédente recherche dans la table entière, 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 contenant les champs "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 au lieu d'effectuer une recherche 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 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 et 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 de type STRING
, mais pas le littéral de NULL
.
Par exemple, la requête suivante récupère toutes les entrées du journal d'audit des accès aux données associées à une adresse IP spécifique dont l'horodatage se situe dans une plage de temps spécifique. 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 les lignes de résultats calculées. Par exemple, vous pouvez utiliser des instructions de requête pour fusionner les résultats des instructions SELECT
sur différentes tables ou ensembles de données de différentes manières, puis sélectionner les colonnes des données combinées.
Combiner les données de deux tables à l'aide de jointures
Pour combiner les informations de deux tables, utilisez l'un des opérateurs join. Le type de jointure et la clause conditionnelle que vous utilisez déterminent la manière dont les lignes sont combinées et supprimées.
La requête suivante vous permet d'obtenir les champs json_payload
des lignes de 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 des colonnes span_id
et trace
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
de TABLE_2, ainsi que les valeurs des champs span_id
et trace
auxquels les deux tables ont été jointes, puis 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 à l'aide d'unions
Pour combiner les résultats d'au moins deux instructions SELECT
et supprimer les lignes en double, utilisez l'opérateur UNION
. Pour conserver les lignes en double, utilisez l'opérateur UNION ALL
.
La requête suivante lit les données de l'heure la plus récente provenant 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 l'horodatage, 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
Étapes suivantes
Pour en savoir plus sur le routage et le stockage des entrées de journal, consultez les documents suivants:
- Créer un bucket de journaux
- Mettre à niveau un bucket pour utiliser l'Analyse de journaux
- Associer un bucket de journaux à un ensemble de données BigQuery
- Configurer et gérer les récepteurs
Pour accéder à la documentation de référence SQL, consultez les documents suivants: