Customer is looking to build a BI that show all associated cost of an instance
includes data-transfer, EBS and snapshots.
I have used the below query but as the EBS does not have direct instance association its not correlated.
Looking for a way to achieve that.
SELECT "line_item_usage_account_id" AS "Account ID",
"line_item_operation" AS "operation%",
"line_item_usage_type" AS "usage type",
"line_item_resource_id" AS "Instance ID",
if ("product_instance_type" != '', "product_instance_type", split_part("line_item_line_item_description",' ',1)) AS "Instance Type",
if(split_part("product_instance_type",'.',2) = 'metal', "product_instance_type",if ("product_instance_type_family" != '', "product_instance_type_family", if ("product_instance_type" != '', split_part("product_instance_type",'.',1), split_part("line_item_line_item_description",'.',1)))) AS Family,
sum(if ("pricing_term" = 'Reserved',"reservation_effective_cost",0)) AS "Reserved Cost",
sum(if ("pricing_term" = 'OnDemand',"line_item_usage_amount",0)) * "pricing_public_on_demand_cost" AS "OnDemand Cost",
sum(if ("pricing_term" = '',"line_item_usage_amount",0)) * "line_item_unblended_cost" AS "Spot Cost"
FROM customer_all
WHERE month(bill_billing_period_start_date) = 6 and year(bill_billing_period_start_date) = 2020
AND "line_item_resource_id" LIKE 'i-02efd3e19e0da53b9'
GROUP BY "line_item_usage_account_id", "line_item_resource_id", "product_instance_type", "line_item_usage_type", "product_instance_type_family", "line_item_line_item_description", "line_item_usage_amount", "pricing_public_on_demand_cost", "line_item_unblended_cost", "line_item_operation", "line_item_usage_type"