View vulnerability reports for the organization by using Cloud Asset Inventory and BigQuery

This document describes how to use VM Manager, Cloud Asset Inventory, and BigQuery to view vulnerability reports for Compute Engine instances in your organization.

By exporting data from Cloud Asset Inventory to BigQuery, you can run advanced queries to identify pending patches and vulnerability information across your entire organization.

Before you begin

Set up VM Manager.
Enable the Cloud Asset Inventory API.
Create a BigQuery dataset to store the exported data.
Verify that you have the permissions required to view vulnerability reports.
If you haven't already, set up authentication. Authentication verifies your identity for access to Google Cloud services and APIs. To run code or samples from a local development environment, you can authenticate to Compute Engine by selecting one of the following options:
Select the tab for how you plan to use the samples on this page:
Console

When you use the Google Cloud console to access Google Cloud services and APIs, you don't need to set up authentication.
gcloud
1. Install the Google Cloud CLI. After installation, initialize the Google Cloud CLI by running the following command:
  gcloud init
  If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity.
  
  Note: If you installed the gcloud CLI previously, make sure you have the latest version by running gcloud components update.
2. Set a default region and zone.

Required roles

To get the permissions that you need to export resource data to BigQuery, ask your administrator to grant you the following IAM roles on the project, folder, or organization:

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

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to export resource data to BigQuery. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to export resource data to BigQuery:

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

You might also be able to get these permissions with custom roles or other predefined roles.

Export VM Manager data to BigQuery

To export OS inventory and resource data to BigQuery, do the following:

Identify your organization ID:
```
gcloud projects get-ancestors PROJECT_ID
```
Replace PROJECT_ID with the project ID of your project.
Export the OS inventory data collected by VM Manager from the VM instances:
```
gcloud asset export \
    --content-type=os-inventory \
    --organization=ORGANIZATION_ID \
    --per-asset-type \
    --bigquery-table="projects/BQ_PROJECT_ID/datasets/DATASET_ID/tables/os"
```
Replace the following:
- ORGANIZATION_ID: your organization ID.
- BQ_PROJECT_ID: the project ID where your BigQuery dataset is located.
- DATASET_ID: the name of your BigQuery dataset.
Export the resource metadata to a BigQuery table:
```
gcloud asset export \
    --content-type=resource \
    --organization=ORGANIZATION_ID \
    --per-asset-type \
    --bigquery-table="projects/BQ_PROJECT_ID/datasets/DATASET_ID/tables/res"
```
Replace the following:
- ORGANIZATION_ID: your organization ID
- BQ_PROJECT_ID: the project ID where your BigQuery dataset is located
- DATASET_ID: the ID of your BigQuery dataset

To learn how to export OS inventory snapshots, see Export asset snapshot}.

Generate vulnerability report for your organization

After you export the inventory data, you can run a SQL query in BigQuery to generate a vulnerability report. This report provides the following information:

A complete list of pending patches across the organization.
A summary of pending patches per Compute Engine instance.
A summary of pending patches per project.

To generate the report, perform the following steps:

In the Google Cloud console, go to the BigQuery page.

Go to BigQuery

In the query editor, paste the following SQL script:

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

Replace DATASET_ID with the ID of your BigQuery dataset.

Click Run.

For more information about querying data, see Running queries.

After you generate the report, you can use Data Studio to create a custom dashboard. For more information, see Analyze data with Data Studio.

What's next

Learn more about OS inventory in VM Manager.
Learn how to export asset metadata to BigQuery.
Use Data Studio to visualize BigQuery data.