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;