Query: directory_user_admin_2fa_enrolled

with admin_users as (
  select
    u.primary_email,
    u.is_admin,
    u.is_delegated_admin,
    u.is_enrolled_in_2sv,
    u.is_enforced_in_2sv,
    r.role_name,
    r.is_super_admin_role
  from
    googledirectory_user u
    left join googledirectory_role_assignment ra on u.id = ra.assigned_to
    left join googledirectory_role r on ra.role_id = r.role_id
  where
    u.is_admin = true
    or u.is_delegated_admin = true
)
select
  primary_email as resource,
  case
    when is_enrolled_in_2sv = false then 'alarm'
    when is_enrolled_in_2sv = true
    and is_enforced_in_2sv = false then 'info'
    else 'ok'
  end as status,
  case
    when is_enrolled_in_2sv = false then format(
      'Admin user %s is not enrolled in 2-Step Verification (role: %s).',
      primary_email,
      coalesce(role_name, 'Admin')
    )
    when is_enrolled_in_2sv = true
    and is_enforced_in_2sv = false then format(
      'Admin user %s has 2FA enrolled but not enforced (role: %s).',
      primary_email,
      coalesce(role_name, 'Admin')
    )
    else format(
      'Admin user %s has 2-Step Verification properly configured (role: %s).',
      primary_email,
      coalesce(role_name, 'Admin')
    )
  end as reason
from
  admin_users
order by
  is_super_admin_role desc,
  is_enrolled_in_2sv asc,
  primary_email;