AWS Cloud Operations Blog

Visualize AWS Systems Manager Patch Manager information using Amazon QuickSight

In this blog post, learn how to build an Amazon QuickSight dashboard to visualize critical patch and inventory information to speed up MTTR. Also, you can use filters to search for a specific AWS Account, specific AWS Region, Amazon Elastic Compute Cloud (Amazon EC2) name, or check installed/missed packages.

You want to visualize system patching information for multiple Amazon EC2 instances running in multiple AWS Accounts, and you may want to have a centralized dashboard where you can find specific packages or troubleshoot systems that are missing some package installation. Also, sometimes you may need to know how many EC2 instances running a specific Operating System (OS) version, filter instances per AWS Account, or see instances per AWS Region for each AWS Account.

Additionally, this solution is not limited to Amazon EC2 instances, but it also allows you to visualize system patching information for servers and virtual machines (VMs) in your hybrid environment.

Solution overview

Blog Figure 1

Figure 1: Architectural overview

The AWS CloudFormation template dynamically creates the following tasks:

  • Amazon Simple Storage Service (Amazon S3)
  • AmazonS3 BucketPolicy – S3BucketPolicy
  • AWS Glue Database – GlueDatabase
  • AWS Glue Crawler – GlueCrawler
  • AWS IAM Role – GlueCrawlerRole
  • AWS IAM Role – DeleteGlueTableColumnFunctionRole
  • AWS Lambda Function – DeleteGlueTableColumnFunction
  • AWS Events Rule – DeleteGlueTableColumnFunctionEventRule
  • AWS Lambda Permission – DeleteGlueTableColumnFunctionCloudWatchPermission
  • AWS CloudFormation StackSet
    • AWS Systems Manager ResourceDataSync – ResourceDataSync
  • AWS IAM Role – QuicksightLambdaExecutionRole
  • AWS Lambda Function – QuickSightFunction
  • Custom Function – QuickSight
    • QuickSight Data Source
    • QuickSight Data Sets

Prerequisites

In order to create the QuickSight dashboard, you will need to meet the prerequisites below:

Walk-through

I’m using an AWS CloudFormation (CFN) template to dynamically build the infrastructure, which will create an Amazon S3 bucket in the primary AWS Account, and setup Systems Manager resource data sync in the AWS Account’s Region specified in the CFN template. This sends Systems Manager’s patching and inventory data to the S3 bucket. Then, you will need to create an Amazon Athena table for data visualization. Finally, create a QuickSight dataset, visualization, and dashboard.

After you have confirmed that you meet all prerequisites, deploy the CloudFormation template: BlogCFN.yaml

Important: The region of the Amazon S3 bucket must be the same where you are deploying CFN.

Connect to QuickSight and add access to Athena

  1. In the Amazon QuickSight console, log in.
  2. Choose Admin/username, Manage QuickSight.
  3. Choose Security & Permissions.
  4. Under QuickSight access to AWS services, choose Manage.
  5. Choose Amazon Athena and Amazon S3, also, select the S3 bucket of Systems Manager resource datasync and check default permissions (Read only).

Create visualizations for “Systems Manager Patch overview” dashboard

Create analysis PatchComplianceItem

  1. In the QuickSight console choose Analyses, then “New analysis”.
  2. Select ssm_aws_complianceitem, and Create analysis or Use in Analysis.

First Visualization: AccountId x InstanceId

  1. In Dataset select confirm that “ssm_aws_complianceitem” is selected.
  2. Select the Visual in the Sheet 1, then click on “accountid” in the Fields List.
  3. Select “resourceid” and drag and drop into Value in the ADD DATA.
  4. Change the “resourceid” values, in the Value Options in the dropdown arrow next to Value “resourceid”, go to “Aggregate: Count” and select “Count distinct”.
  5. In “Visual types”, select “Pie chart”.
  6. In the Visualization, click on the menu option (…), and select “Hide legend”.
  7. Double click the visual and modify the Title to AccountIDs.

Second Visualization: Region x InstanceId

  1. Create a new Visualization by deselecting the current visualization, and click in the blank part.
  2. Click on “region” in the Fields List.
  3. Select “resourceid” and drag and drop into Value in the ADD DATA.
  4. Change the “resourceid” values, in the Value Options in the dropdown arrow next to Value “resourceid”, go to “Aggregate: Count” and select “Count distinct”.
  5. In “Visual types”, select “Pie chart”.
  6. In the Visualization, click on the menu option (…), and select “Hide legend”.
  7. Double click the visual and modify the Title to Regions.

Third and Fourth Visualizations:

  1. Now that you are familiar with steps, use the steps above (from 1 to 6) to create the new visualizations:
  2. Severity: “Y axis: patchseverity”, and “Value: resourceid”.
  3. Patch Baseline ID: “Y axis: patchbaselineid”, and “Value: patchstate”.

