GCP Steampipe Plugin

https://hub.steampipe.io/plugins/turbot/gcp
Admin
The GCP Steampipe plugin allows users to instantly query Google Cloud Platform (GCP) resources and...
Tokens:126,557
Snippets:1,521
Trust Score:9.7
Update:12 hours ago
Show doc for...
Tokens:
Raw
### Query GCP Storage Buckets with Steampipe

Source: https://hub.steampipe.io/plugins/turbot/gcp/index

This example demonstrates how to query GCP storage bucket information, including name, location, and versioning status, using Steampipe's SQL interface. It requires the turbot/gcp plugin to be installed and configured with appropriate GCP credentials.

```sql
select
  name,
  location,
  versioning_enabled
from
  gcp_storage_bucket;
```

--------------------------------

### Get Available CPU Platforms per Zone (Postgres/SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_zone

Query to find the available CPU platforms within each Google Cloud Platform compute zone. This information is valuable for optimizing resource allocation and performance by understanding hardware capabilities.

```sql
select
  name,
  available_cpu_platforms
from
  gcp_compute_zone;
```

--------------------------------

### Install GCP SQLite Extension

Source: https://hub.steampipe.io/plugins/turbot/gcp/index

Shows the command to install the GCP plugin as a SQLite Extension, enabling the querying of GCP resources using SQLite.

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

--------------------------------

### Get Basic GCP Project Information

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_organization_project

Retrieves basic information for active Google Cloud Platform projects, including name, project ID, project number, lifecycle state, and creation time. This helps in managing resources and tracking ongoing projects.

```sql
select
  name,
  project_id,
  project_number,
  lifecycle_state,
  create_time
from
  gcp_organization_project;
```

--------------------------------

### Get Instance Group Manager Details

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_group_manager

Fetch details for each instance group manager, specifically retrieving the name of the manager, the name of the instance group it manages, and the size of that instance group. This helps in understanding the scale of managed deployments.

```sql
select
  m.name,
  g.name as group_name,
  g.size as group_size
from
  gcp_compute_instance_group_manager as m,
  gcp_compute_instance_group as g
where
  m.instance_group ->> 'name' = g.name;
```

```sql
select
  m.name,
  g.name as group_name,
  g.size as group_size
from
  gcp_compute_instance_group_manager as m,
  gcp_compute_instance_group as g
where
  m.instance_group -> 'name' = g.name;
```

--------------------------------

### Get Basic Dataproc Metastore Service Information

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_dataproc_metastore_service

Retrieve fundamental details of Dataproc Metastore services, such as their names, locations, states, creation times, and release channels. This query requires no specific input parameters.

```sql
select
  name,
  location,
  state,
  create_time,
  release_channel
from
  gcp_dataproc_metastore_service;
```

--------------------------------

### Get Basic GCP Compute Instance Group Info

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_group

Retrieves fundamental information about GCP compute instance groups, such as their name, description, size, location, and associated project. This is useful for project management and resource allocation within GCP.

``` Postgres
select
  name,
  description,
  self_link,
  size,
  location,
  akas,
  project
from
  gcp_compute_instance_group;
```

``` Postgres
select
  name,
  description,
  self_link,
  size,
  location,
  akas,
  project
from
  gcp_compute_instance_group;
```

--------------------------------

### Get Basic GCP Compute Autoscaler Info (Postgres)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_autoscaler

Retrieves fundamental details of GCP compute autoscalers, including name, description, self-link, status, location, AKAs, and recommended size. This query is useful for understanding your current autoscaler configuration.

```sql
select
  name,
  description,
  self_link,
  status,
  location,
  akas,
  recommended_size
from
  gcp_compute_autoscaler;
```

--------------------------------

### Get GCP Compute Target Pools

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_target_pool

Retrieves basic information like name, ID, and location for all GCP compute target pools in your environment. This query is useful for understanding your load balancing setup.

```sql
select
  name,
  id,
  location
from
  gcp_compute_target_pool;
```

--------------------------------

### Get GCP Instance Template Boot Disk Info (Postgres)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_template

Retrieves detailed boot disk information for each instance template, including device name, disk type, size, source image, and mode. This query requires PostgreSQL due to its JSON functions.

```sql
select
  name,
  id,
  disk ->> 'deviceName' as disk_device_name,
  disk -> 'initializeParams' ->> 'diskType' as disk_type,
  disk -> 'initializeParams' ->> 'diskSizeGb' as disk_size_gb,
  split_part(
    disk -> 'initializeParams' ->> 'sourceImage',
    '/',
    5
  ) as source_image,
  disk ->> 'mode' as mode
from
  gcp_compute_instance_template,
  jsonb_array_elements(instance_disks) as disk;
```

--------------------------------

### Export Composer Environment Data with Steampipe CLI (Shell)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_composer_environment

Demonstrates how to export data for `gcp_composer_environment` using the Steampipe Exporter CLI. This command requires a configuration argument to specify how the export should be performed. It's a practical application of the installed exporter.

