Control: Ensure that a multifactor authentication policy exists for all users

powerpipe control run azure_compliance.control.ad_all_user_mfa_enabled

powerpipe login
powerpipe control run azure_compliance.control.ad_all_user_mfa_enabled --share

with role_template_ids as (
  select
    array_agg(role_template_id) as rid
  from
    azuread_directory_role
),
policy_with_mfa as (
  select
    tenant_id,
    count(p.*)
  from
    azuread_conditional_access_policy as p,
    role_template_ids as a
  where
    p.built_in_controls ?& array['mfa']
    and (p.users -> 'includeRoles')::jsonb ?| (a.rid)
    and jsonb_array_length(p.users -> 'excludeUsers') < 1
  group by
    tenant_id
),
tenant_list as (
  select
    distinct tenant_id,
    subscription_id,
    _ctx
  from
    azure_tenant
)
select
  t.tenant_id as resource,
  case
    when (select count from policy_with_mfa where tenant_id = t.tenant_id) > 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when (select count from policy_with_mfa where tenant_id = t.tenant_id) > 0 then t.tenant_id || ' has MFA enabled for all users.'
    else t.tenant_id || ' has MFA disabled for all users.'
  end as reason,
  t.tenant_id
  
from
  tenant_list as t;