You're reading for free via Ekant Mate (AWS APN Ambassador)'s Friend Link. Become a member to access the best of Medium.

Member-only story

Centralized AWS SSM Patch Compliance Reporting Using Terraform and Quicksight

Ekant Mate (AWS APN Ambassador)
AWS in Plain English
12 min readDec 11, 2024

Centralize AWS Patch Compliance Reporting with Terraform — Step-by-Step Guide

Architectural Overview

Learn how to set up centralized AWS Patch Compliance Reporting using Terraform. This guide includes resource creation in multiple accounts, delegation to an admin account, and automation with Glue and Athena.

Introduction

Maintaining consistent patch compliance across multiple AWS accounts can be challenging, especially in a multi-account setup. AWS Systems Manager (SSM) provides powerful tools to automate inventory collection and compliance reporting. This blog will guide you through configuring centralized AWS Patch Compliance Reporting using Terraform.

With this approach, you’ll use SSM for inventory collection in each account, centralize the compliance data in an admin account, and enable data analysis using AWS Glue and Athena.

Why Centralized Patch Compliance Reporting?

A centralized approach to patch compliance reporting ensures:

  1. Comprehensive Visibility: Centralized data collection helps track compliance across all managed AWS accounts.
  2. Ease of Management: Using a single S3 bucket for compliance data simplifies management.
  3. Automated Insights: Integration with Glue and Athena enables query-based reporting for actionable insights.
  4. Cost Efficiency: Reduces redundant resources by centralizing data storage and processing.

Implementation Overview

The implementation involves two phases:

  1. Per-Account Setup: Configure SSM associations and enable inventory collection in each AWS account.
  2. Admin Account Configuration: Set up a central S3 bucket, Glue crawlers, Athena workgroups, and permissions in a designated admin account.

Step-by-Step Guide

1. Per-Account Setup

In each AWS account, the following Terraform resources must be deployed:

SSM Association for Inventory Collection

The aws_ssm_association resource ensures that all instances in the account automatically report inventory data to AWS Systems Manager.

resource "aws_ssm_association" "ssm_inventory" {
name = "AWS-GatherSoftwareInventory"
association_name = "SystemAssociationForInventoryCollection"
targets {
key = "InstanceIds"
values = ["*"]
}
parameters = {
applications = "Enabled"
awsComponents = "Enabled"
customInventory = "Enabled"
instanceDetailedInformation = "Enabled"
networkConfig = "Enabled"
services = "Enabled"
windowsRoles = "Enabled"
windowsUpdates = "Enabled"
}
schedule_expression = "rate(30 minutes)"
}

This configuration:

  • Creates an association to gather software inventory from all instances every 30 minutes.
  • Syncs the collected data to an S3 bucket for centralized access.

SSM Resource Data Sync

Data from each account is sent to a central S3 bucket using the aws_ssm_resource_data_sync resource.

resource "aws_ssm_resource_data_sync" "resource_sync" {
name = "resource_sync"

s3_destination {
bucket_name = "patch-compliance-reporting"
region = "ap-southeast-2"
}
}

2. Admin Account Configuration

The admin account aggregates and analyzes the compliance data.

S3 Bucket for Data Storage

Create a central S3 bucket to store compliance data.

resource "aws_s3_bucket" "patch_compliance" {
bucket = "patch-compliance-reporting"
}

S3 Bucket Policy

The bucket policy grants access to AWS services like Systems Manager, Glue, QuickSight, and Athena. Here’s a snippet:

