steampipe plugin install awssteampipe plugin install aws

Table: aws_iam_credential_report

Retrieves a credential report for the AWS account. For more information about the credential report, see Getting Credential Reports in the IAM User Guide.

Please note: This table requires a valid credential report to exist. To generate, please run the follow AWS CLI command:

aws iam generate-credential-report

Examples

List users that have logged into the console in the past 90 days

select
  user_name
from
  aws_iam_credential_report
where
  password_enabled
  and password_last_used > (current_date - interval '90' day);

List users that have NOT logged into the console in the past 90 days

select
  user_name,
  password_last_used,
  age(password_last_used)
from
  aws_iam_credential_report
where
  password_enabled
  and password_last_used <= (current_date - interval '90' day)
order by
  password_last_used;

List users with console access that have never logged in to the console

select
  user_name
from
  aws_iam_credential_report
where
  password_status = 'never_used';

List access keys older than 90 days

select
  user_name,
  access_key_1_last_rotated,
  age(access_key_1_last_rotated) as access_key_1_age,
  access_key_2_last_rotated,
  age(access_key_2_last_rotated) as access_key_2_age
from
  aws_iam_credential_report
where
  access_key_1_last_rotated <= (current_date - interval '90' day)
  or access_key_2_last_rotated <= (current_date - interval '90' day)
order by
  user_name;

List users that have a console password but do not have MFA enabled

select
  user_name,
  mfa_active,
  password_enabled
from
  aws_iam_credential_report
where
  password_enabled
  and not mfa_active;

select
  user_name,
  mfa_active
from
  aws_iam_credential_report
where
  user_name = '<root_account>';

Query examples

.inspect aws_iam_credential_report

AWS IAM Credential Report

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
access_key_1_active	boolean	Does the user have an access key and is the access key's status Active.
access_key_1_last_rotated	timestamp with time zone	The date and time when the user's access key was created or last changed.
access_key_1_last_used_date	timestamp with time zone	The date and time when the user's access key was most recently used to sign an AWS API request. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_1_last_used_region	text	The AWS Region in which the access key was most recently used. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_1_last_used_service	text	The AWS service that was most recently accessed with the access key. The value in this field uses the service's namespace—for example, s3 for Amazon S3 and ec2 for Amazon EC2. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_2_active	boolean	Does the user have a second access key and is the access key's status Active.
access_key_2_last_rotated	timestamp with time zone	The date and time when the user's second access key was created or last changed.
access_key_2_last_used_date	timestamp with time zone	The date and time when the user's second access key was most recently used to sign an AWS API request. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_2_last_used_region	text	The AWS Region in which the user's second access key was most recently used. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
access_key_2_last_used_service	text	The AWS service that was most recently accessed with the user's second access key. The value in this field uses the service's namespace—for example, s3 for Amazon S3 and ec2 for Amazon EC2. When an access key is used more than once in a 15-minute span, only the first use is recorded in this field.
account_id	text	The AWS Account ID in which the resource is located.
cert_1_active	boolean	Does the user have an X.509 signing certificate and is that certificate's status Active.
cert_1_last_rotated	timestamp with time zone	The date and time when the user's signing certificate was created or last changed.
cert_2_active	boolean	Does the user have a second X.509 signing certificate and is that certificate's status Active.
cert_2_last_rotated	timestamp with time zone	The date and time when the user's second signing certificate was created or last changed.
generated_time	timestamp with time zone	The date and time when the credential report was created, in ISO 8601 date-time format (http://www.iso.org/iso/iso8601).
mfa_active	boolean	Whether a multi-factor authentication (MFA) device has been enabled for the user.
partition	text	The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
password_enabled	boolean	When the user has a password, this value is true. Otherwise it is false. The value for the AWS account root user is always false.
password_last_changed	timestamp with time zone	The date and time when the user's password was last set.
password_last_used	timestamp with time zone	The date and time when the AWS account root user or IAM user's password was last used to sign in to an AWS website.
password_next_rotation	timestamp with time zone	When the account has a password policy that requires password rotation, this field contains the date and time.
password_status	text	The status of an user password. Password status can be one of used, never_used and not_set.
region	text	The AWS Region in which the resource is located.
user_arn	text	The Amazon Resource Name (ARN) of the user.
user_creation_time	timestamp with time zone	The date and time when the user was created.
user_name	text	The friendly name of the user.

turbot/aws

Table: aws_iam_credential_report

Examples

List users that have logged into the console in the past 90 days

List users that have NOT logged into the console in the past 90 days

List users with console access that have never logged in to the console

List access keys older than 90 days

List users that have a console password but do not have MFA enabled

Query examples

.inspect aws_iam_credential_report

Steampipe

Resources

Community

turbot/aws

Table: aws_iam_credential_report

Examples

List users that have logged into the console in the past 90 days

List users that have NOT logged into the console in the past 90 days

List users with console access that have never logged in to the console

List access keys older than 90 days

List users that have a console password but do not have MFA enabled

Check if root login has MFA enabled

Query examples

.inspect aws_iam_credential_report