```shell
steampipe_export_gcp --config '<your_config>' gcp_composer_environment
```

--------------------------------

### Get Detailed GCP AlloyDB Cluster Configuration

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_alloydb_cluster

Retrieves in-depth configuration settings for a specific AlloyDB cluster, including its encryption and network configurations. This query is valuable for understanding cluster setup and planning modifications.

```sql
select
  name,
  encryption_config,
  network_config
from
  gcp_alloydb_cluster
where
  display_name = 'your-cluster-name';
```

```sql
select
  name,
  json_extract(encryption_config, '$') as encryption_config,
  json_detail(network_config, '$') as network_config
from
  gcp_alloydb_cluster
where
  display_name = 'your-cluster-name';
```

--------------------------------

### List All GCP Project Services (PostgreSQL/SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_project_service

Retrieves all services associated with a Google Cloud Platform project. Useful for understanding active services within a project for management and allocation. Requires Steampipe with the GCP plugin.

```sql
select
  *
from
  gcp_project_service;
```

```sql
select
  *
from
  gcp_project_service;
```

--------------------------------

### List Composer Environments by Node Configuration (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_composer_environment

Retrieves Composer environments based on their node count and machine types. This query is useful for optimizing performance and cost by identifying environments with specific hardware configurations. It queries the `gcp_composer_environment` table.

```sql
select
  name,
  node_count,
  node_config ->> 'machineType' as machine_type,
  location,
  project
from
  gcp_composer_environment
where
  node_count > 0;
```

```sql
select
  name,
  node_count,
  json_extract(node_config, '$.machineType') as machine_type,
  location,
  project
from
  gcp_composer_environment
where
  node_count > 0;
```

--------------------------------

### Get GCP Dataproc Cluster Config Details (SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_dataproc_cluster

Retrieve detailed configuration information for a specific Dataproc cluster using the `json_extract` function, suitable for SQLite environments. This example targets 'cluster-5824' and extracts nested JSON fields from the 'config' column.

```sql
select
  cluster_name,
  json_extract(config, '$.endpointConfig') as endpoint_config,
  json_extract(config, '$.configBucket') as config_bucket,
  json_extract(config, '$.shieldedInstanceConfig') as shielded_instance_config,
  json_extract(config, '$.masterConfig') as master_config
from
  gcp_dataproc_cluster
where
  cluster_name = 'cluster-5824';
```

--------------------------------

### List GCP Instance Templates by Reservation Type (Postgres)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_template

Identifies instance templates configured to consume specific reservations. This query helps in optimizing resource allocation and managing costs.

```sql
select
  name,
  id,
  instance_reservation_affinity ->> 'consumeReservationType' as consume_reservation_type
from
  gcp_compute_instance_template
where
  instance_reservation_affinity ->> 'consumeReservationType' = 'SPECIFIC_RESERVATION';
```

--------------------------------

### Get GCP Instance Template Network Interface Info (Postgres)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_template

Retrieves network interface details for each instance template, including network name and access configuration tiers. This query requires PostgreSQL due to its JSON and string manipulation functions.

```sql
select
  name,
  id,
  i ->> 'name' as name,
  split_part(i ->> 'network', '/', 10) as network_name,
  p ->> 'name' as access_config_name,
  p ->> 'networkTier' as access_config_network_tier,
  p ->> 'type' as access_config_type
from
  gcp_compute_instance_template,
  jsonb_array_elements(instance_network_interfaces) as i,
  jsonb_array_elements(i -> 'accessConfigs') as p;
```

--------------------------------

### Get Dataplex Task Trigger Specifications

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_dataplex_task

Extract details about how Dataplex tasks are triggered, including schedule, type, start time, and maximum retries. This query demonstrates accessing nested JSON fields within the 'trigger_spec' column using both Steampipe's native operators and standard SQL JSON functions.

```Postgres FDW
select
  name,
  trigger_spec ->> 'type' as trigger_type,
  trigger_spec ->> 'schedule' as trigger_schedule,
  trigger_spec ->> 'startTime' as trigger_start_time,
  trigger_spec ->> 'maxRetries' as trigger_max_retries
from
  gcp_dataplex_task;
```

```SQLite Extension
select
  name,
  json_extract(trigger_spec, '$.type') as trigger_type,
  json_extract(trigger_spec, '$.schedule') as trigger_schedule,
  json_extract(trigger_spec, '$.startTime') as trigger_start_time,
  json_extract(trigger_spec, '$.maxRetries') as trigger_max_retries
from
  gcp_dataplex_task;
```

--------------------------------

### Get Customer-Managed Key Details of GCP Vertex AI Endpoints (PostgreSQL JSONB)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_vertex_ai_endpoint

Extracts the customer-managed encryption key (KMS Key Name) details for Google Cloud Platform Vertex AI endpoints using PostgreSQL's JSONB operators. This helps in assessing and managing the security setup of your AI endpoints.

