turbot/microsoft365_compliance

Query: azuread_legacy_authentication_disabled

Usage

powerpipe query microsoft365_compliance.query.azuread_legacy_authentication_disabled

SQL

with block_legacy_authentication as (
select
tenant_id,
count(*)
from
azuread_conditional_access_policy
where
client_app_types ?& array['exchangeActiveSync', 'other']
and built_in_controls ?& array['block']
and users -> 'includeUser' ?& array['All']
and jsonb_array_length(users -> 'excludeUser') != 0
group by
tenant_id
),
tenant_list as(
select
distinct on(tenant_id) tenant_id,
_ctx
from
azuread_user
)
select
tenant_id as resource,
case
when (select count from block_legacy_authentication where tenant_id = t.tenant_id) > 0 then 'ok'
else 'alarm'
end as status,
case
when (select count from block_legacy_authentication where tenant_id = t.tenant_id) > 0 then tenant_id || ' has Conditional Access policies enabled.'
else tenant_id || ' has Conditional Access policies disabled.'
end as reason
, t.tenant_id as tenant_id
from
tenant_list as t;

Controls

The query is being used by the following controls: