Cost Optimization

These are queries for AWS Services under the AWS Well-Architected Framework Cost Optimization Pillar .

Use the clipboard in the top right of the text boxes below to copy all of the text to be pasted.

CUR Query Library uses placeholder variables, indicated by a dollar sign and curly braces (${ }). ${table_name} and ${date_filter} are common placeholder variables used throughout CUR Query Library, which must be replaced before a query will run. For example, if your CUR table is called cur_table and is in a database called cur_db, you would replace ${table_name} with cur_db.cur_table. For ${date_filter}, you have multiple options. See Filtering by Date in the CUR Query Library Help section for additional details.

Prior to deleting resources, check with the application owner that your analysis is correct and the resources are no longer in use.

Table of Contents

Elastic Load Balancing - Idle ELB

Cost Optimization Technique

This query will display cost and usage of Elastic Load Balancers which didn’t receive any traffic last month and ran for more than 336 hours (14 days). Resources returned by this query could be considered for deletion. AWS Trusted Advisor provides a check for idle load balancers but only covers Classic Load Balancers. This query will provide all Elastic Load Balancer types including Application Load Balancer, Network Load Balancer, and Classic Load Balancer.

The assumption is that if the Load Balancer has not received any traffic within 14 days, it is likely orphaned and can be deleted.

Copy Query

Click here - to expand the query

Please refer to the ELB AWS CLI documentation for deletion instructions. The commands vary between the ELB types.

Back to Table of Contents

EC2 Unallocated Elastic IPs

Cost Optimization Technique

This query will return cost for unallocated Elastic IPs. Elastic IPs incur hourly charges when they are not allocated to a Network Load Balancer, NAT gateway or an EC2 instance (or when there are multiple Elastic IPs allocated to the same EC2 instance). The usage amount (in hours) and cost are summed and returned in descending order, along with the associated Account ID and Region.

Pricing

Please refer to the EC2 Elastic IP pricing page .

Sample Output

Images/ec2-unallocated-elastic-ips.png

Download SQL File

Copy the query below or click to Download SQL File

SELECT
  line_item_usage_account_id,
  line_item_usage_type,
  product_location,
  line_item_line_item_description,
  SUM(line_item_usage_amount) AS sum_line_item_usage_amount,
  SUM(line_item_unblended_cost) AS sum_line_item_unblended_cost
FROM
  ${table_name}
WHERE
  ${date_filter}
  AND line_item_product_code = 'AmazonEC2'
  AND line_item_usage_type LIKE '%ElasticIP:IdleAddress'
GROUP BY
  line_item_usage_account_id,
  line_item_usage_type,
  product_location,
  line_item_line_item_description
ORDER BY
  sum_line_item_unblended_cost DESC,
  sum_line_item_usage_amount DESC;

Back to Table of Contents

Graviton Usage

Cost Optimization Technique

AWS Graviton processors are designed by AWS to deliver the best price performance ratio for cloud workloads, delivering up to 40% improvement over comparable current gen x86 processors. Due to the improved price performance, many organizations track Graviton usage as a KPI to drive cost savings for their cloud workloads. Graviton-based EC2 instances are available, and many other AWS services such as Amazon Relational Database Service, Amazon ElastiCache, Amazon EMR, and Amazon OpenSearch also support Graviton-based instance types.

This query provides detail on Graviton-based usage. Amortized cost, usage hours, and a count of unique resources are summed. Output is grouped by day, payer account ID, linked account ID, service, instance type, and region. Output is sorted by day (descending) and amortized cost (descending).

Download SQL File

Link to Code

Copy Query

SELECT 
  DATE_TRUNC('day',line_item_usage_start_date) AS day_line_item_usage_start_date,
  bill_payer_account_id,
  line_item_usage_account_id,
  line_item_product_code,
  product_instance_type,
  product_region,
  SUM(CASE
    WHEN line_item_line_item_type = 'SavingsPlanCoveredUsage' THEN savings_plan_savings_plan_effective_cost
    WHEN line_item_line_item_type = 'DiscountedUsage' THEN reservation_effective_cost
    WHEN line_item_line_item_type = 'Usage' THEN line_item_unblended_cost
    ELSE 0 
  END) AS sum_amortized_cost, 
  SUM(line_item_usage_amount) as sum_line_item_usage_amount, 
  COUNT(DISTINCT(line_item_resource_id)) AS count_line_item_resource_id
FROM 
  ${table_name}