```sql
select
  name,
  create_time,
  encryption_spec ->> 'KmsKeyName' as kms_key_name
from
  gcp_vertex_ai_endpoint;
```

--------------------------------

### List Composer Environments by Size (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_composer_environment

Identifies Composer environments based on their specified size, such as 'ENVIRONMENT_SIZE_SMALL' or 'ENVIRONMENT_SIZE_LARGE'. This query helps in managing and understanding resource allocation. It uses the `gcp_composer_environment` table.

```sql
select
  name,
  environment_size,
  location,
  project
from
  gcp_composer_environment
where
  environment_size = 'ENVIRONMENT_SIZE_SMALL';
```

--------------------------------

### Get URL Map Host Rule Info (PostgreSQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_url_map

Retrieves URL maps with specific host rules, including their hosts and associated path matchers. Aids in identifying misconfigurations in network traffic distribution. Requires the gcp plugin for Steampipe.

```sql
select
  name,
  id,
  p ->> 'hosts' as hosts,
  p ->> 'pathMatcher' as path_matcher
from
  gcp_compute_url_map,
  jsonb_array_elements(host_rules) as p;
```

--------------------------------

### Get Basic GCP Cloud Run Service Information (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_cloud_run_service

Retrieves core details of Google Cloud Run services, including name, description, client, creation time, and launch stage. This is useful for an overview of service configurations and statuses. Assumes the 'gcp_cloud_run_service' table is available.

```sql
select
  name,
  description,
  client,
  client_version,
  create_time,
  creator,
  generation,
  launch_stage
from
  gcp_cloud_run_service;
```

--------------------------------

### Install GCP Export CLI

Source: https://hub.steampipe.io/plugins/turbot/gcp/index

Provides the command to install the GCP plugin as a standalone Export CLI. This allows data extraction from GCP resources without needing a full Steampipe or database installation.

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

--------------------------------

### Install Steampipe Exporter CLI for GCP

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_cloud_asset

Installs the Steampipe Exporter CLI for the GCP plugin using a shell script. This allows for data extraction without a Steampipe database. The script downloads and installs the necessary binary.

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

```

--------------------------------

### Configure GCP Logging Metric Buckets

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_logging_metric

Examine the bucket configuration details for GCP logging metrics, including growth factor, scale, offset, and width. This is useful for analyzing data distribution.

```sql
select
  name,
  exponential_buckets_options_growth_factor,
  exponential_buckets_options_num_finite_buckets,
  exponential_buckets_options_scale,
  linear_buckets_options_num_finite_buckets,
  linear_buckets_options_offset,
  linear_buckets_options_width,
  explicit_buckets_options_bounds
from
  gcp_logging_metric;
```

--------------------------------

### Find GCP Instance Boot Disk (PostgreSQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance

This query identifies the boot disk for each GCP VM instance and its source image. It's useful for auditing or troubleshooting configurations. It uses PostgreSQL syntax for JSON manipulation.

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

--------------------------------

### Install Turbot GCP Plugin

Source: https://hub.steampipe.io/plugins/turbot/gcp/queries_table=gcp_compute_instance_metric_cpu_utilization_hourly

Command to install the Turbot GCP plugin for Steampipe. This allows Steampipe to connect to and query Google Cloud Platform resources.

```bash
steampipe plugin install gcp
```

--------------------------------

### List Ready GCP Machine Images - Steampipe CLI

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_machine_image

Filters and lists machine images that are in a 'READY' state, ensuring they are available for deployment or immediate use. This is crucial for operational stability and maintaining an efficient image repository by excluding images that are still preparing or have been deprecated.

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

--------------------------------

### Install GCP Postgres FDW

Source: https://hub.steampipe.io/plugins/turbot/gcp/index

Provides the command to install the GCP plugin as a native Postgres Foreign Data Wrapper (FDW). This allows querying GCP data directly from a PostgreSQL database.

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

--------------------------------

### Analyze Daily CPU Utilization Metrics

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_metric_cpu_utilization_daily

Query Google Cloud Compute instance CPU utilization metrics for basic analysis. This helps in understanding usage patterns and performance, useful for capacity planning and cost optimization. The data includes instance name, timestamp, and utilization statistics.

```sql
select
  name,
  timestamp,
  minimum,
  maximum,
  average,
  sample_count
from
  gcp_compute_instance_metric_cpu_utilization_daily
order by
  name,
  timestamp;
```

```sql
select
  name,
  timestamp,
  minimum,
  maximum,
  average,
  sample_count
from
  gcp_compute_instance_metric_cpu_utilization_daily
order by
  name,
  timestamp;
```

--------------------------------

### Get Google Cloud Compute Disk Write Operations Metrics

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_disk_metric_write_ops

Retrieve basic metrics for Google Cloud Compute disks, including name, minimum, maximum, and average write operations, along with the sample count. This query helps in getting an overview of disk performance.

```APIDOC
## GET GCP Compute Disk Write Operations Metrics

