SQL 查詢示例

這份文件提供範例查詢，說明如何查詢已升級為使用記錄檔分析的記錄檔 bucket 中儲存的記錄項目。您可以在這些值區中，透過 Google Cloud 控制台的「記錄檔分析」頁面執行 SQL 查詢。如需更多範例，請參閱 logging-analytics-samples 和 security-analytics GitHub 存放區。

本文不會說明 SQL，也不會介紹如何傳送及儲存記錄檔項目。如要瞭解這些主題，請參閱「後續步驟」一節。

本頁面的範例會查詢記錄檢視。如要查詢數據分析檢視區塊，請使用下列路徑格式： `analytics_view.PROJECT_ID.LOCATION.ANALYTICS_VIEW_ID`。在上一個運算式中，PROJECT_ID 是專案的 ID，LOCATION 和 ANALYTICS_VIEW_ID 則是 Analytics 檢視區的位置和名稱。

支援的 SQL 語言

「記錄檔分析」頁面中使用的查詢支援 GoogleSQL 函式，但有部分例外情況。

透過「記錄檔分析」頁面發出的 SQL 查詢，不支援下列 SQL 指令：

DDL 和 DML 指令
JavaScript 使用者定義函式
BigQuery ML 函式
SQL 變數

只有透過 BigQuery Studio 和 Looker Studio 頁面，或使用 bq 指令列工具查詢連結的資料集時，才支援下列功能：

JavaScript 使用者定義函式
BigQuery ML 函式
SQL 變數

最佳做法

如要設定查詢的時間範圍，建議使用時間範圍選取器。舉例來說，如要查看過去一週的資料，請從時間範圍選取器選取「過去 7 天」。你也可以使用時間範圍選取器指定開始和結束時間、指定要查看的時間，以及變更時區。

如果在 WHERE 子句中加入 timestamp 欄位，系統就不會使用時間範圍選取器設定。以下範例說明如何依時間戳記篩選：

-- Matches log entries whose timestamp is within the most recent 1 hour.
WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

如要進一步瞭解如何依時間篩選，請參閱「時間函式」和「時間戳記函式」。

事前準備

本節說明使用記錄分析前必須完成的步驟。

設定記錄檔 bucket

確認記錄檔 bucket 已升級為使用記錄檔分析：

在 Google Cloud 控制台中，前往「Logs Storage」(記錄檔儲存空間) 頁面：
前往「Logs Storage」(記錄檔儲存空間)

如果您是使用搜尋列尋找這個頁面，請選取子標題為「Logging」的結果。
針對要查詢記錄檢視區塊的每個記錄 bucket，請確認「可使用記錄檔分析」欄顯示「開啟」。如果顯示「升級」，請按一下「升級」並完成對話方塊。

設定 IAM 角色和權限

本節說明使用記錄檔分析功能所需的 IAM 角色或權限：

如要取得使用記錄檔分析和查詢記錄檔檢視畫面所需的權限，請要求管理員在專案中授予您下列 IAM 角色：
- 如要查詢 _Required 和 _Default 記錄檔值區：記錄檢視器 (roles/logging.viewer)
- 如要查詢專案中的所有記錄檢視區塊：記錄檢視存取者 (roles/logging.viewAccessor)
如要將主體限制為特定記錄檢視畫面，請在專案層級授予記錄檢視畫面存取者角色時新增 IAM 條件，或在記錄檢視畫面的政策檔案中新增 IAM 繫結。詳情請參閱「控管記錄檢視畫面存取權」。

您必須具備這些權限，才能在 Logs Explorer 頁面查看記錄項目。如要瞭解查詢使用者定義值區中的檢視畫面，或查詢 _Default 記錄檔值區的 _AllLogs 檢視畫面時，需要哪些額外角色，請參閱「Cloud Logging 角色」。
如要取得查詢 Analytics 檢視畫面所需的權限，請要求管理員授予您專案的「可觀測性 Analytics 使用者」(roles/observability.analyticsUser) IAM 角色。

如何使用這個頁面上的查詢

在 Google Cloud 控制台中，前往「Log Analytics」(記錄檔分析) 頁面：
前往「Log Analytics」(記錄檔分析)

如果您是使用搜尋列尋找這個頁面，請選取子標題為「Logging」的結果。
在「查詢」窗格中，按一下「SQL」，然後複製查詢並貼到 SQL 查詢窗格。

