示例 SQL 查询

本文档包含对存储在 升级为使用 Log Analytics 的日志存储分区。 在这些存储分区中,您可以从以下位置运行 SQL 查询: Google Cloud 控制台中的日志分析页面。如需更多示例,请参阅 logging-analytics-samplessecurity-analytics GitHub 代码库。

本文档未介绍 SQL 或如何路由和存储日志条目。如需了解这些主题,请参阅后续步骤部分。

准备工作

  • 如需使用本文档的 Log Analytics 页面中显示的查询,请执行以下操作: 将 TABLE_NAME_OF_LOG_VIEW 替换为日志视图的表名称 您要查询的数据此名称的格式为 project_ID.region.bucket_ID.view_ID

    要找到此名称,请转到 日志分析页面,找到日志视图,然后选择 Query 返回多个结果。Query 窗格显示默认查询, 包含查询的日志视图的表名称。对于 有关如何访问默认查询的信息 请参阅查询日志视图

  • 要在 BigQuery Studio 页面中,将 将 TABLE_NAME_OF_LOG_VIEW 替换为表路径 关联的数据集中。 例如,查询关联数据集 mydataset 上的视图 _AllLogs 位于项目“myproject”中,请将此字段设为 myproject.mydataset._AllLogs:

    在 Google Cloud 控制台中,前往 BigQuery 页面:

    前往 BigQuery Studio

    您也可以使用搜索栏查找此页面。

  • 如需打开 Log Analytics 页面,请执行以下操作:

    1. 在 Google Cloud 控制台中,前往 Log Analytics 页面:

      转到 Log Analytics

      如果您使用搜索栏查找此页面,请选择子标题为 Logging 的结果。

    2. 可选:如需确定日志视图的架构,请在日志视图列表中找到相应视图,然后选择该视图的名称。

    系统随即会显示架构。您可以使用过滤条件字段 来查找特定字段您无法修改架构。

过滤日志

SQL 查询确定要处理日志视图中的条目,然后对其进行分组 这些条目并执行聚合操作。如果未列出任何分组和汇总操作,则查询结果将包含过滤操作选择的行。本部分中的示例说明了过滤功能。

按时间过滤

如需设置查询的时间范围,我们建议您使用时间范围选择器。如果查询未在 WHERE 子句中指定 timestamp 字段,系统会自动使用此选择器。例如,如需查看过去一周的数据,请从时间范围选择器中选择过去 7 天。您还可以使用时间范围 选择器来指定开始时间和结束时间、查看时段以及 更改时区。

如果您在 WHERE 子句中包含 timestamp 字段,则时间范围 。以下示例按 使用 TIMESTAMP_SUB 函数,该函数可让您指定回溯期 从当前时间开始的时间间隔:

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

如需详细了解如何按时间过滤,请参阅时间函数时间戳函数

按资源过滤

如需按资源过滤,请添加 resource.type 限制。

例如,以下查询可读取最近一个小时的数据,然后 保留资源类型与 gce_instance 匹配的行,然后进行排序 最多可显示 100 个条目:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

按严重级别过滤

您可以按特定严重程度过滤,并设置如下限制: severity = 'ERROR'。另一种方法是使用 IN 语句 并指定一组有效值。

例如,以下查询会读取最近一小时的数据,然后仅保留包含值为 'INFO''ERROR'severity 字段的行:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  severity IS NOT NULL AND
  severity IN ('INFO', 'ERROR')
ORDER BY timestamp ASC
LIMIT 100

上一个查询按 severity 字段的值进行过滤。不过,您还可以编写按日志严重级别的数值过滤的查询。例如,如果您将 severity 行替换为以下代码: 查询会返回严重级别至少为 NOTICE 的所有日志条目:

  severity_number IS NOT NULL AND
  severity_number > 200

如需了解枚举值,请参阅 LogSeverity

按日志名称过滤