### Description
Retrieves basic metrics for Google Cloud Compute disks, including name, minimum, maximum, and average write operations, along with the sample count. This query helps in getting an overview of disk performance.

### Method
SELECT

### Endpoint
N/A (This is a SQL query against a Steampipe table)

### Parameters
#### Query Parameters
None

#### Request Body
None

### Request Example
```sql
select
  name,
  minimum,
  maximum,
  average,
  sample_count
from
  gcp_compute_disk_metric_write_ops
order by
  name;
```

### Response
#### Success Response (200)
- **name** (text) - The name of the disk.
- **minimum** (double precision) - The minimum metric value for the data point.
- **maximum** (double precision) - The maximum metric value for the data point.
- **average** (double precision) - The average of the metric values that correspond to the data point.
- **sample_count** (integer) - The number of samples collected for the metric.

#### Response Example
```json
{
  "name": "disk-name",
  "minimum": 0.5,
  "maximum": 10.2,
  "average": 5.1,
  "sample_count": 100
}
```
```

--------------------------------

### List Running Google Cloud Workstations

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_workstations_workstation

Identify and list all Google Cloud Workstations that are currently in a 'STATE_RUNNING' state. This helps in monitoring active development environments and understanding current resource utilization.

```sql
select
  name,
  display_name,
  state,
  host,
  start_time,
  location
from
  gcp_workstations_workstation
where
  state = 'STATE_RUNNING';
```

--------------------------------

### Execute Steampipe Exporter CLI for GCP

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_region

Executes the Steampipe Exporter CLI for GCP compute regions, passing configuration details. This command allows for targeted data extraction using the installed exporter. The `--config` argument specifies the connection configuration, and `gcp_compute_region` indicates the resource to query. Ensure the Exporter CLI is installed and a valid configuration is provided.

```bash
steampipe_export_gcp --config '<your_config>' gcp_compute_region
```

--------------------------------

### Export GCP Compute Disk Write Ops Data

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_disk_metric_write_ops

Instructions on how to install and use the Steampipe Exporter CLI to extract `gcp_compute_disk_metric_write_ops` data as a standalone executable.

```APIDOC
## Export GCP Compute Disk Write Ops Data using Steampipe Exporter CLI

### Description
This section details how to install and use the Steampipe Exporter CLI to extract `gcp_compute_disk_metric_write_ops` data without requiring a database connection.

### Installation

Install the exporter using the provided script:
```bash
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- gcp
```

### Usage

Run the exporter with the desired configuration and table:
```bash
steampipe_export_gcp --config '<your_config>' gcp_compute_disk_metric_write_ops
```

### Parameters

- **--config** (string) - Path to the Steampipe configuration file. Required for authenticated access.
- **gcp_compute_disk_metric_write_ops** (string) - The name of the table to export data from.

### Example

To export all data from `gcp_compute_disk_metric_write_ops` using a configuration file located at `~/.steampipe/config.spc`:

```bash
steampipe_export_gcp --config '~/.steampipe/config.spc' gcp_compute_disk_metric_write_ops
```
```

--------------------------------

### List GCP Instance Templates by Machine Type (Postgres/SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_template

Lists instance templates that use the 'c2-standard-4' machine type. This query is useful for identifying specific resource usage and planning infrastructure.

```sql
select
  name,
  id,
  instance_machine_type
from
  gcp_compute_instance_template
where
  instance_machine_type = 'c2-standard-4';
```

--------------------------------

### Get Dataplex Zones in Specific State

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_dataplex_zone

Retrieves Dataplex Zones that are in a specific state, such as 'ACTIVE'. This query is useful for monitoring the current status of your data zones.

```sql
select
  name,
  state,
  create_time,
  update_time
from
  gcp_dataplex_zone
where
  state = 'ACTIVE';
```

--------------------------------

### List GCP IAM Roles in BETA Stage (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_iam_role

Identify and list GCP IAM roles that are currently in the 'BETA' stage. This helps in managing roles that might require further testing or development.

```sql
select
  name,
  description,
  stage
from
  gcp_iam_role
where
  stage = 'BETA';
```

--------------------------------

### Export GCP Service Account Data with Exporter CLI

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_service_account

Uses the installed Steampipe Exporter CLI to export data for the 'gcp_service_account' resource. Requires a configuration argument.

```bash
steampipe_export_gcp --config '<your_config>' gcp_service_account
```

--------------------------------

### List Database Instances Without Application Tag

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_sql_database_instance

Finds database instances that are missing an 'application' tag key. Useful for identifying and rectifying configuration gaps to improve resource management and organization.

```sql
select
  name,
  tags
from
  gcp_sql_database_instance
where
  not tags :: JSONB ? 'application';
```

```sql
select
  name,
  tags
from
  gcp_sql_database_instance
where
  not json_valid(tags) or json_extract(tags, '$.application') is null;
