steampipe plugin install gcp

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';

Schema for gcp_compute_image

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
archive_size_bytesbigintSize of the image tar.gz archive stored in Google Cloud Storage (in bytes).
creation_timestamptimestamp with time zoneThe creation timestamp of the resource.
deprecatedjsonbAn object comtaining the detailed deprecation status associated with this image.
deprecation_statetext=The deprecation state associated with this image.
descriptiontextA user-specified, human-readable description of the image.
disk_size_gbbigintSize of the image when restored onto a persistent disk (in GB).
familytext=The name of the image family to which this image belongs.
guest_os_featuresjsonbA list of features to enable on the guest operating system.
iam_policyjsonbAn 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`.
idbigintThe unique identifier for the resource.
image_encryption_keyjsonbThe customer-supplied encryption key of the image.
kindtextThe type of the resource.
label_fingerprinttextA fingerprint for the labels being applied to this image, which is essentially a hash of the labels used for optimistic locking.
labelsjsonbA set of labels to apply to this image.
licensesjsonbA list of applicable license URI.
locationtextThe GCP multi-region, region, or zone in which the resource is located.
nametext=A friendly name that identifies the resource.
projecttext=, !=, ~~, ~~*, !~~, !~~*The gcp project queried.
raw_diskjsonbA set of parameters of the raw disk image.
self_linktextThe server-defined URL for the resource.
source_disktextThe URL of the source disk used to create this image.
source_disk_encryption_keyjsonbThe customer-supplied encryption key of the source disk.
source_disk_idtextThe ID value of the disk used to create this image.
source_imagetextThe URL of the source image used to create this image.
source_image_encryption_keyjsonbThe customer-supplied encryption key of the source image.
source_image_idtextThe ID value of the image used to create this image.
source_projecttext=The project in which the image is defined.
source_snapshottextThe ID value of the snapshot used to create this image.
source_snapshot_encryption_keyjsonbThe customer-supplied encryption key of the source snapshot.
source_snapshot_idtextThe ID value of the snapshot used to create this image.
source_typetext=The type of the image used to create this disk.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustext=The status of the image.
storage_locationsjsonbA list of Cloud Storage bucket storage location of the image (regional or multi-regional).
tagsjsonbA map of tags for the resource.
titletextTitle 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