Query: securitycenter_additional_email_configured

with contact_info as (
  select
    jsonb_agg(email) filter (
      where
        name = 'default'
        and email != ''
    ) as default_email,
    count(*) filter (
      where
        name != 'default'
    ) as non_default_count,
    count(*) filter (
      where
        name = 'default'
    ) as default_count,
    subscription_id
  from
    azure_security_center_contact
  group by
    subscription_id
  limit
    1
)
select
  sub.subscription_id as resource,
  case
    when non_default_count > 0 then 'ok'
    when default_count = 1
    and jsonb_array_length(default_email) != 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when non_default_count > 0 then 'Additional email addresses configured.'
    when default_count = 1
    and default_email is not null then 'Additional email addresses configured.'
    else 'Additional email addresses not configured.'
  end as reason,
  sub.display_name as subscription
from
  azure_subscription sub
  left join contact_info ci on sub.subscription_id = ci.subscription_id;