Build an analytics pipeline for a multi-account support case dashboard

As organizations mature in their cloud journey, they have many accounts (even hundreds) that they need to manage. Imagine having to manage support cases for these accounts without a unified dashboard. Administrators have to access each account either by switching roles or with single sign-on (SSO) in order to view and manage support cases.

This post demonstrates how you can build an analytics pipeline to push support cases created in individual member AWS accounts into a central account. We also show you how to build an analytics dashboard to gain visibility and insights on all support cases created in various accounts within your organization.

Overview of solution

In this post, we go through the process to create a pipeline to ingest, store, process, analyze, and visualize AWS support cases. We use the following AWS services as key components:

• Amazon EventBridge and AWS Lambda to ingest support case data in real time from various AWS accounts
• Amazon Simple Storage Service (Amazon S3) to store the support case data
• AWS Glue DataBrew to clean and transform the data
• AWS Glue crawlers to catalog the data
• AWS Step Functions to orchestrate the flow
• Amazon Athena to query the processed data
• An Amazon QuickSight dashboard that provides insights into various support case metrics

The following diagram illustrates the architecture.

The central account is the AWS account that you use to centrally manage the support case data.

Member accounts are the AWS accounts where, whenever the support cases are created, the data flows into an S3 bucket in the central account that can be visualized using the QuickSight dashboard in the central account.

To implement this solution, you complete the following high-level steps:

1. Determine the AWS accounts to use for the central account and member accounts.
2. Set up permissions for AWS CloudFormation StackSets on the central account and member accounts.
3. Create resources on the central account using AWS CloudFormation.
4. Create resources on the member accounts using CloudFormation StackSets.
5. Open up support cases on the member accounts.
6. Visualize the data in a QuickSight dashboard in the central account.

Prerequisites

Complete the following prerequisite steps:

1. Create AWS accounts if you haven’t done so already.
2. Before you get started, make sure that you have a Business or Enterprise support plan for your member accounts.
3. Sign up for QuickSight if you have never used QuickSight in this account before. To use the forecast capability in QuickSight, sign up for the Enterprise Edition.

Preparation for CloudFormation StackSets

In this section, we go through the steps to set up permissions for StackSets in both the central and member accounts.

Set up permissions for StackSets on the central account

To set up permissions on the central account, complete the following steps:

1. Sign in to the AWS Management Console of the central account.
2. Download the administrator role CloudFormation template.
3. On the AWS CloudFormation console, choose Create stack and With new resources.
4. Leave the Prepare template setting as default.
5. For Template source, select Upload a template file.
6. Choose Choose file and supply the CloudFormation template you downloaded: AWSCloudFormationStackSetAdministrationRole.yml.
7. Choose Next.
8. For Stack name, enter StackSetAdministratorRole.
9. Choose Next.
10. For Configure stack options, we recommend configuring tags, which are key-value pairs that can help you identify your stacks and the resources they create. For example, enter Owner as the key, and your email address as the value.
11. We don’t use additional permissions or advanced options, so accept the default values and choose Next.
12. Review your configuration and select I acknowledge that AWS CloudFormation might create IAM resources with custom names.
13. Choose Create stack.

The stack takes about 30 seconds to complete.

Set up permissions for StackSets on member accounts

Now that we’ve created a StackSet administrator role on the central account, we need to create the StackSet execution role on the member accounts. Perform the following steps on all member accounts:

1. Sign in to the console on the member account.
2. Download the execution role CloudFormation template.
3. On the AWS CloudFormation console, choose Create stack and With new resources.
4. Leave the Prepare template setting as default.
5. For Template source, select Upload a template file.
6. Choose Choose file and supply the CloudFormation template you downloaded: AWSCloudFormationStackSetExecutionRole.yml.
7. Choose Next.
8. For Stack name, use StackSetExecutionRole.
9. For Parameters, enter the 12-digit account ID for the central account.
10. Choose Next.
11. For Configure stack options, we recommend configuring tags. For example, enter Owner as the key and your email address as the value.
12. We don’t use additional permissions or advanced options, so choose Next.

For more information, see Setting AWS CloudFormation stack options.

13. Review your configuration and select I acknowledge that AWS CloudFormation might create IAM resources with custom names.
14. Choose Create stack.

The stack takes about 30 seconds to complete.

Set up the infrastructure for the central account and member accounts

In this section, we go through the steps to create your resources for both accounts and launch the StackSets.

Create resources on the central account with AWS CloudFormation

To launch the provided CloudFormation template, complete the following steps:

1. Sign in to the console on the central account.
2. Choose Launch Stack:
   
