AWS Organizations and AWS Profiles

AWS Organizations is a service designed to help you centrally manage and govern multiple AWS accounts within your organization. It allows you to group accounts into organizational units (OUs), apply policies for better security, control costs, and ensure compliance. With AWS Organizations, you can automate account creation, set resource policies, and consolidate billing across accounts.

The AWS for Query.Farm integration interacts with AWS Organizations by creating AWS Profiles that automatically obtain access to an organizations account so you can query them without having to manually maintain a list of AWS accounts.

Integrating AWS Organizations into AWS Profiles

Each AWS Profile includes two optional settings:

1. Scan organization accounts: This option creates AWS Profiles for each account within the organization.
2. Retrieve organization details: This retrieves information about the account, such as its name (if set), the master account, and the account’s hierarchy within the organization.

Additionally, you can control the role name used when assuming roles in organization accounts and specify which accounts to exclude from the account list.

These properties can be created and modified when you create the AWS Profile.

Filtering Data by AWS Organization

You can filter AWS resources based on their membership in an AWS Organization by including conditions in the WHERE clause of your query. Each row returned from AWS includes a _aws_profile column and inside of that column’s value is all of the AWS Organization information from the AWS Profile.

For example, to filter EC2 instances that belong to an account named Development:

SELECT * FROM aws.ec2.describe_instances
WHERE _aws_profile.organization.account_name = 'Development';

You can filter not only by account name, but also by account ID, tags, master account, or account hierarchy.

Schema of _aws_profile

This is the schema of the _aws_profile column.

_aws_profile Input Column The AWS profile defines the AWS identity used. It can be defined via credentials or by assuming a IAM role.

STRUCT( "type" VARCHAR, "name" VARCHAR, "account_id" VARCHAR, "via_profile_name" VARCHAR, "assumed_role_arn" VARCHAR, "organization" STRUCT( "account_name" VARCHAR, "id" VARCHAR, "tags" STRUCT( "key" VARCHAR, "value" VARCHAR )[], "master_account" STRUCT( "id" VARCHAR, "email" VARCHAR ), "parents" STRUCT( "type" VARCHAR, "id" VARCHAR, "name" VARCHAR, "tags" STRUCT( "key" VARCHAR, "value" VARCHAR )[] )[] ) )

Show child fields _aws_profile.account_id The AWS account id _aws_profile.assumed_role_arn The ARN of the assumed role _aws_profile.name The unique name of the profile. _aws_profile.organization Information about this profile's membership in the AWS organization. Show child fields _aws_profile.organization.account_name The name of account speciifed by the organization _aws_profile.organization.id The organization id _aws_profile.organization.master_account Show child fields _aws_profile.organization.master_account.email The organization master account email address _aws_profile.organization.master_account.id The organization master account id _aws_profile.organization.parents[] Show child fields _aws_profile.organization.parents[].id The id of the parent _aws_profile.organization.parents[].name The name of the parent _aws_profile.organization.parents[].tags[] Show child fields _aws_profile.organization.parents[].tags[].key _aws_profile.organization.parents[].tags[].value _aws_profile.organization.parents[].type The type of parent can be an organization unit or a root _aws_profile.organization.tags[] Show child fields _aws_profile.organization.tags[].key _aws_profile.organization.tags[].value _aws_profile.type The type of profile, either 'credentials' or 'assumed_role' _aws_profile.via_profile_name This IAM role for this profile is assumed by first utilizing another profile with this name to obtain credentials.