IAM access key age and rotation status

Lists the access keys belonging to one IAM user with each key's age in days. Long-lived keys are the classic credential hygiene finding: compare key_age_days against your rotation window (90 days is the common baseline) and treat Active keys past it as needing rotation. For an account-wide sweep, enumerate users with aws/iam/users-list and iterate this query over them.

Query

SELECT
user_name,
access_key_id,
status,
create_date,
ROUND(julianday('now') - julianday(create_date)) as key_age_days
FROM aws.iam.access_keys
WHERE region = 'us-east-1'
AND UserName = '{{user_name}}';

Notes

UserName is load-bearing: without it the API returns only the calling identity's own keys, which silently looks like a complete answer. IAM is global, so region = 'us-east-1' is endpoint routing. julianday date arithmetic is the default embedded SQLite backend's dialect; on PostgreSQL use EXTRACT(DAY FROM now() - create_date::timestamp). A key that has never been rotated is simply the oldest one for the user - there is no separate last-rotated field, since rotation means creating a new key and deleting the old one. Inactive keys still count as credentials that exist and should be deleted rather than left disabled.