Query: azuread_authentication_method_email_otp_disabled

with tenant_list as (
  select distinct on (tenant_id) tenant_id, _ctx
  from azuread_user
),
email_otp_status as (
  select
    tenant_id,
    cfg ->> 'state' as email_otp_state
  from
    azuread_authentication_method_policy,
    jsonb_array_elements(authentication_method_configurations) as cfg
  where
    cfg ->> 'id' = 'Email'
)
select
  t.tenant_id as resource,
  case
    when e.email_otp_state is null then 'ok'
    when e.email_otp_state = 'disabled' then 'ok'
    else 'alarm'
  end as status,
  case
    when e.email_otp_state is null then t.tenant_id || ' has Email OTP authentication method not configured.'
    when e.email_otp_state = 'disabled' then t.tenant_id || ' has Email OTP authentication method disabled.'
    else t.tenant_id || ' has Email OTP authentication method enabled.'
  end as reason
  , t.tenant_id as tenant_id
from
  tenant_list as t
  left join email_otp_status as e on e.tenant_id = t.tenant_id;