Skip to content
AWS for Query.Farm

aws.ce.get_reservation_purchase_recommendation

Example SQL Queries

SELECT * FROM
aws.ce.get_reservation_purchase_recommendation
WHERE
"service" = 'VALUE';

Description

Gets recommendations for reservation purchases. These recommendations might help you to reduce your costs. Reservations provide a discounted hourly rate (up to 75%) compared to On-Demand pricing.

Amazon Web Services generates your recommendations by identifying your On-Demand usage during a specific time period and collecting your usage into categories that are eligible for a reservation. After Amazon Web Services has these categories, it simulates every combination of reservations in each category of usage to identify the best number of each type of Reserved Instance (RI) to purchase to maximize your estimated savings.

For example, Amazon Web Services automatically aggregates your Amazon EC2 Linux, shared tenancy, and c4 family usage in the US West (Oregon) Region and recommends that you buy size-flexible regional reservations to apply to the c4 family usage. Amazon Web Services recommends the smallest size instance in an instance family. This makes it easier to purchase a size-flexible Reserved Instance (RI). Amazon Web Services also shows the equal number of normalized units. This way, you can purchase any instance size that you want. For this example, your RI recommendation is for c4.large because that is the smallest size instance in the c4 instance family.

Table Definition

Column NameColumn Data Type
service Required Input Column

The specific service that you want recommendations for.

VARCHAR
account_id Input Column

The account ID that's associated with the recommendation. 

VARCHAR
account_scope Input Column

The account scope that you want your recommendations for. Amazon Web Services calculates recommendations including the management account and member accounts if the value is set to PAYER. If the value is LINKED, recommendations are calculated for individual member accounts only.

VARCHAR
filter Input Column

Use Expression to filter in various Cost Explorer APIs.

Not all Expression types are supported in each API. Refer to the documentation for each specific API to see what is supported.

