Open11

Cloud Logging の Log Analytics 使ってみる

nbstshnbstsh

Sample SQL Queries

https://cloud.google.com/logging/docs/analyze/examples

nbstshnbstsh

Filter by time

To set the time range of your query, use the time-range selector or add a WHERE clause that specifies the timestamp field. We recommend that you use the time-range selector to specify the time range.

time-range selector 使うのがおすすめらしい
これ↓

WHERE で timestamp 指定も可能

TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR): １時間前

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

直接日時を timestamp で指定することも可能

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
nbstshnbstsh

Filter by resource

cloud function や cloud run といった種別で filtering する場合は resource.type を使う

resource.type はこれ↓

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
nbstshnbstsh

Filter by severity

Log Explorer のこれが severity↓

severity を WHERE で使えばOK

WHERE
  severity = 'ERROR'

複数の severity で query したい場合は、IN を使う

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

severity に割り当てたれた数値で指定することも可能
以下は、INFO 以上のもの全て

  severity_number IS NOT NULL AND
  severity_number > 200
nbstshnbstsh

Filter by log name

log_name もしくは、log_id での指定

SELECT
  timestamp, log_id, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) AND
  log_id = "cloudaudit.googleapis.com/data_access"
ORDER BY timestamp ASC
LIMIT 100

resource.type と大差ない気がする...
projects/myproject/logs/mylog みたいな値だから、仮に query 対象の log が project を跨いでいた場合に project の指定も込みで行いたい場合とかで使うのか...?

nbstshnbstsh

Filter by resource label

resource.label (JSON) の値で query

resouce はこんな感じのデータが入る↓

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

JSON 型の値で query する場合は、JSON_VALUE function を使う
JSON_VALUE(resource.labels.zone) みたいな感じ

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

function 名で filter

function 名は resource.labels.functions_name に入るので、JSON_VALUE(resource.labels.function_name) を使う

WHERE
  JSON_VALUE(resource.labels.function_name) = "my-function-name"
nbstshnbstsh

Filter by HTTP request

http_request の情報はこんな感じ↓

HTTP request 関連の log には http_request が入るので http_request IS NOT NULL で filter できる。

WHERE
  http_request IS NOT NULL

Record type なので、http_request.request_method のような形で nest した filed での filter が可能。

Request Method で filter

WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET', 'POST')

Request status で filter

WHERE
  http_request IS NOT NULL AND
  http_request.status IS NOT NULL
nbstshnbstsh

Filter by a field with a JSON type

JSON type の値を指定する場合、 JSON_VALUE を利用する。

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