turbot/aws_insights

Query: iam_credential_entities_root_access_keys_table

Usage

powerpipe query aws_insights.query.iam_credential_entities_root_access_keys_table

SQL

select
user_name as "User Name",
user_arn as "User ARN",
password_enabled as "Password Enabled",
mfa_active as "MFA Active",
password_status as "Password Status",
now()::date - password_last_changed::date as "Password Age in Days",
password_last_changed as "Password Changed Timestamp",
date_trunc('day',age(now(),password_last_used))::text as "Password Last Used",
password_last_used as "Password Last Used Timestamp",
date_trunc('day',age(now(),password_next_rotation))::text as "Next Password Rotation",
password_next_rotation "Next Password Rotation Timestamp",
access_key_1_active as "Access Key 1 Active",
now()::date - access_key_1_last_rotated::date as "Key 1 Age in Days",
access_key_1_last_rotated as "Key 1 Last Rotated",
date_trunc('day',age(now(),access_key_1_last_used_date))::text as "Key 1 Last Used",
access_key_1_last_used_date as "Key 1 Last Used Timestamp",
access_key_1_last_used_region as "Key 1 Last Used Region",
access_key_1_last_used_service as "Key 1 Last Used Service",
access_key_2_active as "Access Key 2 Active",
now()::date - access_key_2_last_rotated::date as "Key 2 Age in Days",
access_key_2_last_rotated as "Key 2 Last Rotated Timestamp",
date_trunc('day',age(now(),access_key_2_last_used_date))::text as "Key 2 Last Used",
access_key_2_last_used_date as "Key 2 Last Used Timestamp",
access_key_2_last_used_region as "Key 2 Last Used Region",
access_key_2_last_used_service as "Key 2 Last Used Service",
cert_1_active as "Cert 1 Active",
now()::date - cert_1_last_rotated::date as "Cert 1 Age in Days",
cert_1_last_rotated "Cert 1 Last Rotated",
cert_2_active as "Cert 2 Active",
now()::date - cert_2_last_rotated::date as "Cert 2 Age in Days",
cert_2_last_rotated as "Cert 2 Last Rotated",
a.title as "Account",
r.account_id as "Account ID"
from
aws_iam_credential_report as r,
aws_account as a
where
a.account_id = r.account_id
order by
user_name;

Dashboards

The query is used in the dashboards: