turbot/azure_compliance

Query: ad_guest_user_reviewed_monthly

Usage

powerpipe query azure_compliance.query.ad_guest_user_reviewed_monthly

SQL

with distinct_tenant as (
select
distinct tenant_id,
subscription_id,
_ctx
from
azure_tenant
)
select
u.display_name as resource,
case
when not account_enabled then 'alarm'
when u.created_date_time::timestamp <= (current_date - interval '30' day) then 'alarm'
else 'ok'
end as status,
case
when not account_enabled then 'Guest user ''' || u.display_name || ''' inactive.'
else 'Guest user ''' || u.display_name || ''' was created ' || extract(day from current_timestamp - u.created_date_time::timestamp) || ' days ago.'
end as reason,
t.tenant_id
from
azuread_user as u
left join distinct_tenant as t on t.tenant_id = u.tenant_id
where
u.user_type = 'Guest';

Controls

The query is being used by the following controls: