Multiple Accounts and AWS Profiles

Working with multiple AWS accounts is straightforward. You might want to use multiple accounts to compare different environments, such as production and development, and analyze their configurations side by side.

What is an AWS Profile?

An AWS profile stored by Query.Farm is used to obtain access to an AWS account so that API calls can be performed. Profiles can access AWS accounts in two ways: by supplying static credentials or by assuming an IAM role. Each AWS profile has a unique name within its Query.Farm account, defined when the profile is created.

It is common to have multiple profiles setup at the same time, since each is named uniquely it makes it is easy to filter and request results by a particular set of profiles. It is common to have a one profile for a development AWS account and another profile for a production AWS account.

Data Stored Per Profile

An AWS Profile can be thought of as a row in a database table.

Data Common to All Profiles

Field Name	Field Type	Description
Disabled	BOOLEAN	Indicate if this profile is disabled
Allowed Regions	VARCHAR[]	A list of AWS Regions that this profile can utilize, this overrides any detected auto detected regions. This is useful if all activity takes place in a small number of regions
Created	TIMESTAMP	The creation date of the profile
Organization Configuration	STRUCT()	Information about how this AWW Profile should interact with AWS Organizations

AWS Organization Configuration

Each AWS Profile has a subsection of configuration for optionally interacting with AWS Organizations.

Field Name	Field Type	Description
List Accounts	BOOLEAN	A boolean flag that indicates this AWS Profile should list all AWS accounts available and create additional AWS Profiles for those accounts
Get Organization Details	BOOLEAN	A boolean flag that indicates this AWS Profile should attempt to obtain the organization details from the AWS accounts (this includes the master account and account name), but does not list other AWS accounts in the organization.
Static Role Name	VARCHAR	This is the name of the AWS IAM Role that will be used to obtain access to other accounts in the AWS Organization, if the List Accounts flag is set to true
Role Name Lookup	MAP(VARCHAR, VARCHAR)	This is a dictionary that relates a AWS account number to a IAM Role name to obtain access to an account.
Exclude Account Ids	VARCHAR[]	This is a list of AWS Accounts to ignore when the List Accounts flag is set to true.

Credential-Based AWS Profile

A credential-based AWS profile contains the following values:

Field Name	Data Type	Description
AWS Access Key	VARCHAR	The AWS access key generated by AWS.
AWS Secret Access Key	VARCHAR	The secret key used to sign requests to AWS.

IAM Role-Based AWS Profile

An IAM role-based AWS profile contains these values:

Field Name	Data Type	Description
Role ARN	VARCHAR	The ARN of the IAM role to assume.
External ID	VARCHAR	The external ID provided when assuming the role.
Source Identity	VARCHAR	The source identity provided when assuming the role.

AWS Profiles in Query Results

All tables in this service include a column called _aws_profile, which indicates the AWS profile that produced each row.

For example:

SELECT _aws_profile FROM aws.sts.get_caller_identity;

The result might look like this:

_aws_profile = {
  'type': 'assumed_role',
  'name': 'test-account-1',
  'account_id': 1234567890,
  'via_profile_name': NULL,
  'assumed_role_arn': NULL,
  'organization': {
    'account_name': 'Test Account',
    'id': 'o-1234567890',
    'tags': [],
    'master_account': {
      'id': 1234567890,
      'email': 'example@example.com'
    },
    'parents': [
      {
        'type': 'Root',
        'id': 'r-1234567890',
        'name': '',
        'tags': []
      }
    ]
  }
}

Filtering by AWS Profile

When multiple AWS profiles are available, you may want to limit your query to specific profiles. You can do this by adding a WHERE clause to filter by the desired profile.

For example, to query only using the “development” profile:

SELECT * FROM aws.ec2.describe_instances
WHERE _aws_profile.name = 'development';

You can utilize any fields of the AWS Profile to filter data.

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.

Listing Profiles

You can list all profiles with this query.

SELECT * from aws._meta.static_profiles

That will list all profiles with the exclusion of profiles that are created from AWS Organizations.

To list all profiles with organizations run this query

SELECT * from aws._meta.profiles