steampipe plugin install gcp

Table: gcp_compute_machine_image - Query Google Cloud Platform Compute Machine Image using SQL

A machine image is a Compute Engine resource that stores all the configuration, metadata, permissions, and data from multiple disks of a virtual machine (VM) instance. You can use a machine image in many system maintenance, backup and recovery, and instance cloning scenarios.

Table Usage Guide

The gcp_compute_machine_image table provides insights into the available machine images within Google Cloud Platform's Compute Engine. As a cloud architect or DevOps engineer, you can explore machine image-specific details through this table, kind, source instance, instance properties, image status, image storage, and associated metadata. Utilize it to understand the specifications of each machine image, aiding in the selection of the most suitable machine image for your applications based on performance requirements and cost efficiency.

Examples

Basic info

Assess the elements within your Google Cloud Platform to understand the capacity and capabilities of each machine image. This can help to get the metadata about the compute images.

select
name,
id,
description,
creation_timestamp,
guest_flush,
source_instance
from
gcp_compute_machine_image;
select
name,
id,
description,
creation_timestamp,
guest_flush,
source_instance
from
gcp_compute_machine_image;

List machine images that are available

Ensures that only machine images that are ready for deployment or use are considered, which is critical for operational stability and reliability. Useful in automated scripts or applications where only machine images in a 'READY' state should be utilized. Helps in maintaining a clean and efficient image repository by focusing on images that are fully prepared and excluding those that are still in preparation or have been deprecated.

select
name,
id,
description,
creation_timestamp,
status
from
gcp_compute_machine_image
where
status = 'READY';
select
name,
id,
description,
creation_timestamp,
status
from
gcp_compute_machine_image
where
status = 'READY';

List the top 5 machine images that consume highest storage

This query is particularly useful in cloud infrastructure management and optimization, where understanding and managing storage utilization is a key concern. It helps administrators and users quickly identify the most space-efficient machine images available in their GCP environment.

select
name,
id,
self_link,
status,
total_storage_bytes
from
gcp_compute_machine_image
order by
total_storage_bytes asc
limit
5;
select
name,
id,
self_link,
status,
total_storage_bytes
from
gcp_compute_machine_image
order by
total_storage_bytes asc
limit
5;

Get instance properties of the machine images

Useful for analyzing the detailed configurations of machine images, including hardware features, network settings, and security configurations. Assists in planning and optimizing cloud infrastructure based on the capabilities and configurations of available machine images.

select
name,
id,
instance_properties -> 'advancedMachineFeatures' as advanced_machine_features,
instance_properties ->> 'canIpForward' as can_ip_forward,
instance_properties -> 'confidentialInstanceConfig' as confidential_instance_config,
instance_properties ->> 'description' as description,
instance_properties -> 'disks' as disks,
instance_properties -> 'guestAccelerators' as guest_accelerators,
instance_properties ->> 'keyRevocationActionType' as key_revocation_action_type,
instance_properties -> 'labels' as labels,
instance_properties ->> 'machineType' as machine_type,
instance_properties -> 'metadata' as metadata,
instance_properties -> 'minCpuPlatform' as min_cpu_platform,
instance_properties -> 'networkInterfaces' as network_interfaces,
instance_properties -> 'networkPerformanceConfig' as network_performance_config,
instance_properties -> 'privateIpv6GoogleAccess' as private_ipv6_google_access,
instance_properties ->> 'reservationAffinity' as reservation_affinity,
instance_properties -> 'resourceManagerTags' as resource_manager_tags,
instance_properties -> 'resourcePolicies' as resource_policies,
instance_properties -> 'scheduling' as scheduling,
instance_properties -> 'serviceAccounts' as service_accounts,
instance_properties -> 'shieldedInstanceConfig' as shielded_instance_config,
instance_properties -> 'tags' as tags
from
gcp_compute_machine_image;
select
name,
id,
json_extract(instance_properties, '$.advancedMachineFeatures') as advanced_machine_features,
json_extract(instance_properties, '$.canIpForward') as can_ip_forward,
json_extract(
instance_properties,
'$.confidentialInstanceConfig'
) as confidential_instance_config,
json_extract(instance_properties, '$.description') as description,
json_extract(instance_properties, '$.disks') as disks,
json_extract(instance_properties, '$.guestAccelerators') as guest_accelerators,
json_extract(instance_properties, '$.keyRevocationActionType') as key_revocation_action_type,
json_extract(instance_properties, '$.labels') as labels,
json_extract(instance_properties, '$.machineType') as machine_type,
json_extract(instance_properties, '$.metadata') as metadata,
json_extract(instance_properties, '$.minCpuPlatform') as min_cpu_platform,
json_extract(instance_properties, '$.networkInterfaces') as network_interfaces,
json_extract(instance_properties, '$.networkPerformanceConfig') as network_performance_config,
json_extract(instance_properties, '$.privateIpv6GoogleAccess') as private_ipv6_google_access,
json_extract(instance_properties, '$.reservationAffinity') as reservation_affinity,
json_extract(instance_properties, '$.resourceManagerTags') as resource_manager_tags,
json_extract(instance_properties, '$.resourcePolicies') as resource_policies,
json_extract(instance_properties, '$.scheduling') as scheduling,
json_extract(instance_properties, '$.serviceAccounts') as service_accounts,
json_extract(instance_properties, '$.shieldedInstanceConfig') as shielded_instance_config,
json_extract(instance_properties, '$.tags') as tags
from
gcp_compute_machine_image;

