Skip to content
AWS for Query.Farm

aws.ce.get_reservation_coverage

Example SQL Queries

SELECT * FROM
aws.ce.get_reservation_coverage
WHERE
"time_period" = 'VALUE';

Description

Retrieves the reservation coverage for your account, which you can use to see how much of your Amazon Elastic Compute Cloud, Amazon ElastiCache, Amazon Relational Database Service, or Amazon Redshift usage is covered by a reservation. An organization's management account can see the coverage of the associated member accounts. This supports dimensions, Cost Categories, and nested expressions. For any time period, you can filter data about reservation usage by the following dimensions:

  • AZ

  • CACHE_ENGINE

  • DATABASE_ENGINE

  • DEPLOYMENT_OPTION

  • INSTANCE_TYPE

  • LINKED_ACCOUNT

  • OPERATING_SYSTEM

  • PLATFORM

  • REGION

  • SERVICE

  • TAG

  • TENANCY

To determine valid values for a dimension, use the GetDimensionValues operation.

Table Definition

Column NameColumn Data Type
time_period Required Input Column

The start and end dates of the period that you want to retrieve data about reservation coverage for. You can retrieve data for a maximum of 13 months: the last 12 months and the current month. The start date is inclusive, but the end date is exclusive. For example, if start is 2017-01-01 and end is 2017-05-01, then the cost and usage data is retrieved from 2017-01-01 up to and including 2017-04-30 but not including 2017-05-01. 

STRUCT(
    "start" VARCHAR,
    "end" VARCHAR
)
Show child fields
time_period.end

The end of the time period. The end date is exclusive. For example, if end is 2017-05-01, Amazon Web Services retrieves cost and usage data from the start date up to, but not including, 2017-05-01.

time_period.start

The beginning of the time period. The start date is inclusive. For example, if start is 2017-01-01, Amazon Web Services retrieves cost and usage data starting at 2017-01-01 up to the end date. The start date must be equal to or no later than the current date to avoid a validation error.

filter Input Column

Filters utilization data by dimensions. You can filter by the following dimensions:

  • AZ

  • CACHE_ENGINE

  • DATABASE_ENGINE

  • DEPLOYMENT_OPTION

  • INSTANCE_TYPE

  • LINKED_ACCOUNT

  • OPERATING_SYSTEM

  • PLATFORM

  • REGION

  • SERVICE

  • TAG

  • TENANCY

GetReservationCoverage uses the same Expression object as the other operations, but only AND is supported among each dimension. You can nest only one level deep. If there are multiple values for a dimension, they are OR'd together.

If you don't provide a SERVICE filter, Cost Explorer defaults to EC2.

Cost category is also supported.

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[]
granularity Input Column

The granularity of the Amazon Web Services cost data for the reservation. Valid values are MONTHLY and DAILY.

If GroupBy is set, Granularity can't be set. If Granularity isn't set, the response object doesn't include Granularity, either MONTHLY or DAILY.

The GetReservationCoverage operation supports only DAILY and MONTHLY granularities.

VARCHAR
group_by Input Column

You can group the data by the following attributes:

  • AZ

  • CACHE_ENGINE

  • DATABASE_ENGINE

  • DEPLOYMENT_OPTION

  • INSTANCE_TYPE

  • INVOICING_ENTITY

  • LINKED_ACCOUNT

  • OPERATING_SYSTEM

  • PLATFORM

  • REGION

  • TENANCY

STRUCT(
    "type" VARCHAR,
    "key" VARCHAR
)[]
Show child fields
group_by[]
Show child fields
group_by[].key

The string that represents a key for a specified group.

group_by[].type

The string that represents the type of group.

max_results Input Column

The maximum number of objects that you returned for this request. If more objects are available, in the response, Amazon Web Services provides a NextPageToken value that you can use in a subsequent call to get the next batch of objects.

BIGINT
metrics Input Column

The measurement that you want your reservation coverage reported in.

Valid values are Hour, Unit, and Cost. You can use multiple values in a request.

VARCHAR[]
Show child fields
metrics[]
next_page_token Input Column

