Utilize Organization Data Source

Create Glue Crawler

We will prepare the organization data source which we will use to join with the CUR.

1. Go to the Glue Service page:

2. Click Crawlers from the left menu:

3. Click Add crawler:

4. Enter a crawler name of OrgGlueCrawler and click Next:

5. Ensure Data stores is the source type, click Next:

6. 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:

7. Create an IAM role with a name of AWS-Organization-Data-Glue-Crawler, click Next:

8. Change the frequency as Custom and put in 0 8 ? * MON *, and click Next:

9. Click on Add database. Enter a database name of your CUR database managementcur, and click Next:

10. Click Finish:

11. Select the crawler OrgGlueCrawler and click Run crawler:

12. Once its run, you should see tables created.

13. Go to the Athena service page

14. 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;
    

Join with Cost and Usage Report

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.

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;