Get encryption details of the machine image

Understanding the encryption methods and keys used for each machine image is vital for security and compliance. It helps ensure that sensitive data is properly protected and that the encryption methods meet required standards. The query aids in auditing the encryption practices and managing the encryption keys across different machine images. It's particularly useful in environments with strict data protection policies.

select
name,
machine_image_encryption_key ->> 'KmsKeyName' as kms_key_name,
machine_image_encryption_key ->> 'KmsKeyServiceAccount' as kms_key_service_account,
machine_image_encryption_key ->> 'RawKey' as raw_key,
machine_image_encryption_key ->> 'RsaEncryptedKey' as rsa_encrypted_key,
machine_image_encryption_key ->> 'Sha256' as sha256
from
gcp_compute_machine_image;
select
name,
json_extract(machine_image_encryption_key, '$.KmsKeyName') as kms_key_name,
json_extract(
machine_image_encryption_key,
'$.KmsKeyServiceAccount'
) as kms_key_service_account,
json_extract(machine_image_encryption_key, '$.RawKey') as raw_key,
json_extract(machine_image_encryption_key, '$.RsaEncryptedKey') as rsa_encrypted_key,
json_extract(machine_image_encryption_key, '$.Sha256') as sha256
from
gcp_compute_machine_image;

Get the machine type details for the machine images

Analyzing the memory, CPU, and disk capabilities of machine types can inform decisions about image deployment based on performance needs. Knowing the deprecation status and creation timestamp of machine types helps in compliance and migration planning.

select
i.name as image_name,
i.id image_id,
i.instance_properties ->> 'machineType' as machine_type,
t.creation_timestamp as machine_type_creation_timestamp,
t.memory_mb as machine_type_memory_mb,
t.maximum_persistent_disks as machine_type_maximum_persistent_disks,
t.is_shared_cpu as machine_type_is_shared_cpu,
t.zone as machine_type_zone,
t.deprecated as machine_type_deprecated
from
gcp_compute_machine_image as i,
gcp_compute_machine_type as t
where
t.name = (i.instance_properties ->> 'machineType')
and t.zone = split_part(i.source_instance, '/', 9);
select
i.name as image_name,
i.id as image_id,
json_extract(i.instance_properties, '$.machineType') as machine_type,
t.creation_timestamp as machine_type_creation_timestamp,
t.memory_mb as machine_type_memory_mb,
t.maximum_persistent_disks as machine_type_maximum_persistent_disks,
t.is_shared_cpu as machine_type_is_shared_cpu,
t.zone as machine_type_zone,
t.deprecated as machine_type_deprecated
from
gcp_compute_machine_image as i,
gcp_compute_machine_type as t
where
t.name = json_extract(i.instance_properties, '$.machineType')
and t.zone = substr(
i.source_instance,
instr(i.source_instance, '/', -1) + 1
);

Schema for gcp_compute_machine_image

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
creation_timestamptimestamp with time zoneThe creation timestamp for this machine image in RFC3339 text format.
descriptiontextAn optional description of this resource. Provide this property when you create the resource.
guest_flushbooleanWhether to attempt an application consistent machine image by informing the OS to prepare for the snapshot process.
idbigintA unique identifier for this machine image. The server defines this identifier.
instance_propertiesjsonbProperties of source instance.
kindtextThe resource type, which is always compute#machineImage for machine image.
machine_image_encryption_keyjsonbEncrypts the machine image using a customer-supplied encryption key. After you encrypt a machine image using a customer-supplied key, you must provide the same key if you use the machine image later.
nametext=Name of the resource.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project in which the resource is located.
saved_disksjsonbAn array of Machine Image specific properties for disks attached to the source instance.
self_linktextThe URL for this machine image. The server defines this URL.
source_disk_encryption_keysjsonbThe customer-supplied encryption key of the disks attached to the source instance. Required if the source disk is protected by a customer-supplied encryption key.
source_instancetextThe source instance used to create the machine image.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustextThe status of the machine image.
storage_locationsjsonbThe regional or multi-regional Cloud Storage bucket location where the machine image is stored.
titletextTitle of the resource.
total_storage_bytesbigintTotal size of the storage used by the machine image.

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_machine_image