```

--------------------------------

### Get BigQuery Job Creation Times and Locations

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_bigquery_job

Retrieves the job ID, self-link, creation time, and location for all BigQuery jobs. This helps in understanding when and where jobs were initiated.

```sql
select
  job_id,
  self_link,
  creation_time,
  location
from
  gcp_bigquery_job;
```

--------------------------------

### Export GCP Logging Metrics Data

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_logging_metric

Use the installed GCP Exporter CLI to export data for the 'gcp_logging_metric' table. Configuration can be passed using the '--config' argument.

```shell
steampipe_export_gcp --config '<your_config>' gcp_logging_metric
```

--------------------------------

### List GCP Instance Templates by Reservation Type (SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_template

Identifies instance templates configured to consume specific reservations using SQLite's JSON functions. This query helps in optimizing resource allocation and managing costs.

```sql
select
  name,
  id,
  json_extract(instance_reservation_affinity, '$.consumeReservationType') as consume_reservation_type
from
  gcp_compute_instance_template
where
  json_extract(instance_reservation_affinity, '$.consumeReservationType') = 'SPECIFIC_RESERVATION';
```

--------------------------------

### Get Environment Variables for Google Cloud Workstations

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_workstations_workstation

Retrieve the environment variables configured for each Google Cloud Workstation. This allows examination of runtime settings and configurations for your development environments.

```sql
select
  name,
  display_name,
  jsonb_pretty(env) as environment_variables
from
  gcp_workstations_workstation;
```

```sql
select
  name,
  display_name,
  env as environment_variables
from
  gcp_workstations_workstation;
```

--------------------------------

### GCP SQL Instance Daily CPU Utilization Metrics (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_sql_database_instance_metric_cpu_utilization_daily

Retrieves basic daily CPU utilization metrics (minimum, maximum, average, sample count, and timestamp) for all Google Cloud SQL database instances. Useful for a general overview of instance performance.

```sql
select
  instance_id,
  minimum,
  maximum,
  average,
  sample_count,
  timestamp
from
  gcp_sql_database_instance_metric_cpu_utilization_daily
order by
  instance_id;
```

--------------------------------

### Get IAM Policy for Google Cloud Workstations

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_workstations_workstation

Retrieve the IAM policy bindings associated with Google Cloud Workstations. This query helps in understanding and auditing access control configurations for your workstations.

```sql
select
  name,
  i -> 'condition' as condition,
  i -> 'members' as members,
  i ->> 'role' as role
from
  gcp_workstations_workstation,
  jsonb_array_elements(iam_policy -> 'bindings') as i;
```

```sql
select
  name,
  json_extract(i.value, '$.condition') as condition,
  json_extract(i.value, '$.members') as members,
  json_extract(i.value, '$.role') as role
from
  gcp_workstations_workstation,
  json_each(json_extract(iam_policy, '$.bindings')) as i;
```

--------------------------------

### Retrieve Basic Composer Environment Info (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_composer_environment

Fetches fundamental details of Composer environments, including their name, location, state, Airflow URI, and creation time. This query relies on the `gcp_composer_environment` table within the Steampipe GCP plugin.

```sql
select
  name,
  location,
  state,
  airflow_uri,
  create_time
from
  gcp_composer_environment;
```

--------------------------------

### List GCP Vertex AI Notebook Templates by Tag

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_vertex_ai_notebook_runtime_template

Retrieves Notebook Runtime Templates that are associated with specific tags, aiding in resource organization. The examples show how to filter by tag values using Steampipe's property access and standard JSON extraction methods.

```sql
select
  name,
  display_name,
  tags ->> 'foo' as tag_value,
  location
from
  gcp_vertex_ai_notebook_runtime_template
where
  tags ->> 'foo' = 'bar';
```

```sql
select
  name,
  display_name,
  json_extract(tags, '$.foo') as tag_value,
  location
from
  gcp_vertex_ai_notebook_runtime_template
where
  json_extract(tags, '$.foo') = 'bar';
```

--------------------------------

### Get Essential Contacts for GCP Organizations

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_organization

Fetches the essential contacts associated with specific Google Cloud organizations. This helps in quickly identifying key contacts for streamlined communication and operational efficiency.

```sql
select
  organization_id,
  jsonb_pretty(essential_contacts) as essential_contacts
from
  gcp_organization;
```

```sql
select
  organization_id,
  essential_contacts
from
  gcp_organization;
```

--------------------------------

### Get Website Restrictions for GCP Keys

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_apikeys_key

This query lists the website restrictions associated with GCP API keys. It helps in understanding the limitations on where each key can be used, ensuring adherence to security policies.

```sql
select
  uid,
  display_name,
  a as allowed_website
from
  gcp_apikeys_key,
  jsonb_array_elements_text(restrictions -> 'browserKeyRestrictions' -> 'allowedReferrers') as a
where
  restrictions is not null;
```

```sql
select
  uid,
  display_name,
  json_extract(restrictions, '$.browserKeyRestrictions.allowedReferrers') as allowed_website
from
  gcp_apikeys_key
where
  json_type(restrictions, '$.browserKeyRestrictions.allowedIps') = 'array';
```

--------------------------------

### List Composer Environments with Scheduled Snapshots (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_composer_environment

Fetches Composer environments that have scheduled snapshot configurations enabled for disaster recovery. This query is essential for ensuring data resilience and enabling quick environment restoration. It uses the `gcp_composer_environment` table.

```sql
select
  name,
  recovery_config ->> 'scheduledSnapshotsConfig' as scheduled_snapshots_config,
  location,
  project
from
  gcp_composer_environment
where
  recovery_config ->> 'scheduledSnapshotsConfig' is not null;
```

```sql
select
  name,
  json_extract(recovery_config, '$.scheduledSnapshotsConfig') as scheduled_snapshots_config,
  location,
  project
from
  gcp_composer_environment
where
  json_extract(recovery_config, '$.scheduledSnapshotsConfig') is not null;
```

--------------------------------

### Get API Service Restrictions for GCP Keys

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_apikeys_key

This query retrieves the allowed services for each GCP API key that has restrictions defined. It aids in understanding and managing the specific services accessible by each key.

```sql
select
  uid,
  display_name,
  a ->> 'service' as allowed_service
from
  gcp_apikeys_key,
  jsonb_array_elements(restrictions -> 'apiTargets') as a
where
  restrictions is not null;
```

```sql
select
  uid,
  display_name,
  json_extract(a.value, '$.service') as allowed_service
from
  gcp_apikeys_key,
  json_each(restrictions, '$.apiTargets') as a
where
  restrictions is not null;
```

--------------------------------

### Export GCP SQL Database Instance Data with Steampipe Exporter

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_sql_database_instance

Demonstrates how to use the Steampipe Exporter CLI to export data for the 'gcp_sql_database_instance' resource. The command accepts a configuration argument to specify export settings.

```bash
steampipe_export_gcp --config '<your_config>' gcp_sql_database_instance
```

--------------------------------

### List All GCP Compute Routes

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_route

Retrieves basic information about all network routing configurations in GCP, including name, destination range, priority, and network. Useful for a general overview of routing setup.

```sql
select
  name,
  dest_range,
  priority,
  network
from
  gcp_compute_route;
```

--------------------------------

### Join Dataproc Metastore Services with Network Configurations (SQL Standard)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_dataproc_metastore_service

Retrieve Dataproc Metastore services along with their associated network configurations by joining `gcp_dataproc_metastore_service` with `gcp_compute_network`. This query uses standard SQL string manipulation to match network names.

```sql
select
  m.name as service_name,
  m.location,
  m.network,
  m.network_config,
  n.name as network_name,
  n.auto_create_subnetworks,
  n.peerings
from
  gcp_dataproc_metastore_service m
join
  gcp_compute_network n on n.name = split_part(m.network, '/', 5);
```

--------------------------------

### Export GCP Compute Snapshots (Shell)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_snapshot

Exports GCP compute snapshot data using the Steampipe Exporter CLI. This command requires the exporter to be installed and a valid configuration. It targets the `gcp_compute_snapshot` resource.

```bash
steampipe_export_gcp --config '<your_config>' gcp_compute_snapshot
```

--------------------------------

### List Manually Created Snapshots (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_snapshot

Lists snapshots that were not automatically created, indicating manual intervention. Helps in managing and differentiating backup strategies. Requires GCP Compute Engine snapshot data.

```sql
select
  name,
  source_disk_name,
  auto_created
from
  gcp_compute_snapshot
where
  not auto_created;
```

```sql
select
  name,
  source_disk_name,
  auto_created
from
  gcp_compute_snapshot
where
  auto_created = 0;
```

--------------------------------

### List GCP Vertex AI Notebook Templates with Internet Access Enabled

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_vertex_ai_notebook_runtime_template

Retrieves Notebook Runtime Templates where internet access is explicitly enabled within their network configuration. The queries show how to query the 'enable_internet_access' field from the 'network_spec' using both Steampipe and standard JSON functions.

```sql
select
  name,
  network_spec ->> 'enable_internet_access' as internet_access,
  network_spec ->> 'network' as network,
  location
from
  gcp_vertex_ai_notebook_runtime_template
where
  network_spec ->> 'enable_internet_access' = 'true';
```

```sql
select
  name,
  json_extract(network_spec, '$.enable_internet_access') as internet_access,
  json_extract(network_spec, '$.network') as network,
  location
from
  gcp_vertex_ai_notebook_runtime_template
where
  json_extract(network_spec, '$.enable_internet_access') = 'true';
```

--------------------------------

### Count Google Cloud Workstations by State

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_workstations_workstation

Get a count of Google Cloud Workstations grouped by their current state. This provides insights into the distribution of workstations across different statuses, aiding in resource planning.

```sql
select
  state,
  count(*)