3. Choose Next.
4. For Stack name, enter a name. For example, support-case-central-account.
5. For AWSMemberAccountIDs, enter the member account IDs separated by commas from where support case data is gathered.
6. For Support Case Raw Data Bucket, enter the S3 bucket in the central account that holds the support case raw data from all member accounts. Note the name of this bucket to use in future steps.
7. For Support Case Transformed Data Bucket, enter the S3 bucket in central account that holds the support case transformed data. Note the name of this bucket to use in future steps.
8. Choose Next.
9. Enter any tags you want to assign to the stack and choose Next.
10. Select the acknowledgement check boxes and choose Create stack.

The stack takes approximately 5 minutes to complete. Wait until the stack is complete before proceeding to the next steps.

Launch CloudFormation StackSets from the central account

To launch StackSets, complete the following steps:

1. Sign in to the console on the central account.
2. On the AWS CloudFormation console, choose StackSets in the navigation pane.
3. Choose Create StackSet.
4. Leave the IAM execution role name as AWSCloudFormationStackSetExecutionRole.
5. If AWS Organizations is enabled, under permissions, select Service-managed permissions.
6. Leave the Prepare template setting as default.
7. For Template source, select Amazon S3 URL.
8. Enter the following Amazon S3 URL under Specify Template: https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/BDB-2583/AWS_MemberAccount_SupportCaseDashboard_CF.yaml
9. Choose Next.
10. For StackSet name, enter a name. For example, support-case-member-account.
11. For CentralSupportCaseRawBucket, enter the name of the Support Case Raw Data Bucket created in the central account, which you noted previously.
12. For CentralAccountID, enter the account ID of the central account.
13. For Configure StackSet options, we recommend configuring tags.
14. Leave the rest as default and choose Next.
15. If AWS Organizations is enabled, in the Set deployment options step, for Deployment targets, you can either choose Deploy to organization or Deploy to organizational units (OU).
    • If you deploy to OUs, you will need to specify the AWS OU ID.
16. If AWS Organizations is not enabled, on the Set Deployment Options page, under Accounts, select Deploy stacks in accounts.
    • Under Account numbers, enter the 12-digit account IDs for the member accounts as a comma-separated list. For example: 111111111111,222222222222.
17. Under Specify regions, choose US East (N. Virginia).

Due to the limitation of EventBridge with the AWS Support API, this StackSet has to be deployed only in the US East (N. Virginia) Region.

18. Optionally, you can change the maximum concurrent accounts to match the number of member accounts, adjust the failure tolerance to at least 1, and choose Region Concurrency to be Parallel to set up resources in parallel on the member accounts.
19. Review your selections, select the acknowledgement check boxes, and choose Submit.

The operation takes about 2–3 minutes to complete.

Visualize your support cases in QuickSight in the central account

In this section, we go through the steps to visualize your support cases in QuickSight.

Grant QuickSight permissions

To grant QuickSight permissions, complete the following steps:

1. Sign in to the console on the central account.
2. On the QuickSight console, on the Admin drop-down menu in top right-hand corner, choose Manage QuickSight.
3. In the navigation pane, choose Security & permissions.
4. Under QuickSight access to AWS services, choose Manage.
5. Select Amazon Athena.
6. Select Amazon S3 to edit QuickSight access to your S3 buckets.
7. Select the bucket you specified during stack creation.
8. Choose Finish.
9. Choose Save.

Prepare the datasets

To prepare your datasets, complete the following steps:

1. On the QuickSight console, choose Datasets in the navigation pane.
2. Choose New dataset.
3. Choose Athena.
4. For Data source name, enter support-case-data-source.
5. Choose Validate connection.
6. After your connection is validated, choose Create data source.
7. For Database, choose support-case-transformed-data.
8. For Tables, select the table under the database (there should only be one table that matches the name of the S3 bucket you set as the destination for the transformed data).
9. Choose Edit/Preview data.
10. Leave Query mode set as Direct Query.
11. Choose the options menu (three dots) next to the field case_creation_year and set Change data type to Date.
12. Enter the date format as yyyy, then choose Validate and Update.
13. Similarly, right-click on the field case_creation_month and set Change data type to Date.
14. Enter the date format as MM, then choose Validate and Update.
15. Right-click on the field case_creation_day and set Change data type to Date.
16. Enter the date format as dd, then choose Validate and Update.
17. Right-click on the field case_creation_time and set Change data type to Date.
18. Enter the date format as yyyy-MM-dd’T’HH:mm:ss.SSSZ, then choose Validate and Update.
19. Change the name of the QuickSight dataset to support-cases-dataset.
20. Choose Save & publish.
21. Note the dataset ID from the URL (alpha-numeric string between datasets and view, excluding slashes) to use later for QuickSight dashboard creation.

22. Choose Cancel to exit this page.

Set up the QuickSight dashboard from a template

To set up your QuickSight dashboard, complete the following steps:

1. Navigate to the following link, then right-click and choose Save As to download the QuickSight dashboard JSON template from the browser.
2. On the console, choose the user profile drop-down menu.
3. Choose the copy icon next to the Account ID: field (of the central account).

