Query: ad_disabled_user_no_role_assignments

with distinct_tenant as (
  select
    distinct tenant_id,
    subscription_id,
    _ctx
  from
    azure_tenant
), disabled_accounts_with_roles as (
  select
    distinct
    u.display_name,
    u.tenant_id,
    u.id,
    u.account_enabled,
    u.user_principal_name
  from
    azuread_user as u
    left join azure_role_assignment as a on a.principal_id = u.id
  where
  not u.account_enabled  and
  a.principal_id is not null
)
select
  u.user_principal_name as resource,
  case
    when u.account_enabled then 'skip'
    when not u.account_enabled and d.display_name is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when u.account_enabled then u.display_name || ' is enabled account.'
    when not u.account_enabled and d.display_name is not null then u.display_name || ' is disabled and has roles assigned.'
    else u.display_name || ' account is disabled with no roles assigned.'
  end as reason,
  t.tenant_id
  
from
  azuread_user as u
  left join disabled_accounts_with_roles as d on u.id =  d.id
  left join distinct_tenant as t on t.tenant_id = d.tenant_id