Query: ad_admin_portals_require_mfa

with distinct_tenant as (
  select
    distinct tenant_id,
    display_name,
    subscription_id,
    _ctx
  from
    azure_tenant
),
conditional_access_policy as (
  select
    tenant_id,
    count(*) as conditional_access_policy_count
  from
    azuread_conditional_access_policy
  where
    users -> 'includeUsers' ? 'All'
    and applications -> 'includeApplications' ? 'MicrosoftAdminPortals'
    and built_in_controls @> '[1]'::jsonb
    and state = 'enabled'
  group
    by tenant_id
)
select
  t.tenant_id as resource,
  case
    when conditional_access_policy_count > 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when conditional_access_policy_count > 0 then t.display_name || ' has conditional access policy that requires MFA for All users (or admin roles) when accessing admin portals.'
    else t.display_name || ' does not have a conditional access policy that requires MFA for All users (or admin roles) when accessing admin portals.'
  end as reason,
  t.tenant_id
  
from
  distinct_tenant as t
  left join conditional_access_policy as p on p.tenant_id = t.tenant_id;