The token for the next set of retrievable results. Amazon Web Services provides the token when the response from a previous call has more results than the maximum page size.

VARCHAR
sort_by Input Column

The value by which you want to sort the data.

The following values are supported for Key:

  • OnDemandCost

  • CoverageHoursPercentage

  • OnDemandHours

  • ReservedHours

  • TotalRunningHours

  • CoverageNormalizedUnitsPercentage

  • OnDemandNormalizedUnits

  • ReservedNormalizedUnits

  • TotalRunningNormalizedUnits

  • Time

Supported values for SortOrder are ASCENDING or DESCENDING.

STRUCT(
    "key" VARCHAR,
    "sort_order" VARCHAR
)
Show child fields
sort_by.key

The key that's used to sort the data.

sort_by.sort_order

The order that's used to sort the data.

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

coverages_by_time

The amount of time that your reservations covered.

STRUCT(
    "time_period" STRUCT(
        "start" VARCHAR,
        "end" VARCHAR
    ),
    "groups" STRUCT(
        "attributes" MAP(VARCHAR, VARCHAR),
        "coverage" STRUCT(
            "coverage_hours" STRUCT(
                "on_demand_hours" VARCHAR,
                "reserved_hours" VARCHAR,
                "total_running_hours" VARCHAR,
                "coverage_hours_percentage" VARCHAR
            ),
            "coverage_normalized_units" STRUCT(
                "on_demand_normalized_units" VARCHAR,
                "reserved_normalized_units" VARCHAR,
                "total_running_normalized_units" VARCHAR,
                "coverage_normalized_units_percentage" VARCHAR
            ),
            "coverage_cost" STRUCT(
                "on_demand_cost" VARCHAR
            )
        )
    )[],
    "total" STRUCT(
        "coverage_hours" STRUCT(
            "on_demand_hours" VARCHAR,
            "reserved_hours" VARCHAR,
            "total_running_hours" VARCHAR,
            "coverage_hours_percentage" VARCHAR
        ),
        "coverage_normalized_units" STRUCT(
            "on_demand_normalized_units" VARCHAR,
            "reserved_normalized_units" VARCHAR,
            "total_running_normalized_units" VARCHAR,
            "coverage_normalized_units_percentage" VARCHAR
        ),
        "coverage_cost" STRUCT(
            "on_demand_cost" VARCHAR
        )
    )
)[]
Show child fields
coverages_by_time[]
Show child fields
coverages_by_time[].groups[]
Show child fields
coverages_by_time[].groups[].attributes

The attributes for this group of reservations.

coverages_by_time[].groups[].coverage

How much instance usage this group of reservations covered.

Show child fields
coverages_by_time[].groups[].coverage.coverage_cost

The amount of cost that the reservation covered.

Show child fields
coverages_by_time[].groups[].coverage.coverage_cost.on_demand_cost

How much an On-Demand Instance costs.

coverages_by_time[].groups[].coverage.coverage_hours

The amount of instance usage that the reservation covered, in hours.

Show child fields
coverages_by_time[].groups[].coverage.coverage_hours.coverage_hours_percentage

The percentage of instance hours that a reservation covered.

coverages_by_time[].groups[].coverage.coverage_hours.on_demand_hours

The number of instance running hours that On-Demand Instances covered.

coverages_by_time[].groups[].coverage.coverage_hours.reserved_hours

The number of instance running hours that reservations covered.

coverages_by_time[].groups[].coverage.coverage_hours.total_running_hours

The total instance usage, in hours.

coverages_by_time[].groups[].coverage.coverage_normalized_units

The amount of instance usage that the reservation covered, in normalized units.

Show child fields
coverages_by_time[].groups[].coverage.coverage_normalized_units.coverage_normalized_units_percentage

The percentage of your used instance normalized units that a reservation covers.

coverages_by_time[].groups[].coverage.coverage_normalized_units.on_demand_normalized_units

The number of normalized units that are covered by On-Demand Instances instead of a reservation.

coverages_by_time[].groups[].coverage.coverage_normalized_units.reserved_normalized_units

