Table: aws_account - Query AWS Accounts using SQL
The AWS Account is a container for AWS resources. It is used to sign up for, organize, and manage AWS services, and it provides administrative control access to resources. An AWS Account contains its own data, with its own settings, including billing and payment information.
Table Usage Guide
The aws_account
table in Steampipe provides you with information about your AWS Account. This table allows you, as a DevOps engineer, to query account-specific details, including the account status, owner, and associated resources. You can utilize this table to gather insights on your AWS account, such as the account's ARN, creation date, email address, and more. The schema outlines the various attributes of your AWS account, including the account ID, account alias, and whether your account is a root account.
Examples
Basic AWS account info
Discover the segments that are associated with your AWS account, including details about the organization and the master account. This can help you manage and understand the relationships within your AWS structure.This query provides a snapshot of basic details about your AWS account, including its alias and associated organization details. It's useful for quickly accessing key information about your account, particularly in larger organizations where multiple accounts may be in use.
select alias, arn, organization_id, organization_master_account_email, organization_master_account_idfrom aws_account cross join jsonb_array_elements(account_aliases) as alias;
select alias.value as alias, arn, organization_id, organization_master_account_email, organization_master_account_idfrom aws_account, json_each(account_aliases) as alias;
Organization policy of aws account
This query allows you to delve into the various policies within your AWS account, particularly focusing on the type and status of each policy. It's useful for managing and tracking policy configurations across your organization, ensuring compliance and efficient resource utilization.This query is used to understand the types and status of policies available for an AWS organization. This can be beneficial for auditing purposes, ensuring policy compliance across all accounts within the organization.
select organization_id, policy ->> 'Type' as policy_type, policy ->> 'Status' as policy_statusfrom aws_account cross join jsonb_array_elements(organization_available_policy_types) as policy;
select organization_id, json_extract(policy.value, '$.Type') as policy_type, json_extract(policy.value, '$.Status') as policy_statusfrom aws_account, json_each(organization_available_policy_types) as policy;
Query examples
- account_count
- account_table
- acm_certificate_by_account
- api_gatewayv2_api_by_account
- api_gatewayv2_api_table
- cloudfront_distribution_by_account
- cloudtrail_trail_encryption_table
- cloudtrail_trail_logging_table
- codebuild_project_age_table
- codebuild_project_by_account
- codecommit_repository_age_table
- codecommit_repository_by_account
- codepipeline_pipeline_age_table
- codepipeline_pipeline_by_account
- dynamodb_table_by_account
- dynamodb_table_encryption_table
- dynamodb_table_item_count_by_account
- ebs_snapshot_age_table
- ebs_snapshot_by_account
- ebs_snapshot_public_table
- ebs_snapshot_storage_by_account
- ebs_volume_age_table
- ebs_volume_by_account
- ebs_volume_encryption_table
- ebs_volume_storage_by_account
- ec2_instance_age_table
- ec2_instance_by_account
- ec2_instance_public_access_table
- ecr_repository_age_table
- ecr_repository_by_account
- ecs_cluster_by_account
- efs_file_system_by_account
- efs_file_system_table
- eks_cluster_by_account
- elasticache_cluster_node_by_account
- emr_cluster_by_account
- iam_groups_by_account
- iam_roles_by_account
- iam_root_access_keys_table
- iam_user_excessive_permissions_report
- iam_user_mfa_table
- iam_users_by_account
- kms_cmk_lifecycle_table
- kms_key_age_table
- kms_key_by_account
- lambda_function_by_account
- lambda_function_code_size_by_account
- lambda_function_encryption_table
- lambda_function_memory_size_by_account
- lambda_function_public_access_table
- rds_db_cluster_by_account
- rds_db_cluster_encryption_table
- rds_db_cluster_logging_table
- rds_db_cluster_snapshot_by_account
- rds_db_cluster_snapshot_encryption_table
- rds_db_instance_by_account
- rds_db_instance_encryption_table
- rds_db_instance_logging_table
- rds_db_instance_snapshot_by_account
- rds_db_instance_snapshot_encryption_table
- redshift_cluster_by_account
- redshift_cluster_encryption_table
- s3_bucket_age_table
- s3_bucket_by_account
- s3_bucket_lifecycle_table
- s3_bucket_logging_table
- s3_bucket_public_access_table
- sns_topic_by_account
- sns_topic_encryption_table
- sqs_queue_by_account
- sqs_queue_encryption_table
- vpc_by_account
- vpc_security_group_by_acount
Control examples
- 1 CloudTrail should be enabled and configured with at least one multi-Region trail
- 1 Security contact information should be provided for an AWS account
- 1.1 Avoid the use of the "root" account
- 1.1 Maintain current contact details
- 1.1 Maintain current contact details
- 1.1 Maintain current contact details
- 1.1 Maintain current contact details
- 1.1 Maintain current contact details
- 1.10 Ensure IAM password policy prevents password reuse
- 1.11 Ensure IAM password policy expires passwords within 90 days or less
- 1.15 Ensure security questions are registered in the AWS account
- 1.17 Ensure a support role has been created to manage incidents with AWS Support
- 1.17 Ensure a support role has been created to manage incidents with AWS Support
- 1.17 Ensure a support role has been created to manage incidents with AWS Support
- 1.17 Ensure a support role has been created to manage incidents with AWS Support
- 1.17 Ensure a support role has been created to manage incidents with AWS Support
- 1.17 Maintain current contact details
- 1.18 Ensure IAM instance roles are used for AWS resource access from instances
- 1.18 Ensure IAM instance roles are used for AWS resource access from instances
- 1.18 Ensure IAM instance roles are used for AWS resource access from instances
- 1.18 Ensure IAM instance roles are used for AWS resource access from instances
- 1.18 Ensure IAM instance roles are used for AWS resource access from instances
- 1.18 Ensure security contact information is registered
- 1.19 Ensure IAM instance roles are used for AWS resource access from instances
- 1.2 Ensure security contact information is registered
- 1.2 Ensure security contact information is registered
- 1.2 Ensure security contact information is registered
- 1.2 Ensure security contact information is registered
- 1.2 Ensure security contact information is registered
- 1.20 Ensure a support role has been created to manage incidents with AWS Support
- 1.21 Ensure IAM users are managed centrally via identity federation or AWS Organizations for multi-account environments
- 1.21 Ensure IAM users are managed centrally via identity federation or AWS Organizations for multi-account environments
- 1.21 Ensure IAM users are managed centrally via identity federation or AWS Organizations for multi-account environments
- 1.21 Ensure IAM users are managed centrally via identity federation or AWS Organizations for multi-account environments
- 1.22 Ensure IAM users are managed centrally via identity federation or AWS Organizations for multi-account environments
- 1.3 Ensure security questions are registered in the AWS account
- 1.3 Ensure security questions are registered in the AWS account
- 1.3 Ensure security questions are registered in the AWS account
- 1.3 Ensure security questions are registered in the AWS account
- 1.3 Ensure security questions are registered in the AWS account
- 1.5 Ensure IAM password policy requires at least one uppercase letter
- 1.6 Ensure IAM password policy require at least one lowercase letter
- 1.7 Ensure IAM password policy require at least one symbol
- 1.8 Ensure IAM password policy require at least one number
- 1.8 Ensure IAM password policy requires minimum length of 14 or greater
- 1.8 Ensure IAM password policy requires minimum length of 14 or greater
- 1.8 Ensure IAM password policy requires minimum length of 14 or greater
- 1.8 Ensure IAM password policy requires minimum length of 14 or greater
- 1.8 Ensure IAM password policy requires minimum length of 14 or greater
- 1.9 Ensure IAM password policy prevents password reuse
- 1.9 Ensure IAM password policy prevents password reuse
- 1.9 Ensure IAM password policy prevents password reuse
- 1.9 Ensure IAM password policy prevents password reuse
- 1.9 Ensure IAM password policy prevents password reuse
- 1.9 Ensure IAM password policy requires minimum length of 14 or greater
- 2.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.1 Ensure CloudTrail is enabled in all regions
- 3.10 Ensure a log metric filter and alarm exist for security group changes
- 3.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 3.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 3.13 Ensure a log metric filter and alarm exist for route table changes
- 3.14 Ensure a log metric filter and alarm exist for VPC changes
- 3.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 3.3 Ensure a log metric filter and alarm exist for usage of "root" account
- 3.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 3.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 3.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 3.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 3.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 3.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure a log metric filter and alarm exist for unauthorized API calls
- 4.1 Ensure unauthorized API calls are monitored
- 4.1 Ensure unauthorized API calls are monitored
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure a log metric filter and alarm exist for security group changes
- 4.10 Ensure security group changes are monitored
- 4.10 Ensure security group changes are monitored
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- 4.11 Ensure Network Access Control Lists (NACL) changes are monitored
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure a log metric filter and alarm exist for changes to network gateways
- 4.12 Ensure changes to network gateways are monitored
- 4.12 Ensure changes to network gateways are monitored
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure a log metric filter and alarm exist for route table changes
- 4.13 Ensure route table changes are monitored
- 4.13 Ensure route table changes are monitored
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure a log metric filter and alarm exist for VPC changes
- 4.14 Ensure VPC changes are monitored
- 4.14 Ensure VPC changes are monitored
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure a log metric filter and alarm exists for AWS Organizations changes
- 4.15 Ensure AWS Organizations changes are monitored
- 4.15 Ensure AWS Organizations changes are monitored
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure a log metric filter and alarm exist for Management Console sign-in without MFA
- 4.2 Ensure management console sign-in without MFA is monitored
- 4.2 Ensure management console sign-in without MFA is monitored
- 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- 4.3 Ensure a log metric filter and alarm exist for usage of 'root' account
- 4.3 Ensure a log metric filter and alarm exist for usage of "root" account
- 4.3 Ensure usage of 'root' account is monitored
- 4.3 Ensure usage of 'root' account is monitored
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure a log metric filter and alarm exist for IAM policy changes
- 4.4 Ensure IAM policy changes are monitored
- 4.4 Ensure IAM policy changes are monitored
- 4.4 Ensure routing tables for VPC peering are "least access"
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- 4.5 Ensure CloudTrail configuration changes are monitored
- 4.5 Ensure CloudTrail configuration changes are monitored
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- 4.6 Ensure AWS Management Console authentication failures are monitored
- 4.6 Ensure AWS Management Console authentication failures are monitored
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer created CMKs
- 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- 4.7 Ensure disabling or scheduled deletion of customer created CMKs is monitored
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure a log metric filter and alarm exist for S3 bucket policy changes
- 4.8 Ensure S3 bucket policy changes are monitored
- 4.8 Ensure S3 bucket policy changes are monitored
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure a log metric filter and alarm exist for AWS Config configuration changes
- 4.9 Ensure AWS Config configuration changes are monitored
- 4.9 Ensure AWS Config configuration changes are monitored
- 5.4 Ensure routing tables for VPC peering are 'least access'
- 5.4 Ensure routing tables for VPC peering are "least access"
- 5.5 Ensure routing tables for VPC peering are "least access"
- 5.5 Ensure routing tables for VPC peering are "least access"
- 5.5 Ensure routing tables for VPC peering are "least access"
- 7 Password policies for IAM users should have strong configurations
- At least one CloudTrail trail should be enabled in the AWS account
- At least one multi-region AWS CloudTrail should be present in an account
- AWS account should be part of AWS Organizations
- CloudTrail trails should be enabled in all regions
- CloudWatch should not allow cross-account sharing
- Ensure a log metric filter and alarm exist for AWS Config configuration changes
- Ensure a log metric filter and alarm exist for AWS Management Console authentication failures
- Ensure a log metric filter and alarm exist for AWS Management Console sign-in without MFA
- Ensure a log metric filter and alarm exist for changes to Network Access Control Lists (NACL)
- Ensure a log metric filter and alarm exist for changes to network gateways
- Ensure a log metric filter and alarm exist for CloudTrail configuration changes
- Ensure a log metric filter and alarm exist for disabling or scheduled deletion of customer managed keys
- Ensure a log metric filter and alarm exist for IAM policy changes
- Ensure a log metric filter and alarm exist for route table changes
- Ensure a log metric filter and alarm exist for S3 bucket policy changes
- Ensure a log metric filter and alarm exist for security group changes
- Ensure a log metric filter and alarm exist for unauthorized API calls
- Ensure a log metric filter and alarm exist for usage of 'root' account
- Ensure a log metric filter and alarm exist for VPC changes
- Ensure a support role has been created to manage incidents with AWS Support
- Ensure AWS Organizations changes are monitored
- Ensure IAM password policy expires passwords within 90 days or less
- Ensure IAM password policy prevents password reuse
- Ensure IAM password policy requires a minimum length of 14 or greater
- Ensure IAM password policy requires at least one lowercase letter
- Ensure IAM password policy requires at least one number
- Ensure IAM password policy requires at least one symbol
- Ensure IAM password policy requires at least one uppercase letter
- IAM password policies for users should have strong configurations
- IAM Security Audit role should be created to conduct security audits
- Manual verification required
- Password policies for IAM users should have strong configurations with minimum length of 8 or greater
- Security contact information should be provided for an AWS account
- VPCs should exist in multiple regions
Schema for aws_account
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
account_aliases | jsonb | A list of aliases associated with the account, if applicable. | |
account_id | text | =, !=, ~~, ~~*, !~~, !~~* | The AWS Account ID in which the resource is located. |
akas | jsonb | Array of globally unique identifier strings (also known as) for the resource. | |
arn | text | The Amazon Resource Name (ARN) specifying the account. | |
organization_arn | text | The Amazon Resource Name (ARN) of an organization. | |
organization_available_policy_types | jsonb | The Region opt-in status. The possible values are opt-in-not-required, opted-in, and not-opted-in | |
organization_feature_set | text | Specifies the functionality that currently is available to the organization. If set to "ALL", then all features are enabled and policies can be applied to accounts in the organization. If set to "CONSOLIDATED_BILLING", then only consolidated billing functionality is available. | |
organization_id | text | The unique identifier (ID) of an organization, if applicable. | |
organization_master_account_arn | text | The Amazon Resource Name (ARN) of the account that is designated as the management account for the organization | |
organization_master_account_email | text | The email address that is associated with the AWS account that is designated as the management account for the organization | |
organization_master_account_id | text | The unique identifier (ID) of the management account of an organization | |
partition | text | The AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov). | |
region | text | The AWS Region in which the resource is located. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
title | text | Title of the resource. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws
You can pass the configuration to the command with the --config
argument:
steampipe_export_aws --config '<your_config>' aws_account