使用 Cloud Asset Inventory 和 BigQuery 查看组织的漏洞报告

本文档介绍了如何使用虚拟机管理器、Cloud Asset Inventory 和 BigQuery 查看组织中 Compute Engine 实例的漏洞报告。

通过将数据从 Cloud Asset Inventory 导出到 BigQuery，您可以运行高级查询，以识别整个组织中待处理的补丁和漏洞信息。

准备工作

设置虚拟机管理器。
启用 Cloud Asset Inventory API。
创建 BigQuery 数据集来存储导出的数据。
验证您是否拥有查看漏洞报告所需的权限。
如果您尚未设置身份验证，请进行设置。身份验证用于验证您的身份，以便访问 Google Cloud 服务和 API。如需从本地开发环境运行代码或示例，您可以通过选择以下选项之一向 Compute Engine 进行身份验证：
选择标签页以了解您打算如何使用本页面上的示例：
控制台

当您使用 Google Cloud 控制台访问 Google Cloud 服务和 API 时，无需设置身份验证。
gcloud
1. 安装 Google Cloud CLI。安装完成后，运行以下命令来初始化 Google Cloud CLI：
  gcloud init
  如果您使用的是外部身份提供方 (IdP)，则必须先使用联合身份登录 gcloud CLI。
  
  注意：如果您之前安装了 gcloud CLI，请确保通过运行 gcloud components update 来获得最新版本。
设置默认区域和可用区。

所需的角色

如需获得将资源数据导出到 BigQuery 所需的权限，请让管理员向您授予项目、文件夹或组织的以下 IAM 角色：

Cloud Asset Viewer (roles/cloudasset.viewer)
BigQuery Data Editor (roles/bigquery.dataEditor)
BigQuery Job User (roles/bigquery.jobUser)

如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

这些预定义角色可提供将资源数据导出到 BigQuery 所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

如需将资源数据导出到 BigQuery，您需要具备以下权限：

cloudasset.assets.exportOSInventories
cloudasset.assets.exportResource
bigquery.datasets.get
bigquery.tables.create
bigquery.tables.update
bigquery.tables.get
bigquery.jobs.create

您也可以使用自定义角色或其他预定义角色来获取这些权限。

将虚拟机管理器数据导出到 BigQuery

如需将操作系统清单和资源数据导出到 BigQuery，请执行以下操作：

确定您的组织 ID：
```
gcloud projects get-ancestors PROJECT_ID
```
将 PROJECT_ID 替换为项目的项目 ID。
导出虚拟机管理器从虚拟机实例收集的操作系统清单数据：
```
gcloud asset export \
    --content-type=os-inventory \
    --organization=ORGANIZATION_ID \
    --per-asset-type \
    --bigquery-table="projects/BQ_PROJECT_ID/datasets/DATASET_ID/tables/os"
```
替换以下内容：
- ORGANIZATION_ID：您的组织 ID。
- BQ_PROJECT_ID：BigQuery 数据集所在的项目 ID。
- DATASET_ID：BigQuery 数据集的名称。

将资源元数据导出到 BigQuery 表：

gcloud asset export \
    --content-type=resource \
    --organization=ORGANIZATION_ID \
    --per-asset-type \
    --bigquery-table="projects/BQ_PROJECT_ID/datasets/DATASET_ID/tables/res"

替换以下内容：

ORGANIZATION_ID：您的组织 ID。
BQ_PROJECT_ID：BigQuery 数据集所在的项目 ID
DATASET_ID：BigQuery 数据集的 ID

如需了解如何导出操作系统库存快照，请参阅导出资产快照。

为组织生成漏洞报告

导出资产清单数据后，您可以在 BigQuery 中运行 SQL 查询来生成漏洞报告。此报告提供以下信息：

组织中所有待处理补丁的完整列表。
每个 Compute Engine 实例的待处理补丁摘要。
每个项目的待处理补丁摘要。

如需生成报告，请执行以下步骤：

在 Google Cloud 控制台中，前往 BigQuery 页面。

转到 BigQuery

在查询编辑器中，粘贴以下 SQL 脚本：

WITH UPDATES_GRANULAR_DATA AS (
  SELECT
    project,
    instance,
    os,
    available_update,
    vuln.cve AS vuln_cve,
    vuln.severity AS linux_vuln_severity,
    windows_categories
  FROM (
    SELECT
      SPLIT(inv.name, '/')[OFFSET(4)] AS project,
      SPLIT(inv.name, '/')[OFFSET(8)] AS instance,
      inv.os_inventory.os_Info.long_Name AS os,
      inv_item.key AS available_update,
      (
        SELECT
          ARRAY_AGG(c.name) windows_cat_names
        FROM UNNEST(inv_item.value.available_Package.wua_Package.categories) c
      ) AS windows_categories
    FROM
      DATASET_ID.os_compute_googleapis_com_Instance AS inv
    CROSS JOIN UNNEST(inv.os_inventory.items) AS inv_item
    WHERE
      inv.name NOT LIKE '%/locations/%'
      AND inv_item.value.type = 2 --"AVAILABLE_PACKAGE"

    UNION ALL

    SELECT
      project,
      instance,
      os,
      NULL AS available_update,
      NULL AS windows_categories
    FROM (
      SELECT
        SPLIT(name, '/')[OFFSET(4)] AS project,
        SPLIT(name, '/')[OFFSET(8)] AS instance,
        os_Inventory.os_Info.long_Name AS os,
        (
          SELECT COUNT(*)
          FROM UNNEST(os_Inventory.items)
          WHERE value.type = 2 --"AVAILABLE_PACKAGE"
        ) AS count_available_updates
      FROM DATASET_ID.os_compute_googleapis_com_Instance
      WHERE name NOT LIKE '%/locations/%'
    )
    WHERE count_available_updates = 0
  )
  LEFT JOIN (
    SELECT
      inv_item,
      v.details.severity AS severity,
      v.details.cve AS cve
    FROM DATASET_ID.res_osconfig_googleapis_com_VulnerabilityReport
    CROSS JOIN UNNEST(resource.data.vulnerabilities) AS v
    CROSS JOIN UNNEST(v.availableInventoryItemIds) AS inv_item
    WHERE
      ARRAY_LENGTH(resource.data.vulnerabilities)>0 AND
      ARRAY_LENGTH(v.availableInventoryItemIds)>0
  ) AS vuln
  ON vuln.inv_item = available_update
),

