Unveiling GCP Asset Inventory: A Comprehensive Guide to Querying VMs, Disks, and Source Images
Introduction
Google Cloud Platform empowers organizations with a suite of tools to manage cloud infrastructure efficiently. A cornerstone of this suite is the Asset Inventory, a powerful resource that provides a holistic view of your cloud assets, encompassing virtual machines (VMs), disks, and the source images they are derived from. In this guide, we’ll delve into the art of harnessing GCP’s Asset Inventory to query VMs, disks, and source images using distinct tables, offering insights into your environment and aiding in tasks like cost analysis, security audits, and resource optimization.
Understanding Asset Inventory
Before embarking on our querying journey, let’s solidify our understanding of the Asset Inventory. GCP’s Asset Inventory is a centralized repository of metadata about your cloud resources. It serves as a single source of truth, allowing you to discover, track, and analyze your cloud assets across projects, organizations, and even folders. By aggregating metadata, the Asset Inventory enables comprehensive reporting, risk assessment, and informed decision-making.
Why Query VMs, Disks, and Source Images?
The ability to query these interconnected resources opens up numerous possibilities:
- Cost Optimization: Analyze which VM types consume the most resources and identify opportunities to downsize or switch to more cost-effective options.
- Security Audits: Track the lineage of VM images to ensure they originate from trusted sources and haven’t been tampered with.
- Capacity Planning: Assess the distribution of VM sizes and associated disks to plan for future growth or consolidation.
- Troubleshooting: Quickly pinpoint VMs running on outdated or potentially vulnerable source images.
Step-by-Step Guide: Querying VMs, Disks, and Source Images
Accessing Asset Inventory:
- Log in to your GCP Console.
- Navigate to “IAM & Admin” -> “Asset Inventory” or simply search for it in the search bar.
- Click on “Asset Query” to enter the querying interface.
Exploring the Tables
- Familiarize yourself with the “Virtual Machines,” “Disks,” and “Images” tables in the Asset Inventory dashboard.
- Each table offers a unique set of columns containing valuable information.
- Use the
_schema
field to get detailed information about the columns and their data types.
Querying VM Instances
Navigate to the “Virtual Machines” section or table in the Asset Inventory dashboard. Here, you’ll find a comprehensive list of all VM instances within your GCP project. You can filter and sort these instances based on attributes like name, zone, and status. Additionally, examine the schema to gather more detailed information.
Retrieving Disk Information
After identifying the VM instance, proceed to the “Disks” section or table in the Asset Inventory dashboard. Here, you can access information about associated disks and query their details.
Writing Queries
Now that you’re familiar with the tables and schema in the asset inventory, let’s begin writing queries to obtain the desired outcome.
Our goal is to gather details about all VMs within the GCP Organization, including their associated disks and utilized source images. We’ll exclude default images and only show running VMs.
To begin, navigate to your GCP Organization, locate “Asset Inventory,” and access “Asset Query.” Then, proceed to paste the provided queries and click “Run” to execute them.
Step 1: Listing the VMs along with the attached disk
This query will display all VMs along with their attached disks retrieved from the instance table.
SELECT
name,
ARRAY(
SELECT AS STRUCT
d.source
FROM
UNNEST(resource.data.disks) AS d
) AS source
FROM
`compute_googleapis_com_Instance`
Step 2: Listing the disk and the source image
This query will display all disks along with the source images utilized, retrieved from the disk table.
SELECT name, resource.data.sourceImage
FROM compute_googleapis_com_Disk
Step 3: Listing the disk and the source image and exclude image with prefix
In this step, we aim to generate a query that will identify all disks along with their corresponding source images from the disk table. Additionally, we’ll filter out any source images that have a specific prefix, such as “abc”.
SELECT name, resource.data.sourceImage
FROM compute_googleapis_com_Disk
WHERE NOT CONTAINS_SUBSTR(resource.data.sourceImage, "images/abc")
Step 5: Use Joins to list Instance, Disk and the Source Image from two different tables
This query retrieves compute instances from compute_googleapis_com_Instance
and their boot disk sources. It then joins this information with disk data from compute_googleapis_com_Disk
to get the source image of each boot disk. The WHERE clause filters out instances whose boot disk source images contain the string "images/abc".
SELECT vms.name, vms.disk_source, disks.resource.data.sourceImage as source_image
FROM (
SELECT name, source[ordinal(1)] as disk_source
FROM (
SELECT
name,
ARRAY(
SELECT AS STRUCT
d.source
FROM
UNNEST(resource.data.disks) AS d
) AS source
FROM
`compute_googleapis_com_Instance`
)
) as vms
LEFT JOIN compute_googleapis_com_Disk AS disks
ON array_reverse(SPLIT(vms.disk_source.source,'/'))[ordinal(1)]=array_reverse(SPLIT(disks.name,'/'))[ordinal(1)]
WHERE NOT CONTAINS_SUBSTR(disks.resource.data.sourceImage, "images/abc")
Step 6: Use Joins to list Instance, Disk and the Source Image from two different tables and remove jargons
This query extracts project, VM name, disk name, and source image information for Compute Engine instances in a Google Cloud project.
- It joins instance data with disk data to link the boot disk to its source image.
- The WHERE clause filters out instances with source images containing “images/abc”.
- The REGEXP_REPLACE function cleans up the source image URL by removing a standard prefix.
- The SPLIT and ARRAY_REVERSE functions are used to parse resource names and extract relevant parts.
SELECT vms.name, vms.disk_source, disks.resource.data.sourceImage as source_image
FROM (
SELECT name, source[ordinal(1)] as disk_source
FROM (
SELECT
name,
ARRAY(
SELECT AS STRUCT
d.source
FROM
UNNEST(resource.data.disks) AS d
) AS source
FROM
`compute_googleapis_com_Instance`
)
) as vms
LEFT JOIN compute_googleapis_com_Disk AS disks
ON array_reverse(SPLIT(vms.disk_source.source,'/'))[ordinal(1)]=array_reverse(SPLIT(disks.name,'/'))[ordinal(1)]
WHERE NOT CONTAINS_SUBSTR(disks.resource.data.sourceImage, "images/abc")
Step 6: Final Query to list the Running Instances their Disk and the Source Image and exclude some images
This query generates a report of GCE instances in your project, listing their project, name, boot disk name, and the source image of the boot disk. It excludes instances with source images containing “images/abc,” allowing you to focus on specific types of VM configurations or deployments.
SELECT
SPLIT(vms.name, '/')[OFFSET(4)] AS project,
ARRAY_REVERSE(SPLIT(vms.name, '/'))[OFFSET(0)] AS vm_name,
ARRAY_REVERSE(SPLIT(vms.disk_source.source, '/'))[OFFSET(0)] AS disk_name,
REGEXP_REPLACE(disks.resource.data.sourceImage, r"https://www.googleapis.com/compute/v1/", "") AS source_image
FROM (
SELECT name, source[ORDINAL(1)] AS disk_source
FROM (
SELECT
name,
ARRAY(
SELECT AS STRUCT
d.source
FROM
UNNEST(resource.data.disks) AS d
) AS source
FROM
`compute_googleapis_com_Instance`
)
) AS vms
LEFT JOIN compute_googleapis_com_Disk AS disks
ON ARRAY_REVERSE(SPLIT(vms.disk_source.source,'/'))[ORDINAL(1)]=ARRAY_REVERSE(SPLIT(disks.name,'/'))[ORDINAL(1)]
WHERE NOT CONTAINS_SUBSTR(disks.resource.data.sourceImage, "images/abc");
This SQL query is designed to retrieve information about Google Compute Engine virtual machine instances and their associated disks within a Google Cloud Project.
Here’s a breakdown of its components and functionality:
1. Data Sources:
compute_googleapis_com_Instance
: This table represents the inventory of GCE virtual machine instances in your Google Cloud Project.compute_googleapis_com_Disk
: This table stores information about persistent disks associated with your GCE instances.
2. Subquery (vms):
- The innermost subquery (
SELECT...FROM UNNEST
) extracts thename
of each VM instance and thesource
(a URI indicating the source image or snapshot) of its attached disks. It does this by unnesting theresource.data.disks
array, which typically contains multiple disks per VM. - The next level of the subquery (
SELECT name, source[ORDINAL(1)] AS disk_source
) selects the VM'sname
and thesource
of its first (boot) disk, labeled asdisk_source
.
3. LEFT JOIN:
- The main query uses a
LEFT JOIN
to combine information from thevms
subquery with thecompute_googleapis_com_Disk
table. This join is based on matching the last part of thedisk_source.source
(disk name) with the last part of thedisks.name
. ALEFT JOIN
is used to ensure that even VMs without matching disk entries in thecompute_googleapis_com_Disk
table are included in the result.
4. WHERE Clause:
- The
WHERE NOT CONTAINS_SUBSTR
clause filters out rows where the source image (disks.resource.data.sourceImage
) of the boot disk contains the substring "images/abc". This is presumably used to exclude specific types of images or snapshots from the results.
5. SELECT and Output:
- The final
SELECT
statement extracts and formats the desired information: project
: The Google Cloud project where the VM resides.vm_name
: The name of the VM instance.disk_name
: The name of the VM's boot disk.source_image
: The source image or snapshot used to create the boot disk, with a standardized format by removing the "https://www.googleapis.com/compute/v1/" prefix usingREGEXP_REPLACE
.
Conclusion
GCP’s Asset Inventory is a powerful ally in managing your cloud resources. By querying VMs, disks, and source images, you gain insights to optimize costs, enhance security, plan capacity, and troubleshoot efficiently. With a little practice, you can turn the Asset Inventory into your compass for navigating the cloud landscape. Remember, its a dynamic tool that evolves with your infrastructure. Keep exploring and refining your queries to make the most of it.
Let's connect on LinkedIn for more opportunities to learn together!
https://www.linkedin.com/in/utkarsh6/