Utilize Data

  CREATE OR REPLACE VIEW "account_map" AS 
  SELECT DISTINCT
    id account_id
  , split_part(arn, ':',5) parent_account_id
  , name account_name
  , email account_email_id
  FROM
    "optimization_data"."organization_data" 

1. In the Athena service page run the below query to join the Organizations data with the CUR table. Make the below changes as needed:

• Change managementcur if your named your database differently

• month = Chosen Month

• year = Chosen Year

    SELECT line_item_usage_account_id,
        line_item_product_code,
         name,
        sum(line_item_unblended_cost) AS line_item_unblended_cost_cost
    FROM "managementcur"."cur" cur
    JOIN  "managementcur"."organisation_data"
    ON "cur".line_item_usage_account_id = organisation_data.id
    WHERE month = '10'
            AND year = '2020'
    GROUP BY  line_item_usage_account_id,  name, line_item_product_code
    limit 10;
  

2. The important part of this query is the join. The line_item_usage_account_id from your Cost & Usage Report should match a account_number from the Organizations data. You can now see the account name in your data.

Create a View with Cost and Usage Report

If you would like to always have your Organizations data connected to your CUR then we can create a view.

1. In the Athena service page run the below query to join the Organizations data with the CUR table as a view.

   CREATE OR REPLACE VIEW org_cur AS
   SELECT *
   FROM ("managementcur"."cur" cur
   INNER JOIN "managementcur"."organisation_data"
   	ON ("cur"."line_item_usage_account_id" = "organisation_data"."id")) 
   

2. Going forward you will now be able to run your queries from this view and have the data connected to your Organizations data. To see a preview where your org data is, which is at the end of the returned data, run the below query.

    SELECT * FROM "managementcur"."org_cur" limit 10;
   

  SELECT *,
  CASE
  WHEN snap_ami_id = imageid THEN
  'AMI Avalible'
  WHEN snap_ami_id LIKE 'ami%' THEN
  'AMI Removed'
  ELSE 'Not AMI'
  END AS status
    FROM ( 
  (SELECT snapshotid AS snap_id,
      volumeid as volume,
      volumesize,
      starttime,
      Description AS snapdescription,
      year,
      month,
      ownerid,
      
      CASE
      WHEN substr(Description, 1, 22) = 'Created by CreateImage' THEN
      split_part(Description,' ', 5)
      WHEN substr(Description, 2, 11) = 'Copied snap' THEN
      split_part(Description,' ', 9)
      WHEN substr(Description, 1, 22) = 'Copied for Destination' THEN
      split_part(Description,' ', 4)
      ELSE ''
      END AS "snap_ami_id"
  FROM "optimization_data"."snapshot_data"
  ) AS snapshots
  LEFT JOIN 
      (SELECT imageid,
      name,
      description,
      state,
      rootdevicetype,
      virtualizationtype
      FROM "optimization_data"."ami_data") AS ami
          ON snapshots.snap_ami_id = ami.imageid )

Lambda

1. Go to AWS Lambda
2. Find the pricing-Lambda-Function function
3. Test the function

Athena

1. Go to AWS Athena

2. Go to Saved queries at the top of the screen

3. Run the pricing_ec2_create_table Query to create a pricing table

4. In Saved queries Run the pricing_region_names Query to create a normalized region name table

5. In Saved queries run inventory_snapshot_connected_to_ami_with_pricing to create a view

6. Run the below to see your data

    SELECT * FROM "optimization_data"."snapshot_ami_quicksight_view" limit 10;
   

You must have access to your Cost & Usage data in the same account and region so you can join through athena

Athena

1. Go to AWS Athena
2. Go to Saved queries at the top of the screen
3. In Saved queries run inventory_snapshot_connected_to_ami_with_cur to create a view
4. Change the value ${table_name} to your Cost and Usage report database and name and your ${date_filter} to look at a certain month/year
5. You will see the price of all Snapshots and how much they cost based on their connection with AMIS

Please note that if you delete the snapshot and it is part of a lineage you may only make a small saving

    SELECT * FROM
        "optimization_data"."ebs_data"
    LEFT JOIN 
    (select "volume id","volume name", "volume type","volume size",	"monthly storage cost" ,accountid, category, region, year,month
    from
    "optimization_data".ta_data ) ta
    ON "ebs_data"."volumeid" = "ta"."volume id" and "ebs_data"."year" = "ta"."year" and "ebs_data"."month" = "ta"."month"

Lambda

1. Go to AWS Lambda
2. Find the pricing-Lambda-Function function
3. Test the function

Athena

1. Go to AWS Athena and run the below

2. Go to Saved queries at the top of the screen

3. Run the ec2-view Query to create a view of ebs and ta data

4. Run the ec2_pricing Query to create a pricing table