data "aws_iam_policy_document" "ssm_bucket_policy" {
statement {
sid = "SSMBucketPermissionsCheck"
effect = "Allow"
principals {
type = "Service"
identifiers = ["ssm.amazonaws.com"]
}
actions = ["s3:GetBucketAcl"]
resources = ["arn:aws:s3:::${aws_s3_bucket.patch_compliance.id}"]
}

statement {
sid = "ServicesQuickSightGlueAthena"
effect = "Allow"
principals {
type = "Service"
identifiers = ["glue.amazonaws.com", "quicksight.amazonaws.com", "athena.amazonaws.com"]
}
actions = ["s3:Get*", "s3:List*"]
resources = ["arn:aws:s3:::${aws_s3_bucket.patch_compliance.id}"]
}

statement {
sid = "SSMBucketDelivery"
effect = "Allow"
principals {
type = "Service"
identifiers = ["ssm.amazonaws.com"]
}
actions = ["s3:PutObject"]
resources = ["arn:aws:s3:::${aws_s3_bucket.patch_compliance.id}/*/accountid=*/*"]
condition {
test = "StringEquals"
variable = "s3:x-amz-acl"
values = ["bucket-owner-full-control"]
}
condition {
test = "StringEquals"
variable = "aws:SourceOrgID"
values = ["o-*******"]
}
}

Statement 1 allows the AWS Systems Manager (SSM) service to perform the s3:GetBucketAcl action on the specified S3 bucket. This is necessary for SSM to verify bucket permissions and ensure it can deliver data into the bucket.

Statement 2 grants AWS Glue, QuickSight, and Athena services permission to read objects and list bucket contents. These permissions are needed for these services to process and analyze data stored in the bucket.

Statement 3 allows SSM to upload objects to the S3 bucket, specifically for compliance reporting data. The conditions ensure that SSM applies the bucket-owner-full-control ACL and restricts access to objects originating from the specified AWS organization.

Glue and Athena for Data Analysis

  1. Glue Database:
    Create a Glue database for storing the cataloged compliance data.

This creates a Glue database named ssm_datasync_resources, which serves as a central repository for metadata about the data collected by the Glue crawler. It is essential for organizing and querying data in AWS Glue.

resource "aws_glue_catalog_database" "ssm_data_sync_db" {
name = "datasync_resources"
description = "Systems Manager Global Resource Data Sync Database"
}

2. Glue Crawler:
Configure Glue crawlers to index compliance data stored in the S3 bucket.

This resource defines a Glue crawler that scans data in the specified S3 bucket path (AWS:Application/) and updates the Glue database with table schemas. The crawler ensures that data is ready for analysis using AWS Athena or other services.

resource "aws_glue_crawler" "ssm_crawler" {
database_name = aws_glue_catalog_database.ssm_data_sync_db.name
name = "ssm-crawler"
role = aws_iam_role.glue_crawler_role.arn
s3_target {
path = "s3://${aws_s3_bucket.patch_compliance.bucket}/AWS:Application/"
}
}

3. Athena Workgroup:
Set up Athena for running SQL queries on the compliance data.

This defines an Athena workgroup named primary-workgroup for executing SQL queries on the data cataloged by Glue. The result configuration specifies an S3 bucket location for storing the query results, making it easier to analyze compliance data efficiently.

resource "aws_athena_workgroup" "primary" {
name = "primary-workgroup"
configuration {
result_configuration {
output_location = "s3://aws-athena-query-results-region-accountid/"
}
}
}

Now Configure AWS Quicksight

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”.

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”.

Cleanup

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

Benefits of This Approach

  • Scalability: Easily manage multiple accounts with centralized reporting.
  • Automation: SSM, Glue, and Athena automate data collection and analysis.
  • Compliance: Monitor and ensure patch compliance in real time.
  • Cost-Effectiveness: Reduces manual effort and redundant data storage.

Conclusion

Setting up centralized AWS Patch Compliance Reporting ensures streamlined inventory management and actionable insights for maintaining compliance across multiple accounts. By leveraging Terraform, you can automate the entire process and reduce operational overhead. Start today and keep your AWS environment secure and compliant.

Please follow me for more such innovative blogs And if you find my blogs helpful, I’d really appreciate your claps — they motivate me to keep sharing more valuable insights.

Thank you for being awesome!

In Plain English 🚀

Thank you for being a part of the In Plain English community! Before you go:

Published in AWS in Plain English

New AWS, Cloud, and DevOps content every day. Follow to join our 3.5M+ monthly readers.

Written by Ekant Mate (AWS APN Ambassador)

Technologist, Cloud evangelist & Solution Architect specializing in Design, DevOps, Security, Network. Expert advisor, World Tech Enthusiast, Motivational Blog.

No responses yet

What are your thoughts?