There are two patterns:

  • Simple dimension values.

    • There are three types of simple dimension values: CostCategories, Tags, and Dimensions.

      • Specify the CostCategories field to define a filter that acts on Cost Categories.

      • Specify the Tags field to define a filter that acts on Cost Allocation Tags.

      • Specify the Dimensions field to define a filter that acts on the DimensionValues .

    • For each filter type, you can set the dimension name and values for the filters that you plan to use.

      • For example, you can filter for REGION==us-east-1 OR REGION==us-west-1. For GetRightsizingRecommendation, the Region is a full name (for example, REGION==US East (N. Virginia).

      • The corresponding Expression for this example is as follows: { "Dimensions": { "Key": "REGION", "Values": [ "us-east-1", "us-west-1" ] } }

      • As shown in the previous example, lists of dimension values are combined with OR when applying the filter.

    • You can also set different match options to further control how the filter behaves. Not all APIs support match options. Refer to the documentation for each specific API to see what is supported.

      • For example, you can filter for linked account names that start with "a".

      • The corresponding Expression for this example is as follows: { "Dimensions": { "Key": "LINKED_ACCOUNT_NAME", "MatchOptions": [ "STARTS_WITH" ], "Values": [ "a" ] } }

  • Compound Expression types with logical operations.

    • You can use multiple Expression types and the logical operators AND/OR/NOT to create a list of one or more Expression objects. By doing this, you can filter by more advanced options.

    • For example, you can filter by ((REGION == us-east-1 OR REGION == us-west-1) OR (TAG.Type == Type1)) AND (USAGE_TYPE != DataTransfer).

    • The corresponding Expression for this example is as follows: { "And": [ {"Or": [ {"Dimensions": { "Key": "REGION", "Values": [ "us-east-1", "us-west-1" ] }}, {"Tags": { "Key": "TagName", "Values": ["Value1"] } } ]}, {"Not": {"Dimensions": { "Key": "USAGE_TYPE", "Values": ["DataTransfer"] }}} ] }

    Because each Expression can have only one operator, the service returns an error if more than one is specified. The following example shows an Expression object that creates an error: { "And": [ ... ], "Dimensions": { "Key": "USAGE_TYPE", "Values": [ "DataTransfer" ] } }

    The following is an example of the corresponding error message: "Expression has more than one roots. Only one root operator is allowed for each expression: And, Or, Not, Dimensions, Tags, CostCategories"

For the GetRightsizingRecommendation action, a combination of OR and NOT isn't supported. OR isn't supported between different dimensions, or dimensions and tags. NOT operators aren't supported. Dimensions are also limited to LINKED_ACCOUNT, REGION, or RIGHTSIZING_TYPE.

For the GetReservationPurchaseRecommendation action, only NOT is supported. AND and OR aren't supported. Dimensions are limited to LINKED_ACCOUNT.

STRUCT(
    "dimensions" STRUCT(
        "key" VARCHAR,
        "values" VARCHAR[],
        "match_options" VARCHAR[]
    ),
    "tags" STRUCT(
        "key" VARCHAR,
        "values" VARCHAR[],
        "match_options" VARCHAR[]
    ),
    "cost_categories" STRUCT(
        "key" VARCHAR,
        "values" VARCHAR[],
        "match_options" VARCHAR[]
    )
)
Show child fields
filter.cost_categories

The filter that's based on CostCategory values.

Show child fields
filter.cost_categories.key

The unique name of the Cost Category.

filter.cost_categories.match_options[]
filter.cost_categories.values[]
filter.dimensions

The specific Dimension to use for Expression.

Show child fields
filter.dimensions.key

The names of the metadata types that you can use to filter and group your results. For example, AZ returns a list of Availability Zones.

Not all dimensions are supported in each API. Refer to the documentation for each specific API to see what is supported.

LINK_ACCOUNT_NAME and SERVICE_CODE can only be used in CostCategoryRule.

ANOMALY_TOTAL_IMPACT_ABSOLUTE and ANOMALY_TOTAL_IMPACT_PERCENTAGE can only be used in AnomalySubscriptions.

filter.dimensions.match_options[]
filter.dimensions.values[]
filter.tags

The specific Tag to use for Expression.

Show child fields
filter.tags.key

The key for the tag.

filter.tags.match_options[]
filter.tags.values[]
lookback_period_in_days Input Column

The number of previous days that you want Amazon Web Services to consider when it calculates your recommendations.

VARCHAR
next_page_token Input Column

The pagination token for the next set of retrievable results.

VARCHAR
page_size Input Column

The number of recommendations that you want returned in a single response object.

BIGINT
payment_option Input Column

The reservation purchase option that you want recommendations for.

VARCHAR
service_specification Input Column

The hardware specifications for the service instances that you want recommendations for, such as standard or convertible Amazon EC2 instances.

STRUCT(
    "ec2_specification" STRUCT(
        "offering_class" VARCHAR
    )
)
Show child fields
service_specification.ec2_specification

The Amazon EC2 hardware specifications that you want Amazon Web Services to provide recommendations for.

Show child fields
service_specification.ec2_specification.offering_class

Indicates whether you want a recommendation for standard or convertible reservations.

term_in_years Input Column

The reservation term that you want recommendations for.

VARCHAR
_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.

metadata

Information about this specific recommendation call, such as the time stamp for when Cost Explorer generated this recommendation.

STRUCT(
    "recommendation_id" VARCHAR,
    "generation_timestamp" VARCHAR,
    "additional_metadata" VARCHAR
)
Show child fields
metadata.additional_metadata

Additional metadata that might be applicable to the recommendation.

metadata.generation_timestamp

The timestamp for when Amazon Web Services made the recommendation.

metadata.recommendation_id

The ID for the recommendation.

recommendations

Recommendations for reservations to purchase.

STRUCT(
    "account_scope" VARCHAR,
    "lookback_period_in_days" VARCHAR,
    "term_in_years" VARCHAR,
    "payment_option" VARCHAR,
    "service_specification" STRUCT(
        "ec2_specification" STRUCT(
            "offering_class" VARCHAR
        )
    ),
    "recommendation_details" STRUCT(
        "account_id" VARCHAR,
        "instance_details" STRUCT(
            "ec2_instance_details" STRUCT(
                "family" VARCHAR,
                "instance_type" VARCHAR,
                "region" VARCHAR,
                "availability_zone" VARCHAR,
                "platform" VARCHAR,
                "tenancy" VARCHAR,
                "current_generation" BOOLEAN,
                "size_flex_eligible" BOOLEAN
            ),
            "rds_instance_details" STRUCT(
                "family" VARCHAR,
                "instance_type" VARCHAR,
                "region" VARCHAR,
                "database_engine" VARCHAR,
                "database_edition" VARCHAR,
                "deployment_option" VARCHAR,
                "license_model" VARCHAR,
                "current_generation" BOOLEAN,
                "size_flex_eligible" BOOLEAN
            ),
            "redshift_instance_details" STRUCT(
                "family" VARCHAR,
                "node_type" VARCHAR,
                "region" VARCHAR,
                "current_generation" BOOLEAN,
                "size_flex_eligible" BOOLEAN
            ),
            "elasti_cache_instance_details" STRUCT(
                "family" VARCHAR,
                "node_type" VARCHAR,
                "region" VARCHAR,
                "product_description" VARCHAR,
                "current_generation" BOOLEAN,
                "size_flex_eligible" BOOLEAN
            ),
            "es_instance_details" STRUCT(
                "instance_class" VARCHAR,
                "instance_size" VARCHAR,
                "region" VARCHAR,
                "current_generation" BOOLEAN,
                "size_flex_eligible" BOOLEAN
            ),
            "memory_db_instance_details" STRUCT(
                "family" VARCHAR,
                "node_type" VARCHAR,
                "region" VARCHAR,
                "current_generation" BOOLEAN,
                "size_flex_eligible" BOOLEAN
            )
        ),
        "recommended_number_of_instances_to_purchase" VARCHAR,
        "recommended_normalized_units_to_purchase" VARCHAR,
        "minimum_number_of_instances_used_per_hour" VARCHAR,
        "minimum_normalized_units_used_per_hour" VARCHAR,
        "maximum_number_of_instances_used_per_hour" VARCHAR,
        "maximum_normalized_units_used_per_hour" VARCHAR,
        "average_number_of_instances_used_per_hour" VARCHAR,
        "average_normalized_units_used_per_hour" VARCHAR,
        "average_utilization" VARCHAR,
        "estimated_break_even_in_months" VARCHAR,
        "currency_code" VARCHAR,
        "estimated_monthly_savings_amount" VARCHAR,
        "estimated_monthly_savings_percentage" VARCHAR,
        "estimated_monthly_on_demand_cost" VARCHAR,
        "estimated_reservation_cost_for_lookback_period" VARCHAR,
        "upfront_cost" VARCHAR,
        "recurring_standard_monthly_cost" VARCHAR
    )[],
    "recommendation_summary" STRUCT(
        "total_estimated_monthly_savings_amount" VARCHAR,
        "total_estimated_monthly_savings_percentage" VARCHAR,
        "currency_code" VARCHAR
    )
)[]
Show child fields
recommendations[]
Show child fields
recommendations[].account_scope

The account scope that Amazon Web Services recommends that you purchase this instance for. For example, you can purchase this reservation for an entire organization in Amazon Web Services Organizations.

recommendations[].lookback_period_in_days

How many days of previous usage that Amazon Web Services considers when making this recommendation.

recommendations[].payment_option

The payment option for the reservation (for example, AllUpfront or NoUpfront).

recommendations[].recommendation_details[]
Show child fields
recommendations[].recommendation_details[].account_id

The account that this Reserved Instance (RI) recommendation is for.

recommendations[].recommendation_details[].average_normalized_units_used_per_hour

The average number of normalized units that you used in an hour during the historical period. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].average_number_of_instances_used_per_hour

The average number of instances that you used in an hour during the historical period. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].average_utilization

The average utilization of your instances. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].currency_code

The currency code that Amazon Web Services used to calculate the costs for this instance.

recommendations[].recommendation_details[].estimated_break_even_in_months

How long Amazon Web Services estimates that it takes for this instance to start saving you money, in months.

recommendations[].recommendation_details[].estimated_monthly_on_demand_cost

How much Amazon Web Services estimates that you spend on On-Demand Instances in a month.

recommendations[].recommendation_details[].estimated_monthly_savings_amount

How much Amazon Web Services estimates that this specific recommendation might save you in a month.

recommendations[].recommendation_details[].estimated_monthly_savings_percentage

How much Amazon Web Services estimates that this specific recommendation might save you in a month, as a percentage of your overall costs.

recommendations[].recommendation_details[].estimated_reservation_cost_for_lookback_period

How much Amazon Web Services estimates that you might spend for all usage during the specified historical period if you had a reservation.

recommendations[].recommendation_details[].instance_details

Details about the reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.ec2_instance_details

The Amazon EC2 reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.ec2_instance_details.availability_zone

The Availability Zone of the recommended reservation.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.current_generation

Determines whether the recommendation is for a current-generation instance.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.family

The instance family of the recommended reservation.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.instance_type

The type of instance that Amazon Web Services recommends.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.platform

The platform of the recommended reservation. The platform is the specific combination of operating system, license model, and software on an instance.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.region

The Amazon Web Services Region of the recommended reservation.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.size_flex_eligible

Determines whether the recommended reservation is size flexible.

recommendations[].recommendation_details[].instance_details.ec2_instance_details.tenancy

Determines whether the recommended reservation is dedicated or shared.

recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details

The ElastiCache reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details.current_generation

Determines whether the recommendation is for a current generation instance.

recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details.family

The instance family of the recommended reservation.

recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details.node_type

The type of node that Amazon Web Services recommends.

recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details.product_description

The description of the recommended reservation.

recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details.region

The Amazon Web Services Region of the recommended reservation.

recommendations[].recommendation_details[].instance_details.elasti_cache_instance_details.size_flex_eligible

Determines whether the recommended reservation is size flexible.

recommendations[].recommendation_details[].instance_details.es_instance_details

The Amazon OpenSearch Service reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.es_instance_details.current_generation

Determines whether the recommendation is for a current-generation instance.

recommendations[].recommendation_details[].instance_details.es_instance_details.instance_class

The class of instance that Amazon Web Services recommends.

recommendations[].recommendation_details[].instance_details.es_instance_details.instance_size

The size of instance that Amazon Web Services recommends.

recommendations[].recommendation_details[].instance_details.es_instance_details.region

The Amazon Web Services Region of the recommended reservation.

recommendations[].recommendation_details[].instance_details.es_instance_details.size_flex_eligible

Determines whether the recommended reservation is size flexible.

recommendations[].recommendation_details[].instance_details.memory_db_instance_details

The MemoryDB reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.memory_db_instance_details.current_generation

Determines whether the recommendation is for a current generation instance.

recommendations[].recommendation_details[].instance_details.memory_db_instance_details.family

The instance family of the recommended reservation.

recommendations[].recommendation_details[].instance_details.memory_db_instance_details.node_type

The node type of the recommended reservation.

recommendations[].recommendation_details[].instance_details.memory_db_instance_details.region

The Amazon Web Services Region of the recommended reservation.

recommendations[].recommendation_details[].instance_details.memory_db_instance_details.size_flex_eligible

Determines whether the recommended reservation is size flexible.

recommendations[].recommendation_details[].instance_details.rds_instance_details

