steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_account_alternate_contactaws_account_contactaws_acm_certificateaws_amplify_appaws_api_gateway_api_keyaws_api_gateway_authorizeraws_api_gateway_domain_nameaws_api_gateway_methodaws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_routeaws_api_gatewayv2_stageaws_appautoscaling_policyaws_appautoscaling_targetaws_appconfig_applicationaws_appstream_fleetaws_appstream_imageaws_appsync_graphql_apiaws_athena_query_executionaws_athena_workgroupaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_frameworkaws_backup_legal_holdaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_report_planaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudformation_stack_resourceaws_cloudformation_stack_setaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_functionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudfront_response_headers_policyaws_cloudsearch_domainaws_cloudtrail_channelaws_cloudtrail_event_data_storeaws_cloudtrail_importaws_cloudtrail_lookup_eventaws_cloudtrail_queryaws_cloudtrail_trailaws_cloudtrail_trail_eventaws_cloudwatch_alarmaws_cloudwatch_log_eventaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_resource_policyaws_cloudwatch_log_streamaws_cloudwatch_log_subscription_filteraws_cloudwatch_metricaws_cloudwatch_metric_data_pointaws_cloudwatch_metric_statistic_data_pointaws_codeartifact_domainaws_codeartifact_repositoryaws_codebuild_buildaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codedeploy_appaws_codedeploy_deployment_configaws_codedeploy_deployment_groupaws_codepipeline_pipelineaws_cognito_identity_poolaws_cognito_identity_provideraws_cognito_user_poolaws_config_aggregate_authorizationaws_config_configuration_recorderaws_config_conformance_packaws_config_retention_configurationaws_config_ruleaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_record_type_dailyaws_cost_by_record_type_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_by_tagaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dax_parameteraws_dax_parameter_groupaws_dax_subnet_groupaws_directory_service_certificateaws_directory_service_directoryaws_directory_service_log_subscriptionaws_directory_servicelog_subscriptionaws_dlm_lifecycle_policyaws_dms_certificateaws_dms_replication_instanceaws_docdb_clusteraws_docdb_cluster_instanceaws_drs_jobaws_drs_recovery_instanceaws_drs_recovery_snapshotaws_drs_source_serveraws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_dynamodb_tableaws_dynamodb_table_exportaws_ebs_snapshotaws_ebs_volumeaws_ebs_volume_metric_read_opsaws_ebs_volume_metric_read_ops_dailyaws_ebs_volume_metric_read_ops_hourlyaws_ebs_volume_metric_write_opsaws_ebs_volume_metric_write_ops_dailyaws_ebs_volume_metric_write_ops_hourlyaws_ec2_amiaws_ec2_ami_sharedaws_ec2_application_load_balanceraws_ec2_application_load_balancer_metric_request_countaws_ec2_application_load_balancer_metric_request_count_dailyaws_ec2_autoscaling_groupaws_ec2_capacity_reservationaws_ec2_classic_load_balanceraws_ec2_client_vpn_endpointaws_ec2_gateway_load_balanceraws_ec2_instanceaws_ec2_instance_availabilityaws_ec2_instance_metric_cpu_utilizationaws_ec2_instance_metric_cpu_utilization_dailyaws_ec2_instance_metric_cpu_utilization_hourlyaws_ec2_instance_typeaws_ec2_key_pairaws_ec2_launch_configurationaws_ec2_launch_templateaws_ec2_launch_template_versionaws_ec2_load_balancer_listeneraws_ec2_managed_prefix_listaws_ec2_managed_prefix_list_entryaws_ec2_network_interfaceaws_ec2_network_load_balanceraws_ec2_network_load_balancer_metric_net_flow_countaws_ec2_network_load_balancer_metric_net_flow_count_dailyaws_ec2_regional_settingsaws_ec2_reserved_instanceaws_ec2_spot_priceaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_routeaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_imageaws_ecr_image_scan_findingaws_ecr_registry_scanning_configurationaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_cluster_metric_cpu_utilizationaws_ecs_cluster_metric_cpu_utilization_dailyaws_ecs_cluster_metric_cpu_utilization_hourlyaws_ecs_container_instanceaws_ecs_serviceaws_ecs_taskaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_eks_fargate_profileaws_eks_identity_provider_configaws_eks_node_groupaws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_redis_metric_cache_hits_hourlyaws_elasticache_redis_metric_curr_connections_hourlyaws_elasticache_redis_metric_engine_cpu_utilization_dailyaws_elasticache_redis_metric_engine_cpu_utilization_hourlyaws_elasticache_redis_metric_get_type_cmds_hourlyaws_elasticache_redis_metric_list_based_cmds_hourlyaws_elasticache_redis_metric_new_connections_hourlyaws_elasticache_replication_groupaws_elasticache_reserved_cache_nodeaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_block_public_access_configurationaws_emr_clusteraws_emr_cluster_metric_is_idleaws_emr_instanceaws_emr_instance_fleetaws_emr_instance_groupaws_emr_security_configurationaws_eventbridge_busaws_eventbridge_ruleaws_fms_app_listaws_fms_policyaws_fsx_file_systemaws_glacier_vaultaws_globalaccelerator_acceleratoraws_globalaccelerator_endpoint_groupaws_globalaccelerator_listeneraws_glue_catalog_databaseaws_glue_catalog_tableaws_glue_connectionaws_glue_crawleraws_glue_data_catalog_encryption_settingsaws_glue_data_quality_rulesetaws_glue_dev_endpointaws_glue_jobaws_glue_security_configurationaws_guardduty_detectoraws_guardduty_filteraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_memberaws_guardduty_publishing_destinationaws_guardduty_threat_intel_setaws_health_affected_entityaws_health_eventaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_open_id_connect_provideraws_iam_policyaws_iam_policy_attachmentaws_iam_policy_simulatoraws_iam_roleaws_iam_saml_provideraws_iam_server_certificateaws_iam_service_specific_credentialaws_iam_useraws_iam_virtual_mfa_deviceaws_identitystore_groupaws_identitystore_group_membershipaws_identitystore_useraws_inspector2_coverageaws_inspector2_coverage_statisticsaws_inspector2_findingaws_inspector2_memberaws_inspector_assessment_runaws_inspector_assessment_targetaws_inspector_assessment_templateaws_inspector_exclusionaws_inspector_findingaws_iot_thingaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_aliasaws_kms_keyaws_lambda_aliasaws_lambda_event_source_mappingaws_lambda_functionaws_lambda_function_metric_duration_dailyaws_lambda_function_metric_errors_dailyaws_lambda_function_metric_invocations_dailyaws_lambda_layeraws_lambda_layer_versionaws_lambda_versionaws_lightsail_instanceaws_macie2_classification_jobaws_media_store_containeraws_mgn_applicationaws_mq_brokeraws_msk_clusteraws_msk_serverless_clusteraws_neptune_db_clusteraws_neptune_db_cluster_snapshotaws_networkfirewall_firewallaws_networkfirewall_firewall_policyaws_networkfirewall_rule_groupaws_oam_linkaws_oam_sinkaws_opensearch_domainaws_organizations_accountaws_organizations_organizational_unitaws_organizations_policyaws_organizations_policy_targetaws_organizations_rootaws_pinpoint_appaws_pipes_pipeaws_pricing_productaws_pricing_service_attributeaws_ram_principal_associationaws_ram_resource_associationaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_event_subscriptionaws_rds_db_instanceaws_rds_db_instance_automated_backupaws_rds_db_instance_metric_connectionsaws_rds_db_instance_metric_connections_dailyaws_rds_db_instance_metric_connections_hourlyaws_rds_db_instance_metric_cpu_utilizationaws_rds_db_instance_metric_cpu_utilization_dailyaws_rds_db_instance_metric_cpu_utilization_hourlyaws_rds_db_instance_metric_read_iopsaws_rds_db_instance_metric_read_iops_dailyaws_rds_db_instance_metric_read_iops_hourlyaws_rds_db_instance_metric_write_iopsaws_rds_db_instance_metric_write_iops_dailyaws_rds_db_instance_metric_write_iops_hourlyaws_rds_db_option_groupaws_rds_db_parameter_groupaws_rds_db_proxyaws_rds_db_snapshotaws_rds_db_subnet_groupaws_rds_reserved_db_instanceaws_redshift_clusteraws_redshift_cluster_metric_cpu_utilization_dailyaws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_redshiftserverless_namespaceaws_redshiftserverless_workgroupaws_regionaws_resource_explorer_indexaws_resource_explorer_searchaws_resource_explorer_supported_resource_typeaws_route53_domainaws_route53_health_checkaws_route53_query_logaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_query_log_configaws_route53_resolver_ruleaws_route53_traffic_policyaws_route53_traffic_policy_instanceaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_s3_bucket_intelligent_tiering_configurationaws_s3_multi_region_access_pointaws_s3_objectaws_sagemaker_appaws_sagemaker_domainaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_action_targetaws_securityhub_findingaws_securityhub_finding_aggregatoraws_securityhub_hubaws_securityhub_insightaws_securityhub_memberaws_securityhub_productaws_securityhub_standards_controlaws_securityhub_standards_subscriptionaws_securitylake_data_lakeaws_securitylake_subscriberaws_serverlessapplicationrepository_applicationaws_service_discovery_instanceaws_service_discovery_namespaceaws_service_discovery_serviceaws_servicecatalog_portfolioaws_servicecatalog_productaws_servicecatalog_provisioned_productaws_servicequotas_default_service_quotaaws_servicequotas_service_quotaaws_servicequotas_service_quota_change_requestaws_ses_domain_identityaws_ses_email_identityaws_sfn_state_machineaws_sfn_state_machine_executionaws_sfn_state_machine_execution_historyaws_simspaceweaver_simulationaws_sns_subscriptionaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_document_permissionaws_ssm_inventoryaws_ssm_inventory_entryaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_managed_instance_patch_stateaws_ssm_parameteraws_ssm_patch_baselineaws_ssmincidents_response_planaws_ssoadmin_account_assignmentaws_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_sts_caller_identityaws_tagging_resourceaws_transfer_serveraws_trusted_advisor_check_summaryaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_eip_address_transferaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_nat_gateway_metric_bytes_out_to_destinationaws_vpc_network_aclaws_vpc_peering_connectionaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_verified_access_endpointaws_vpc_verified_access_groupaws_vpc_verified_access_instanceaws_vpc_verified_access_trust_provideraws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_waf_rule_groupaws_waf_web_aclaws_wafregional_ruleaws_wafregional_rule_groupaws_wafregional_web_aclaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_answeraws_wellarchitected_check_detailaws_wellarchitected_check_summaryaws_wellarchitected_consolidated_reportaws_wellarchitected_lensaws_wellarchitected_lens_reviewaws_wellarchitected_lens_review_improvementaws_wellarchitected_lens_review_reportaws_wellarchitected_lens_shareaws_wellarchitected_milestoneaws_wellarchitected_notificationaws_wellarchitected_share_invitationaws_wellarchitected_workloadaws_wellarchitected_workload_shareaws_workspaces_directoryaws_workspaces_workspace

