Table: gcp_compute_image - Query Google Cloud Compute Engine Images using SQL

Google Cloud Compute Engine Images are binary data that is used to create instances in Google Cloud Compute Engine. These images contain a boot loader, an operating system, and a root file system. Google Cloud Compute Engine Images are essential for creating and managing instances in Google Cloud Compute Engine.

Table Usage Guide

The gcp_compute_image table provides insights into images within Google Cloud Compute Engine. As a Cloud Engineer, you can explore image-specific details through this table, including their creation timestamps, descriptions, disk sizes, and more. Utilize it to uncover information about images, such as those that are deprecated, the operating systems they contain, and their source disk IDs.

Examples

Compute image basic info

Explore the basic information of your Google Cloud Platform compute images to identify their status and deprecation state. This can assist in managing and maintaining your compute resources effectively.

select
  name,
  id,
  kind,
  status,
  deprecation_state
from
  gcp_compute_image;

select
  name,
  id,
  kind,
  status,
  deprecation_state
from
  gcp_compute_image;

List of active, standard compute images

Explore the active compute images that are sourced from different projects. This can help in managing resources and identifying potential redundancies.

select
  name,
  id,
  source_project
from
  gcp_compute_image
where
  deprecation_state = 'ACTIVE'
  and source_project != project;

select
  name,
  id,
  source_project
from
  gcp_compute_image
where
  deprecation_state = 'ACTIVE'
  and source_project != project;

List of custom (user-defined) images defined in this project

Explore which custom images have been defined within a specific project. This can help in understanding the customization and modifications made to the project.

select
  name,
  id,
  source_project
from
  gcp_compute_image
where
  source_project = project;

select
  name,
  id,
  source_project
from
  gcp_compute_image
where
  source_project = project;

List of compute images which are not encrypted with a customer key

Explore which compute images are not secured with a unique customer key. This query is useful to identify potential security vulnerabilities in your GCP compute images.

select
  name,
  id,
  image_encryption_key
from
  gcp_compute_image
where
  image_encryption_key is null;

select
  name,
  id,
  image_encryption_key
from
  gcp_compute_image
where
  image_encryption_key is null;

List of user-defined compute images which do not have owner tag key

Explore which user-defined compute images lack an owner tag key. This is useful to identify potential gaps in image management, ensuring that all images are properly attributed to an owner.

select
  name,
  id
from
  gcp_compute_image
where
  tags -> 'owner' is null
  and source_project = project;

select
  name,
  id
from
  gcp_compute_image
where
  json_extract(tags, '$.owner') is null
  and source_project = project;

List of active compute images older than 90 days

Explore which compute images have remained active for more than 90 days. This can help identify areas for potential optimization or cleanup in your GCP environment.

select
  name,
  creation_timestamp,
  age(creation_timestamp),
  deprecation_state
from
  gcp_compute_image
where
  creation_timestamp <= (current_date - interval '90' day)
  and deprecation_state = 'ACTIVE'
order by
  creation_timestamp;

select
  name,
  creation_timestamp,
  julianday('now') - julianday(creation_timestamp) as age,
  deprecation_state
from
  gcp_compute_image
where
  julianday(creation_timestamp) <= julianday(datetime('now', '-90 day'))
  and deprecation_state = 'ACTIVE'
order by
  creation_timestamp;

Find VM instances built from images older than 90 days

This query is useful for maintaining the security and effectiveness of your virtual machine instances. It helps identify any instances that were built from images older than 90 days, allowing you to update or replace them as necessary to ensure optimal performance and compliance with best practices.

select
  vm.name as instance_name,
  d.name as disk_name,
  img.name as image,
  img.creation_timestamp as image_creation_time,
  age(img.creation_timestamp) as image_age,
  img.deprecation_state
from
  gcp_compute_instance as vm,
  jsonb_array_elements(vm.disks) as vmd,
  gcp_compute_disk as d,
  gcp_compute_image as img
where
  vmd ->> 'source' = d.self_link
  and (vmd ->> 'boot') :: bool
  and d.source_image = img.self_link
  and img.creation_timestamp <= (current_date - interval '90' day);

select
  vm.name as instance_name,
  d.name as disk_name,
  img.name as image,
  img.creation_timestamp as image_creation_time,
  julianday('now') - julianday(img.creation_timestamp) as image_age,
  img.deprecation_state
from
  gcp_compute_instance as vm,
  json_each(vm.disks) as vmd,
  gcp_compute_disk as d,
  gcp_compute_image as img