REPORT_WITH_WINDOWS_CATEGORIES_VERBOSE AS (
  SELECT
    project,
    instance,
    os,
    COUNTIF(available_update IS NOT NULL) as updates_pending,
    IF(
      COUNTIF(available_update IS NOT NULL)>0 AND ARRAY_LENGTH(ARRAY_CONCAT_AGG(windows_categories)) IS NULL,
        IF(
          ARRAY_LENGTH(ARRAY_AGG(DISTINCT(linux_vuln_severity) IGNORE NULLS)) > 0,
          IF(
            CONTAINS_SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(DISTINCT(linux_vuln_severity) IGNORE NULLS),""), "CRITICAL"),
            "CRITICAL",
            IF(
              CONTAINS_SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(DISTINCT(linux_vuln_severity) IGNORE NULLS),""), "HIGH"),
              "HIGH",
              IF(
                CONTAINS_SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(DISTINCT(linux_vuln_severity) IGNORE NULLS),""), "MEDIUM"),
                "MEDIUM",
                IF(
                  CONTAINS_SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(DISTINCT(linux_vuln_severity) IGNORE NULLS),""), "LOW"),
                  "LOW",
                  "SEVERITY_UNSPECIFIED"
                )
              )
            )
          ),
          "UNKNOWN"
        ),
        NULL
    ) as linux_vuln_severity,
    ARRAY_CONCAT_AGG(windows_categories) as windows_categories_agg
  FROM UPDATES_GRANULAR_DATA
  GROUP BY project, instance, os
  ORDER BY project, instance, os
),

REPORT_BY_VM AS (
  SELECT
    project,
    instance,
    os,
    updates_pending,
    linux_vuln_severity,
    IF(
      ARRAY_LENGTH(windows_categories_agg) > 0,
      IF(
        CONTAINS_SUBSTR(ARRAY_TO_STRING(ARRAY(SELECT DISTINCT(a) as n FROM UNNEST(windows_categories_agg) a ORDER BY n ASC),","), "Security Updates"),
        "SECURITY UPDATES",
        IF(
          CONTAINS_SUBSTR(ARRAY_TO_STRING(ARRAY(SELECT DISTINCT(a) as n FROM UNNEST(windows_categories_agg) a ORDER BY n ASC),","), "Update Rollups"),
          "UPDATE ROLLUPS",
          "OTHER UPDATES"
        )
      ),
      NULL
    ) as windows_category
  FROM REPORT_WITH_WINDOWS_CATEGORIES_VERBOSE
),

REPORT_BY_PROJECT AS (
  SELECT
    project,
    COUNT(*) as total_vms,
    COUNTIF(updates_pending=0) as vms_up_to_date,
    COUNTIF(updates_pending>0) as vms_with_updates_pending,
    COUNTIF(linux_vuln_severity = "CRITICAL") as linux_vms_critical,
    COUNTIF(linux_vuln_severity = "HIGH") as linux_vms_high,
    COUNTIF(linux_vuln_severity = "MEDIUM") as linux_vms_medium,
    COUNTIF(linux_vuln_severity = "LOW") as linux_vms_low,
    COUNTIF(linux_vuln_severity = "SEVERITY_UNSPECIFIED") as linux_vms_severity_unspecified,
    COUNTIF(linux_vuln_severity = "UNKNOWN") as linux_vms_unknown,
    COUNTIF(windows_category = "SECURITY UPDATES") as win_vms_security_updates,
    COUNTIF(windows_category = "UPDATE ROLLUPS") as win_vms_update_rollups,
    COUNTIF(windows_category = "OTHER UPDATES") as win_vms_other_updates
  FROM REPORT_BY_VM
  GROUP BY project
)

-- To view the report, uncomment one of the following SELECT statements:

-- 1. List of every pending update package across all VMs with associated vulnerability severity:
-- SELECT * FROM UPDATES_GRANULAR_DATA

-- 2. List of VMs and pending updates count:
-- SELECT * FROM REPORT_BY_VM

-- 3. Summary of projects, showing count of VMs up-to-date and with pending updates:
SELECT * FROM REPORT_BY_PROJECT

将 DATASET_ID 替换为您的 BigQuery 数据集的 ID。

点击运行。

如需详细了解如何查询数据，请参阅运行查询。

生成报告后，您可以使用数据洞察创建自定义信息中心。如需了解详情，请参阅使用数据洞察分析数据。