要按日志名称进行过滤,您可以为 log_namelog_id 字段。log_name 字段包含资源路径。也就是说,此字段包含类似 projects/myproject/logs/mylog 的值。 log_id 字段仅存储日志名称,例如 mylog

例如,以下查询可读取最近一个小时的数据,然后 并保留 log_id 字段中的值是 cloudaudit.googleapis.com/data_access,然后对 结果:

SELECT
  timestamp, log_id, severity, json_payload, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  log_id = "cloudaudit.googleapis.com/data_access"
ORDER BY timestamp ASC
LIMIT 100

按资源标签过滤

大多数受监控资源描述符都会定义用于标识特定资源的标签。例如,Compute Engine 实例的描述符 包含可用区、项目 ID 和实例 ID 的标签。写入日志条目时,系统会为每个字段分配值。以下就是这样的示例:

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

由于 labels 字段的数据类型为 JSON,因此在查询中添加 resource.labels.zone = "us-centra1-f" 等限制会导致语法错误。如需获取数据类型为 JSON 的字段的值,请使用函数 JSON_VALUE

例如,以下查询可读取最新的数据,然后保留 资源是 Compute Engine 实例的那些行, 位于 us-central1-f 可用区中:

SELECT
  timestamp, log_name, severity, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  resource.type = "gce_instance" AND
  JSON_VALUE(resource.labels.zone) = "us-central1-f"
ORDER BY timestamp ASC
LIMIT 100

了解可以检索和转换 JSON 的所有函数 请参阅 JSON 函数

按 HTTP 请求过滤

过滤日志视图,使其仅包含与 HTTP 请求对应的日志条目 请求或回复,添加 http_request IS NOT NULL 限制:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  http_request IS NOT NULL
ORDER BY timestamp
LIMIT 100

以下查询仅包含与 GETPOST 对应的行 请求:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET', 'POST')
ORDER BY timestamp ASC
LIMIT 100

按 HTTP 状态过滤

如需按 HTTP 状态过滤,请修改 WHERE 子句,以要求定义 http_request.status 字段:

SELECT
  timestamp, log_name, http_request.status, http_request, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  http_request IS NOT NULL AND
  http_request.status IS NOT NULL
ORDER BY timestamp ASC
LIMIT 100

如需确定字段中存储的数据类型,请查看架构或显示 字段。上一个查询的结果显示 http_request.status 字段存储整数值。

按 JSON 类型的字段过滤

如需从数据类型为 JSON 的列中提取值,请使用函数 JSON_VALUE

请考虑以下查询:

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

SELECT
  json_payload
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  JSON_VALUE(json_payload.status) IS NOT NULL

前面的查询测试了日志条目中的 json_payload 字段的值。两个查询 舍弃不包含标记为“json_payload”的字段的日志条目。 这两个查询之间的差异就是最后一行,它定义了 针对 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"
}

前面的两个日志条目都符合限制 json_payload.status IS NOT NULL。 也就是说,第一个查询的结果包含这两个日志条目。不过,当限制为 JSON_VALUE(json_payload.status) IS NOT NULL 时,查询结果中仅包含第二个日志条目。

按正则表达式过滤

要返回与正则表达式匹配的子字符串,请使用函数 REGEXP_EXTRACT。此函数的返回类型为 STRINGBYTES

以下查询会显示收到的最新日志条目,保留包含 json_payload.jobName 字段的条目,然后显示名称中以 test 开头的部分:

SELECT
  timestamp, REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  json_payload.jobName IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20

如需查看更多示例,请参阅 REGEXP_EXTRACT 文档。 如需查看可用的其他正则表达式示例,请参阅函数、运算符和条件

此示例中显示的查询效率不高。对于子字符串匹配,例如 如图中所示,请使用 CONTAINS_SUBSTR 函数。

对日志条目进行分组和汇总

本部分在前面的示例的基础上,说明了如何 对日志条目进行分组和汇总如果您未指定分组,但 则输出单个结果,因为 SQL 会将所有 将满足 WHERE 子句的所有行作为一个组。

