Utilize Data

  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 )

    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"

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
        , 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?)