Query: identity_user_one_active_api_key

with active_keys as (
  select
    user_id,
    count(*) as active_api_key_count
  from
    oci_identity_api_key
  where
    lifecycle_state = 'ACTIVE'
  group by
    user_id
)
select
  u.id as resource,
  case
    when u.user_type <> 'IAM' then 'skip'
    when coalesce(k.active_api_key_count, 0) > 1 then 'alarm'
    else 'ok'
  end as status,
  case
    when u.user_type <> 'IAM' then u.name || ' is a federated user.'
    when coalesce(k.active_api_key_count, 0) = 0 then u.name || ' has no active API keys.'
    when coalesce(k.active_api_key_count, 0) = 1 then name || ' has one active API key.'
    else format('%s has %s active API keys.', u.name, coalesce(k.active_api_key_count, 0))
  end as reason
  , tenant_name as tenant
from
  oci_identity_user u
  left join active_keys k on k.user_id = u.id
where
  u.lifecycle_state = 'ACTIVE';