steampipe plugin install aws

Table: aws_codestar_notification_rule - Query AWS CodeStar notification rules using SQL

The AWS CodeStar notification rules allow you to set up notifications for the AWS Developer Tools, including AWS CodePipeline and AWS CodeBuild, to various destinations including AWS SNS and AWS Chatbot.

Table Usage Guide

The aws_codestar_notification_rule table in Steampipe provides you with information about notification rules. This table allows you, as a DevOps engineer, to query notification rule details, including the notification rule ARN, status, level of detail, enabled event types, as well as the ARN of the resource producing notifications and the notification targets. You can use this table to gather insights on notification rules, and combine it with other tables such as aws_codepipeline_pipeline to check notification rules are set up consistently.

Examples

Basic info

Review the configured rules and their status.

select
name,
resource,
detail_type,
status
from
aws_codestar_notification_rule;
select
name,
resource,
detail_type,
status
from
aws_codestar_notification_rule;

Identify which CI/CD pipelines have notification rules

Determine which AWS CodePipeline pipelines do or do not have associated notification rules.

select
pipeline.name as pipeline,
notification_rule.name notification_rule,
notification_rule.status
from
aws_codepipeline_pipeline as pipeline
left join aws_codestar_notification_rule as notification_rule on pipeline.arn = notification_rule.resource;
select
pipeline.name as pipeline,
notification_rule.name as notification_rule,
notification_rule.status
from
aws_codepipeline_pipeline as pipeline
left join aws_codestar_notification_rule as notification_rule on pipeline.arn = notification_rule.resource;

Check for notification rules with no targets

Determine which notification rules lack targets. This query uses PostgreSQL's JSON querying capabilities to count the number of targets configured.

select
name
from
aws_codestar_notification_rule
where
jsonb_array_length(targets) = 0;
select
name
from
aws_codestar_notification_rule
where
json_array_length(targets) = 0;

Name the SNS topics associated with notification rules

Determine which AWS SNS topics the notification rules are targeting. This query uses PostgreSQL's JSON querying capabilities to join on the notification rule targets. Note that due to the cross join, this query will not list notification rules that don't have any targets.

select
notification_rule.name as notification_rule,
target ->> 'TargetType' as target_type,
topic.title as target_topic
from
aws_codestar_notification_rule as notification_rule
cross join jsonb_array_elements(notification_rule.targets) as target
left join aws_sns_topic as topic on target ->> 'TargetAddress' = topic.topic_arn;
select
notification_rule.name as notification_rule,
json_extract(target.value, '$.TargetType') as target_type,
topic.title as target_topic
from
aws_codestar_notification_rule as notification_rule
cross join json_each(notification_rule.targets) as target
left join aws_sns_topic as topic on json_extract(target.value, '$.TargetAddress') = topic.topic_arn;

Using CTE to retain notification rules without targets

By using a Common Table Expression (with query), it is possible to join on targets without discarding notification rules that don't have any targets.

with rule_target as (
select
arn,
target ->> 'TargetAddress' as target_address,
target ->> 'TargetStatus' as target_status,
target ->> 'TargetType' as target_type
from
aws_codestar_notification_rule
cross join jsonb_array_elements(targets) as target
)
select
notification_rule.name as notification_rule,
rule_target.target_type,
topic.title as target_topic
from
aws_codestar_notification_rule as notification_rule
left join rule_target on rule_target.arn = notification_rule.arn
left join aws_sns_topic as topic on rule_target.target_address = topic.topic_arn;
with rule_target as (
select
notification_rule.arn,
json_extract(target.value, '$.TargetAddress') as target_address,
json_extract(target.value, '$.TargetStatus') as target_status,
json_extract(target.value, '$.TargetType') as target_type
from
aws_codestar_notification_rule as notification_rule
cross join json_each(notification_rule.targets) as target
)
select
notification_rule.name as notification_rule,
rule_target.target_type,
topic.title as target_topic
from
aws_codestar_notification_rule as notification_rule
left join rule_target on rule_target.arn = notification_rule.arn
left join aws_sns_topic as topic on rule_target.target_address = topic.topic_arn;

Schema for aws_codestar_notification_rule

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntext=The Amazon Resource Name (ARN) of the notification rule.
created_bytext=The name or email alias of the person who created the notification rule.
created_timestamptimestamp with time zoneThe date and time the notification rule was created.
detail_typetextThe level of detail included in the notifications for this resource. BASIC will include only the contents of the event as it would appear in Amazon CloudWatch. FULL will include any supplemental information provided by AWS CodeStar Notifications and/or the service for the resource for which the notification is created.
event_type_idtext=Specifies that only notification rules with the given event type enabled are returned.
event_typesjsonbA list of the event types associated with the notification rule.
idtextThe unique ID of the notification rule.
last_modified_timestamptimestamp with time zoneThe date and time the notification rule was most recently updated.
nametextThe name of the notification rule.
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.
resourcetext=The Amazon Resource Name (ARN) of the resource associated with the notification rule.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustextThe status of the notification rule. Valid statuses are on (sending notifications) or off (not sending notifications).
tagsjsonbA map of tags for the resource.
target_addresstext=Specifies that only notification rules with a target with the given address are returned.
targetsjsonbA list of targets associated with the notification rule.
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_codestar_notification_rule