where
  json_extract(vmd.value, '$.source') = d.self_link
  and json_extract(vmd.value, '$.boot') = 1
  and d.source_image = img.self_link
  and img.creation_timestamp <= date('now', '-90 day');

Find VM instances built from deprecated, deleted, or obsolete images

Determine the instances where virtual machines (VMs) are built using outdated, deleted, or obsolete images. This is useful for identifying potential security risks and ensuring optimal performance by keeping your VMs up-to-date.

select
  vm.name as instance_name,
  d.name as disk_name,
  img.name as image,
  img.creation_timestamp as image_creation_time,
  age(img.creation_timestamp) as image_age,
  img.deprecation_state
from
  gcp_compute_instance as vm,
  jsonb_array_elements(vm.disks) as vmd,
  gcp_compute_disk as d,
  gcp_compute_image as img
where
  vmd ->> 'source' = d.self_link
  and (vmd ->> 'boot') :: bool
  and d.source_image = img.self_link
  and deprecation_state != 'ACTIVE';

select
  vm.name as instance_name,
  d.name as disk_name,
  img.name as image,
  img.creation_timestamp as image_creation_time,
  strftime('%s', 'now') - strftime('%s', img.creation_timestamp) as image_age,
  img.deprecation_state
from
  gcp_compute_instance as vm,
  json_each(vm.disks) as vmd,
  gcp_compute_disk as d,
  gcp_compute_image as img
where
  json_extract(vmd.value, '$.source') = d.self_link
  and (json_extract(vmd.value, '$.boot') = 'true')
  and d.source_image = img.self_link
  and deprecation_state != 'ACTIVE';

Query examples

Schema for gcp_compute_image

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
archive_size_bytes	bigint		Size of the image tar.gz archive stored in Google Cloud Storage (in bytes).
creation_timestamp	timestamp with time zone		The creation timestamp of the resource.
deprecated	jsonb		An object comtaining the detailed deprecation status associated with this image.
deprecation_state	text	=	The deprecation state associated with this image.
description	text		A user-specified, human-readable description of the image.
disk_size_gb	bigint		Size of the image when restored onto a persistent disk (in GB).
family	text	=	The name of the image family to which this image belongs.
guest_os_features	jsonb		A list of features to enable on the guest operating system.
iam_policy	jsonb		An Identity and Access Management (IAM) policy, which specifies access controls for Google Cloud resources. A `Policy` is a collection of `bindings`. A `binding` binds one or more `members` to a single `role`. Members can be user accounts, service accounts, Google groups, and domains (such as G Suite). A `role` is a named list of permissions; each `role` can be an IAM predefined role or a user-created custom role. For some types of Google Cloud resources, a `binding` can also specify a `condition`, which is a logical expression that allows access to a resource only if the expression evaluates to `true`.
id	bigint		The unique identifier for the resource.
image_encryption_key	jsonb		The customer-supplied encryption key of the image.
kind	text		The type of the resource.
label_fingerprint	text		A fingerprint for the labels being applied to this image, which is essentially a hash of the labels used for optimistic locking.
labels	jsonb		A set of labels to apply to this image.
licenses	jsonb		A list of applicable license URI.
location	text		The GCP multi-region, region, or zone in which the resource is located.
name	text	=	A friendly name that identifies the resource.
project	text		The gcp project queried.
raw_disk	jsonb		A set of parameters of the raw disk image.
self_link	text		The server-defined URL for the resource.
source_disk	text		The URL of the source disk used to create this image.
source_disk_encryption_key	jsonb		The customer-supplied encryption key of the source disk.
source_disk_id	text		The ID value of the disk used to create this image.
source_image	text		The URL of the source image used to create this image.
source_image_encryption_key	jsonb		The customer-supplied encryption key of the source image.
source_image_id	text		The ID value of the image used to create this image.
source_project	text	=	The project in which the image is defined.
source_snapshot	text		The ID value of the snapshot used to create this image.
source_snapshot_encryption_key	jsonb		The customer-supplied encryption key of the source snapshot.
source_snapshot_id	text		The ID value of the snapshot used to create this image.
source_type	text	=	The type of the image used to create this disk.
status	text	=	The status of the image.
storage_locations	jsonb		A list of Cloud Storage bucket storage location of the image (regional or multi-regional).
tags	jsonb		A map of tags for the resource.
title	text		Title of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp

You can pass the configuration to the command with the --config argument:

steampipe_export_gcp --config '<your_config>' gcp_compute_image