Lab complete!
Now that you have completed this lab, make sure to update your Well-Architected review if you have implemented these changes in your workload.
Click here to access the Well-Architected Tool
We will prepare the organization data source which we will use to join with the CUR.
Go to the Glue Service page:
Click Crawlers from the left menu:
Click Add crawler:
Enter a crawler name of Org_Glue_Crawler and click Next:
Ensure Data stores is the source type, click Next:
Click the folder icon to list the S3 folders in your account and find your S3 bucket and find the organisation-data folder and click Next:
Create an IAM role with a name of AWS-Organization-Data-Glue-Crawler, click Next:
Change the frequency as Custom and put in 0 8 ? * MON *, and click Next:
Click on Add database. Enter a database name of your CUR database managementcur, and click Next:
Click Finish:
Select the crawler OrgGlueCrawler and click Run crawler:
Once its run, you should see tables created.
Go to the Athena service page
Run the below query, to view your data in Amazon S3. As you can see, we have the account number, the name, when it was created and the current status of that account.
SELECT * FROM "managementcur"."organisation_data" limit 10;
You have now created your Athena table that will query the organization data in the S3 Bucket.
We will be running an example query on how you can connect your CUR to this Organizations data as a one off. In this query you will see the service costs split by account names.
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;
If you would like to always have your Organizations data connected to your CUR then we can create a view.
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"))
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;
Having run these queries, you can now see how the Organization data connects to your Cost and Usage Report.
Now that you have completed this lab, make sure to update your Well-Architected review if you have implemented these changes in your workload.
Click here to access the Well-Architected Tool