steampipe plugin install gcp

Table: gcp_compute_instance - Query Google Compute Engine Instances using SQL

Google Compute Engine is a service within Google Cloud Platform that provides scalable, high-performance virtual machines (VMs) that run on Google's innovative data center infrastructure. It offers a flexible computing environment that lets you choose from pre-defined or custom machine types to suit your workloads. Google Compute Engine also allows you to use various operating systems, frameworks, and languages to build your applications.

Table Usage Guide

The gcp_compute_instance table provides comprehensive information about instances within Google Compute Engine. As a system administrator or DevOps engineer, you can leverage this table to explore instance-specific details, including machine type, operating system, network configuration, and status. This table is particularly useful for assessing the state of your instances, identifying instances with non-standard configurations, and understanding the distribution of resources across your instances.

Examples

Instance count in each availability zone

Analyze the distribution of instances across different availability zones to understand the load balancing within your Google Cloud Platform. This can help optimize resource allocation and improve system resilience.

select
zone_name,
count(*)
from
gcp_compute_instance
group by
zone_name
order by
count desc;
select
zone_name,
count(*)
from
gcp_compute_instance
group by
zone_name
order by
count(*) desc;

Count the number of instances by instance type

Identify the distribution of different machine types within your Google Cloud Compute instances. This allows you to understand which machine types are most commonly used, aiding in resource allocation and cost management.

select
machine_type_name,
count(*) as count
from
gcp_compute_instance
group by
machine_type_name
order by
count desc;
select
machine_type_name,
count(*) as count
from
gcp_compute_instance
group by
machine_type_name
order by
count desc;

List of instances without application label

Identify instances where the application label is missing. This is useful for maintaining consistent tagging practices across your Google Cloud Compute instances.

select
name,
tags
from
gcp_compute_instance
where
tags -> 'application' is null;
select
name,
tags
from
gcp_compute_instance
where
json_extract(tags, '$.application') is null;

List instances having deletion protection feature disabled

Determine the areas in which instances lack deletion protection, a feature that safeguards against accidental data loss. This query is useful for identifying potential vulnerabilities and ensuring data integrity.

select
name,
deletion_protection
from
gcp_compute_instance
where
not deletion_protection;
select
name,
deletion_protection
from
gcp_compute_instance
where
deletion_protection = 0;

List the disk stats attached to the instances

Determine the overall storage capacity across all instances by analyzing the number of disks attached and their respective sizes. This aids in managing resources and planning for storage expansion.

select
name,
count(d) as num_disks,
sum((d ->> 'diskSizeGb') :: int) as total_storage
from
gcp_compute_instance as i,
jsonb_array_elements(disks) as d
group by
name;
select
i.name,
count(d.value) as num_disks,
sum(json_extract(d.value, '$.diskSizeGb')) as total_storage
from
gcp_compute_instance i,
json_each(i.disks) as d
group by
i.name;

Find instances with IP in a given CIDR range

Identify instances within a specific IP range in your Google Cloud Platform's compute instances. This can be useful in understanding your network distribution and identifying potential security risks.

select
name,
nic ->> 'networkIP' as ip_address
from
gcp_compute_instance as i,
jsonb_array_elements(network_interfaces) as nic
where
(nic ->> 'networkIP') :: inet << = '10.128.0.0/16';
select
i.name,
json_extract(nic.value, '$.networkIP') as ip_address
from
gcp_compute_instance i,
json_each(i.network_interfaces) as nic
where
json_extract(nic.value, '$.networkIP') like '10.128.%';

Find instances that have been stopped for more than 30 days

Explore instances that have been inactive for an extended period of time. This is useful for identifying potential cost-saving opportunities by eliminating unused resources.

select
name,
status,
last_stop_timestamp
from
gcp_compute_instance
where
status = 'TERMINATED'
and last_stop_timestamp < current_timestamp - interval '30 days';
select
name,
status,
last_stop_timestamp
from
gcp_compute_instance
where
status = 'TERMINATED'
and last_stop_timestamp < datetime('now', '-30 days');

Find the boot disk of each instance

This query allows you to identify the boot disk associated with each virtual machine instance, particularly useful when you need to understand the source image used for the boot disk, such as in instances where you're troubleshooting or auditing system configurations.

