Table: aws_rds_db_snapshot - Query Amazon RDS DB Snapshots using SQL

The AWS RDS DB Snapshot is a feature of Amazon RDS that enables you to create backups of your database instances. These snapshots are point-in-time copies of your databases that can be used for disaster recovery, database migration, and improving backup compliance. The DB Snapshot captures the entire DB instance and not just individual databases, ensuring a consistent snapshot of all your databases at a specific time.

Table Usage Guide

The aws_rds_db_snapshot table in Steampipe provides you with information about manual and automatic snapshots of an Amazon RDS DB instance. This table allows you as a DevOps engineer to query snapshot-specific details, such as snapshot type, creation time, allocated storage, and associated metadata. You can utilize this table to gather insights into snapshot details, including whether a snapshot is shared, public, or encrypted, its engine version, and more. The schema outlines the various attributes of the DB snapshot for you, including the snapshot ARN, DB instance identifier, snapshot status, and associated tags.

Examples

DB snapshot basic info

Explore which database snapshots in your AWS RDS service are not encrypted. This can help you identify potential security risks and ensure compliance with encryption policies.

select
  db_snapshot_identifier,
  encrypted
from
  aws_rds_db_snapshot
where
  not encrypted;

select
  db_snapshot_identifier,
  encrypted
from
  aws_rds_db_snapshot
where
  encrypted = 0;

List of all manual DB snapshots

Discover the segments that consist of all manually created database snapshots, which can help in tracking and managing your backups effectively. This is particularly useful in scenarios where you want to ensure that all your important data is being manually backed up as per your organization's policies.

select
  db_snapshot_identifier,
  type
from
  aws_rds_db_snapshot
where
  type = 'manual';

select
  db_snapshot_identifier,
  type
from
  aws_rds_db_snapshot
where
  type = 'manual';

List of snapshots which are not encrypted

Determine the areas in which your AWS RDS database snapshots are lacking encryption. This is useful for identifying potential security vulnerabilities and ensuring compliance with data protection policies.

select
  db_snapshot_identifier,
  encrypted
from
  aws_rds_db_snapshot
where
  not encrypted;

select
  db_snapshot_identifier,
  encrypted
from
  aws_rds_db_snapshot
where
  encrypted = 0;

DB instance info of each db snapshot

Determine the areas in which specific database snapshots are associated with their respective database instances. This query can be beneficial for understanding the storage and engine details of each snapshot, helping in efficient resource management and optimization.

select
  db_snapshot_identifier,
  db_instance_identifier,
  engine,
  engine_version,
  allocated_storage,
  storage_type
from
  aws_rds_db_snapshot;

select
  db_snapshot_identifier,
  db_instance_identifier,
  engine,
  engine_version,
  allocated_storage,
  storage_type
from
  aws_rds_db_snapshot;

Query examples

Control examples

Schema for aws_rds_db_snapshot

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account_id	text	=, !=, ~~, ~~, !~~, !~~	The AWS Account ID in which the resource is located.
akas	jsonb		Array of globally unique identifier strings (also known as) for the resource.
allocated_storage	bigint		Specifies the allocated storage size in gibibytes(GiB).
arn	text		The Amazon Resource Name (ARN) for the DB snapshot.
availability_zone	text		Specifies the name of the Availability Zone the DB instance was located in, at the time of the DB snapshot.
create_time	timestamp with time zone		Specifies when the snapshot was taken.
db_instance_identifier	text	=	Specifies the DB instance identifier of the DB instance this DB snapshot was created from.
db_snapshot_attributes	jsonb		A list of DB snapshot attribute names and values for a manual DB snapshot.
db_snapshot_identifier	text	=	The friendly name to identify the DB snapshot.
db_system_id	text		The Oracle system identifier (SID), which is the name of the Oracle database instance that manages your database files. The Oracle SID is also the name of your CDB.
dbi_resource_id	text	=	The identifier for the source DB instance, which can't be changed and which is unique to an AWS Region.
dedicated_log_volume	boolean		Indicates whether the DB instance has a dedicated log volume (DLV) enabled.
encrypted	boolean		Specifies whether the DB snapshot is encrypted, or not.
engine	text	=	Specifies the name of the database engine.
engine_version	text		Specifies the version of the database engine.
iam_database_authentication_enabled	boolean		Specifies whether the mapping of AWS IAM accounts to database accounts is enabled, or not.
instance_create_time	timestamp with time zone		Specifies the time when the DB instance, from which the snapshot was taken, was created.
iops	bigint		Specifies the Provisioned IOPS (I/O operations per second) value of the DB instance at the time of the snapshot.
kms_key_id	text		Specifies the AWS KMS key identifier for the encrypted DB snapshot.
license_model	text		Specifies the License model information for the restored DB instance.
master_user_name	text		Provides the master username for the DB snapshot.
multi_tenant	boolean		Indicates whether the snapshot is of a DB instance using the multi-tenant configuration (TRUE) or the single-tenant configuration (FALSE).
option_group_name	text		Provides the option group name for the DB snapshot.
partition	text		The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
percent_progress	bigint		The percentage of the estimated data that has been transferred.
port	bigint		Specifies the port that the database engine was listening on at the time of the snapshot.
processor_features	jsonb		The number of CPU cores and the number of threads per core for the DB instance class of the DB instance when the DB snapshot was created.
region	text		The AWS Region in which the resource is located.
snapshot_database_time	timestamp with time zone		The timestamp of the most recent transaction applied to the database that you're backing up.
snapshot_target	text		Specifies where manual snapshots are stored: Amazon Web Services Outposts or the Amazon Web Services Region.
source_db_snapshot_identifier	text		The DB snapshot ARN that the DB snapshot was copied from.
source_region	text		The AWS Region that the DB snapshot was created in or copied from.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
status	text		Specifies the status of this DB snapshot.
storage_type	text		Specifies the storage type associated with DB snapshot.
tags	jsonb		A map of tags for the resource.
tags_src	jsonb		A list of tags attached to the DB snapshot.
tde_credential_arn	text		The ARN from the key store with which to associate the instance for TDE encryption.
timezone	text		The time zone of the DB snapshot.
title	text		Title of the resource.
type	text	=	Provides the type of the DB snapshot.
vpc_id	text		Provides the VPC ID associated with the DB snapshot.

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

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

steampipe_export_aws --config '<your_config>' aws_rds_db_snapshot