Table: aws_backup_framework - Query AWS Backup Frameworks using SQL

The AWS Backup service provides a centralized framework to manage and automate data backup across AWS services. It helps you to meet business and regulatory backup compliance requirements by simplifying the management and reducing the cost of backup operations. AWS Backup offers a cost-effective, fully managed, policy-based backup solution, protecting your data in AWS services.

Table Usage Guide

The aws_backup_framework table in Steampipe provides you with information about each backup framework within AWS Backup service. This table empowers you, as a DevOps engineer, to query backup plan-specific details, including the backup plan's ARN, version, creation date, deletion date, and more. You can utilize this table to gather insights on backup plans, such as their status, associated rules, and other relevant metadata. The schema outlines the various attributes of the backup plan for you, including the backup plan ARN, version, creation and deletion dates, and more.

Examples

Basic info

This query is used to gain insights into the deployment status, creation time, and other details of your AWS backup frameworks. The practical application is to understand the configuration and status of your backup systems for effective management and troubleshooting.

select
account_id,
arn,
creation_time,
deployment_status,
framework_controls,
framework_description,
framework_name,
framework_status,
number_of_controls,
region,
tags
from
aws_backup_framework;
select
account_id,
arn,
creation_time,
deployment_status,
framework_controls,
framework_description,
framework_name,
framework_status,
number_of_controls,
region,
tags
from
aws_backup_framework;