5. In Saved queries run the region_names Query to create a normalized region name table

6. In Saved queries run ebs-ta-query-pricing to create a view

7. Run the below to see your data

    SELECT * FROM "optimization_data"."ebs_quicksight_view" limit 10;
   

1. Go to AWS Athena and run the below
2. Go to Saved queries at the top of the screen
3. Run the ec2-view Query to create a view of ebs and ta data
4. Run the ec2_pricing Query to create a pricing table
5. In Saved queries run the region_names Query to create a normalized region name table
6. In Saved queries run gp3-opportunity to create a view

    CREATE OR REPLACE VIEW "ebs_view" AS 
    SELECT *FROM
                "optimization_data"."ebs_data"
            LEFT JOIN 
            (select "volume id","volume name", "volume type","volume size",	"monthly storage cost" ,accountid as ta_accountid, status, category, region as ta_region, year as ta_year ,month as ta_month
            from
            "optimization_data".ta_data
            where category = 'cost_optimizing') ta
            ON "ebs_data"."volumeid" = "ta"."volume id" and "ebs_data"."year" = "ta"."ta_year" and "ebs_data"."month" = "ta"."ta_month"
            LEFT JOIN (
      SELECT
        "region" "region_code"
      , "regionname"
      FROM
        storage.region_names
    )  region ON ("ebs_data"."region" = "region"."region_code")

1. Ensure you have budget data in your Amazon Athena table

2. Create a Amazon Athena View of this data to extract the relevant information. The costfilters identifies if this is just an account spend budget or filtered. As this if for CUDOS we have filtered these out for the query but all budgets data is in the table.

    CREATE OR REPLACE VIEW aws_budgets_view AS 
        SELECT
          budgetname budget_name
        , CAST(budgetlimit.amount AS decimal) budget_amount
        , CAST(calculatedspend.actualspend.amount AS decimal) actualspend
        , CAST(calculatedspend.forecastedspend.amount AS decimal) forecastedspend
        , timeunit
        , account_id
        , budgettype budget_type
        , year budget_year
        , month budget_month
        FROM
          "optimization_data"."budgets"
        WHERE (budgettype = 'COST')  AND costfilters.filter[1] = 'None'
   

3. Go to the Amazon QuickSight service homepage

4. In QuickSight, select the summary_view Data Set

5. Select Edit data set

6. Select Add data:

7. Select your Amazon Athena aws_budgets_view table and click Select

8. Click on the join and choose month, year from summary_view and budget_month, budget_year to join. Click Save.

9. In your Analysis you can now add a Budget figure to your lines. Make sure to change to Average.

    WITH data as (
        Select volumeid,
          snapshotid,
          ownerid "account_id",
          cast(  replace(split(split(starttime, '+') [ 1 ], '.') [ 1 ], 'T', ' ') as timestamp) as start_time,
          CAST("concat"("year", '-', "month", '-01') AS date) "data_date", 
          sum(volumesize) "volume_size" 
        from "optimization_data"."snapshot_data"
        group by 1,2,3,4,5
      ),
      latest AS(
        Select max(data_date) "latest_date" from data
      ),
      ratio AS(
        Select distinct volumeid, data_date, latest_date,
          count(distinct snapshotid) AS "snapshot_count_per_volume"
        from data
        LEFT JOIN latest ON latest.latest_date = data_date
          WHERE volumeid like 'vol%' and data_date = latest_date
        group by 1,2,3
      )
      select data.volumeid,
        data.snapshotid,
        account_id,
        data.data_date,
        start_time,
        volume_size,
        snapshot_count_per_volume,
          CASE WHEN data.volumeid NOT LIKE 'vol%' THEN 1 ELSE dense_rank() OVER (partition by data.volumeid ORDER by start_time) END AS "snapshot_lineage"  
        from data
        Left JOIN ratio ON ratio.volumeid = data.volumeid
        ORDER by volumeid, snapshot_lineage

      WITH cur_mapping AS (
        SELECT DISTINCT 
        split_part(line_item_resource_id,'/',2) AS "snapshot_id",
        line_item_usage_account_id AS "linked_account_id",
        CAST("concat"("year", '-', "month", '-01') AS date) "billing_period", sum(line_item_usage_amount) "snapshot_size",
        sum(line_item_unblended_cost) "snapshot_cost"
        FROM "athenacurcfn_mybillingreport"."mybillingreport"
        WHERE (CAST("concat"("year", '-', "month", '-01') AS date) = ("date_trunc"('month', current_date) - INTERVAL  '1' MONTH)) AND (line_item_resource_id <> '') AND (line_item_line_item_type LIKE '%Usage%') AND (line_item_product_code = 'AmazonEC2') AND (line_item_usage_type LIKE '%EBS:Snapshot%')
        group by 1,2,3
      ),
      snapshot_data AS (
        Select volumeid,
          snapshotid,
          ownerid "account_id",
          cast(
            replace(split(split(starttime, '+') [ 1 ], '.') [ 1 ], 'T', ' ') as timestamp
          ) as start_time,
          CAST("concat"("year", '-', "month", '-01') AS date) "data_date", 
          sum(volumesize) "volume_size" 
        from "optimization_data"."snapshot_data"
        group by 1,2,3,4,5
      ),
      data AS (
        SELECT DISTINCT volumeid,
          snapshotid,
          account_id,
          billing_period,
          data_date,
          start_time,
          sum(snapshot_size) AS snapshot_size,
          sum(snapshot_cost) AS snapshot_cost,
          sum(volume_size) AS "volume_size" 
        FROM snapshot_data
        LEFT JOIN cur_mapping ON cur_mapping.snapshot_id = snapshotid AND cur_mapping.linked_account_id = account_id
        group by 1,2,3,4,5,6
      ),
      latest AS(
        Select max(data_date) "latest_date"
            from data
      ),
      ratio AS(
        Select distinct volumeid, data_date, latest_date,
          count(distinct snapshotid) AS "snapshot_count_per_volume",
          sum(snapshot_cost) AS "all_snapshot_cost_per_volume",
          sum(snapshot_size) AS "all_snapshot_size_per_volume"
        from data
        LEFT JOIN latest ON latest.latest_date = data_date
        WHERE volumeid like 'vol%' and data_date = latest_date
        group by 1,2,3
      )
      select data.volumeid,
        data.snapshotid,
        account_id,
        data.data_date,
        start_time,
        billing_period,
        snapshot_size,
        volume_size,
        all_snapshot_cost_per_volume
        all_snapshot_size_per_volume,
        snapshot_count_per_volume,
        CASE WHEN data.volumeid NOT LIKE 'vol%' THEN 1 ELSE dense_rank() OVER (partition by data.volumeid ORDER by start_time) END AS "snapshot_lineage"  
        from data
        LEFT JOIN ratio ON ratio.volumeid = data.volumeid

