Query: iam_conditional_access_mfa_enabled_for_administrators

with distinct_tenant as (
  select
    u.id,
    tenant_id
  from
    azuread_user as u
    left join azure_role_assignment as a on a.principal_id = u.id
    left join azure_role_definition as d on d.id = a.role_definition_id
  where role_type = 'BuiltInRole' and (role_name like '%Administrator%' or role_name = 'Owner')
)
select
  p.id as resource,
  case
    when p.built_in_controls @> '["mfa"]' then 'ok'
    else 'alarm'
  end as status,
  case
    when p.built_in_controls @> '["mfa"]' then p.display_name || ' MFA enabled.'
    else p.display_name || ' MFA disabled.'
  end as reason,
  t.tenant_id
  
from
  distinct_tenant as t,
  azuread_conditional_access_policy as p;