Skip to content
AWS for Query.Farm

aws.ce.get_cost_and_usage_with_resources

Example SQL Queries

SELECT * FROM
aws.ce.get_cost_and_usage_with_resources
WHERE
"time_period" = 'VALUE'
AND "granularity" = 'VALUE'
AND "filter" = 'VALUE';

Description

Retrieves cost and usage metrics with resources for your account. You can specify which cost and usage-related metric, such as BlendedCosts or UsageQuantity, that you want the request to return. You can also filter and group your data by various dimensions, such as SERVICE or AZ, in a specific time range. For a complete list of valid dimensions, see the GetDimensionValues operation. Management account in an organization in Organizations have access to all member accounts.

Hourly granularity is only available for EC2-Instances (Elastic Compute Cloud) resource-level data. All other resource-level data is available at daily granularity.

This is an opt-in only feature. You can enable this feature from the Cost Explorer Settings page. For information about how to access the Settings page, see Controlling Access for Cost Explorer in the Billing and Cost Management User Guide.

Table Definition

Column NameColumn Data Type
filter Required Input Column

Filters Amazon Web Services costs by different dimensions. For example, you can specify SERVICE and LINKED_ACCOUNT and get the costs that are associated with that account's usage of that service. You can nest Expression objects to define any combination of dimension filters. For more information, see Expression.

The GetCostAndUsageWithResources operation requires that you either group by or filter by a ResourceId. It requires the Expression "SERVICE = Amazon Elastic Compute Cloud - Compute" in the filter.

Valid values for MatchOptions for Dimensions are EQUALS and CASE_SENSITIVE.

Valid values for MatchOptions for CostCategories and Tags are EQUALS, ABSENT, and CASE_SENSITIVE. Default values are EQUALS and CASE_SENSITIVE.

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 Required Input Column

Sets the Amazon Web Services cost granularity to MONTHLY, DAILY, or HOURLY. If Granularity isn't set, the response object doesn't include the Granularity, MONTHLY, DAILY, or HOURLY. 

VARCHAR
time_period Required Input Column

Sets the start and end dates for retrieving Amazon Web Services costs. The range must be within the last 14 days (the start date cannot be earlier than 14 days ago). 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.

group_by Input Column

You can group Amazon Web Services costs using up to two different groups: DIMENSION, TAG, COST_CATEGORY.

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.

metrics Input Column

Which metrics are returned in the query. For more information about blended and unblended rates, see Why does the "blended" annotation appear on some line items in my bill?.

Valid values are AmortizedCost, BlendedCost, NetAmortizedCost, NetUnblendedCost, NormalizedUsageAmount, UnblendedCost, and UsageQuantity.

If you return the UsageQuantity metric, the service aggregates all usage numbers without taking the units into account. For example, if you aggregate usageQuantity across all of Amazon EC2, the results aren't meaningful because Amazon EC2 compute hours and data transfer are measured in different units (for example, hour or GB). To get more meaningful UsageQuantity metrics, filter by UsageType or UsageTypeGroups.

Metrics is required for GetCostAndUsageWithResources requests.

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

dimension_value_attributes

The attributes that apply to a specific dimension value. For example, if the value is a linked account, the attribute is that account name.

STRUCT(
    "value" VARCHAR,
    "attributes" MAP(VARCHAR, VARCHAR)
)[]
Show child fields
dimension_value_attributes[]
Show child fields
dimension_value_attributes[].attributes

The attribute that applies to a specific Dimension.

dimension_value_attributes[].value

The value of a dimension with a specific attribute.

group_definitions

The groups that are specified by the Filter or GroupBy parameters in the request.

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

The string that represents a key for a specified group.

group_definitions[].type

The string that represents the type of group.

results_by_time

The time period that's covered by the results in the response.

STRUCT(
    "time_period" STRUCT(
        "start" VARCHAR,
        "end" VARCHAR
    ),
    "total" MAP(VARCHAR, STRUCT(
            "amount" VARCHAR,
            "unit" VARCHAR
        )),
    "groups" STRUCT(
        "keys" VARCHAR[],
        "metrics" MAP(VARCHAR, STRUCT(
                "amount" VARCHAR,
                "unit" VARCHAR
            ))
    )[],
    "estimated" BOOLEAN
)[]
Show child fields
results_by_time[]
Show child fields
results_by_time[].estimated

Determines whether the result is estimated.

results_by_time[].groups[]
Show child fields
results_by_time[].groups[].keys[]
results_by_time[].groups[].metrics

The metrics that are included in this group.

results_by_time[].time_period

The time period that the result covers.

Show child fields
results_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.

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

results_by_time[].total

The total amount of cost or usage accrued during the time period.