The number of normalized units that a reservation covers.

coverages_by_time[].groups[].coverage.coverage_normalized_units.total_running_normalized_units

The total number of normalized units that you used.

coverages_by_time[].time_period

The period that this coverage was used over.

Show child fields
coverages_by_time[].time_period.end

The end of the time period. The end date is exclusive. For example, if end is 2017-05-01, Amazon Web Services retrieves cost and usage data from the start date up to, but not including, 2017-05-01.

coverages_by_time[].time_period.start

The beginning of the time period. The start date is inclusive. For example, if start is 2017-01-01, Amazon Web Services retrieves cost and usage data starting at 2017-01-01 up to the end date. The start date must be equal to or no later than the current date to avoid a validation error.

coverages_by_time[].total

The total reservation coverage, in hours.

Show child fields
coverages_by_time[].total.coverage_cost

The amount of cost that the reservation covered.

Show child fields
coverages_by_time[].total.coverage_cost.on_demand_cost

How much an On-Demand Instance costs.

coverages_by_time[].total.coverage_hours

The amount of instance usage that the reservation covered, in hours.

Show child fields
coverages_by_time[].total.coverage_hours.coverage_hours_percentage

The percentage of instance hours that a reservation covered.

coverages_by_time[].total.coverage_hours.on_demand_hours

The number of instance running hours that On-Demand Instances covered.

coverages_by_time[].total.coverage_hours.reserved_hours

The number of instance running hours that reservations covered.

coverages_by_time[].total.coverage_hours.total_running_hours

The total instance usage, in hours.

coverages_by_time[].total.coverage_normalized_units

The amount of instance usage that the reservation covered, in normalized units.

Show child fields
coverages_by_time[].total.coverage_normalized_units.coverage_normalized_units_percentage

The percentage of your used instance normalized units that a reservation covers.

coverages_by_time[].total.coverage_normalized_units.on_demand_normalized_units

The number of normalized units that are covered by On-Demand Instances instead of a reservation.

coverages_by_time[].total.coverage_normalized_units.reserved_normalized_units

The number of normalized units that a reservation covers.

coverages_by_time[].total.coverage_normalized_units.total_running_normalized_units

The total number of normalized units that you used.

total

The total amount of instance usage that a reservation covered.

STRUCT(
    "coverage_hours" STRUCT(
        "on_demand_hours" VARCHAR,
        "reserved_hours" VARCHAR,
        "total_running_hours" VARCHAR,
        "coverage_hours_percentage" VARCHAR
    ),
    "coverage_normalized_units" STRUCT(
        "on_demand_normalized_units" VARCHAR,
        "reserved_normalized_units" VARCHAR,
        "total_running_normalized_units" VARCHAR,
        "coverage_normalized_units_percentage" VARCHAR
    ),
    "coverage_cost" STRUCT(
        "on_demand_cost" VARCHAR
    )
)
Show child fields
total.coverage_cost

The amount of cost that the reservation covered.

Show child fields
total.coverage_cost.on_demand_cost

How much an On-Demand Instance costs.

total.coverage_hours

The amount of instance usage that the reservation covered, in hours.

Show child fields
total.coverage_hours.coverage_hours_percentage

The percentage of instance hours that a reservation covered.

total.coverage_hours.on_demand_hours

The number of instance running hours that On-Demand Instances covered.

total.coverage_hours.reserved_hours

The number of instance running hours that reservations covered.

total.coverage_hours.total_running_hours

The total instance usage, in hours.

total.coverage_normalized_units

The amount of instance usage that the reservation covered, in normalized units.

Show child fields
total.coverage_normalized_units.coverage_normalized_units_percentage

The percentage of your used instance normalized units that a reservation covers.

total.coverage_normalized_units.on_demand_normalized_units

The number of normalized units that are covered by On-Demand Instances instead of a reservation.

total.coverage_normalized_units.reserved_normalized_units

The number of normalized units that a reservation covers.

total.coverage_normalized_units.total_running_normalized_units

The total number of normalized units that you used.