List AWS frameworks created within the last 90 days

Determine the AWS frameworks that have been established within the past three months. This is beneficial for understanding recent changes and additions to your AWS environment, allowing you to stay updated on your current configurations and controls.

select
framework_name,
arn,
creation_time,
number_of_controls
from
aws_backup_framework
where
creation_time >= (current_date - interval '90' day)
order by
creation_time;
select
framework_name,
arn,
creation_time,
number_of_controls
from
aws_backup_framework
where
creation_time >= date('now', '-90 day')
order by
creation_time;

List frameworks that are using a specific control (BACKUP_RESOURCES_PROTECTED_BY_BACKUP_VAULT_LOCK)

Determine the frameworks which are utilizing a specific control for resource protection in a backup vault. This is useful for identifying potential areas of risk or for compliance monitoring.

select
framework_name
from
aws_backup_framework,
jsonb_array_elements(framework_controls) as controls
where
controls ->> 'ControlName' = 'BACKUP_RESOURCES_PROTECTED_BY_BACKUP_VAULT_LOCK';
select
framework_name
from
aws_backup_framework
where
json_extract(framework_controls, '$[*].ControlName') = 'BACKUP_RESOURCES_PROTECTED_BY_BACKUP_VAULT_LOCK';

List control names and scopes for each framework

