steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_acm_certificateaws_api_gateway_api_authorizeraws_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_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudtrail_trailaws_cloudtrail_trail_eventaws_cloudwatch_alarmaws_cloudwatch_log_eventaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_resource_policyaws_cloudwatch_log_streamaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codepipeline_pipelineaws_config_configuration_recorderaws_config_conformance_packaws_config_ruleaws_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_directory_service_directoryaws_dms_replication_instanceaws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_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_application_load_balancer_metric_request_countaws_ec2_application_load_balancer_metric_request_count_dailyaws_ec2_autoscaling_groupaws_ec2_capacity_reservationaws_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_network_load_balancer_metric_net_flow_countaws_ec2_network_load_balancer_metric_net_flow_count_dailyaws_ec2_regional_settingsaws_ec2_reserved_instanceaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_routeaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_cluster_metric_cpu_utilizationaws_ecs_cluster_metric_cpu_utilization_dailyaws_ecs_cluster_metric_cpu_utilization_hourlyaws_ecs_container_instanceaws_ecs_serviceaws_ecs_taskaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_eks_identity_provider_configaws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_redis_metric_cache_hits_hourlyaws_elasticache_redis_metric_curr_connections_hourlyaws_elasticache_redis_metric_engine_cpu_utilization_hourlyaws_elasticache_redis_metric_get_type_cmds_hourlyaws_elasticache_redis_metric_list_based_cmds_hourlyaws_elasticache_redis_metric_new_connections_hourlyaws_elasticache_replication_groupaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_clusteraws_emr_cluster_metric_is_idleaws_emr_instance_groupaws_eventbridge_busaws_eventbridge_ruleaws_fsx_file_systemaws_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_identitystore_groupaws_identitystore_useraws_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_function_metric_duration_dailyaws_lambda_function_metric_errors_dailyaws_lambda_function_metric_invocations_dailyaws_lambda_layeraws_lambda_layer_versionaws_lambda_versionaws_macie2_classification_jobaws_media_store_containeraws_organizations_accountaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_event_subscriptionaws_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_cluster_metric_cpu_utilization_dailyaws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_regionaws_route53_domainaws_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_securityhub_standards_subscriptionaws_serverlessapplicationrepository_applicationaws_sfn_state_machineaws_sfn_state_machine_executionaws_sfn_state_machine_execution_historyaws_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_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_tagging_resourceaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_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_workloadaws_workspaces_workspace

Table: aws_iam_access_advisor

Access Advisor returns details about when an IAM principal (user, group, role, or policy) was last used in an attempt to access AWS services. Recent activity usually appears within four hours. IAM reports activity for the last 365 days, or less if your Region began supporting this feature within the last year.

Important Notes:

  • You must specify a single principal_arn in a where or join clause in order to use this table.

  • The service last accessed data includes all attempts to access an AWS API, not just the successful ones. This includes all attempts that were made using the AWS Management Console, the AWS API through any of the SDKs, or any of the command line tools. An unexpected entry in the service last accessed data does not mean that your account has been compromised, because the request might have been denied. Refer to your CloudTrail logs as the authoritative source for information about all API calls and whether they were successful or denied access.

  • Service last accessed data does not use other policy types when determining whether a resource could access a service. These other policy types include resource-based policies, access control lists, AWS Organizations policies, IAM permissions boundaries, and AWS STS assume role policies. It only applies permissions policy logic. For more about the evaluation of policy types, see Evaluating Policies in the IAM User Guide.

Examples

Show the most recently used services for a user, role, group, or policy

select
principal_arn,
service_name,
last_authenticated,
age(last_authenticated::date)
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::123456789123:user/john'
and last_authenticated is not null
order by
age asc;

Show unused services granted a user, role, group, or policy (unused in the last year)

select
principal_arn,
service_name
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'
and last_authenticated is null
order by
service_name

Show the last time a policy was used to access services, and the user, role, or group that used it

select
principal_arn,
service_name,
last_authenticated,
age(last_authenticated::date),
last_authenticated_entity,
last_authenticated_region
from
aws_iam_access_advisor
where
principal_arn = 'arn:aws:iam::aws:policy/AdministratorAccess'
and last_authenticated is not null
order by
age asc;

Show unused services granted to a role, including the policy that grants access and the actions granted

select
adv.service_name,
action as action_granted,
attached as granted_in,
adv.service_namespace
from
aws_iam_access_advisor as adv,
aws_iam_role as r,
jsonb_array_elements_text(r.attached_policy_arns) as attached,
aws_iam_policy as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action
where
principal_arn = 'arn:aws:iam::123456789123:role/turbot/admin'
and r.arn = adv.principal_arn
and last_authenticated is null
and attached = p.arn
and stmt ->> 'Effect' = 'Allow'
and action like adv.service_namespace || ':%'
order by
adv.service_name;

Show action-level last accessed info (currently, only supported for S3)

select
principal_arn,
service_name,
last_authenticated,
age(last_authenticated::date),
a ->> 'ActionName' as action_name,
a ->> 'LastAccessedEntity' as action_last_accessed_entity,
a ->> 'LastAccessedRegion' as action_last_accessed_region,
a ->> 'LastAccessedTime' as action_last_accessed_time
from
aws_iam_access_advisor,
jsonb_array_elements(tracked_actions_last_accessed) as a
where
principal_arn = 'arn:aws:iam::123456789123:user/jane'
and last_authenticated is not null
and service_namespace = 's3'
order by
age asc;

For all users in the account, find unused services

select
principal_arn,
service_name
from
aws_iam_user as u,
aws_iam_access_advisor as adv
where
adv.principal_arn = u.arn
and last_authenticated is null;

.inspect aws_iam_access_advisor

AWS IAM Access Advisor

NameTypeDescription
account_idtextThe AWS Account ID in which the resource is located.
last_authenticatedtimestamp without time zoneThe date and time when an authenticated entity most recently attempted to access the service. AWS does not report unauthenticated requests.
last_authenticated_entitytextThe ARN of the authenticated entity (user or role) that last attempted to access the service. AWS does not report unauthenticated requests.
last_authenticated_regiontextThe Region from which the authenticated entity (user or role) last attempted to access the service. AWS does not report unauthenticated requests.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
principal_arntextThe ARN of the IAM resource (user, group, role, or managed policy) used to generate information about when the resource was last used in an attempt to access an AWS service.
regiontextThe AWS Region in which the resource is located.
service_nametextThe name of the service in which access was attempted.
service_namespacetextThe namespace of the service in which access was attempted.
total_authenticated_entitiesbigintThe total number of authenticated principals (root user, IAM users, or IAM roles) that have attempted to access the service.
tracked_actions_last_accessedjsonbAn array of objects that contains details about the most recent attempt to access a tracked action within the service. Currently, only S3 supports action level tracking.