turbot/alicloud
steampipe plugin install alicloud

Table: alicloud_rds_instance - Query Alibaba Cloud RDS Instances using SQL

Alibaba Cloud Relational Database Service (RDS) is a stable and reliable online database service that supports MySQL, SQL Server, PostgreSQL, and PPAS. RDS handles routine database tasks such as database backup, patch upgrades, and failure detection and recovery. It provides automatic monitoring, backup, and disaster recovery capabilities, freeing up developers to focus on their applications rather than managing databases.

Table Usage Guide

The alicloud_rds_instance table provides insights into RDS instances within Alibaba Cloud Relational Database Service (RDS). As a database administrator, explore instance-specific details through this table, including the instance's ID, creation time, status, and associated metadata. Utilize it to uncover information about instances, such as their storage and memory usage, the network type they are using, and their security settings.

Examples

Basic info

Discover the segments that provide insights into the instances of database services, including when they were created and the type of engine used. This information can be useful in assessing the overall setup of your cloud services and ensuring they align with your operational requirements.

select
db_instance_id,
arn,
vpc_id,
creation_time,
engine
from
alicloud_rds_instance;
select
db_instance_id,
arn,
vpc_id,
creation_time,
engine
from
alicloud_rds_instance;

DB instance count in each region

Determine the distribution of database instances across different regions to better understand resource allocation and usage patterns.

select
region_id as region,
db_instance_class,
count(*)
from
alicloud_rds_instance
group by
region_id,
db_instance_class;
select
region_id as region,
db_instance_class,
count(*)
from
alicloud_rds_instance
group by
region_id,
db_instance_class;

List DB instances whose engine is MySQL

Explore which database instances are running on the MySQL engine. This can be useful for assessing your infrastructure's dependencies on this particular database engine.

select
db_instance_id,
vpc_id,
creation_time,
engine
from
alicloud_rds_instance
where
engine = 'MySQL';
select
db_instance_id,
vpc_id,
creation_time,
engine
from
alicloud_rds_instance
where
engine = 'MySQL';

List DB instances that are currently running

Identify instances where your database is currently active. This is useful for monitoring system performance and ensuring resources are not being wasted on idle databases.

select
db_instance_id,
vpc_id,
creation_time,
engine
from
alicloud_rds_instance
where
db_instance_status = 'Running';
select
db_instance_id,
vpc_id,
creation_time,
engine
from
alicloud_rds_instance
where
db_instance_status = 'Running';

List DB instances that allow 0.0.0.0/0

Determine the areas in which database instances are set to allow all IP addresses. This query is useful in identifying potential security risks, as allowing all IP addresses may expose your database to unwanted access.

select
db_instance_id,
security_ips
from
alicloud_rds_instance
where
security_ips :: jsonb ? '0.0.0.0/0';
Error: SQLite does not support CIDR operations.

List DB instances with SSL encryption disabled

Identify instances where the SSL encryption is disabled on your database, allowing you to pinpoint potential security vulnerabilities and improve your database's protection against unauthorized access.

select
db_instance_id,
vpc_id,
creation_time,
engine,
ssl_encryption
from
alicloud_rds_instance
where
ssl_status = 'Disabled';
select
db_instance_id,
vpc_id,
creation_time,
engine,
ssl_encryption
from
alicloud_rds_instance
where
ssl_status = 'Disabled';

List DB instances with TDE disabled

Identify instances where the Transparent Data Encryption (TDE) feature is disabled within your database instances. This can be useful for enhancing data security by pinpointing potential vulnerabilities and areas that need attention.

select
db_instance_id,
vpc_id,
creation_time,
engine,
tde_status
from
alicloud_rds_instance
where
tde_status = 'Disabled';
select
db_instance_id,
vpc_id,
creation_time,
engine,
tde_status
from
alicloud_rds_instance
where
tde_status = 'Disabled';

Get security group configuration details for instances

Explore the security configurations of your database instances to understand their network types and geographical regions. This can help in assessing your security measures and ensuring they align with your organization's standards and requirements.

select
i.db_instance_id,
s ->> 'NetworkType' as network_type,
s ->> 'RegionId' as security_group_region_id,
s ->> 'SecurityGroupId' as security_group_id
from
alicloud_rds_instance as i,
jsonb_array_elements(security_group_configuration) as s;
select
i.db_instance_id,
json_extract(s.value, '$.NetworkType') as network_type,
json_extract(s.value, '$.RegionId') as security_group_region_id,
json_extract(s.value, '$.SecurityGroupId') as security_group_id
from
alicloud_rds_instance as i,
json_each(security_group_configuration) as s;

Get encryption details for all the instances

Identify the encryption details for all instances to enhance your understanding of your database security. This is useful for auditing your security measures and ensuring necessary precautions are in place.

select
i.arn as instance_arn,
i.title as instance_name,
encryption_key,
k.title as kms_key_name
from
alicloud_rds_instance i
left join alicloud_kms_key k on encryption_key = key_id;
select
i.arn as instance_arn,
i.title as instance_name,
encryption_key,
k.title as kms_key_name
from
alicloud_rds_instance i
left join alicloud_kms_key k on encryption_key = k.key_id;

