BigQuery查询如何筛选不在指定IP段列表内的callerIp日志

你当前遇到的语法错误，是Legacy SQL的语法规则触发的，核心问题两点：

• Legacy SQL中用于全文检索匹配的:操作符，后面的内容不会作为完整字符串被解析，会自动按空格拆分为多个独立的匹配字面值。你写的x.y.z.f-x.y.z.e格式的IP范围片段，会被解析器从开头的x.y结构识别为非法的浮点数字面量，直接抛出对应行、列位置的报错。
• 这种解析逻辑本身也不支持传入IP范围字符串做匹配，仅能匹配单个完整的IP字面值，完全无法识别起始IP-结束IP的范围格式。把多个IP范围直接用空格拼在同一个引号里的写法，本身也不符合语法规则。

不需要改动已验证正确的查询逻辑，仅需替换原来的IP排除条件，有两种适配Legacy SQL的实现方式：

• 基于BETWEEN的范围判断（写法最直观，推荐优先用）
  直接把原来单条带范围的匹配，拆成独立的IP段上下限判断。
  注：IPv4地址的字符串字典排序和实际网络地址的数值顺序完全一致，直接用BETWEEN做字符串范围匹配，不会出现漏匹配、错匹配的问题，不需要额外转换为整数计算。

原来的错误写法：

-- 触发解析报错的写法
AND NOT (protopayload_auditlog.requestMetadata.callerIp : "IP-RANGE IP-RANGE IP-RANGE IP-RANGE")

替换后的正确写法：

AND NOT (
  -- 逐个填入每个IP范围的起始、结束值即可
  (protopayload_auditlog.requestMetadata.callerIp BETWEEN "x1.y1.z1.f1" AND "x1.y1.z1.e1")
  OR (protopayload_auditlog.requestMetadata.callerIp BETWEEN "x2.y2.z2.f2" AND "x2.y2.z2.e2")
  OR (protopayload_auditlog.requestMetadata.callerIp BETWEEN "x3.y3.z3.f3" AND "x3.y3.z3.e3")
  OR (protopayload_auditlog.requestMetadata.callerIp BETWEEN "x4.y4.z4.f4" AND "x4.y4.z4.e4")
)

• 基于正则的批量匹配（适合IP范围较多的场景）
  把所有要排除的IP段转换为正则匹配规则，用REGEXP_MATCH做统一判断，减少重复的判断逻辑：

AND NOT REGEXP_MATCH(
  protopayload_auditlog.requestMetadata.callerIp,
  r"^(IP1正则|IP2正则|IP3正则|IP4正则)$"
)

注意正则里的IP点号需要加反斜杠转义，避免被识别为通配符。比如IP段10.1.2.0-10.1.2.255对应的正则片段就是10\.1\.2\.[0-9]{1,3}。

替换错误条件后，可直接运行的完整LegacySQL语句如下：

#legacySQL
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS principalEmail,
  resource.labels.project_id AS project_id,
  resource.labels.bucket_name AS bucket_name,
  resource.labels.method AS method,
  protopayload_auditlog.requestMetadata.callerIp AS callerIp,
  timestamp AS timestamp
FROM (TABLE_DATE_RANGE([projectid.organisation_audit_logging.cloudaudit_googleapis_com_data_access_],
      DATE_ADD(CURRENT_TIMESTAMP(), -40, 'DAY'),
      DATE_ADD(CURRENT_TIMESTAMP(), -38, 'DAY')))
WHERE
  protopayload_auditlog.serviceName = "storage.googleapis.com"
  AND resource.labels.method = 'google.storage.objects.get'
  AND REGEXP_MATCH(protopayload_auditlog.authenticationInfo.principalEmail, r"^.*@mycompany\.com")
  -- 替换为上述两种方案任意一种的IP排除条件即可
  AND NOT (
    (protopayload_auditlog.requestMetadata.callerIp BETWEEN "第一个IP段起始值" AND "第一个IP段结束值")
    OR (protopayload_auditlog.requestMetadata.callerIp BETWEEN "第二个IP段起始值" AND "第二个IP段结束值")
    OR (protopayload_auditlog.requestMetadata.callerIp BETWEEN "第三个IP段起始值" AND "第三个IP段结束值")
    OR (protopayload_auditlog.requestMetadata.callerIp BETWEEN "第四个IP段起始值" AND "第四个IP段结束值")
  )
LIMIT 500

内容的提问来源于stack exchange，提问作者ellefc