Query: azuread_guest_user_access_reviews_configured

with tenant_list as (
  select distinct on (tenant_id) tenant_id, _ctx
  from azuread_user
),
guest_user_access_reviews as (
  select
    tenant_id,
    count(*) as guest_user_access_review
  from
    azuread_access_review_schedule_definition
  where
    display_name = 'Review guest access across Microsoft 365 groups'
    and (settings -> 'mailNotificationsEnabled')::bool
    and (settings -> 'reminderNotificationsEnabled')::bool
    and (settings -> 'justificationRequiredOnApproval')::bool
    and settings -> 'recurrence' -> 'pattern' ->> 'type' in ('absoluteMonthly', 'weekly')
    and (settings -> 'autoApplyDecisionsEnabled')::bool
    and settings ->> 'defaultDecision' = 'Deny'
  group by tenant_id
)
select
  t.tenant_id as resource,
  case
    when guest_user_access_review > 0 then 'ok'
    else 'alarm'
  end as status,
  case
    when guest_user_access_review > 0
      then t.tenant_id || ' has access reviews configured for guest users.'
    else t.tenant_id || ' does not have access reviews configured for guest users.'
  end as reason
  , t.tenant_id as tenant_id
from
  tenant_list as t
  left join guest_user_access_reviews as p on p.tenant_id = t.tenant_id;