Table: aws_sns_topic_subscription - Query AWS Simple Notification Service (SNS) Topic Subscriptions using SQL
The AWS Simple Notification Service (SNS) Topic Subscriptions allow you to manage and handle messages published to topics. Subscriptions define the endpoints to which messages will be delivered, allowing for the decoupling of microservices, distributed systems, and serverless applications. AWS SNS Topic Subscriptions support a variety of protocols including HTTP, HTTPS, Email, Email-JSON, SQS, Application, Lambda, and SMS.
Table Usage Guide
The aws_sns_topic_subscription
table in Steampipe provides you with information about topic subscriptions within AWS Simple Notification Service (SNS). This table allows you, as a DevOps engineer, to query subscription-specific details, including subscription ARN, owner, protocol, endpoint, and more. You can utilize this table to gather insights on subscriptions, such as subscription status, delivery policy, raw message delivery, and more. The schema outlines the various attributes of the SNS topic subscription for you, including the subscription ARN, topic ARN, owner, protocol, and associated tags.
Examples
List of subscriptions which are not configured with dead letter queue
Determine the areas in which AWS SNS Topic subscriptions lack a configured dead letter queue. This is useful for identifying potential points of failure in message delivery, as messages could be lost if the subscription service is unavailable and there is no dead letter queue set up.
select title, redrive_policyfrom aws_sns_topic_subscriptionwhere redrive_policy is null;
select title, redrive_policyfrom aws_sns_topic_subscriptionwhere redrive_policy is null;
List of subscriptions which are not configured to filter messages
Determine the areas in which subscriptions are not set up to filter messages. This is beneficial for identifying potential inefficiencies or areas of improvement within your notification system.
select title, filter_policyfrom aws_sns_topic_subscriptionwhere filter_policy is null;
select title, filter_policyfrom aws_sns_topic_subscriptionwhere filter_policy is null;
Subscription count by topic arn
Determine the areas in which your AWS SNS topics are gaining the most traction by analyzing the number of subscriptions each topic has. This can help prioritize content creation and resource allocation for popular topics.
select title, count(subscription_arn) as subscription_countfrom aws_sns_topic_subscriptiongroup by title;
select title, count(subscription_arn) as subscription_countfrom aws_sns_topic_subscriptiongroup by title;
Query examples
Control examples
- All Controls > CloudWatch > Ensure AWS Organizations changes are monitored
- CIS v1.2.0 > 3 Monitoring > 3.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- CIS v1.2.0 > 3 Monitoring > 3.10 Ensure a log metric filter and alarm exist for security group changes
- CIS v1.2.0 > 3 Monitoring > 3.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- CIS v1.2.0 > 3 Monitoring > 3.12 Ensure a log metric filter and alarm exist for changes to network gateways
- CIS v1.2.0 > 3 Monitoring > 3.13 Ensure a log metric filter and alarm exist for route table changes
- CIS v1.2.0 > 3 Monitoring > 3.14 Ensure a log metric filter and alarm exist for VPC changes
- CIS v1.2.0 > 3 Monitoring > 3.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- CIS v1.2.0 > 3 Monitoring > 3.3 Ensure a log metric filter and alarm exist for usage of "root" account
- CIS v1.2.0 > 3 Monitoring > 3.4 Ensure a log metric filter and alarm exist for IAM policy changes
- CIS v1.2.0 > 3 Monitoring > 3.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- CIS v1.2.0 > 3 Monitoring > 3.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- CIS v1.2.0 > 3 Monitoring > 3.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- CIS v1.2.0 > 3 Monitoring > 3.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- CIS v1.2.0 > 3 Monitoring > 3.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- CIS v1.3.0 > 4 Monitoring > 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- CIS v1.3.0 > 4 Monitoring > 4.10 Ensure a log metric filter and alarm exist for security group changes
- CIS v1.3.0 > 4 Monitoring > 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- CIS v1.3.0 > 4 Monitoring > 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- CIS v1.3.0 > 4 Monitoring > 4.13 Ensure a log metric filter and alarm exist for route table changes
- CIS v1.3.0 > 4 Monitoring > 4.14 Ensure a log metric filter and alarm exist for VPC changes
- CIS v1.3.0 > 4 Monitoring > 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- CIS v1.3.0 > 4 Monitoring > 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- CIS v1.3.0 > 4 Monitoring > 4.3 Ensure a log metric filter and alarm exist for usage of "root" account
- CIS v1.3.0 > 4 Monitoring > 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- CIS v1.3.0 > 4 Monitoring > 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- CIS v1.3.0 > 4 Monitoring > 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- CIS v1.3.0 > 4 Monitoring > 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- CIS v1.3.0 > 4 Monitoring > 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- CIS v1.3.0 > 4 Monitoring > 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- CIS v1.4.0 > 4 Monitoring > 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- CIS v1.4.0 > 4 Monitoring > 4.10 Ensure a log metric filter and alarm exist for security group changes
- CIS v1.4.0 > 4 Monitoring > 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- CIS v1.4.0 > 4 Monitoring > 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- CIS v1.4.0 > 4 Monitoring > 4.13 Ensure a log metric filter and alarm exist for route table changes
- CIS v1.4.0 > 4 Monitoring > 4.14 Ensure a log metric filter and alarm exist for VPC changes
- CIS v1.4.0 > 4 Monitoring > 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- CIS v1.4.0 > 4 Monitoring > 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- CIS v1.4.0 > 4 Monitoring > 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- CIS v1.4.0 > 4 Monitoring > 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- CIS v1.4.0 > 4 Monitoring > 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- CIS v1.4.0 > 4 Monitoring > 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- CIS v1.4.0 > 4 Monitoring > 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- CIS v1.4.0 > 4 Monitoring > 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- CIS v1.4.0 > 4 Monitoring > 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- CIS v1.5.0 > 4 Monitoring > 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- CIS v1.5.0 > 4 Monitoring > 4.10 Ensure a log metric filter and alarm exist for security group changes
- CIS v1.5.0 > 4 Monitoring > 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- CIS v1.5.0 > 4 Monitoring > 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- CIS v1.5.0 > 4 Monitoring > 4.13 Ensure a log metric filter and alarm exist for route table changes
- CIS v1.5.0 > 4 Monitoring > 4.14 Ensure a log metric filter and alarm exist for VPC changes
- CIS v1.5.0 > 4 Monitoring > 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- CIS v1.5.0 > 4 Monitoring > 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- CIS v1.5.0 > 4 Monitoring > 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- CIS v1.5.0 > 4 Monitoring > 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- CIS v1.5.0 > 4 Monitoring > 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- CIS v1.5.0 > 4 Monitoring > 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- CIS v1.5.0 > 4 Monitoring > 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- CIS v1.5.0 > 4 Monitoring > 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- CIS v1.5.0 > 4 Monitoring > 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- CIS v2.0.0 > 4 Monitoring > 4.1 Ensure unauthorized API calls are monitored
- CIS v2.0.0 > 4 Monitoring > 4.10 Ensure security group changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.12 Ensure changes to network gateways are monitored
- CIS v2.0.0 > 4 Monitoring > 4.13 Ensure route table changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.14 Ensure VPC changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.15 Ensure AWS Organizations changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.2 Ensure management console sign-in without MFA is monitored
- CIS v2.0.0 > 4 Monitoring > 4.3 Ensure usage of 'root' account is monitored
- CIS v2.0.0 > 4 Monitoring > 4.4 Ensure IAM policy changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.5 Ensure CloudTrail configuration changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.6 Ensure AWS Management Console authentication failures are monitored
- CIS v2.0.0 > 4 Monitoring > 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- CIS v2.0.0 > 4 Monitoring > 4.8 Ensure S3 bucket policy changes are monitored
- CIS v2.0.0 > 4 Monitoring > 4.9 Ensure AWS Config configuration changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.1 Ensure unauthorized API calls are monitored
- CIS v3.0.0 > 4 Monitoring > 4.10 Ensure security group changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.12 Ensure changes to network gateways are monitored
- CIS v3.0.0 > 4 Monitoring > 4.13 Ensure route table changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.14 Ensure VPC changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.15 Ensure AWS Organizations changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.2 Ensure management console sign-in without MFA is monitored
- CIS v3.0.0 > 4 Monitoring > 4.3 Ensure usage of 'root' account is monitored
- CIS v3.0.0 > 4 Monitoring > 4.4 Ensure IAM policy changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.5 Ensure CloudTrail configuration changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.6 Ensure AWS Management Console authentication failures are monitored
- CIS v3.0.0 > 4 Monitoring > 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- CIS v3.0.0 > 4 Monitoring > 4.8 Ensure S3 bucket policy changes are monitored
- CIS v3.0.0 > 4 Monitoring > 4.9 Ensure AWS Config configuration changes are monitored
- Ensure a log metric filter and alarm exist for AWS Config configuration changes
- Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- Ensure a log metric filter and alarm exist for AWS Management Console sign-in without MFA
- Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- Ensure a log metric filter and alarm exist for changes to network gateways
- Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer managed keys
- Ensure a log metric filter and alarm exist for IAM policy changes
- Ensure a log metric filter and alarm exist for route table changes
- Ensure a log metric filter and alarm exist for S3 bucket policy changes
- Ensure a log metric filter and alarm exist for security group changes
- Ensure a log metric filter and alarm exist for unauthorized API calls
- Ensure a log metric filter and alarm exist for usage of 'root' account
- Ensure a log metric filter and alarm exist for VPC changes
Schema for aws_sns_topic_subscription
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. | |
confirmation_was_authenticated | boolean | Reflects authentication status of the subscription. | |
delivery_policy | jsonb | The JSON of the subscription's delivery policy. | |
effective_delivery_policy | jsonb | The JSON of the effective delivery policy that takes into account the topic delivery policy and account system defaults. | |
endpoint | text | The subscription's endpoint (format depends on the protocol). | |
filter_policy | jsonb | The filter policy JSON that is assigned to the subscription. | |
owner | text | The AWS account ID of the subscription's owner. | |
partition | text | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov). | |
pending_confirmation | boolean | Reflects the confirmation status of the subscription. True if the subscription hasn't been confirmed. | |
protocol | text | The subscription's protocol. | |
raw_message_delivery | boolean | true if raw message delivery is enabled for the subscription. | |
redrive_policy | jsonb | When specified, sends undeliverable messages to the specified Amazon SQS dead-letter queue. Messages that can't be delivered due to client errors (for example, when the subscribed endpoint is unreachable) or server errors (for example, when the service that powers the subscribed endpoint becomes unavailable) are held in the dead-letter queue for further analysis or reprocessing. | |
region | text | The AWS Region in which the resource is located. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
subscription_arn | text | = | Amazon Resource Name of the subscription. |
title | text | Title of the resource. | |
topic_arn | text | = | The topic ARN that the subscription is associated with. |
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_sns_topic_subscription