select
vm.name as instance_name,
d.name as disk_name,
d.source_image
from
gcp_compute_instance as vm,
jsonb_array_elements(vm.disks) as vmd,
gcp_compute_disk as d
where
vmd ->> 'source' = d.self_link
and (vmd ->> 'boot') :: bool
and d.source_image like '%debian-10-buster-v20201014';
select
vm.name as instance_name,
d.name as disk_name,
d.source_image
from
gcp_compute_instance as vm,
json_each(vm.disks) as vmd,
gcp_compute_disk as d
where
json_extract(vmd.value, '$.source') = d.self_link
and json_extract(vmd.value, '$.boot') = 'true'
and d.source_image like '%debian-10-buster-v20201014';

Control examples

Schema for gcp_compute_instance

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
can_ip_forwardboolean!=, =Allows this instance to send and receive packets with non-matching destination or source IPs. This is required if you plan to use this instance to forward routes.
confidential_instance_configjsonbConfidential VM detail for the instance, if applicable. Confidential VMs, now in beta, is the first product in Google Cloud’s Confidential Computing portfolio.
cpu_platformtext!=, =The CPU platform used by this instance.
creation_timestamptimestamp with time zoneTimestamp the instance was created.
deletion_protectionboolean!=, =Whether the resource should be protected against deletion.
descriptiontextThe instance description.
disksjsonbAn Array of disks associated with this instance
display_devicejsonbDisplay device for the instance.
fingerprinttextSpecifies a fingerprint for this resource, which is essentially a hash of the instance's contents and used for optimistic locking. The fingerprint is initially generated by Compute Engine and changes after every request to modify or update the instance. You must always provide an up-to-date fingerprint hash in order to update the instance.
guest_acceleratorsjsonbA list of the type and count of accelerator cardsattached to the instance.
hostnametext!=, =The instance hostname.
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 instance id.
kindtextType of the resource. Always compute#instance for instances.
label_fingerprinttextA fingerprint for this request, which is essentially a hash of the label's contents and used for optimistic locking. The fingerprint is initially generated by Compute Engine and changes after every request to modify or update labels. You must always provide an up-to-date fingerprint hash in order to update or change labels.
labelsjsonbLabels that apply to this instance.
last_start_timestamptimestamp with time zoneTimestamp when the instance was last started.
last_stop_timestamptimestamp with time zoneTimestamp when the instance was last stopped.
last_suspended_timestamptimestamp with time zoneTimestamp when the instance was last suspended.
locationtextThe GCP multi-region, region, or zone in which the resource is located.
machine_typetextFull or partial URL of the machine type resource for this instance, in the format: zones/zone/machineTypes/machine-type.
machine_type_nametextName of the machine type resource for this instance.
metadatajsonbThe metadata key/value pairs assigned to this instance.
min_cpu_platformtextSpecifies a minimum CPU platform for the VM instance.
nametext!=, =The name of the instance.
network_interfacesjsonbAn array of network configurations for this instance.
network_tagsjsonbNetwork tags applied to this instance. Network tags are used to identify valid sources or targets for network firewalls.
private_ipv6_google_accesstextThe private IPv6 google access type for the instance.
projecttextThe GCP Project in which the resource is located.
reservation_affinityjsonbSpecifies the reservations that this instance can consume from.
resource_policiesjsonbResource policies applied to this instance.
schedulingjsonbThe scheduling options for this instance.
self_linktextServer-defined URL for this resource.
service_accountsjsonbA list of service accounts, with their specified scopes, authorized for this instance.
shielded_instance_configjsonbShielded instance configuration. Shielded VM provides verifiable integrity to prevent against malware and rootkits.
shielded_instance_integrity_policyjsonbShielded instance integrity policy. Shielded instance configuration. Shielded VM provides verifiable integrity to prevent against malware and rootkits.
start_restrictedboolean!=, =Whether a VM has been restricted for start because Compute Engine has detected suspicious activity.
statustext!=, =The status of the instance (PROVISIONING, STAGING, RUNNING, STOPPING, SUSPENDING, SUSPENDED, REPAIRING, and TERMINATED).
status_messagetextAn optional, human-readable explanation of the status.
tagsjsonbA map of tags for the resource.
titletextTitle of the resource.
zonetextThe zone in which the instance resides.
zone_nametextThe zone name in which the instance resides.

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_instance