steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_acm_certificateaws_api_gateway_api_keyaws_api_gateway_authorizeraws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_stageaws_appautoscaling_targetaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_frameworkaws_availability_zoneaws_backup_planaws_backup_vaultaws_cloudformation_stackaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudtrail_trailaws_cloudwatch_alarmaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_streamaws_codebuild_projectaws_config_configuration_recorderaws_config_conformance_packaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dms_replication_instanceaws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_tableaws_ebs_snapshotaws_ebs_volumeaws_ebs_volume_metric_read_opsaws_ebs_volume_metric_read_ops_dailyaws_ebs_volume_metric_read_ops_hourlyaws_ebs_volume_metric_write_opsaws_ebs_volume_metric_write_ops_dailyaws_ebs_volume_metric_write_ops_hourlyaws_ec2_amiaws_ec2_ami_sharedaws_ec2_application_load_balanceraws_ec2_autoscaling_groupaws_ec2_classic_load_balanceraws_ec2_gateway_load_balanceraws_ec2_instanceaws_ec2_instance_availabilityaws_ec2_instance_metric_cpu_utilizationaws_ec2_instance_metric_cpu_utilization_dailyaws_ec2_instance_metric_cpu_utilization_hourlyaws_ec2_instance_typeaws_ec2_key_pairaws_ec2_launch_configurationaws_ec2_load_balancer_listeneraws_ec2_network_interfaceaws_ec2_network_load_balanceraws_ec2_regional_settingsaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_container_instanceaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_replication_groupaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_clusteraws_eventbridge_ruleaws_glacier_vaultaws_glue_catalog_databaseaws_guardduty_detectoraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_threat_intel_setaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_policyaws_iam_policy_simulatoraws_iam_roleaws_iam_server_certificateaws_iam_useraws_iam_virtual_mfa_deviceaws_inspector_assessment_targetaws_inspector_assessment_templateaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_keyaws_lambda_aliasaws_lambda_functionaws_lambda_versionaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_instanceaws_rds_db_instance_metric_connectionsaws_rds_db_instance_metric_connections_dailyaws_rds_db_instance_metric_connections_hourlyaws_rds_db_instance_metric_cpu_utilizationaws_rds_db_instance_metric_cpu_utilization_dailyaws_rds_db_instance_metric_cpu_utilization_hourlyaws_rds_db_instance_metric_read_iopsaws_rds_db_instance_metric_read_iops_dailyaws_rds_db_instance_metric_read_iops_hourlyaws_rds_db_instance_metric_write_iopsaws_rds_db_instance_metric_write_iops_dailyaws_rds_db_instance_metric_write_iops_hourlyaws_rds_db_option_groupaws_rds_db_parameter_groupaws_rds_db_snapshotaws_rds_db_subnet_groupaws_redshift_clusteraws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_regionaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_ruleaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_hubaws_securityhub_productaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_parameteraws_ssm_patch_baselineaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_network_aclaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_workload
On This Page
Get Involved

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);

Report of 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 of 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';

Find 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;

Find 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;

Check if root login has MFA enabled

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

.inspect aws_iam_credential_report

AWS IAM Credential Report

NameTypeDescription
access_key_1_activebooleanDoes the user have an access key and is the access key's status Active.
access_key_1_last_rotatedtimestamp without time zoneThe date and time when the user's access key was created or last changed.
access_key_1_last_used_datetimestamp without time zoneThe 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_regiontextThe 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_servicetextThe 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_activebooleanDoes the user have a second access key and is the access key's status Active.
access_key_2_last_rotatedtimestamp without time zoneThe date and time when the user's second access key was created or last changed.
access_key_2_last_used_datetimestamp without time zoneThe 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_regiontextThe 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_servicetextThe 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_idtextThe AWS Account ID in which the resource is located.
cert_1_activebooleanDoes the user have an X.509 signing certificate and is that certificate's status Active.
cert_1_last_rotatedtimestamp without time zoneThe date and time when the user's signing certificate was created or last changed.
cert_2_activebooleanDoes the user have a second X.509 signing certificate and is that certificate's status Active.
cert_2_last_rotatedtimestamp without time zoneThe date and time when the user's second signing certificate was created or last changed.
generated_timetimestamp without time zoneThe date and time when the credential report was created, in ISO 8601 date-time format (http://www.iso.org/iso/iso8601).
mfa_activebooleanWhether a multi-factor authentication (MFA) device has been enabled for the user.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
password_enabledbooleanWhen 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_changedtimestamp without time zoneThe date and time when the user's password was last set.
password_last_usedtimestamp without time zoneThe 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_rotationtimestamp without time zoneWhen the account has a password policy that requires password rotation, this field contains the date and time.
password_statustextThe status of an user password. Password status can be one of used, never_used and not_set.
regiontextThe AWS Region in which the resource is located.
user_arntextThe Amazon Resource Name (ARN) of the user.
user_creation_timetimestamp without time zoneThe date and time when the user was created.
user_nametextThe friendly name of the user.