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;

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