Trigger: Detect & correct CloudWatch log groups without metric filter for network gateway changes

with trails as (
  select
    trail.account_id,
    trail.name as trail_name,
    trail.is_logging,
    split_part(trail.log_group_arn, ':', 7) as log_group_name
  from
    aws_cloudtrail_trail as trail,
    jsonb_array_elements(trail.event_selectors) as se
  where
    trail.is_multi_region_trail is true
    and trail.is_logging
    and se ->> 'ReadWriteType' = 'All'
    and trail.log_group_arn is not null
  order by
    trail_name
),
alarms as (
  select
    metric_name,
    action_arn as topic_arn
  from
    aws_cloudwatch_alarm,
    jsonb_array_elements_text(aws_cloudwatch_alarm.alarm_actions) as action_arn
  order by
    metric_name
),
topic_subscriptions as (
  select
    subscription_arn,
    topic_arn
  from
    aws_sns_topic_subscription
  order by
    subscription_arn
),
metric_filters as (
  select
    filter.name as filter_name,
    filter_pattern,
    log_group_name,
    metric_transformation_name
  from
    aws_cloudwatch_log_metric_filter as filter
  where
    filter.filter_pattern ~ '\s*\$\.eventName\s*=\s*CreateCustomerGateway.+\$\.eventName\s*=\s*DeleteCustomerGateway.+\$\.eventName\s*=\s*AttachInternetGateway.+\$\.eventName\s*=\s*CreateInternetGateway.+\$\.eventName\s*=\s*DeleteInternetGateway.+\$\.eventName\s*=\s*DetachInternetGateway'
  order by
    filter_name
),
filter_data as (
  select
    t.account_id,
    t.trail_name,
    f.filter_name
  from
    trails as t
    join metric_filters as f on f.log_group_name = t.log_group_name
    join alarms as alarm on alarm.metric_name = f.metric_transformation_name
    join topic_subscriptions as subscription on subscription.topic_arn = alarm.topic_arn
)
select
  a.account_id as title,
  region,
  a.account_id,
  sp_connection_name as conn
from
  aws_account as a
  left join filter_data as f on a.account_id = f.account_id
where
  f.trail_name is null;
with trails as (
  select
    trail.account_id,
    trail.name as trail_name,
    trail.is_logging,
    split_part(trail.log_group_arn, ':', 7) as log_group_name
  from
    aws_cloudtrail_trail as trail,
    jsonb_array_elements(trail.event_selectors) as se
  where
    trail.is_multi_region_trail is true
    and trail.is_logging
    and se ->> 'ReadWriteType' = 'All'
    and trail.log_group_arn is not null
  order by
    trail_name
),
alarms as (
  select
    metric_name,
    action_arn as topic_arn
  from
    aws_cloudwatch_alarm,
    jsonb_array_elements_text(aws_cloudwatch_alarm.alarm_actions) as action_arn
  order by
    metric_name
),
topic_subscriptions as (
  select
    subscription_arn,
    topic_arn
  from
    aws_sns_topic_subscription
  order by
    subscription_arn
),
metric_filters as (
  select
    filter.name as filter_name,
    filter_pattern,
    log_group_name,
    metric_transformation_name
  from
    aws_cloudwatch_log_metric_filter as filter
  where
    filter.filter_pattern ~ '\s*\$\.eventName\s*=\s*CreateCustomerGateway.+\$\.eventName\s*=\s*DeleteCustomerGateway.+\$\.eventName\s*=\s*AttachInternetGateway.+\$\.eventName\s*=\s*CreateInternetGateway.+\$\.eventName\s*=\s*DeleteInternetGateway.+\$\.eventName\s*=\s*DetachInternetGateway'
  order by
    filter_name
),
filter_data as (
  select
    t.account_id,
    t.trail_name,
    f.filter_name
  from
    trails as t
    join metric_filters as f on f.log_group_name = t.log_group_name
    join alarms as alarm on alarm.metric_name = f.metric_transformation_name
    join topic_subscriptions as subscription on subscription.topic_arn = alarm.topic_arn
)
select
  a.account_id as title,
  region,
  a.account_id,
  sp_connection_name as conn
from
  aws_account as a
  left join filter_data as f on a.account_id = f.account_id
where
  f.trail_name is null