複製查詢之前，請在 FROM 子句中替換下列欄位
- PROJECT_ID：專案的 ID。
- LOCATION：記錄檢視畫面或分析資料檢視畫面的位置。
- BUCKET_ID：記錄 bucket 的名稱或 ID。
- LOG_VIEW_ID：記錄檢視的 ID，最多 100 個字元，只能包含英文字母、數字、底線和連字號。
以下顯示記錄檢視畫面的 FROM 子句格式：
```
FROM `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
```
本頁面的記錄範例會查詢記錄檢視。如要查詢數據分析檢視區塊，請使用下列路徑格式： `analytics_view.PROJECT_ID.LOCATION.ANALYTICS_VIEW_ID`。在上一個運算式中，PROJECT_ID 是專案的 ID，LOCATION 和 ANALYTICS_VIEW_ID 則是 Analytics 檢視區的位置和名稱。

注意： 路徑的分隔符號為半形句號 (.)。如果路徑元素 (例如 Google Cloud 專案) 含有半形句號字元，請務必以單引號反引號 (`) 包住每個路徑欄位。

如要在 BigQuery Studio 頁面使用本文顯示的查詢，或使用 bq 指令列工具，請編輯 FROM 子句，然後輸入連結資料集的路徑。舉例來說，如要查詢專案 myproject 中名為 mydataset 的連結資料集上的檢視區塊，路徑為 myproject.mydataset._AllLogs。_AllLogs

常見用途

本節列出幾個常見用途，協助您建立自訂查詢。

在預設記錄值區中顯示記錄項目

如要查詢 _Default bucket，請執行下列查詢：

SELECT
  timestamp, severity, resource.type, log_name, text_payload, proto_payload, json_payload
FROM
  `PROJECT_ID.LOCATION._Default._AllLogs`
-- Limit to 1000 entries
LIMIT 1000

使用規則運算式擷取欄位值

如要使用規則運算式從字串中擷取值，請使用 REGEXP_EXTRACT 函式：

SELECT
  -- Display the timestamp, and the part of the name that begins with test.
  timestamp, REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  -- Get the value of jobName, which is a subfield in a JSON structure.
  JSON_VALUE(json_payload.jobName) IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20

詳情請參閱 REGEXP_EXTRACT 說明文件。

如要比對子字串 (如先前的查詢)，使用 CONTAINS_SUBSTR 函式可提高查詢效率。

篩選記錄項目

如要對查詢套用篩選器，請新增 WHERE 子句。您在這個子句中使用的語法取決於欄位的資料類型。本節提供幾種不同資料類型的範例。

依酬載類型篩選記錄項目

記錄項目可採用下列三種輸出內容類型之一。如要依酬載類型篩選記錄項目，請使用下列其中一個子句：

文字酬載

-- Matches log entries that have a text payload.
WHERE text_payload IS NOT NULL

JSON 酬載

-- Matches log entries that have a JSON payload.
WHERE json_payload IS NOT NULL

Proto 酬載

-- Matches log entries that have a proto payload.
-- Because proto_payload has a data type of RECORD, this statement tests
-- whether a mandatory subfield exits.
WHERE proto_payload.type IS NOT NULL

在查詢結果中，json_payload 和 proto_payload 欄位都會以 JSON 格式呈現，您可以瀏覽這些欄位。

依時間戳記篩選記錄資料

如要依時間戳記篩選記錄項目，建議使用時間範圍選取器。不過，您也可以在 WHERE 子句中指定 timestamp：

-- Matches log entries whose timestamp is within the most recent hour
WHERE timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

如要進一步瞭解如何依時間篩選，請參閱「時間函式」和「時間戳記函式」。

依資源篩選

如要依資源篩選記錄資料，請將 resource.type 陳述式新增至 WHERE 子句：

-- Matches log entries whose resource type is gce_instance
WHERE resource.type = "gce_instance"

依嚴重程度篩選

如要依嚴重性篩選記錄資料，請在 WHERE 子句中新增 severity 陳述式：

-- Matches log entries whose severity is INFO or ERROR
WHERE severity IS NOT NULL AND severity IN ('INFO', 'ERROR')

您也可以依 severity_number (整數) 篩選記錄項目。舉例來說，下列子句會比對嚴重性等級至少為 NOTICE 的所有記錄項目：

-- Matches log entries whose severity level is at least NOTICE
WHERE severity_number IS NOT NULL AND severity_number > 200

