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
- 3.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 3.10 Ensure a log metric filter and alarm exist for security group changes
- 3.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 3.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 3.13 Ensure a log metric filter and alarm exist for route table changes
- 3.14 Ensure a log metric filter and alarm exist for VPC changes
- 3.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 3.3 Ensure a log metric filter and alarm exist for usage of "root" account
- 3.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 3.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 3.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 3.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 3.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 3.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure unauthorized API calls are monitored
- 4.1 Ensure unauthorized API calls are monitored
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure security group changes are monitored
- 4.10 Ensure security group changes are monitored
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure changes to network gateways are monitored
- 4.12 Ensure changes to network gateways are monitored
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure route table changes are monitored
- 4.13 Ensure route table changes are monitored
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure VPC changes are monitored
- 4.14 Ensure VPC changes are monitored
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure AWS Organizations changes are monitored
- 4.15 Ensure AWS Organizations changes are monitored
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure management console sign-in without MFA is monitored
- 4.2 Ensure management console sign-in without MFA is monitored
- 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- 4.3 Ensure a log metric filter and alarm exist for usage of "root" account
- 4.3 Ensure usage of 'root' account is monitored
- 4.3 Ensure usage of 'root' account is monitored
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure IAM policy changes are monitored
- 4.4 Ensure IAM policy changes are monitored
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure CloudTrail configuration changes are monitored
- 4.5 Ensure CloudTrail configuration changes are monitored
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure AWS Management Console authentication failures are monitored
- 4.6 Ensure AWS Management Console authentication failures are monitored
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure S3 bucket policy changes are monitored
- 4.8 Ensure S3 bucket policy changes are monitored
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure AWS Config configuration changes are monitored
- 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
- Ensure AWS Organizations changes are monitored
Schema for aws_sns_topic_subscription
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
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. | |
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