本页面列出了一些示例 SQL 查询,以便在将资产数据导出到 BigQuery 后对其进行分析。如需了解详情,请参阅标准 SQL 查询语法。
直接查询可用列
要查找每种资产类型的数量,请运行以下查询:
SELECT asset_type, COUNT(*) AS asset_count
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
GROUP BY asset_type
ORDER BY asset_count DESC
查询重复字段
如需查找向 Gmail 账号授予访问权限的 Identity and Access Management (IAM) 政策,请运行以下查询。BigQuery 使用 UNNEST
将重复字段展平为可以直接查询的表:
SELECT name, asset_type, bindings.role
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
JOIN UNNEST(iam_policy.bindings) AS bindings
JOIN UNNEST(bindings.members) AS principals
WHERE principals like "%@gmail.com"
如需查找允许使用公共 IP 进行创建的组织、文件夹或项目,请运行以下查询。此查询非常有用,因为除非配置 SSL 或代理,否则允许将公共 IP 地址用于 Cloud SQL 实例可能会引入漏洞:
SELECT name
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
JOIN UNNEST(org_policy) AS op
WHERE
op.constraint = "constraints/sql.restrictPublicIp"
AND (op.boolean_policy IS NULL OR op.boolean_policy.enforced = FALSE);
如需在项目的同一 VPC Service Controls 服务边界中查找组织、文件夹或项目,请运行以下查询:
SELECT service_perimeter.title, service_perimeter.status.resources
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
CROSS JOIN UNNEST(service_perimeter.status.resources) as resource
WHERE resource = "projects/PROJECT_ID";
查询提取的 JSON 字符串
如需查找打开的防火墙规则,请运行以下查询。详细了解 BigQuery 中使用的 JSON 函数。
CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return JSON.parse(json).map(x=>JSON.stringify(x));
""";
SELECT firewall.name, firewall.resource.parent, JSON_EXTRACT(firewall.resource.data, '$.sourceRanges') AS sourceRanges
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` AS firewall
JOIN UNNEST(json2array(JSON_EXTRACT(firewall.resource.data, '$.sourceRanges'))) AS source_ranges
WHERE asset_type="compute.googleapis.com/Firewall" AND JSON_EXTRACT(firewall.resource.data, '$.sourceRanges') IS NOT NULL AND JSON_EXTRACT_SCALAR(source_ranges, '$') = "0.0.0.0/0"
通过按资源类型导出到不同的表,我们可以更轻松、更快速地查找打开的防火墙规则。
SELECT firewall.name, firewall.resource.parent, sourceRanges
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_INSTANCE_TABLE_NAME` AS firewall
JOIN UNNEST(firewall.resource.data.sourceRanges) AS sourceRanges
WHERE sourceRanges = "0.0.0.0/0";
查询联接的资源类型
要联接不同资源类型的表,请运行以下查询。以下示例显示了如何查找未挂接任何虚拟机的所有子网。首先,查询会查找所有子网。然后,查询会从该列表中选择其 selfLinks
不存在的子网。
CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
return JSON.parse(json).map(x=>JSON.stringify(x));
""";
SELECT name, JSON_EXTRACT(subnetwork.resource.data, '$.selfLink') AS selflink
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` AS subnetwork
WHERE asset_type = "compute.googleapis.com/Subnetwork" AND (JSON_EXTRACT(subnetwork.resource.data, '$.selfLink') NOT IN
(SELECT DISTINCT JSON_EXTRACT(network_interfaces, '$.subnetwork')
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` as instance
JOIN UNNEST(json2array(JSON_EXTRACT(instance.resource.data, '$.networkInterfaces'))) AS network_interfaces
WHERE asset_type ="compute.googleapis.com/Instance"
AND JSON_EXTRACT(instance.resource.data, '$.networkInterfaces') IS NOT NULL
)) IS NULL
通过按资源类型分隔表,我们可以通过更轻松快捷的查询来查找未挂接虚拟机的所有子网。
SELECT name, subnetwork.resource.data.selfLink
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_SUBNETWORK_TABLE_NAME` AS subnetwork
WHERE
(
subnetwork.resource.data.selfLink
NOT IN (
SELECT DISTINCT networkInterface.subnetwork
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_INSTANCE_TABLE_NAME` as instance
JOIN
UNNEST(instance.resource.data.networkInterfaces) AS networkInterface
WHERE
networkInterface IS NOT NULL
)
) IS NULL;
查找由于 CVE-2021-44228 导致的易受攻击的 Dataproc 集群的查询
CREATE TEMP FUNCTION vulnerable_version(imageVersion STRING)
RETURNS BOOL
LANGUAGE js AS r"""
const version_regexp = /(?<major>\d+)(?:\.)(?<minor>\d+)(?:\.)?(?<sub>\d+)?/g;
let match = version_regexp.exec(imageVersion);
if(match.groups.major < 1){
return true;
}
if (match.groups.major == 1){
if (match.groups.minor < 3){
return true;
}
if(match.groups.minor == 3 && match.groups.sub < 95){
return true;
}
if(match.groups.minor == 4 && match.groups.sub < 77){
return true;
}
if(match.groups.minor == 5 && match.groups.sub < 53){
return true;
}
}
if (match.groups.major == 2 && match.groups.minor == 0 && match.groups.sub < 27){
return true;
}
return false;
""";
SELECT
c.name,
c.resource.data.projectId AS project_id,
c.resource.data.clusterName AS cluster_name,
c.resource.data.config.softwareConfig.imageVersion AS image_version,
c.resource.data.status.state AS cluster_state,
vulnerable_version(c.resource.data.config.softwareConfig.imageVersion) AS is_vulnerable
FROM
`PROJECT_ID.DATASET_ID.TABLE_NAME_PREFIX_dataproc_googleapis_com_Cluster` c
INNER JOIN `PROJECT_ID.DATASET_ID.TABLE_NAME_PREFIX_cloudresourcemanager_googleapis_com_Project` p
ON p.resource.data.projectId = c.resource.data.projectId
WHERE
c.resource.data.config.softwareConfig.imageVersion IS NOT NULL
AND c.resource.data.status.state = "RUNNING"
AND p.resource.data.lifecycleState = "ACTIVE";