steampipe plugin install gcp

Table: gcp_sql_database_instance - Query Google Cloud SQL Database Instances using SQL

Google Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform. It provides a cloud-based environment for running MySQL, PostgreSQL, and SQL Server databases. Google Cloud SQL offers high performance, scalability, and convenience for application developers.

Table Usage Guide

The gcp_sql_database_instance table provides insights into the configuration and performance of Google Cloud SQL Database Instances. As a database administrator or developer, you can use this table to explore details about each instance, including its configuration, status, and performance metrics. This can help you optimize database performance, ensure proper configuration, and monitor the overall health of your databases.

Examples

Basic info

Explore which Google Cloud Platform SQL databases are currently active, their versions, and locations. This can help in understanding the distribution and usage of different databases across various regions.

select
name,
state,
instance_type,
database_version,
machine_type,
location
from
gcp_sql_database_instance;
select
name,
state,
instance_type,
database_version,
machine_type,
location
from
gcp_sql_database_instance;

List of users in the specified Cloud SQL instance.

Assess the elements within a specific Cloud SQL instance to identify all associated users. This is beneficial in managing access control and maintaining security protocols.

select
name,
instance_users
from
gcp_sql_database_instance
where
name = 'my-sql-instance';
select
name,
instance_users
from
gcp_sql_database_instance
where
name = 'my-sql-instance';

List of replica databases and their master instances

Discover the segments that utilize replica databases by identifying their corresponding master instances. This can be beneficial in understanding the structure and distribution of your database system, particularly in scenarios where redundancy or load balancing is a key concern.

select
name,
master_instance_name,
replication_type,
gce_zone as replica_database_zone
from
gcp_sql_database_instance
where
database_replication_enabled;
select
name,
master_instance_name,
replication_type,
gce_zone as replica_database_zone
from
gcp_sql_database_instance
where
database_replication_enabled = 1;

List of assigned IP addresses to the database instances

Explore which IP addresses have been assigned to your database instances. This can help you maintain a secure network and monitor potential unauthorized access.

select
name,
ip ->> 'ipAddress' as ip_address,
ip ->> 'type' as type
from
gcp_sql_database_instance,
jsonb_array_elements(ip_addresses) as ip;
select
name,
json_extract(ip.value, '$.ipAddress') as ip_address,
json_extract(ip.value, '$.type') as type
from
gcp_sql_database_instance,
json_each(ip_addresses) as ip;

List of external networks that can connect to the database instance

Explore which external networks have access to your database instance. This is useful to maintain security by ensuring only authorized networks can connect.

select
name as instance_name,
i ->> 'name' as authorized_network_name,
i ->> 'value' as authorized_network_value,
ip_configuration ->> 'ipv4Enabled' as ipv4_enabled
from
gcp_sql_database_instance,
jsonb_array_elements(ip_configuration -> 'authorizedNetworks') as i;
select
name as instance_name,
json_extract(i.value, '$.name') as authorized_network_name,
json_extract(i.value, '$.value') as authorized_network_value,
json_extract(ip_configuration, '$.ipv4Enabled') as ipv4_enabled
from
gcp_sql_database_instance,
json_each(
json_extract(ip_configuration, '$.authorizedNetworks')
) as i;

List of database instances without application tag key

Identify instances where database instances lack an application tag key. This is useful in understanding and rectifying configurations that are missing vital tagging, thereby improving resource management and organization.

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

Count of database instances per location

Explore which locations have the highest number of database instances. This can help in understanding the distribution of resources and potentially identifying areas for infrastructure optimization.

select
location,
count(*) instance_count
from
gcp_sql_database_instance
group by
location;
select
location,
count(*) instance_count
from
gcp_sql_database_instance
group by
location;

Control examples