WHERE 
  ${date_filter}
  AND REGEXP_LIKE(line_item_usage_type, '.[a-z]([1-9]|[1-9][0-9]).?.?[g]\.')
  AND line_item_usage_type NOT LIKE '%EBSOptimized%' 
  AND (line_item_line_item_type = 'Usage'
    OR line_item_line_item_type = 'SavingsPlanCoveredUsage'
    OR line_item_line_item_type = 'DiscountedUsage'
  )
GROUP BY
  DATE_TRUNC('day',line_item_usage_start_date),
  bill_payer_account_id,
  line_item_usage_account_id,
  line_item_product_code,
  line_item_usage_type,
  product_instance_type,
  product_region
ORDER BY 
  day_line_item_usage_start_date DESC,
  sum_amortized_cost DESC;

Back to Table of Contents

Amazon WorkSpaces - Auto Stop

Cost Optimization Technique

AutoStop Workspaces are cost effective when used for several hours per day. If AutoStop Workspaces run for more than 80 hrs per month it is more cost effective to switch to AlwaysOn mode. This query shows AutoStop Workspaces which ran more that 80 hrs in previous month. If the usage pattern for these Workspaces is the same month over month it’s possible to optimize cost by switching to AlwaysOn mode. For example, Windows PowerPro (8 vCPU, 32GB RAM) bundle in eu-west-1 runs for 400 hrs per month. In AutoStop mode it costs $612/month ($8.00/month + 400 * $1.53/hour) while if used in AlwaysOn mode it would cost $141/month.

Copy Query

Click here - to expand the query

Please refer to the AWS Solution, Amazon WorkSpaces Cost Optimizer . This solution analyzes all of your Amazon WorkSpaces usage data and automatically converts the WorkSpace to the most cost-effective billing option (hourly or monthly), depending on your individual usage. This solution also helps you monitor your WorkSpace usage and optimize costs. This automates the manual process of running the above query and adjusting your WorkSpaces configuration.

Back to Table of Contents

NAT Gateway - Idle NATGW

Cost Optimization Technique

This query shows cost and usage of NAT Gateways which didn’t receive any traffic last month and ran for more than 336 hrs. Resources returned by this query could be considered for deletion.

Besides deleting idle NATGWs you should also consider the following tips:

  • Determine What Types of Data Transfers Occur the Most - Deploy the CUDOS dashboard to help visualize top talkers
  • Eliminate Costly Cross Availability Zone Transfer Charges - create new NAT Gateways in the same availability zone as your instances
  • Consider Sending Amazon S3 and Dynamo Traffic Through Gateway VPC Endpoints Instead of NAT Gateways
  • Consider Setting up Interface VPC Endpoints Instead of NAT Gateways for Other Intra-AWS Traffic

Copy Query

Click here - to expand the query

Data Transfer Costs Explained

Back to Table of Contents

Amazon EBS Volumes Modernize gp2 to gp3

Cost Optimization Technique

This query will display cost and usage of general purpose Elastic Block Storage Volumes and provide the estimated cost savings for modernizing a gp2 volume to gp3 These resources returned by this query could be considered for upgrade to gp3 as with up to 20% cost savings, gp3 volumes help you achieve more control over your provisioned IOPS, giving the ability to provision storage with your unique applications in mind. This query assumes you would provision the max iops and throughput based on the volume size, but not all resources will require the max amount and should be validated by the resource owner.

If you are running this for all accounts in a large organization we recommend running the query below first to confirm export size is not over ~1M rows. If the count shown in the query is greater than 1M you will want to filter to groupings of accounts or feed this query into a BI tool such as QuickSight

Click here - to expand the query

Copy Query

Click here - to expand the query

Back to Table of Contents

Cost Optimization Technique

This query looks across your EC2 EBS Snapshots to identify all snapshots that still exist today with their previous month spend. It then provides the start date which is the first billing period the snapshot appeared in your CUR and groups them so you can see if they are over 1yr old. Snapshots over 1yr old should be tagged to keep, cleaned up, or archived.

Copy Query

Copy the query below or click to Download SQL File

Click here - to expand the query

Back to Table of Contents

Cost Optimization Technique

This query breaks out the previous month’s costs and usage of each S3 bucket by storage class and includes and separates out identifiers that can be used to identify trends or potential areas to look into for optimization across Lifecycle Policies or Intelligent Tiering. The query uses this information to provide a variety of checks for each S3 bucket including:

Click here - to see Bucket Trend Checks

Copy Query

Click here - to expand the query

Back to Table of Contents

CUR queries are provided as is. We recommend validating your data by comparing it against your monthly bill and Cost Explorer prior to making any financial decisions. If you wish to provide feedback on these queries, there is an error, or you want to make a suggestion, please email: curquery@amazon.com