from
  gcp_workstations_workstation
group by
  state;
```

--------------------------------

### Get GCP Project Access Approval Settings

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_project

Fetch the access approval settings for all Google Cloud Platform projects. This query is useful for auditing and managing permissions and approval workflows across projects.

```sql
select
  name,
  jsonb_pretty(access_approval_settings) as access_approval_settings
from
  gcp_project;
```

```sql
select
  name,
  access_approval_settings
from
  gcp_project;
```

--------------------------------

### Find GCP Instance Boot Disk (SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance

This query identifies the boot disk for each GCP VM instance and its source image. It's useful for auditing or troubleshooting configurations. It uses SQLite syntax for JSON manipulation.

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

--------------------------------

### Get Basic GCP Organization Information

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_organization

Retrieves fundamental details about Google Cloud organizations, such as their display name, organization ID, lifecycle state, and creation time. This is useful for administrative and auditing purposes.

```sql
select
  display_name,
  organization_id,
  lifecycle_state,
  creation_time
from
  gcp_organization;
```

--------------------------------

### Get Basic Google Cloud SQL Backup Info (Postgres/SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_sql_backup

Retrieve fundamental details of Google Cloud SQL backups, including ID, instance name, status, and timing. This query is useful for understanding the overall state and schedule of your backups. Requires the Steampipe GCP plugin.

```sql
select
  id,
  instance_name,
  description,
  status,
  end_time,
  enqueued_time,
  start_time,
  window_start_time
from
  gcp_sql_backup;
```

--------------------------------

### Get IP Restrictions for GCP Keys

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_apikeys_key

This query identifies GCP API keys that have IP address restrictions. It is crucial for enhancing security by ensuring that API access is limited to authorized IP addresses.

```sql
select
  uid,
  display_name,
  a as allowed_ip
from
  gcp_apikeys_key,
  jsonb_array_elements_text(restrictions -> 'serverKeyRestrictions' -> 'allowedIps') as a
where
  restrictions is not null;
```

```sql
select
  uid,
  display_name,
  json_extract(restrictions, '$.serverKeyRestrictions.allowedIps') as allowed_ip
from
  gcp_apikeys_key
where
  json_type(restrictions, '$.serverKeyRestrictions.allowedIps') = 'array';
```

--------------------------------

### List Dataproc Metastore Services with Scaling Configurations

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_dataproc_metastore_service

Retrieve Dataproc Metastore services that have defined scaling configurations. This is useful for monitoring resource allocation and performance tuning. The query filters for services where the scaling_config is not null.

```sql
select
  name,
  scaling_config,
  project,
  location
from
  gcp_dataproc_metastore_service
where
  scaling_config is not null;
```

--------------------------------

### Get GCP App Engine Application Feature Settings (PostgreSQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_app_engine_application

Fetches specific feature settings for GCP App Engine applications, such as 'SplitHealthChecks' and 'UseContainerOptimizedOs'. This allows for granular inspection of application configurations.

```sql
select
  name,
  id,
  location,
  feature_settings -> 'SplitHealthChecks' as split_health_checks,
  feature_settings -> 'UseContainerOptimizedOs' as use_container_optimized_os
from
  gcp_app_engine_application;
```

--------------------------------

### Export GCP Compute Instance Metrics with Exporter CLI

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_metric_cpu_utilization

Demonstrates how to use the Steampipe Exporter CLI to export `gcp_compute_instance_metric_cpu_utilization` data. The `--config` argument allows passing specific configurations for the export process.

```bash
steampipe_export_gcp --config '<your_config>' gcp_compute_instance_metric_cpu_utilization
```

--------------------------------

### Export GCP SQL Database Instance Metrics

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_sql_database_instance_metric_cpu_utilization_hourly

This command demonstrates how to use the Steampipe Exporter CLI to export data for the `gcp_sql_database_instance_metric_cpu_utilization_hourly` table. It requires a configuration argument to be passed, specifying how the exporter should connect to GCP.

```shell
steampipe_export_gcp --config '<your_config>' gcp_sql_database_instance_metric_cpu_utilization_hourly
```

--------------------------------

### Export GCP Vertex AI Notebook Runtime Template Data

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_vertex_ai_notebook_runtime_template

Demonstrates how to use the installed Steampipe GCP Exporter to extract data for the 'gcp_vertex_ai_notebook_runtime_template' table. The command includes an option to pass a configuration file.

```bash
steampipe_export_gcp --config '<your_config>' gcp_vertex_ai_notebook_runtime_template
```

--------------------------------

### Get Basic Log Entry Information

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_logging_log_entry

Retrieves fundamental details of log entries, including their name, insertion ID, operation information, and receive timestamp. This query is useful for a general overview of logged events.

```sql
select
  log_name,
  insert_id,
  log_entry_operation_first,
  log_entry_operation_id,
  receive_timestamp
from
  gcp_logging_log_entry;
```

```sql
select
  log_name,
  insert_id,
  operation_id,
  receive_timestamp
