Query: identity_user_credentials_unused_45_days

select
  u.id as resource,
  case
    when u.user_type <> 'IAM' then 'skip'
    when coalesce(u.can_use_console_password, false)
        or coalesce(u.can_use_api_keys, false)
        or coalesce(u.can_use_auth_tokens, false)
        or coalesce(u.can_use_smtp_credentials, false)
        or coalesce(u.can_use_customer_secret_keys, false)
        or coalesce(u.can_use_o_auth2_client_credentials, false)
      then case
        when u.last_successful_login_time is null
          then 'alarm'
        when u.last_successful_login_time <= (current_timestamp - interval '45 day')
          then 'alarm'
        else 'ok'
      end
    else 'ok'
  end as status,
  case
    when u.user_type <> 'IAM' then name || ' is a federated user.'
    when not (
        coalesce(u.can_use_console_password, false)
      or coalesce(u.can_use_api_keys, false)
      or coalesce(u.can_use_auth_tokens, false)
      or coalesce(u.can_use_smtp_credentials, false)
      or coalesce(u.can_use_customer_secret_keys, false)
      or coalesce(u.can_use_o_auth2_client_credentials, false)
    ) then name || ' user all console/API credentials already disabled.'
    when u.last_successful_login_time is null
      then name || ' credentials enabled but has never logged in.'
    when u.last_successful_login_time <= (current_timestamp - interval '45 day')
      then name || ' credentials enabled and last successful login over 45 days ago.'
    else name || ' credentials enabled and last successful login within 45 days.'
  end as reason
  , tenant_name as tenant
from
  oci_identity_user u
where
  u.lifecycle_state = 'ACTIVE';