4. Open the JSON file with a text editor and replace xxxxx with the account ID. This will be replaced in two places.
5. Replace yyyyy with the dataset ID that you previously noted.
6. Replace rrrrr with the Region where you deployed resources in the central account.

To determine the principal (user) to be used for the dashboard creation, you can use AWS CloudShell.

7. Navigate to CloudShell on the console. Ensure it’s the same Region where your resources are deployed.

8. Wait until the environment gets created and you see the CloudShell prompt.

9. Run the following command, providing your account ID (central account) and Region:

   aws quicksight list-users –region <region> --aws-account-id <account-id> --namespace default

10. From the output, select the value of the ARN field. Replace the value of zzzzz with the ARN.
11. Optionally, you can change the name of the dashboard by changing the value of the fields in the JSON file:
    • For DashboardId, enter SupportCaseCentralDashboard.
    • For Name, enter SupportCaseCentralDashboard.
12. Save the changes to the JSON file.

Now we use CloudShell to upload the JSON file provided in the previous step.

13. On the Actions menu, choose Upload file.

14. To create the QuickSight dashboard from the JSON template, use the following AWS Command Line Interface (AWS CLI) command and pass the updated JSON file as an argument, providing your Region:

    aws quicksight create-dashboard –region <region> --cli-input-json file://support-case-dashboard-template.json

The output of the command looks similar to the following screenshot.

15. In case of any issues or if you want to see more details about the dashboard, you can use the following command:

    aws quicksight describe-dashboard --region <region> --aws-account-id <central-account-id> --dashboard-id <DashboardId in screenshot above>

16. On the QuickSight console, choose Dashboards in the navigation pane.
17. Choose Support Cases Dashboard.

You should see a dashboard similar to the screenshot shown at the beginning of this post, but there should only be one case.

Add additional member accounts

If you want to add additional member accounts, you need to update the CloudFormation stack that you created earlier on the central account. If you followed our name recommendation, the stack is called support-case-central-account-stack. Add the additional account number in the Member Account IDs parameter.

Next, go to the StackSet in the central account. If you followed our naming recommendation, the StackSet is called support-case-member-account. Select the StackSet and on the Actions menu, choose Add stacks to StackSet. Then follow the same instructions that you followed previously when you created the StackSet.

Monitor support cases created in the central account

So far, our setup will monitor all support cases created in the member accounts that you specified. However, it doesn’t include support cases that you create in the central account. To set up monitoring for the central account, complete the following steps:

1. Update the CloudFormation stack that you created earlier on the central account. If you followed our name recommendation, the stack is called support-case-central-account-stack. Add the central account ID in the Member Account IDs parameter.
2. Sign in to the CloudFormation console in the central account.
3. Choose Launch Stack:
   
4. Choose Next.
5. For Stack name, enter a name. For example, support-case-central-as-member-account.
6. For CentralAccountIDs, enter the central account ID.
7. For CentralSupportCaseRawBucket, enter the S3 bucket in the central account that holds the support case raw data from all member accounts.
8. Choose Next.
9. Enter any tags you want to assign to the stack and choose Next.
10. Select the acknowledgement check boxes and choose Create stack.

Clean up

To avoid incurring future charges, delete the resources you created as part of this solution.

Troubleshooting

Note the following troubleshooting tips:

• Make sure that you create the CloudFormation stacks and StackSet in the correct accounts: central and member.
• If you get a permission denied error from Athena on the S3 path (see the following screenshot), review the steps to grant QuickSight permissions.

• When creating the QuickSight dashboard using the template, if you get an error similar to the following, make sure that you use the ARN value from the output generated by the aws quicksight list-users --region <region> --aws-account-id <account-id> --namespace default command.

An error occurred (InvalidParameterValueException) when calling the CreateDashboard operation: Principal ARN xxxx is not part of the same account yyyy

• When deleting the stack, if you encounter the DELETE_FAILED error, it means that your S3 bucket is not empty. To fix it, empty the contents of the bucket and try to delete the Stack again.

Conclusion

Congratulations! You have successfully built an analytics pipeline to push support cases created in individual member accounts into a central account. You have also built an analytics dashboard to gain visibility and insights on all support cases created in various accounts. As you start creating support cases in your member accounts, you will be able to view them in a single pane of glass.

With the steps and resources described in this post, you can build your own analytics dashboard to gain visibility and insights on all support cases created in various accounts within your organization.

About the authors

Sindhura Palakodety is a Solutions Architect at AWS. She is passionate about helping customers build enterprise-scale Well-Architected solutions on the AWS platform and specializes in the data analytics domain.

Shu Sia Lukito is a Partner Solutions Architect at AWS. She is on a mission to help AWS partners build successful AWS practices and help their customers accelerate their journey to the cloud. In her spare time, she enjoys spending time with her family and making spicy food.