Schema for alicloud_rds_instance

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The Alicloud Account ID in which the resource is located.
account_max_quantitybigintThe maximum number of accounts that can be created on the instance.
account_typetext
advanced_featurestextAn array that consists of advanced features. The advanced features are separated by commas (,). This parameter is supported only for instances that run SQL Server.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Alibaba Cloud Resource Name (ARN) of the RDS instance.
auto_upgrade_minor_versiontextThe method that is used to update the minor engine version of the instance.
availability_valuetextThe availability status of the instance. Unit: %.
categorytextThe RDS edition of the instance.
collationtextThe character set collation of the instance.
connection_modetextThe connection mode of the instances.
connection_stringtextThe internal endpoint of the instance.
console_versiontextThe type of proxy that is enabled on the instance.
creation_timetimestamp with time zoneThe creation time of the Instance.
db_instance_classtextThe instance type of the instances.
db_instance_cputextThe number of CPUs that are configured for the instance.
db_instance_descriptiontextThe description of the DB Instance.
db_instance_disk_usedtext
db_instance_idtext=The ID of the single instance to query.
db_instance_memorydouble precisionThe memory capacity of the instance. Unit: MB.
db_instance_net_typetextThe ID of the resource group to which the VPC belongs.
db_instance_statustextThe status of the instances
db_instance_storagebigintThe type of storage media that is used by the instance.
db_instance_storage_typetextThe type of storage media that is used by the instance.
db_instance_typetextThe role of the instances.
db_max_quantitybigintThe maximum number of databases that can be created on the instance.
dedicated_host_group_idtextThe ID of the dedicated cluster to which the instances belong if the instances are created in a dedicated cluster.
dispense_modetext
encryption_keytextThe custom encryption key for the instance.
enginetextThe database engine that the instances run.
engine_versiontextThe version of the database engine that the instances run.
expire_timetimestamp with time zoneInstance expire time
guard_db_instance_idtextThe ID of the disaster recovery instance that is attached to the instance if a disaster recovery instance is deployed.
increment_source_db_instance_idtextThe ID of the instance from which incremental data comes. The incremental data of a disaster recovery or read-only instance comes from its primary instance. If this parameter is not returned, the instance is a primary instance.
ins_idbigint
instance_network_typetextThe network type of the instances.
ip_typetext
latest_kernel_versiontext
lock_modetextThe lock mode of the instance.
lock_reasontextThe reason why the instance is locked.
maintain_timetextThe maintenance window of the instance. The maintenance window is displayed in UTC+8 in the ApsaraDB RDS console.
master_instance_idtextThe ID of the primary instance to which the instance is attached. If this parameter is not returned, the instance is a primary instance.
max_connectionsbigintThe maximum number of concurrent connections that are allowed by the instance.
max_iopsbigintThe maximum number of I/O requests that the instance can process per second.
multiple_temp_upgradeboolean
origin_configurationtext
parametersjsonbThe list of running parameters for the instance.
pay_typetextThe billing method of the instances.
porttextThe internal port of the instance.
proxy_typebigintThe type of proxy that is enabled on the instance.
readonly_db_instance_idsjsonbAn array that consists of the IDs of the read-only instances attached to the primary instance.
regiontextThe Alicloud region in which the resource is located.
region_idtextThe ID of the region to which the instances belong.
resource_group_idtextThe ID of the resource group to which the instances belong.
security_group_configurationjsonbECS security groups that are bound to an ApsaraDB for the instance.
security_ip_modetextThe network isolation mode of the instance.
security_ipsjsonbAn array that consists of IP addresses in the IP address whitelist.
security_ips_srcjsonbAn array that consists of IP details.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
sql_collector_policyjsonbThe status of the SQL Explorer (SQL Audit) feature.
sql_collector_retentionbigintThe log backup retention duration that is allowed by the SQL explorer feature on the instance.
ssl_statustextThe SSL encryption status of the Instance
super_permission_modetextIndicates whether the instance supports superuser accounts, such as the system administrator (SA) account, Active Directory (AD) account, and host account.
support_create_super_accounttext
support_upgrade_account_typetext
tagsjsonbA map of tags for the resource.
tags_srcjsonbA map of tags for the resource.
tde_statustextThe TDE status at the instance level. Valid values: Enable | Disable.
temp_db_instance_idtextThe ID of the temporary instance that is attached to the instance if a temporary instance is deployed.
temp_upgrade_recovery_classtext
temp_upgrade_recovery_cpubigint
temp_upgrade_recovery_max_connectionstext
temp_upgrade_recovery_max_iopstext
temp_upgrade_recovery_memorybigint
temp_upgrade_recovery_timetext
temp_upgrade_time_endtext
temp_upgrade_time_starttext
time_zonetextThe time zone of the instance.
titletextTitle of the resource.
vpc_cloud_instance_idtextThe ID of the cloud instance on which the specified VPC is deployed.
vpc_idtextThe ID of the VPC to which the instances belong.
vswitch_idtextThe ID of the vSwitch associated with the specified VPC.
zone_idtextThe ID of the zone to which the instances belong.

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)" -- alicloud

You can pass the configuration to the command with the --config argument:

steampipe_export_alicloud --config '<your_config>' alicloud_rds_instance