Determine the areas in which specific control names and scopes are applied within each framework. This is particularly useful for understanding the scope of control within AWS backup frameworks, aiding in effective resource management and compliance. This query will return an empty control scope if the control doesn't apply to a specific AWS resource type. Otherwise, the query will list the control name and the AWS resource type.

select
framework_name,
controls ->> 'ControlName' as control_name,
control_scope
from
aws_backup_framework,
jsonb_array_elements(framework_controls) as controls,
json_array_elements_text(
coalesce(
controls -> 'ControlScope' ->> 'ComplianceResourceTypes',
'[""]'
) :: json
) as control_scope
where
framework_name = 'framework_name';
select
framework_name,
json_extract(controls.value, '$.ControlName') as control_name,
control_scope.value as control_scope
from
aws_backup_framework,
json_each(framework_controls) as controls,
json_each(
json(
coalesce(
json_extract(
controls.value,
'$.ControlScope.ComplianceResourceTypes'
),
'[""]'
)
)
) as control_scope
where
framework_name = 'framework_name';

List framework controls that have non-compliant resources

Determine the areas in which framework controls are not compliant with the rules. This can be useful for identifying and rectifying non-compliant resources to ensure adherence to organizational policies and standards.

select
rule_name,
compliance_result -> 'Compliance' ->> 'ComplianceType' as compliance_type,
compliance_result -> 'Compliance' -> 'ComplianceContributorCount' ->> 'CappedCount' as count_of_noncompliant_resources
from
aws_config_rule
inner join (
-- The sub-query will create the AWS Config rule name from information stored in the AWS Backup framework table.
select
case
when framework_information.control_scope = '' then concat(
framework_information.control_name,
'-',
framework_information.framework_uuid
)
else concat(
upper(framework_information.control_scope),
'-',
framework_information.control_name,
'-',
framework_information.framework_uuid
)
end as rule_name
from
(
select
framework_name,
controls ->> 'ControlName' as control_name,
control_scope,
right(arn, 36) as framework_uuid
from
aws_backup_framework,
jsonb_array_elements(framework_controls) as controls,
json_array_elements_text(
coalesce(
controls -> 'ControlScope' ->> 'ComplianceResourceTypes',
'[""]'
) :: json
) as control_scope
) as framework_information
) as backup_framework on aws_config_rule.name = backup_framework.rule_name,
jsonb_array_elements(compliance_by_config_rule) as compliance_result
where
compliance_result -> 'Compliance' ->> 'ComplianceType' = 'NON_COMPLIANT';
select
rule_name,
json_extract(compliance_result, '$.Compliance.ComplianceType') as compliance_type,
json_extract(
compliance_result,
'$.Compliance.ComplianceContributorCount.CappedCount'
) as count_of_noncompliant_resources
from
aws_config_rule
join (
-- The sub-query will create the AWS Config rule name from information stored in the AWS Backup framework table.
select
case
when control_scope = '' then control_name || '-' || framework_uuid
else upper(control_scope) || '-' || control_name || '-' || framework_uuid
end as rule_name
from
(
select
framework_name,
json_extract(controls, '$.ControlName') as control_name,
control_scope,
substr(arn, -36) as framework_uuid
from
aws_backup_framework,
json_each(framework_controls) as controls,
json_each(
coalesce(
json_extract(
controls,
'$.

List framework controls that are compliant

Identify the compliant framework controls within your AWS Config rules. This allows you to gain insights into your compliance status and helps in maintaining adherence to regulatory standards.

select
rule_name,
compliance_result -> 'Compliance' ->> 'ComplianceType' as compliance_type
from
aws_config_rule
inner join (
-- The sub-query will create the AWS Config rule name from information stored in the AWS Backup framework table.
select
case
when framework_information.control_scope = '' then concat(
framework_information.control_name,
'-',
framework_information.framework_uuid
)
else concat(
upper(framework_information.control_scope),
'-',
framework_information.control_name,
'-',
framework_information.framework_uuid
)
end as rule_name
from
(
select
framework_name,
controls ->> 'ControlName' as control_name,
control_scope,
right(arn, 36) as framework_uuid
from
aws_backup_framework,
jsonb_array_elements(framework_controls) as controls,
json_array_elements_text(
coalesce(
controls -> 'ControlScope' ->> 'ComplianceResourceTypes',
'[""]'
) :: json
) as control_scope
) as framework_information
) as backup_framework on aws_config_rule.name = backup_framework.rule_name,
jsonb_array_elements(compliance_by_config_rule) as compliance_result
where
compliance_result -> 'Compliance' ->> 'ComplianceType' = 'COMPLIANT';
select
rule_name,
json_extract(compliance_result, '$.Compliance.ComplianceType') as compliance_type
from
aws_config_rule
inner join (
-- The sub-query will create the AWS Config rule name from information stored in the AWS Backup framework table.
select
case
when framework_information.control_scope = '' then framework_information.control_name || '-' || framework_information.framework_uuid
else upper(framework_information.control_scope) || '-' || framework_information.control_name || '-' || framework_information.framework_uuid
end as rule_name
from
(
select
framework_name,
json_extract(controls, '$.ControlName') as control_name,
control_scope,
substr(arn, -36) as framework_uuid
from
aws_backup_framework,
json_each(framework_controls) as controls,
json_each(
coalesce(
json_extract(
controls,
'$.ControlScope.ComplianceResourceTypes'
),
'[""]'
)
) as control_scope

Schema for aws_backup_framework

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextAn Amazon Resource Name (ARN) that uniquely identifies a backup framework resource.
creation_timetimestamp with time zoneThe date and time that a framework was created.
deployment_statustextThe deployment status of a backup framework.
framework_controlsjsonbA list of the controls that make up the framework. Each control in the list has a name, input parameters, and scope.
framework_descriptiontextAn optional description of the backup framework.
framework_nametext=The unique name of a backup framework.
framework_statustextThe framework status based on recording statuses for resources governed by the framework (ACTIVE | PARTIALLY_ACTIVE | INACTIVE | UNAVAILABLE).
number_of_controlsbigintThe number of controls contained by the framework.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
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)" -- aws

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

steampipe_export_aws --config '<your_config>' aws_backup_framework