Skip to content
AWS for Query.Farm

aws.ce.get_cost_and_usage

Example SQL Queries

SELECT * FROM
aws.ce.get_cost_and_usage
WHERE
"time_period" = 'VALUE'
AND "granularity" = 'VALUE'
AND "metrics" = 'VALUE';

Description

Retrieves cost and usage metrics for your account. You can specify which cost and usage-related metric that you want the request to return. For example, you can specify BlendedCosts or UsageQuantity. 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.

For information about filter limitations, see Quotas and restrictions in the Billing and Cost Management User Guide.

Table Definition

Column NameColumn Data Type
granularity Required Input Column

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

VARCHAR
metrics Required 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 into account the units. 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, hours and GB). To get more meaningful UsageQuantity metrics, filter by UsageType or UsageTypeGroups.

Metrics is required for GetCostAndUsage requests.

VARCHAR[]
Show child fields
metrics[]
time_period Required Input Column

Sets the start date and end date for retrieving Amazon Web Services costs. 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 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.

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

You can group Amazon Web Services costs using up to two different groups, either dimensions, tag keys, cost categories, or any two group by types.

Valid values for the DIMENSION type are AZ, INSTANCE_TYPE, LEGAL_ENTITY_NAME, INVOICING_ENTITY, LINKED_ACCOUNT, OPERATION, PLATFORM, PURCHASE_TYPE, SERVICE, TENANCY, RECORD_TYPE, and USAGE_TYPE.

When you group by the TAG type and include a valid tag key, you get all tag values, including empty strings.

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.

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.