steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_acm_certificateaws_api_gateway_api_keyaws_api_gateway_authorizeraws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_stageaws_appautoscaling_targetaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_planaws_backup_selectionaws_backup_vaultaws_cloudformation_stackaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudtrail_trailaws_cloudwatch_alarmaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_streamaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codepipeline_pipelineaws_config_configuration_recorderaws_config_conformance_packaws_config_ruleaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dms_replication_instanceaws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_dynamodb_tableaws_ebs_snapshotaws_ebs_volumeaws_ebs_volume_metric_read_opsaws_ebs_volume_metric_read_ops_dailyaws_ebs_volume_metric_read_ops_hourlyaws_ebs_volume_metric_write_opsaws_ebs_volume_metric_write_ops_dailyaws_ebs_volume_metric_write_ops_hourlyaws_ec2_amiaws_ec2_ami_sharedaws_ec2_application_load_balanceraws_ec2_autoscaling_groupaws_ec2_classic_load_balanceraws_ec2_gateway_load_balanceraws_ec2_instanceaws_ec2_instance_availabilityaws_ec2_instance_metric_cpu_utilizationaws_ec2_instance_metric_cpu_utilization_dailyaws_ec2_instance_metric_cpu_utilization_hourlyaws_ec2_instance_typeaws_ec2_key_pairaws_ec2_launch_configurationaws_ec2_load_balancer_listeneraws_ec2_network_interfaceaws_ec2_network_load_balanceraws_ec2_regional_settingsaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_container_instanceaws_ecs_serviceaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_replication_groupaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_clusteraws_eventbridge_ruleaws_glacier_vaultaws_glue_catalog_databaseaws_guardduty_detectoraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_threat_intel_setaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_policyaws_iam_policy_simulatoraws_iam_roleaws_iam_server_certificateaws_iam_useraws_iam_virtual_mfa_deviceaws_inspector_assessment_targetaws_inspector_assessment_templateaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_keyaws_lambda_aliasaws_lambda_functionaws_lambda_versionaws_macie2_classification_jobaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_instanceaws_rds_db_instance_metric_connectionsaws_rds_db_instance_metric_connections_dailyaws_rds_db_instance_metric_connections_hourlyaws_rds_db_instance_metric_cpu_utilizationaws_rds_db_instance_metric_cpu_utilization_dailyaws_rds_db_instance_metric_cpu_utilization_hourlyaws_rds_db_instance_metric_read_iopsaws_rds_db_instance_metric_read_iops_dailyaws_rds_db_instance_metric_read_iops_hourlyaws_rds_db_instance_metric_write_iopsaws_rds_db_instance_metric_write_iops_dailyaws_rds_db_instance_metric_write_iops_hourlyaws_rds_db_option_groupaws_rds_db_parameter_groupaws_rds_db_snapshotaws_rds_db_subnet_groupaws_redshift_clusteraws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_regionaws_route53_domainaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_ruleaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_hubaws_securityhub_productaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_parameteraws_ssm_patch_baselineaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_network_aclaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_workload

Table: aws_cost_by_account_daily

Amazon Cost Explorer helps you visualize, understand, and manage your AWS costs and usage. The aws_cost_by_account_daily table provides a simplified view of cost for your account (or all linked accounts when run against the organization master), summarized by day, for the last year.

Note that pricing for the Cost Explorer API is per API request - Each request will incur a cost of $0.01.

Examples

Basic info

select
linked_account_id,
period_start,
blended_cost_amount::numeric::money,
unblended_cost_amount::numeric::money,
amortized_cost_amount::numeric::money,
net_unblended_cost_amount::numeric::money,
net_amortized_cost_amount::numeric::money
from
aws_cost_by_account_daily
order by
linked_account_id,
period_start;

Min, Max, and average daily unblended_cost_amount by account

select
linked_account_id,
min(unblended_cost_amount)::numeric::money as min,
max(unblended_cost_amount)::numeric::money as max,
avg(unblended_cost_amount)::numeric::money as average
from
aws_cost_by_account_daily
group by
linked_account_id
order by
linked_account_id;

Ranked - Top 10 Most expensive days (unblended_cost_amount) by account

with ranked_costs as (
select
linked_account_id,
period_start,
unblended_cost_amount::numeric::money,
rank() over(partition by linked_account_id order by unblended_cost_amount desc)
from
aws_cost_by_account_daily
)
select * from ranked_costs where rank <= 10

.inspect aws_cost_by_account_daily

AWS Cost Explorer - Cost by Linked Account (Daily)

NameTypeDescription
account_idtextThe AWS Account ID in which the resource is located.
amortized_cost_amountdouble precisionThis cost metric reflects the effective cost of the upfront and monthly reservation fees spread across the billing period. By default, Cost Explorer shows the fees for Reserved Instances as a spike on the day that you're charged, but if you choose to show costs as amortized costs, the costs are amortized over the billing period. This means that the costs are broken out into the effective daily rate. AWS estimates your amortized costs by combining your unblended costs with the amortized portion of your upfront and recurring reservation fees.
amortized_cost_unittextUnit type for amortized costs.
blended_cost_amountdouble precisionThis cost metric reflects the average cost of usage across the consolidated billing family. If you use the consolidated billing feature in AWS Organizations, you can view costs using blended rates.
blended_cost_unittextUnit type for blended costs.
estimatedbooleanWhether the result is estimated.
linked_account_idtextThe AWS Account ID.
net_amortized_cost_amountdouble precisionThis cost metric amortizes the upfront and monthly reservation fees while including discounts such as RI volume discounts.
net_amortized_cost_unittextUnit type for net amortized costs.
net_unblended_cost_amountdouble precisionThis cost metric reflects the unblended cost after discounts.
net_unblended_cost_unittextUnit type for net unblended costs.
normalized_usage_amountdouble precisionThe amount of usage that you incurred, in normalized units, for size-flexible RIs. The NormalizedUsageAmount is equal to UsageAmount multiplied by NormalizationFactor.
normalized_usage_unittextUnit type for normalized usage.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
period_endtimestamp without time zoneEnd timestamp for this cost metric.
period_starttimestamp without time zoneStart timestamp for this cost metric.
regiontextThe AWS Region in which the resource is located.
unblended_cost_amountdouble precisionUnblended costs represent your usage costs on the day they are charged to you. In finance terms, they represent your costs on a cash basis of accounting.
unblended_cost_unittextUnit type for unblended costs.
usage_quantity_amountdouble precisionThe amount of usage that you incurred. NOTE: 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 vs. GB).
usage_quantity_unittextUnit type for usage quantity.