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](https://docs.cloud.google.com/compute/vm-manager/docs/setup).
- Enable the [Cloud Asset Inventory API](https://console.cloud.google.com/apis/library/cloudasset.googleapis.com).
- [Create a BigQuery
  dataset](https://docs.cloud.google.com/bigquery/docs/datasets#create-dataset) to store the exported data.
- Verify that you have [the permissions required](https://docs.cloud.google.com/compute/vm-manager/docs/os-inventory/vulnerability-reports#view-vulnerability-roles) to view vulnerability reports.
- If you haven't already, set up [authentication](https://docs.cloud.google.com/compute/docs/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](https://docs.cloud.google.com/sdk/docs/install) the Google Cloud CLI.

     After installation,
     [initialize](https://docs.cloud.google.com/sdk/docs/initializing) the Google Cloud CLI by running the following command:

     ```bash
     gcloud init
     ```


     If you're using an external identity provider (IdP), you must first
     [sign in to the gcloud CLI with your federated identity](https://docs.cloud.google.com/iam/docs/workforce-log-in-gcloud).

     > [!NOTE]
     > **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](https://docs.cloud.google.com/compute/docs/gcloud-compute#set_default_zone_and_region_in_your_local_client).

## 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](https://docs.cloud.google.com/iam/docs/roles-permissions/cloudasset#cloudasset.viewer) (`roles/cloudasset.viewer`)
- [BigQuery Data Editor](https://docs.cloud.google.com/iam/docs/roles-permissions/bigquery#bigquery.dataEditor) (`roles/bigquery.dataEditor`)
- [BigQuery Job User](https://docs.cloud.google.com/iam/docs/roles-permissions/bigquery#bigquery.jobUser) (`roles/bigquery.jobUser`)


For more information about granting roles, see [Manage access to projects, folders, and organizations](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access).


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](https://docs.cloud.google.com/iam/docs/creating-custom-roles) or
other [predefined roles](https://docs.cloud.google.com/iam/docs/roles-overview#predefined).

## Export VM Manager data to BigQuery

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

1. Identify your organization ID:

       gcloud projects get-ancestors PROJECT_ID

   Replace `PROJECT_ID` with the project ID of your project.
2. 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](https://docs.cloud.google.com/resource-manager/docs/creating-managing-organization#retrieving_your_organization_id).
   - `BQ_PROJECT_ID`: the project ID where your BigQuery dataset is located.
   - `DATASET_ID`: the name of your BigQuery dataset.
3. 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}](https://docs.cloud.google.com/asset-inventory/docs/export-bigquery#export).

## 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:

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

   [Go to BigQuery](https://console.cloud.google.com/bigquery)
2. 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.
3. Click **Run**.

For more information about querying data, see [Running queries](https://docs.cloud.google.com/bigquery/docs/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](https://docs.cloud.google.com/bigquery/docs/visualize-looker-studio).

## What's next

- Learn more about [OS inventory in VM Manager](https://docs.cloud.google.com/compute/vm-manager/docs/patch/view-patch-summary).
- Learn how to [export asset metadata to BigQuery](https://docs.cloud.google.com/asset-inventory/docs/exporting-to-bigquery).
- Use [Data Studio to visualize BigQuery data](https://docs.cloud.google.com/bigquery/docs/visualize-looker-studio).