from
  gcp_logging_log_entry;
```

--------------------------------

### Get GCP Project Parent and Organization ID

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_project

Retrieve the parent resource ID, type, and organization ID for all Google Cloud Platform projects. This query aids in understanding the resource hierarchy and project organization.

```sql
select
  project_id,
  parent ->> 'id' as parent_id,
  parent ->> 'type' as parent_type,
  case when jsonb_array_length(ancestors) > 1 then ancestors -> -1 -> 'resourceId' ->> 'id' else null end as organization_id
from
  gcp_project;
```

```sql
select
  project_id,
  parent ->> 'id' as parent_id,
  parent ->> 'type' as parent_type,
  case when json_array_length(ancestors) > 1 then ancestors -> -1 -> 'resourceId' ->> 'id' else null end as organization_id
from
  gcp_project;
```

--------------------------------

### List GCP Resource Policies Used to Schedule an Instance

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_resource_policy

Identifies which resource policies are configured to schedule specific GCP Compute Engine instances. This helps in understanding instance scheduling and resource allocation. It uses a JOIN operation to link policies with instances based on their self-links.

```sql
select
  p.name as policy_name,
  i.name,
  p.instance_schedule_policy
from
  gcp_compute_resource_policy as p
  join gcp_compute_instance as i on i.resource_policies ?| array[p.self_link]
where
  p.instance_schedule_policy is not null;
```

```sql
select
  p.name as policy_name,
  i.name,
  p.instance_schedule_policy
from
  gcp_compute_resource_policy as p
  join gcp_compute_instance as i on json_extract(i.resource_policies, p.self_link) is not null
where
  p.instance_schedule_policy is not null;
```

--------------------------------

### Get Disk Info for Each Snapshot (SQL)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_snapshot

Retrieves detailed information about the source disk associated with each snapshot, including disk name, size, type, and location. Useful for resource management and understanding snapshot composition. Requires GCP Compute Engine snapshot and disk data.

```sql
select
  s.name as snapshot_name,
  d.name as disk_name,
  d.size_gb as disk_size,
  d.type_name as disk_type,
  d.location_type
from
  gcp_compute_snapshot as s
  join gcp_compute_disk as d on s.source_disk = d.self_link;
```

--------------------------------

### Get Basic GCP SSL Policy Information

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_ssl_policy

Retrieves the name, ID, self-link, and minimum TLS version for all GCP SSL policies. This is useful for understanding the current configurations and ensuring adherence to security standards.

```sql
select
  name,
  id,
  self_link,
  min_tls_version
from
  gcp_compute_ssl_policy;
```

--------------------------------

### GCP Instance Count by Availability Zone (Postgres, SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance

Analyze the distribution of GCP compute instances across different availability zones. This helps in understanding load balancing and optimizing resource allocation. It requires the Steampipe plugin for GCP.

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

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

--------------------------------

### Export GCP Backend Service Data with Steampipe Exporter

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_backend_service

Demonstrates how to use the Steampipe Exporter CLI to export data for the 'gcp_compute_backend_service' resource. It shows how to pass configuration options to the command.

```bash
steampipe_export_gcp --config '<your_config>' gcp_compute_backend_service
```

--------------------------------

### Get GCP Compute Router Basic Info

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_router

Retrieves the name, BGP ASN, and BGP advertise mode for GCP compute routers. This information is useful for understanding network behavior and managing routing protocols.

```sql
select
  name,
  bgp_asn,
  bgp_advertise_mode
from
  gcp_compute_router;
```

--------------------------------

### Query All AlloyDB Instances

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_alloydb_instance

Retrieve basic information such as name, state, availability type, and display name for all AlloyDB instances. This query is useful for getting a quick overview of your instance landscape. It utilizes the 'gcp_alloydb_instance' table.

```sql
select
  name,
  state,
  availability_type,
  display_name
from
  gcp_alloydb_instance;
```

```sql
select
  name,
  state,
  availability_type,
  display_name
from
  gcp_alloydb_instance;
```

--------------------------------

### Get Basic Google Cloud Workstation Information

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_workstations_workstation

Retrieve fundamental details of Google Cloud Workstations, including their names, display names, states, host information, creation times, UIDs, locations, and associated projects. This query is useful for an overview of your workstation inventory.

```sql
select
  name,
  display_name,
  state,
  host,
  create_time,
  uid,
  location,
  project
from
  gcp_workstations_workstation;
```

--------------------------------

### List GCP Instance Templates with IP Forwarding Enabled (Postgres/SQLite)

Source: https://hub.steampipe.io/plugins/turbot/gcp/tables/gcp_compute_instance_template

Discover instance templates where IP forwarding is enabled. This feature is useful for managing network traffic routing effectively and securely.

```sql
select
  name,
  id,
  instance_can_ip_forward
from
  gcp_compute_instance_template
where
  instance_can_ip_forward;
```
Tokens:
Raw