Fifth Visualization: Patch table

  1. Create a new Visualization table by deselecting the current visualization, and click in the blank part.
  2. Click on “installedtime” in the Fields List, and then in “Visuals” select “Table”.
  3. Now select multiple fields from Fields list: Select “accountid”, “region”, “patchbaselineid”, “resourceid”, “patchseverity”, “status”, and “title”.
  4. Double click the visual and modify the Title to Patch Details, and align to the center.
  5. Select the Patch table visualization, and select the Filter option in the top of pane.
  6. Create a new filter and select “patchbaselineid”.
  7. After create the filter, open the filter and modify Filter condition to Exclude, and select empty patchbaseline to keep only entries associated with patchbaselineid.

Add Tags table

  1. In the top left, click on the “Dataset” dropdown menu and click on “Add a new dataset”.
  2. Add dataset, and select ssm_aws_tag.
  3. In Dataset confirm that “ssm_aws_tags” is selected.
  4. Create a new Visualization table by deselecting the current visualization, and click in the blank part.
  5. Click on “capturetime” in the Fields List, and then in “Visuals” select “Table”.
  6. Now select multiple fields from Fields list: Select “accountid”, “resourceid”, “region”, “key”, and “value”.
  7. Double click the visual and modify the Title to Tag Details, and align to the center.

Filters

  1. Select the Patch Details table visualization, and select the Filter option in the top of pane.
  2. Create a new filter and select “CaptureDateTime”.
  3. After create the filter, click on the menu option (…) and choose “Add to sheet”.
  4. Open the filter “CaptureDateTime”, and choose “All applicable visuals” in the top of the filter.
  5. After add the filter to the sheet, select it in the Sheet and click on the menu option (…) then choose “Pin to top”. This will create a “Control” pane in the top with the filter.
  6. Create a new filter for “accountid” by following the steps above (from 1 to 4).
  7. Create a new filter for “resourceid” by following the steps above (from 1 to 4).
  8. Create a new filter for “region” by following the steps above (from 1 to 4).
  9. Create a new filter for “status” by following the steps above (from 1 to 4).
  10. Select the Tag Details table visualization, and select the Filter option in the top of pane.
  11. Create a new filter for “key” by following the steps above (from 1 to 4).
  12. Create a new filter for “value” by following the steps above (from 1 to 4).
  13. Add relationship between filters. Follow the steps below for filters “accountid”, “resourceid”, “region”, “status”, “key”, and “value”:
  14. Expand the “Controls” in the top of the Sheet, then select the filter and click on the menu (…) then “Edit”.
  15. Change the name to reflect the filters name.
  16. Click on Control Options and “Show relevant values only”, then select the box for all filters, click in “Update” then “Apply”.

Note: For relevant values on filter “CaptureDateTime”, select the field to match the text box value “CaptureDateTime”.

Publish the new dashboard – Systems Manager Patch Overview

  1. In the top right of the Analysis pane, click on “Share”, and “Publish dashboard”.
  2. Add a Dashboard name: “SSM Patch Overview”, then click “Publish dashboard”.

Blog Figure 2

Figure 2: QuickSight Dashboard – Systems Manager Patch Overview

Create visualizations for “Systems Manager Instances Overview” dashboard

Create analysis InstanceCompliance

  1. In the QuickSight console choose Analyses, then New analysis.
  2. Select instances_compliance, and Create analysis or Use in Analysis.

First Visualization: OS Platform

  1. In Dataset select confirm that “instances_compliance” is selected.
  2. Select the Visual in the Sheet 1, then click on “accountid” in the Fields List.
  3. Select “resourceid” and drag and drop into Value in the ADD DATA.
  4. Select “platformname” and drag and drop into Group/Color.
  5. Change the “resourceid” values, in the Value Option in the dropdown arrow next to Value “resourceid”, go to “Aggregate: Count” and select “Count distinct”.
  6. In “Visual types”, select “Vertical stacked bar chart”.
  7. In the Visualization, click on the menu option (…), and select “Hide legend”.
  8. Select the Visual and open “Properties” option in the top of pane:
  9. Edit title to “OS Platform”.
  10. Expand “Legend” and check: “Show legend”, “Hide legend title”, and Position Top.
  11. Expand “Data labels” and check: “Show data labels”, and “Show totals”.

Second Visualization: Regions

  1. Create a new Visualization by deselecting the current visualization, and click in the blank part.
  2. Click on “accountid” in the Fields List.
  3. Select “resourceid” and drag and drop into Value in the ADD DATA.
  4. Select “region” and drag and drop into Group/Color.
  5. Change the “resourceid” values, in the Value Option in the dropdown arrow next to Value “resourceid”, go to “Aggregate: Count” and select “Count distinct”.
  6. In “Visual types”, select “Vertical stacked bar chart”.
  7. In the Visualization, click on the menu option (…), and select “Hide legend”.
  8. Select the Visual and open “Properties” option in the top of pane:
  9. Edit title to “Regions”.
  10. Expand “Legend” and check: “Show legend”, “Hide legend title”, and Position Top.
  11. Expand “Data labels” and check: “Show data labels”, and “Show totals”.