Schema for gcp_sql_database_instance

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
activation_policytextDescribes the activation policy specifies when the instance is activated.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
authorized_gae_applicationsjsonbA list of App Engine app IDs, that can access this instance.
availability_typetextSpecifies the availability type of the instance. This field is used only for PostgreSQL and MySQL instances.
backend_typetext!=, =Specifies the backend type. Possible values are: FIRST_GEN, SECOND_GEN, EXTERNAL, and SQL_BACKEND_TYPE_UNSPECIFIED.
backup_enabledbooleanIndicates whether backup configuration is enabled, or not.
backup_locationtextSpecifies the backup location.
backup_replication_log_archiving_enabledbooleanIndicates whether backup replication log archiving is enabled, or not.
backup_start_timetextSpecifies the start time for the daily backup configuration.
binary_log_enabledbooleanIndicates whether binary log is enabled, or not.
can_defer_maintenancebooleanIndicates whether the scheduled maintenance can be deferred, or not.
can_reschedule_maintenancebooleanIndicates whether the scheduled maintenance can be rescheduled, or not.
connection_nametextSpecifies the connection name of the Cloud SQL instance used in connection strings.
crash_safe_replication_enabledbooleanSpecifies whether the database flags for crash-safe replication are enabled, or not.
create_timetimestamp with time zoneThe time when the instance was created.
current_disk_sizebigintSpecifies the current disk usage of the instance in bytes.
data_disk_size_gbbigintSpecifies the size of the data disk, in GB. Minimum size is 10GB. Not used for First Generation instances.
data_disk_typetextSpecifies the type of the data disk used for this instance.
database_flagsjsonbA list of database flags passed to the instance at startup.
database_replication_enabledbooleanSpecifies whether the replication of database is enabled, or not.
database_versiontext!=, =Specifies the type and version of the database engine.
enable_point_in_time_recoverybooleanAllows user to recover data from a specific point in time, down to a fraction of a second.
failover_replica_availablebooleanThe availability status of the failover replica. A false status indicates that the failover replica is out of sync.
failover_replica_nametextThe name of the failover replica. If specified at instance creation, a failover replica is created for the instance.
gce_zonetext!=, =Specifies the Compute Engine zone that the instance is currently serving from.
instance_typetext!=, =Specifies the type of the instance.
instance_usersjsonbA list of users in the specified Cloud SQL instance.
ip_addressesjsonbA list of assigned IP addresses for the instance.
ip_configurationjsonbDescribes the settings for IP management. It allows to enable or disable the instance IP and manage which external networks can connect to the instance.
ipv6_addressinetSpecifies the IPv6 address assigned to the instance. This property is applicable only to First Generation instances.
kindtextThe type of the resource.
kms_key_nametextSpecifies the resource name of KMS key used for disk encryption.
kms_key_version_nametextSpecifies the KMS key version used to encrypt the Cloud SQL instance.
labelsjsonbA label is a key-value pair that helps you organize your Google Cloud instances. You can attach a label to each resource, then filter the resources based on their labels.
locationtextThe GCP multi-region, region, or zone in which the resource is located.
location_preferencejsonbDescribes the location preference settings. This allows the instance to be located as near as possible to either an App Engine app or Compute Engine zone for better performance.
machine_typetextSpecifies the tier or machine type for this instance.
maintenance_start_timetimestamp with time zoneThe start time of any upcoming scheduled maintenance for this instance.
maintenance_windowjsonbDescribes the maintenance window for this instance.
master_instance_nametextSpecifies the name of the instance which will act as master in the replication setup.
max_disk_sizebigintSpecifies the maximum disk size of the instance in bytes.
nametext=A friendly name that identifies the resource.
on_premises_configurationjsonbDescribes the configurations specific to on-premises instances.
pricing_plantextSpecifies the pricing plan for this instance.
projecttext=, !=, ~~, ~~*, !~~, !~~*The GCP Project in which the resource is located.
replica_namesjsonbA list of replicas of the instance.
replication_configurationjsonbDescribes the configurations specific to failover replicas and read replicas.
replication_typetextSpecifies the type of replication this instance uses.
self_linktextThe server-defined URL for the resource.
service_account_email_addresstextThe service account email address assigned to the instance. This property is applicable only to Second Generation instances.
settings_versionbigintSpecifies the version of instance settings.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
ssl_configurationjsonbDescribes the SSL configuration of the instance.
statetext!=, =Specifies the current serving state of the Cloud SQL instance.
storage_auto_resizebooleanSpecifies whether the configuration for automatic increment of the the storage size is enabled, or not.
storage_auto_resize_limitbigintSpecifies the maximum size to which storage capacity can be automatically increased.
suspension_reasonjsonbA list of reasons for the suspension, if the instance state is SUSPENDED.
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_sql_database_instance