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
-
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 .
- 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 permissionssection:
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_IDReplace
PROJECT_IDwith 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 BigQuerypage.
-
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_PROJECTReplace
DATASET_IDwith 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 .