Third Visualization: Instances Status

  1. Create a new Visualization by deselecting the current visualization, and click in the blank part.
  2. Click on “instancestatus” in the Fields List.
  3. Select “resourceid” and drag and drop into Value in the ADD DATA.
  4. Select “accountid” and drag and drop into Group/Color.
  5. Change the “resourceid” values, in the Fields List click in the dropdown arrow next to Value “resourceid”, go to “Aggregate: Count” and select “Count distinct”.
  6. In “Visual types”, select “Vertical stacked bar chart”.
  7. In the Visualization, click on the menu option (…), and select “Hide legend”.
  8. Select the Visual and open “Properties” option in the top of pane:
  9. Edit title to “Instances status”.
  10. Expand “Legend” and check: “Show legend”, “Hide legend title”, and Position Top.
  11. Expand “Data labels” and check: “Show data labels”, and “Show totals”.

Fourth Visualization: Compliance Status

  1. Create a new Visualization by deselecting the current visualization, and click in the blank part.
  2. Click on “status” in the Fields List.
  3. Select “resourceid” and drag and drop into Value in the ADD DATA.
  4. Select “accountid” and drag and drop into Group/Color.
  5. Change the “resourceid” values, in the Fields List click in the dropdown arrow next to Value “resourceid”, go to “Aggregate: Count” and select “Count distinct”.
  6. In “Visual types”, select “Vertical stacked bar chart”.
  7. In the Visualization, click on the menu option (…), and select “Hide legend”.
  8. Select the Visual and open “Properties” option in the top of pane:
  9. Edit title to “Compliance Status”.
  10. Expand “Legend” and check: “Show legend”, “Hide legend title”, and Position Top.
  11. Expand “Data labels” and check: “Show data labels”, and “Show totals”.

Instance details

  1. Create a new Visualization table by deselecting the current visualization, and click in the blank part.
  2. Click on “resourceid” in the Fields List, and then in “Visual types” select “Table”.
  3. Now select multiple fields from Fields list: Select “accountid”, “region”, and “name”.
  4. Double click the visual and modify the Title to “Instance Details”, and align to the center.

Filters

  1. Select the “OS Platform” visualization, and select the Filter option in the top of pane.
  2. Create a new filter and select “CaptureDateTime”.
  3. After create the filter, click on the menu (…) and choose “Add to sheet”.
  4. Open the filter “CaptureDateTime”, and choose “All applicable visuals” in the top of the filter.
  5. After add the filter to the sheet, select it in the Sheet and click on the menu (…) then choose “Pin to top”. This will create a “Control” pane in the top with the filter.
  6. Create a new filter for “accountid” by following the steps above (from 1 to 5).
  7. Create a new filter for “region” by following the steps above (from 1 to 5).
  8. Create a new filter for “resourceid” by following the steps above (from 1 to 5).
  9. Create a new filter for “platformname” by following the steps above (from 1 to 5).
  10. Create a new filter for “status” by following the steps above (from 1 to 5).
  11. Create a new filter for “Name” by following the steps above (from 1 to 5).
  12. Add relationship between filters. Follow the steps below for filters “accountid”, “region”, resourceid”, “platformname”, “status”, and “Name”:
  13. Expand the “Controls” in the top of the Sheet, then select the filter and click on the menu (…) then “Edit”.
  14. Change the name to reflect the filters name.
  15. Click on Control Options and “Show relevant values only”, then select the box for all filters, click in “Update” then “Apply”.

Note: For relevant values on filter “CaptureDateTime”, select the field to match the text box value “CaptureDateTime”.

Publish the new dashboard – Systems Manager Instances Overview

  1. In the top right of the Analysis pane, click on “Share”, and “Publish dashboard”.
  2. Add a Dashboard name: “SSM Instances Overview”, then click “Publish dashboard”.

Blog Figure 3

Figure 3: QuickSight Dashboard – Systems Manager Instances Overview

Cleanup

After you have tested this solution, delete all resources to avoid continuing charges to your AWS Accounts.

Delete the pipeline

In order to delete all resources, follow the instructions in Delete a stack from the CloudFormation console. Deleting the stack will delete all the resources. You must delete all of the objects in the Amazon S3 buckets first.

Conclusion

In this post, I provided a CloudFormation template to dynamically build a QuickSight dashboard where you can visualize Systems Manager patching/inventory information. Also, I’ve provided the steps to create the QuickSight dashboard with useful filters.

The template can be used as is, and you can follow the steps to build the QuickSight dashboard with filters, but I encourage you to develop your own dashboard and filters that suits your requirements.

For more information, see the Setting up AWS Systems Manager user guide, Creating your first stack Walkthroughs, and Getting started with Amazon QuickSight data analysis.

About the author:

Eloi da Silva

Eloi is a Cloud Support Engineer in AWS Premium Support. He is a multi-domain enthusiast with a vast experience in Network, Security, Automation, and he specializes in Amazon EC2 Linux. In his role, Eloi enjoys helping customers and his colleagues to solve complex issues, and is passionate about coding solutions to automate processes. Outside of work, he loves spending time with his family and friends, enjoys sports and music.