每个 SELECT 表达式都必须包含在组字段中或者进行汇总。

按时间分组

如需按时间对数据进行分组,请使用函数 TIMESTAMP_TRUNC,该函数会将时间戳截断为指定的精细度(例如 MINUTE)。对于 例如 15:30:11 的时间戳,其格式为 hours:minutes:seconds,当粒度设置为 15:30:00 时, MINUTE

以下查询会读取时间范围选择器指定的时间范围内收到的数据,然后保留 json_payload.status 字段的值不为 NULL 的行。该查询按小时截断每行的时间戳,然后对 行(按截断的时间戳和状态排序):

SELECT
  TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,
  JSON_VALUE(json_payload.status) AS status,
  COUNT(*) AS count
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  json_payload IS NOT NULL AND
  JSON_VALUE(json_payload.status) IS NOT NULL
GROUP BY hour,status
ORDER BY hour ASC

如需查看其他示例,请参阅 TIMESTAMP_TRUNC 文档。如需了解其他基于时间的函数,请参阅日期时间函数

按资源分组

以下查询会读取最近一个小时的数据,然后将 日志条目。然后,它会计算每个 以及返回一个包含两列的表。第一列列出了 第二列是该资源类型的行数 资源类型:

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

按严重程度分组

以下查询可读取最近一个小时的数据,然后保留行 包含 severity 字段的应用。然后,该查询会按严重程度对行进行分组,并统计每个组的行数:

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  severity IS NOT NULL
GROUP BY severity
ORDER BY severity
LIMIT 100

log_id分组

以下查询的结果是一个包含两列的表。第一个 列列出了日志名称,第二列列出了 写入日志的日志条目。通过 查询会按条目数对结果进行排序:

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

计算 HTTP 请求的平均延迟时间

以下查询展示了按多个列进行分组并计算平均值。该查询会按 HTTP 请求中包含的网址和 labels.checker_location 字段的值对行进行分组。对行进行分组后,查询会计算每个组的平均延迟时间:

SELECT
  JSON_VALUE(labels.checker_location) AS location,
  AVG(http_request.latency.seconds) AS secs, http_request.request_url
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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

在前面的表达式中,由于 labels 的数据类型为 JSON,因此必须使用 JSON_VALUE 提取 labels.checker_location 字段的值。不过,您不会使用此函数从 http_request.latency.seconds 字段中提取值。第二个字段的数据类型为 整数。

计算为子网测试发送的平均字节数

以下查询展示了如何按位置显示发送的平均字节数。

该查询会读取最近一小时的数据,然后仅保留资源类型列为 gce_subnetworkjson_payload 列不为 NULL 的行。接下来,查询 资源。与上例中将数据存储为数字值不同,bytes_sent 字段的值是字符串,因此您必须先将该值转换为 FLOAT64,然后才能计算平均值:

SELECT JSON_VALUE(resource.labels.location) AS location,
   AVG(CAST(JSON_VALUE(json_payload.bytes_sent) AS FLOAT64)) AS bytes
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  resource.type = "gce_subnetwork" AND
  json_payload IS NOT NULL
GROUP BY location
LIMIT 100

上一个查询的结果是一个表,表中的每一行都列出了一个位置 以及为该位置发送的平均字节数。

了解可以检索和转换 JSON 的所有函数 请参阅 JSON 函数

如需了解 CAST 和其他转换函数,请参阅转换函数

统计包含与模式匹配的字段的日志条目数

如需返回与正则表达式匹配的子字符串,请使用函数 REGEXP_EXTRACT。此函数的返回类型为 STRINGBYTES

