Query: iam_user_access_key_unused_45

select
  user_arn as resource,
  case
    when not access_key_1_active then 'ok'
    when access_key_1_active
    and access_key_1_last_used_date is null then 'alarm'
    when access_key_1_active
    and access_key_1_last_used_date < (current_date - interval '45' day) then 'alarm'
    when not access_key_2_active then 'ok'
    when access_key_2_active
    and access_key_2_last_used_date is null then 'alarm'
    when access_key_2_active
    and access_key_2_last_used_date < (current_date - interval '45' day) then 'alarm'
    else 'ok'
  end as status,
  user_name || case
    when not access_key_1_active then ' key 1 not enabled,'
    when access_key_1_active
    and access_key_1_last_used_date is null then ' key 1 created ' || to_char(access_key_1_last_rotated, 'DD-Mon-YYYY') || ' never used,'
    else ' key 1 used ' || to_char(access_key_1_last_used_date, 'DD-Mon-YYYY') || ','
  end || case
    when not access_key_2_active then ' key 2 not enabled.'
    when access_key_2_active
    and access_key_2_last_used_date is null then ' key 2 created ' || to_char(access_key_2_last_rotated, 'DD-Mon-YYYY') || ' never used.'
    else ' key 2 used ' || to_char(access_key_2_last_used_date, 'DD-Mon-YYYY') || '.'
  end as reason,
  account_id
from
  aws_iam_credential_report;