1. Navigate to the Athena service
2. Select the “optimization data” database
3. In Saved Queries find “cluster_metadata_view”" Change ‘BU’ to the tag you wish to do chargeback for
4. Click the Run button
5. In Saved Queries find “ec2_cluster_costs_view”" – Replace ${CUR} in the “FROM” clause with your CUR table name – For example, “curdb”.“ecs_services_clusters_data”
6. Click the Run button
7. In Saved Queries find “bu_usage_view”" – Replace ${CUR} in the “FROM” clause with your CUR table name – For example, “curdb”.“ecs_services_clusters_data”
8. Click the Run button

Now your views are created you can run your report

Manually execute billing report

• In Saved Queries find “ecs_chargeback_report” – Replace “bu_usage_view.month” value with the appropriate month desired for the report – For example, a value of ‘2’ returns the charges for February
• Click the Run button

Example Output

Breakdown:

• task_usage: total memory resources reserved (in GBs) by all tasks over the billing period (i.e. – monthly)
• percent: task_usage / total_usage
• ec2_cost: monthly cost for EC2 instance in $
• Services: Name of service
• servicearn: Arn of service
• Value: Value of specified tag for the ECS service (could be App, TeamID, etc?)

1. Navigate to the Athena service and open Saved Queries.
2. Select your database where you have your Cost and Usage Report
3. In Saved Queries find “tgw_chargeback_cur”"
4. Replace < CURDatabase > with your database name in the tgw_chargeback_cur. For example:

"cur"."cost_and_usage_report"

The Cloud Watch data collection is automated for all the regions. However, if you are destined to only chargeback to a subset of selected regions, you need to specify it in “product_location LIKE ‘%US%'” line.

5. Click the Run button
6. In Saved Queries find “tgw_chargeback_cw”"
7. Select the “optimization data” database
8. Replace < CURDatabase > with your database name in the tgw_chargeback_cw.
9. Click the Run button

Now your views are created and you can run your report

1. Navigate to Lambda and test the Accounts-Collector-Function-OptimizationDataCollectionStack and pricing-Lambda-Function-OptimizationDataCollectionStack lambdas
2. Navigate to Athena
3. Go to Saved queries at the top of the screen
4. Run the region_names Query to create a normalized region name table
5. Run the rds_pricing_table Query to create a pricing look up table for RDS
6. Run the graviton_mapping Query to create a mapping of existing Intel based instances to the proper Graviton based instance
7. Run the rds_metrics-rds-graviton Query to provide the output of your RDS instances and their graviton eligibility and savings. You can download the results to further filter and analyze the results

    Analyzing Your Results
	
	</span>
</div>
<div class="expand-content" style="display: none;">
    <ol>

</div>