以下查询会保留 json_payload.jobName 字段的值不为 NULL 的日志条目。然后,它会按名称后缀(以 尽在 test。最后,查询计算每组中的条目数:

SELECT
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `TABLE_NAME_OF_LOG_VIEW`
WHERE
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

如需查看更多示例,请参阅 REGEXP_EXTRACT 文档。有关其他正则表达式 请参阅函数、运算符和条件

本部分介绍了可用于搜索 多个列。

要在日志视图中搜索与一组搜索字词匹配的条目,请执行以下操作: 使用函数 SEARCH。此函数需要两个参数: 搜索位置以及搜索查询。 由于 SEARCH 函数对数据搜索方式有特定规则,因此我们建议您阅读 SEARCH 文档

以下查询仅会保留字段与“35.193.12.15”完全匹配的行:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW` 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

在上一个查询中,反引号用于封装要搜索的值。这可确保 SEARCH 函数在字段值与反引号之间的值之间搜索完全匹配项。

如果在查询字符串中省略了反引号,系统会根据 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_payloadjson_payload 的列:

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

如需了解如何处理 SEARCH 函数的参数,请参阅 BigQuery 参考页面搜索函数

要执行不区分大小写的测试来确定 表达式,请使用函数 CONTAINS_SUBSTR。 当该值存在时,此函数会返回 TRUE,并且 否则为 FALSE。搜索值必须是 STRING 字面量,但不能是 字面量 NULL

例如,以下查询会提取所有数据访问审核日志条目 特定 IP 地址,且时间戳在特定时间范围内。 最后,查询会对结果进行排序,然后显示最早的 20 条结果:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW` 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

上一个查询会执行子字符串测试。因此,包含“35.193.12.152”的行与 CONTAINS_SUBSTR 语句匹配。

合并来自多个来源的数据

查询语句会扫描一个或多个表或表达式,并返回 计算结果行。例如,您可以使用查询语句以多种方式合并对不同表或数据集执行的 SELECT 语句的结果,然后从合并的数据中选择列。

使用联接合并两个表中的数据

如需组合两个表中的信息,请使用某个联接运算符。联接类型和您使用的条件子句决定了行如何组合和舍弃。

以下查询为您提供了json_payload 由同一跟踪记录 span 写入的两个不同的表。该查询会对两个表执行内部分 JOIN,找出两个表中 span_idtrace 列值匹配的行。然后,查询会从此结果中选择来自 TABLE_NAME_OF_LOG_VIEW_1timestampseverityjson_payload 字段、来自 ABLE_NAME_OF_LOG_VIEW_2json_payload 字段,以及用于联接这两个表的 span_idtrace 字段的值,并最多返回 100 行:

SELECT
  a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace
FROM `TABLE_NAME_OF_LOG_VIEW_1` a
JOIN `ABLE_NAME_OF_LOG_VIEW_2` b
ON
  a.span_id = b.span_id AND
  a.trace = b.trace
LIMIT 100

将多个选择与联合结合

合并两个或更多 SELECT 语句的结果并舍弃 请使用 UNION 运算符。如需保留重复行,请使用 UNION ALL 运算符。

以下查询从 TABLE_NAME_OF_LOG_VIEW_1 - 将结果与最近一个小时的内容合并 来自 ABLE_NAME_OF_LOG_VIEW_2 的数据,按升序对合并的数据进行排序 timestamp,然后显示最早的 100 个条目:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM(
  SELECT * FROM `TABLE_NAME_OF_LOG_VIEW_1`
  UNION ALL
  SELECT * FROM `ABLE_NAME_OF_LOG_VIEW_2`
)
ORDER BY timestamp ASC
LIMIT 100

限制

Log Analytics 页面中使用的查询支持 GoogleSQL 函数,但存在一些例外情况。

使用 Log Analytics 页面:

  • DDL 和 DML 命令
  • JavaScript 用户定义的函数
  • BigQuery ML 函数
  • SQL 变量

仅当您使用 BigQuery StudioLooker Studio 页面,以及 bq 命令行工具

  • JavaScript 用户定义的函数
  • BigQuery ML 函数
  • SQL 变量

后续步骤

如需了解如何路由和存储日志条目, 请参阅以下文档:

如需查看 SQL 参考文档,请参阅以下文档: