How to analyze Extended Support Costs using Amazon Athena and CUR 2.0

AWS managed services like Amazon RDS, Amazon EKS, Amazon OpenSearch Service, and Amazon ElastiCache incur Extended Support charges when running on older, out-of-support versions. While these charges appear in your AWS Cost and Usage Report (CUR), identifying specific resources and their associated costs can be challenging. This article shows you how to use Amazon Athena to query CUR 2.0 data and gain detailed insights into Extended Support charges across your AWS accounts and resources.

Prerequisites

• AWS Cost and Usage Report (CUR) 2.0 configured with Amazon Athena integration (using Cost Intelligence Dashboards setup guide).
• Access to AWS Management Console with appropriate permissions.
• Basic SQL knowledge.

Here are some ways to obtain the Extended Support costs at different levels of detail:

---

1. Basic Analysis - using AWS Cost Explorer

For a quick overview of Extended Support costs, please follow How to identify Extended Support costs in Cost Explorer

Limitations:

• Shows only costs, not resource-specific details
• Cannot identify specific instances running older versions

---

2. Detailed Analysis - using Extended Support Cost Projection Dashboard

Deploy the Extended Support Cost Projection Dashboard for:

• Visualizing resources approaching extended support
• Planning for upcoming version upgrades
• View cost projections for 3, 6, and 12-month in future

---

3. Custom Analysis - using Athena Queries

Run the following SQL query to view the resource details. Here are the key elements in the query:

• Update the start_date and end_date TIMESTAMP as desired for your data range
• Update the <your_cur2_table> with your CUR2 table name
• The SQL statement element_at(resource_tags, ‘user_product’) retrieves the value associated with the cost allocation tag ‘user_product’ from the resource_tags field in the CUR table. Modify this line as per your tags. If you wish to omit this step, comment this line twice in the query by adding “--“ at the beginning of each line. e.g., "-- element_at(resource_tags, ‘user_product’)”.

-- Edit start_date, end_date TIMESTAMPs with your desired date range
-- Replace <your_cur2_table> with your CUR2 table name in Athena

WITH params AS (
  SELECT
    TIMESTAMP '2025-09-01 00:00:00' AS start_date,
    TIMESTAMP '2025-10-01 00:00:00' AS end_date
)
SELECT
  line_item_usage_account_id AS account_id,
  line_item_usage_account_name AS account_name,
  
-- Resource Tag value for user_product. 
-- Edit or comment this as needed based on the tags you want to group the output result with.
  element_at(resource_tags, 'user_product') AS "Tag:user_product",

  line_item_resource_id AS resource_arn,
  
-- To reliably derive the AWS Region value, because the region information in CUR 2.0 can appear in different columns depending on the service or line item type.
  COALESCE(
    NULLIF(regexp_replace(line_item_availability_zone, '[a-z]$', ''), ''),
    NULLIF(product_region_code, ''),
    NULLIF(product_location, '')
  ) AS region,

  -- Version only for RDS, blank for others
  CASE 
    WHEN line_item_product_code = 'AmazonRDS' THEN
      COALESCE(
        regexp_extract(line_item_usage_type, '.*:([^:]+)$', 1),
        regexp_extract(line_item_line_item_description, '.*:([^:]+)$', 1)
      )
    ELSE ''
  END AS version,

  line_item_product_code AS service,
  line_item_usage_type AS usage_type,

  SUM(line_item_unblended_cost) AS extended_support_cost_usd,
  
  bill_payer_account_id AS payer_id,
  bill_payer_account_name AS payer_name

FROM <your_cur2_table>, params
WHERE 
  line_item_product_code IN (
    'AmazonRDS',
    'AmazonEKS',
    'AmazonOpenSearchService',
    'AmazonElastiCache'
  )
  AND line_item_line_item_type IN ('Usage','DiscountedUsage')
  AND (
       regexp_like(line_item_usage_type, '(?i)extended ?support')
       OR regexp_like(line_item_line_item_description, '(?i)extended ?support')
  )
  AND line_item_usage_start_date >= params.start_date
  AND line_item_usage_start_date <  params.end_date

GROUP BY
  line_item_usage_account_id,
  line_item_usage_account_name,

  element_at(resource_tags, 'user_product'),

  line_item_resource_id,
    
  COALESCE(
    NULLIF(regexp_replace(line_item_availability_zone, '[a-z]$', ''), ''),
    NULLIF(product_region_code, ''),
    NULLIF(product_location, '')
  ),

  CASE 
    WHEN line_item_product_code = 'AmazonRDS' THEN
      COALESCE(
        regexp_extract(line_item_usage_type, '.*:([^:]+)$', 1),
        regexp_extract(line_item_line_item_description, '.*:([^:]+)$', 1)
      )
    ELSE ''
  END,
  line_item_product_code,
  line_item_usage_type,
  bill_payer_account_id,
  bill_payer_account_name

ORDER BY extended_support_cost_usd DESC;

Example output:

account_id	account_name	Tag:user_product	resource_arn	region	version	service	usage_type	extended_support_cost_usd	payer_id	payer_name
12345678910	Prod Account	Unicorn Rentals	arn:aws:rds:us-east-1:12345678910:db:unicorn-db-prod-1	us-east-1	MySQL5.7	AmazonRDS	ExtendedSupport:Yr1-Yr2:MySQL5.7	691.22	12345678910	Payer Account Name
12345678910	Prod Account	Unicorn Rentals	arn:aws:eks:us-east-1:12345678910:cluster/unicorn-prod-1	us-east-1		AmazonEKS	AmazonEKS-Hours:extendedSupport	124.54	12345678910	Payer Account Name

Limitations:

• Shows only major version numbers (e.g., MySQL 5.7) but not minor versions or patch levels of resources
• Only provides historical and current Extended Support costs, not future projections. Use the Extended Support Cost Projection Dashboard for projections.

---

Sometimes, it helps to track the progress in resource upgrades and monitor the daily count of affected resources. The following SQL query helps you visualize trends by providing a daily breakdown of resources incurring Extended Support charges across your AWS services.

-- CUR 2.0 ONLY
-- Edit start_date, end_date TIMESTAMPs with your desired date range
-- Replace <your_cur2_table> with your CUR2 Glue table name

WITH params AS (
  SELECT
    TIMESTAMP '2025-09-01 00:00:00' AS start_date,
    TIMESTAMP '2025-10-01 00:00:00' AS end_date
)
SELECT
  CAST(line_item_usage_start_date AS DATE) AS usage_day,
  line_item_product_code AS service,
  COUNT(DISTINCT line_item_resource_id) AS daily_resource_count,
  SUM(line_item_unblended_cost) AS daily_extended_support_cost
FROM <your_cur2_table>, params
WHERE 
  line_item_product_code IN (
    'AmazonRDS',
    'AmazonEKS',
    'AmazonOpenSearchService',
    'AmazonElastiCache'
  )
  AND line_item_line_item_type IN ('Usage','DiscountedUsage')
  AND (
       regexp_like(line_item_usage_type, '(?i)extended ?support')
       OR regexp_like(line_item_line_item_description, '(?i)extended ?support')
  )
  AND line_item_usage_start_date >= params.start_date
  AND line_item_usage_start_date <  params.end_date
GROUP BY
  CAST(line_item_usage_start_date AS DATE),
  line_item_product_code
ORDER BY
  usage_day,
  service;

Example output:

usage_day	daily_resource_count	daily_extended_support_cost	service
9/1/25	16	124.54	AmazonEKS
9/1/25	34	1117.80	AmazonRDS
9/2/25	12	98.22	AmazonEKS
9/2/25	30	1085.41	AmazonRDS

---

4. GenAI Analysis – using Strands, AgentCore, MCP Server, and Q CLI

Use the AWS EOL Detection & Remediation Workshop to:

• Analyze usage patterns with natural language
• Get AI-powered insights

---

Best Practices

• Regularly monitor Extended Support costs
• Plan version upgrades before entering Extended Support
• Use resource tags for better cost allocation
• Export query results for detailed analysis

---

Additional Resources

• CUR 2.0 Migration Guide
• Cost Intelligence Dashboards
• Amazon Athena Best Practices