Query: account_alternate_contact_security_registered

with alternate_security_contact as (
  select
    name,
    account_id
  from
    aws_account_alternate_contact
  where
    contact_type = 'SECURITY'
),
account as (
  select
    arn,
    partition,
    title,
    account_id,
    _ctx
  from
    aws_account
)
select
  arn as resource,
  case
    when a.partition = 'aws-us-gov' then 'info'
    -- Name is a required field if setting a security contact
    when c.name is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when a.partition = 'aws-us-gov' then a.title || ' in GovCloud, manual verification required.'
    when c.name is not null then a.title || ' has security contact ' || c.name || ' registered.'
    else a.title || ' security contact not registered.'
  end as reason
  , a.account_id
from
  account as a,
  alternate_security_contact as c
where
  c.account_id = a.account_id;