The Amazon RDS reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.rds_instance_details.current_generation

Determines whether the recommendation is for a current-generation instance.

recommendations[].recommendation_details[].instance_details.rds_instance_details.database_edition

The database edition that the recommended reservation supports.

recommendations[].recommendation_details[].instance_details.rds_instance_details.database_engine

The database engine that the recommended reservation supports.

recommendations[].recommendation_details[].instance_details.rds_instance_details.deployment_option

Determines whether the recommendation is for a reservation in a single Availability Zone or a reservation with a backup in a second Availability Zone.

recommendations[].recommendation_details[].instance_details.rds_instance_details.family

The instance family of the recommended reservation.

recommendations[].recommendation_details[].instance_details.rds_instance_details.instance_type

The type of instance that Amazon Web Services recommends.

recommendations[].recommendation_details[].instance_details.rds_instance_details.license_model

The license model that the recommended reservation supports.

recommendations[].recommendation_details[].instance_details.rds_instance_details.region

The Amazon Web Services Region of the recommended reservation.

recommendations[].recommendation_details[].instance_details.rds_instance_details.size_flex_eligible

Determines whether the recommended reservation is size flexible.

recommendations[].recommendation_details[].instance_details.redshift_instance_details

The Amazon Redshift reservations that Amazon Web Services recommends that you purchase.

Show child fields
recommendations[].recommendation_details[].instance_details.redshift_instance_details.current_generation

Determines whether the recommendation is for a current-generation instance.

recommendations[].recommendation_details[].instance_details.redshift_instance_details.family

The instance family of the recommended reservation.

recommendations[].recommendation_details[].instance_details.redshift_instance_details.node_type

The type of node that Amazon Web Services recommends.

recommendations[].recommendation_details[].instance_details.redshift_instance_details.region

The Amazon Web Services Region of the recommended reservation.

recommendations[].recommendation_details[].instance_details.redshift_instance_details.size_flex_eligible

Determines whether the recommended reservation is size flexible.

recommendations[].recommendation_details[].maximum_normalized_units_used_per_hour

The maximum number of normalized units that you used in an hour during the historical period. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].maximum_number_of_instances_used_per_hour

The maximum number of instances that you used in an hour during the historical period. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].minimum_normalized_units_used_per_hour

The minimum number of normalized units that you used in an hour during the historical period. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].minimum_number_of_instances_used_per_hour

The minimum number of instances that you used in an hour during the historical period. Amazon Web Services uses this to calculate your recommended reservation purchases.

recommendations[].recommendation_details[].recommended_normalized_units_to_purchase

The number of normalized units that Amazon Web Services recommends that you purchase.

recommendations[].recommendation_details[].recommended_number_of_instances_to_purchase

The number of instances that Amazon Web Services recommends that you purchase.

recommendations[].recommendation_details[].recurring_standard_monthly_cost

How much purchasing this instance costs you on a monthly basis.

recommendations[].recommendation_details[].upfront_cost

How much purchasing this instance costs you upfront.

recommendations[].recommendation_summary

A summary about the recommended purchase.

Show child fields
recommendations[].recommendation_summary.currency_code

The currency code used for this recommendation.

recommendations[].recommendation_summary.total_estimated_monthly_savings_amount

The total amount that Amazon Web Services estimates that this recommendation could save you in a month.

recommendations[].recommendation_summary.total_estimated_monthly_savings_percentage

The total amount that Amazon Web Services estimates that this recommendation could save you in a month, as a percentage of your costs.

recommendations[].service_specification

Hardware specifications for the service that you want recommendations for.

Show child fields
recommendations[].service_specification.ec2_specification

The Amazon EC2 hardware specifications that you want Amazon Web Services to provide recommendations for.

Show child fields
recommendations[].service_specification.ec2_specification.offering_class

Indicates whether you want a recommendation for standard or convertible reservations.

recommendations[].term_in_years

The term of the reservation that you want recommendations for, in years.