Query: ad_guest_user_reviewed_monthly

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';