如要瞭解列舉值，請參閱 LogSeverity。

依記錄檔名稱篩選

如要依記錄檔名稱篩選記錄檔資料，請將 log_name 或 log_id 陳述式新增至 WHERE 子句：

記錄名稱會指定資源路徑：

-- Matches log entries that have the following log ID.
WHERE log_name="projects/cloud-logs-test-project/logs/cloudaudit.googleapis.com%2Factivity"

記錄 ID 會省略資源路徑：

-- Matches log entries that have the following log id.
WHERE log_id = "cloudaudit.googleapis.com/data_access"

依資源標籤篩選記錄項目

資源標籤會儲存為 JSON 結構。如要依據 JSON 結構中的欄位值篩選，請使用 JSON_VALUE 函式：

SELECT
  timestamp, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  -- Matches log entries whose resource type is gce_instance and whose zone is
  -- us-central1-f. Because resource has data type JSON, you must use JSON_VALUE
  -- to get the value for subfields, like zone.
  resource.type = "gce_instance" AND
  JSON_VALUE(resource.labels.zone) = "us-central1-f"
ORDER BY timestamp ASC

先前的查詢依賴資源標籤的格式，因為這些標籤會儲存在記錄項目中。以下是資源欄位的範例：

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

如要瞭解所有可擷取及轉換 JSON 資料的函式，請參閱 JSON 函式。

依 HTTP 要求篩選

如要只查詢含有 HTTP 要求欄位的記錄項目，請使用下列子句：

-- Matches log entries that have a HTTP request_method field.
-- Don't compare http_request to NULL. This field has a data type of RECORD.
WHERE http_request.request_method IS NOT NULL

您也可以使用 IN 陳述式：

-- Matches log entries whose HTTP request_method is GET or POST.
WHERE http_request.request_method IN ('GET', 'POST')

依 HTTP 狀態篩選

如要只查詢具有 HTTP 狀態的記錄項目，請使用下列子句：

-- Matches log entries that have an http_request.status field.
WHERE http_request.status IS NOT NULL

依 JSON 資料類型中的欄位篩選

如要只在 JSON 資料類型欄位的子欄位具有特定值時查詢記錄項目，請使用 JSON_VALUE 函式擷取值：

-- Compare the value of the status field to NULL.
WHERE JSON_VALUE(json_payload.status) IS NOT NULL

前一個子句與下列子句略有不同：

-- Compare the status field to NULL.
WHERE json_payload.status IS NOT NULL

第一個子句會測試狀態欄位的值是否為 NULL。第二個子句會測試狀態欄位是否存在。假設記錄檢視包含兩個記錄項目。在一個記錄項目中，json_payload 欄位採用以下形式：

{
    status: {
        measureTime: "1661517845"
    }
}

另一個記錄項目的 json_payload 欄位結構不同：

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

子句 WHERE json_payload.status IS NOT NULL 會比對兩個記錄項目。不過，WHERE JSON_VALUE(json_payload.status) IS NOT NULL 子句只會比對第二個記錄項目。

將記錄項目分組及匯總

本節將以先前的範例為基礎，說明如何分組及彙整記錄項目。如果您未指定分組，但指定了匯總，則會列印一個結果，因為 SQL 會將滿足 WHERE 子句的所有資料列視為一個群組。

每個 SELECT 運算式都必須包含在群組欄位中，或經過匯總。

依時間戳記將記錄項目分組

如要依時間戳記分組資料，請使用 TIMESTAMP_TRUNC 函式，將時間戳記截斷至指定精細程度 (如 HOUR)：

SELECT
  -- Truncate the timestamp by hour.
  TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,
  JSON_VALUE(json_payload.status) AS status,
  -- Count the number log entries in each group.
  COUNT(*) AS count
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  -- Matches log entries that have a status field whose value isn't NULL.
  json_payload IS NOT NULL AND JSON_VALUE(json_payload.status) IS NOT NULL
GROUP BY
  -- Group by hour and status
  hour,status
ORDER BY hour ASC

詳情請參閱 TIMESTAMP_TRUNC 說明文件和日期時間函式。

依資源將記錄項目分組

下列查詢說明如何依資源類型將記錄項目分組，然後計算每個群組中的記錄項目數：

SELECT
   -- Count the number of log entries for each resource type
   resource.type, COUNT(*) AS count
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
GROUP BY resource.type
LIMIT 100

依嚴重性將記錄項目分組

下列查詢說明如何依嚴重性將記錄項目分組，然後計算每個群組中的記錄項目數：

SELECT
  -- Count the number of log entries for each severity.
  severity, COUNT(*) AS count
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  severity IS NOT NULL
GROUP BY severity
ORDER BY severity
LIMIT 100

依據記錄項目的 `log_id` 分組

下列查詢說明如何依記錄 ID 將記錄項目分組，然後計算每個群組中的記錄項目數：

SELECT
  -- Count the number of log entries for each log ID.
  log_id, COUNT(*) AS count
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
GROUP BY log_id
ORDER BY count DESC
LIMIT 100

計算每個網址的 HTTP 要求平均延遲時間

下列查詢說明如何依 HTTP 要求網址和位置將記錄項目分組，然後計算每個群組中的記錄項目數：

SELECT
  -- Compute the average latency for each group. Because the labels field has a
  -- data type of JSON, use JSON_VALUE to get the value of checker_location.
  JSON_VALUE(labels.checker_location) AS location,
  AVG(http_request.latency.seconds) AS secs, http_request.request_url
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  -- Matches log entries when the request_method field is GET.
  http_request IS NOT NULL AND http_request.request_method IN ('GET')
GROUP BY
  -- Group by request URL and location
  http_request.request_url, location
ORDER BY location
LIMIT 100

計算子網路測試的平均傳送位元組數

下列查詢說明如何依資源標籤中指定的位置將記錄項目分組，然後計算每個群組中的記錄項目數：

SELECT
  -- Compute the average number of bytes sent per location. Because labels has
  -- a data type of JSON, use JSON_VALUE to get the value of the location field.
  -- bytes_sent is a string. Must cast to a FLOAT64 before computing average.
  JSON_VALUE(resource.labels.location) AS location,
  AVG(CAST(JSON_VALUE(json_payload.bytes_sent) AS FLOAT64)) AS bytes
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  resource.type = "gce_subnetwork" AND json_payload IS NOT NULL
GROUP BY
  -- Group by location
  location
LIMIT 100

詳情請參閱 JSON 函式和轉換函式。

計算欄位符合模式的記錄項目

如要傳回與規則運算式相符的子字串，請使用 REGEXP_EXTRACT 函式：

SELECT
  -- Extract the value that begins with test.
  -- Count the number of log entries for each name.
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID`
WHERE
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

如需其他範例，請參閱REGEXP_EXTRACT說明文件。

跨欄搜尋

本節說明兩種方法，可用於搜尋所查詢檢視區塊的多個資料欄：

以權杖為準的搜尋：指定搜尋位置和搜尋查詢，然後使用 SEARCH 函式。由於 SEARCH 函式有特定的資料搜尋規則，建議您參閱 SEARCH 說明文件。
以子字串為準的搜尋：您提供搜尋位置和字串常值，然後使用 CONTAINS_SUBSTR 函式。系統會執行不區分大小寫的測試，判斷運算式中是否存在字串常值。如果字串常值存在，CONTAINS_SUBSTR 函式會傳回 TRUE，否則會傳回 FALSE。搜尋值必須是 STRING 常值，但不得為 NULL 常值。

在記錄檢視畫面中依權杖搜尋

下列查詢只會保留欄位與「35.193.12.15」完全相符的資料列：

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` AS t
WHERE
  -- Search data access audit logs for the IP address that matches 35.193.12.15.
  -- The use of backticks prevents the string from being tokenized.
  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

如果查詢字串中省略反引號，系統會根據SEARCH說明文件中定義的規則分割查詢字串。舉例來說，執行下列陳述式時，查詢字串會分割為四個符記：「35」、「193」、「12」和「15」：

  SEARCH(t,"35.193.12.15")

先前的 SEARCH 陳述式會在單一欄位符合所有四個符記時，比對資料列。權杖的順序沒有影響。

您可以在查詢中加入多個 SEARCH 陳述式。舉例來說，在先前的查詢中，您可以將記錄 ID 的篩選條件替換為下列陳述式：

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

先前的陳述式會搜尋記錄檢視畫面中記錄項目的每個欄位，而原始陳述式只會搜尋記錄項目的 log_id 欄位。

如要對多個欄位執行多項搜尋，請以半形空格分隔個別字串。舉例來說，下列陳述式會比對欄位包含「Hello World」、「happy」和「days」的資料列：

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

最後，您可以搜尋特定欄位，不必搜尋整個表格。舉例來說，下列陳述式只會搜尋名為 text_payload 和 json_payload 的資料欄：

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

如要瞭解 SEARCH 函式的參數處理方式，請參閱 BigQuery 參考頁面「搜尋函式」。

在記錄檢視中搜尋子字串

舉例來說，下列查詢會擷取所有具有特定 IP 位址的資料存取稽核記錄項目，且這些項目的時間戳記位於特定時間範圍內。最後，查詢會排序結果，然後顯示最舊的 20 個結果：

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_ID` AS t
WHERE
  -- Search data access audit logs for the IP address that matches 35.193.12.15.
  -- CONTAINS_SUBSTR performs a contains-test.
  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

查詢多個檢視區塊

查詢陳述式可掃描一或多個資料表或運算式，然後傳回運算的結果資料列。舉例來說，您可以使用查詢陳述式，以各種方式合併不同資料表或資料集中的 SELECT 陳述式結果，然後從合併的資料中選取資料欄。

如要聯結檢視區塊，請遵守下列限制：

檢視區塊的位置符合下列其中一項條件：
- 所有檢視區都有相同的位置。
- 所有檢視畫面都位於 global 或 us 位置。
如果儲存空間資源使用客戶自行管理的加密金鑰 (CMEK)，則必須符合下列其中一項條件：
- 使用 CMEK 的儲存空間資源會使用相同的 Cloud KMS 金鑰。
- 使用 CMEK 的儲存空間資源具有共同祖先，而該祖先會指定與儲存空間資源位於相同位置的預設 Cloud KMS 金鑰。
當一或多個儲存空間資源使用 CMEK 時，系統會使用通用 Cloud KMS 金鑰或上層的預設 Cloud KMS 金鑰，加密聯結產生的暫時資料。

舉例來說，假設您有兩個位於相同位置的檢視區塊。接著，在符合下列任一條件時，即可聯結這些檢視畫面：

儲存空間資源不會使用 CMEK。
一個儲存空間資源使用 CMEK，另一個則否。
兩個儲存空間資源都使用 CMEK，且都使用相同的 Cloud KMS 金鑰。
這兩種儲存空間資源都使用 CMEK，但使用的金鑰不同。不過，這些資源共用一個祖先，該祖先會指定與儲存空間資源位於相同位置的預設 Cloud KMS 金鑰。

舉例來說，假設記錄 bucket 和可觀測性 bucket 的資源階層包含同一個機構。如果已為機構設定 Cloud Logging 的預設資源設定，並為可觀測性 bucket 設定儲存位置的預設 Cloud KMS 金鑰，即可加入這些 bucket 的檢視畫面。

依追蹤記錄 ID 彙整兩個記錄檢視畫面

如要合併兩個資料表的資訊，請使用其中一個 join 運算子：

SELECT
  -- Do an inner join on two tables by using the span ID and trace ID.
  -- Don't join only by span ID, as this field isn't globally unique.
  -- From the first view, show the timestamp, severity, and JSON payload.
  -- From the second view, show the JSON payload.
  a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace
FROM  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_1` a
JOIN  `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_2` b
ON
  a.span_id = b.span_id AND
  a.trace = b.trace
LIMIT 100

使用聯集陳述式查詢兩個記錄檢視區塊

如要合併兩個以上的 SELECT 陳述式結果並捨棄重複資料列，請使用 UNION 運算子。如要保留重複的資料列，請使用 UNION ALL 運算子：

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
-- Create a union of two log views
FROM(
  SELECT * FROM `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_1`
  UNION ALL
  SELECT * FROM `PROJECT_ID.LOCATION.BUCKET_ID.LOG_VIEW_2`
)
-- Sort the union by timestamp.
ORDER BY timestamp ASC
LIMIT 100

移除重複的記錄項目

記錄檔分析不會在執行查詢前移除重複的記錄項目。使用記錄檔瀏覽器查詢記錄項目時，系統會比較記錄名稱、時間戳記和插入 ID 欄位，並移除重複項目，這與上述行為不同。

您可以使用資料列層級驗證來移除重複的記錄項目。

詳情請參閱「疑難排解：記錄檔分析結果中出現重複的記錄項目」。

後續步驟

如要瞭解如何轉送及儲存記錄檔項目，請參閱下列文件：

如需 SQL 參考說明文件，請參閱下列文件：