turbot/gcp

steampipe plugin install gcpsteampipe plugin install gcp
gcp_audit_policygcp_bigquery_datasetgcp_bigquery_jobgcp_bigquery_tablegcp_bigtable_instancegcp_cloudfunctions_functiongcp_compute_addressgcp_compute_backend_bucketgcp_compute_backend_servicegcp_compute_diskgcp_compute_disk_metric_read_opsgcp_compute_disk_metric_read_ops_dailygcp_compute_disk_metric_read_ops_hourlygcp_compute_disk_metric_write_opsgcp_compute_disk_metric_write_ops_dailygcp_compute_disk_metric_write_ops_hourlygcp_compute_firewallgcp_compute_forwarding_rulegcp_compute_global_addressgcp_compute_global_forwarding_rulegcp_compute_imagegcp_compute_instancegcp_compute_instance_metric_cpu_utilizationgcp_compute_instance_metric_cpu_utilization_dailygcp_compute_instance_metric_cpu_utilization_hourlygcp_compute_instance_templategcp_compute_machine_typegcp_compute_networkgcp_compute_node_groupgcp_compute_node_templategcp_compute_project_metadatagcp_compute_regiongcp_compute_resource_policygcp_compute_routergcp_compute_snapshotgcp_compute_ssl_policygcp_compute_subnetworkgcp_compute_target_https_proxygcp_compute_target_poolgcp_compute_target_ssl_proxygcp_compute_target_vpn_gatewaygcp_compute_url_mapgcp_compute_vpn_tunnelgcp_compute_zonegcp_dns_managed_zonegcp_dns_policygcp_dns_record_setgcp_iam_policygcp_iam_rolegcp_kms_keygcp_kms_key_ringgcp_logging_bucketgcp_logging_exclusiongcp_logging_metricgcp_logging_sinkgcp_monitoring_alert_policygcp_monitoring_groupgcp_monitoring_notification_channelgcp_organizationgcp_projectgcp_project_organization_policygcp_project_servicegcp_pubsub_snapshotgcp_pubsub_subscriptiongcp_pubsub_topicgcp_service_accountgcp_service_account_keygcp_sql_backupgcp_sql_databasegcp_sql_database_instancegcp_sql_database_instance_metric_connectionsgcp_sql_database_instance_metric_connections_dailygcp_sql_database_instance_metric_connections_hourlygcp_sql_database_instance_metric_cpu_utilizationgcp_sql_database_instance_metric_cpu_utilization_dailygcp_sql_database_instance_metric_cpu_utilization_hourlygcp_storage_bucket

Table: gcp_compute_image

An Image resource contains a boot loader, an operating system and a root file system that is necessary for starting an instance.

Examples

Compute image basic info

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

List of active, standard compute images

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

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

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

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

List of active compute images older than 90 days

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;

Find VM instances built from images older than 90 days

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

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

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

.inspect gcp_compute_image

GCP Compute Image

NameTypeDescription
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 without time zoneThe creation timestamp of the resource.
deprecatedjsonbAn object comtaining the detailed deprecation status associated with this image.
deprecation_statetextThe 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).
familytextThe 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.
nametextA friendly name that identifies the resource.
projecttextThe 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_projecttextThe 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_typetextThe type of the image used to create this disk.
statustextThe 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.