Query: azuread_conditional_access_signin_frequency_intune_every_time

with tenant_list as (
  select distinct on (tenant_id) tenant_id, _ctx
  from azuread_user
),
conditional_access_policy as (
  select
    tenant_id,
    count(*) as conditional_access_policy_count
  from
    azuread_conditional_access_policy
  where
    users -> 'includeUsers' ? 'All'
    and (
      built_in_controls @> '[1]'::jsonb
      or authentication_strength is not null
    )
    and applications -> 'includeApplications' ? 'd4ebce55-015a-49b5-a083-c84d1797ae8c'
    and (sign_in_frequency ->> 'isEnabled')::boolean = true
    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.tenant_id || ' has a Conditional Access policy enforcing sign-in frequency set to Every time for Microsoft Intune Enrollment.'
    else t.tenant_id || ' does not have a conditional access policy enforcing sign-in frequency set to Every time for Microsoft Intune Enrollment.'
  end as reason
  , t.tenant_id as tenant_id
from
  tenant_list as t
  left join conditional_access_policy as p on p.tenant_id = t.tenant_id;