Query: azuread_authentication_method_restrict_insecure_methods

with tenant_list as (
  select distinct on (tenant_id) tenant_id, _ctx
  from azuread_user
),
authentication_method_policy as (
  select
    tenant_id,
    count(*) as required_methods_enabled
  from
    azuread_authentication_method_policy,
    jsonb_array_elements(authentication_method_configurations) as cfg
  where
    cfg ->> 'id' in ('Sms', 'Voice', 'Email')
    and cfg ->> 'state' = 'enabled'
  group by tenant_id
)
select
  t.tenant_id as resource,
  case
    when required_methods_enabled = 3 then 'ok'
    else 'alarm'
  end as status,
  case
    when required_methods_enabled = 3 then t.tenant_id || ' has SMS, Voice call, and Email OTP authentication methods all enabled.'
    else t.tenant_id || ' does not have all of SMS, Voice call, and Email OTP authentication methods enabled.'
  end as reason
  , t.tenant_id as tenant_id
from
  tenant_list as t
  left join authentication_method_policy as p on p.tenant_id = t.tenant_id;