Table: aws_sns_subscriptions
This table shows data for Sns Subscriptions.
https://docs.aws.amazon.com/sns/latest/api/API_GetSubscriptionAttributes.html (opens in a new tab)
The primary key for this table is arn.
Columns
| Name | Type |
|---|---|
| _cq_id | uuid |
| _cq_parent_id | uuid |
| account_id | utf8 |
| region | utf8 |
| arn (PK) | utf8 |
| delivery_policy | json |
| effective_delivery_policy | json |
| filter_policy | json |
| redrive_policy | json |
| endpoint | utf8 |
| owner | utf8 |
| protocol | utf8 |
| subscription_arn | utf8 |
| topic_arn | utf8 |
| confirmation_was_authenticated | bool |
| pending_confirmation | bool |
| raw_message_delivery | bool |
| subscription_role_arn | utf8 |
| unknown_fields | json |
Example Queries
These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.
Unused SNS topic
WITH
subscription AS (SELECT DISTINCT topic_arn FROM aws_sns_subscriptions)
SELECT
'Unused SNS topic' AS title,
topic.account_id,
topic.arn AS resource_id,
'fail' AS status
FROM
aws_sns_topics AS topic
LEFT JOIN subscription ON subscription.topic_arn = topic.arn
WHERE
subscription.topic_arn IS NULL;