How do I use QuickSight to manage and view the Systems Manager patch and association compliance data for all my accounts?

Resolution

Configure the prerequisite services

Before you use Amazon QuickSight to manage and view compliance data, set up Systems Manager Inventory and resource data sync in your AWS account. 

You can use the resource data sync setup for multiple accounts and AWS Regions, and synchronize the data to a central Amazon Simple Storage Service (Amazon S3) bucket.

Systems Manager Inventory uses AWS Glue to aggregate the inventory data in the S3 bucket. AWS Glue uses the Amazon-GlueServiceRoleForSSM role to aggregate the data. If the S3 bucket is encrypted with AWS Key Management Service (AWS KMS) keys, then edit the Amazon-GlueServiceRoleForSSM role permissions to use AWS KMS keys. You must also configure your Identity and Access Management (IAM) account for AWS KMS encryption.

By default, AWS Glue crawler aggregates the inventory data in the central S3 bucket twice daily. So, data is updated based on this schedule. To modify the frequency, edit the AWS Glue crawler schedule.

Amazon Athena queries data that you can view on the Detailed View page of the Systems Manager console. You can view this data only in the Regions where Amazon Athena is available. To join multiple Amazon Athena tables, create a joined dataset in QuickSight. 

After you set up Systems Manager inventory, resource data sync, AWS Glue and Amazon Athena access, you can set up your QuickSight account.

Set up a QuickSight account

If you don't have a QuickSight account, then complete the following steps:

1. Use an IAM identity that has QuickSight permissions to sign in to the AWS Management Console.
2. Navigate to Amazon QuickSight.
3. Choose Enterprise or Enterprise + Q.
   -or-
   Choose Sign up for Standard Edition.
4. Select the appropriate IAM identity.
5. Under QuickSight access to AWS services, select Amazon Athena and Amazon S3.
6. For Select Amazon S3 buckets, select the target S3 bucket where inventory data is stored. To give write permission for Athena Workgroup, select Write permission for Athena Workgroup.
7. Choose Finish.

If you have an existing QuickSight account, then complete the following steps:

1. Open the QuickSight console.
2. Choose Manage QuickSight.
3. Choose Security & permissions.
4. Under QuickSight access to AWS services, choose Add or remove.
5. Select Amazon Athena and Amazon S3.
6. Choose Update.

Create a dataset in QuickSight

To create datasets in QuickSight, you can use Amazon Athena tables as the source. You can view these tables in Amazon Athena. Each inventory metadata has a corresponding Amazon Athena table that AWS Glue created. To create the dataset and analyze the data, use the aws_compliancesummary and aws_complianceitem tables.

Complete the following steps:

1. Open the QuickSight console.
2. In the navigation pane, choose Datasets.
3. On the Datasets page, choose New dataset.
4. Under Create a dataset, select Athena as the data source.
5. Enter the data source name.
6. Choose Create data source.
7. From the dropdown list of databases, select the S3 bucket.
   Note: The database name is in the S3_bucket_name-region-database format.
8. From the list of tables, select aws_compliancesummary.
9. Choose Select.
10. Select Directly query your data.
11. Choose Edit/Preview data.
12. Choose Save and publish.

Repeat the preceding steps to create another dataset for the aws_complianceitem table.

Analyze the dataset

To use the aws_compliancesummary and aws_complianceitem datasets for data analysis, complete the following steps:

1. Open the QuickSight console.
2. Choose New analysis.
3. On the Datasets page, choose aws_compliancesummary.
4. Choose USE IN ANALYSIS.
5. To add multiple datasets in the same analysis, choose Edit.
6. In the pop-up window that appears, choose Add dataset.
7. From the dropdown list, choose aws_complianceitem.
8. Choose Select.

Note: You can also add other datasets to the same analysis to create the visuals.

Add visuals

You can add a visual to your Amazon QuickSight analysis based on the QuickSight dataset. The dataset includes the tables from Amazon Athena that have the Systems Manager inventory data with compliance information.

Visuals for aws_compliancesummary

To view the number of resources based on patch compliance status, complete the following steps:

1. Open the QuickSight console.
2. Choose the analysis that you want to add a visual to.
3. On the Analysis page, choose the dataset that you want to use.
4. Choose Visualize.
5. On the Visualize pane, choose Add, and then choose Add visual.
6. Choose Visual types.
7. On the Visual types pane, select Donut chart.
8. From the Fields list, choose Status to add it to Group/Color dimension.
9. Under Value, drag and drop Resourceid.
10. To count distinct values, next to the resourceid field, choose the arrow.
11. Choose Aggregate: Count.
12. Choose Count distinct.
13. Select the graph.
14. Choose Format Visual icon.
15. Under Data labels, choose Show metric.

To view the compliant or non-compliant instances by Region, complete the following steps:

1. Choose the preceding visual.
2. Choose the More information icon on the chart.
3. Choose Duplicate visual.
4. Under Field wells, in the Group/Color dimension dropdown list, choose Region.
5. Choose Filter.
6. Choose ADD FILTER.
7. Select Status.
8. Select COMPLIANT or NON-COMPLIANT.
9. Choose Apply.

To view the account information for all accounts in a multi-account setup, use the preceding visual. Under Fields wells, in the Group/Color dimension, select accountid.

Visuals for aws_complianceitem dataset

To view the list of missing patches by instances, complete the following steps:

1. Open the QuickSight console.
2. Choose Visualize.
3. In the Visualize pane, choose Visual types.
4. In the Visual types pane, select Donut chart.
5. In Visual types, select Pivot table.
6. Under Rows, add the following values:
   Region
   resourceid
   patchstate
   id
   title
7. Under Values, add id.
8. To count distinct values, choose the arrow next to id.
9. Choose Aggregate: Count.
10. Choose Count distinct.
11. Choose Filter.
12. In Filter, choose Add Filter.
13. Choose Patchstate.
14. Select Missing.
15. Choose Apply.

To view the list of instances by compliance status, do the following:

1. Open the QuickSight console.
2. Choose New Analysis.
3. On the Datasets page, in the pop-up window that appears, choose Add dataset.
4. Choose aws_complianceitem.
5. Choose Add.
6. Choose Add visual.
7. In Visual types, select Pivot table.
8. Under Rows, add the following values:
   Region
   resourceid
   patchstate
   id
   title
9. Under Values, add id.
10. To count distinct values, choose the arrow next to id.
11. Choose Aggregate: Count.
12. Choose Count distinct.

To get information about all accounts, under Rows, add accountid.

Add filters

You can also add filters to filter the data based on compliance type, such as patch compliance and association compliance.

To add filters, complete the following steps:

1. Open the QuickSight console.
2. In the navigation pane, choose Filter.
3. Under Filters, choose Add filter.
4. Select Compliance type.
5. To include only patch compliance, choose Patch from the list.
6. From the Applied to dropdown list, select all applicable visuals.
7. Choose Apply.

Publish a dashboard

You can publish all the visuals as a dashboard to share with other users.

Complete the following steps:

1. After you add all the visuals, in the application bar, choose Themes.
2. Select the appropriate theme.
3. Choose Share.
4. Select Publish Dashboard.
5. Enter a name for the dashboard.
6. Choose Publish dashboard.