Systems Manager inventory data in a resource-synced centralized bucket: Looking for useful Athena or QuickSight example queries or visualizations to create useful reports and dashboards from the data.

Question

I have setup systems manager inventory within multiple accounts and am using resource sync to copy the inventory to a centralized s3 bucket. I am then using AWS Glue and Athena to create a database and tables based on the inventory data. Everything is working great and I can query the data in Athena just fine.

What I am looking for is if anyone has created useful queries for creating inventory reports that they are willing to share or have a link to? I am basically just trying to not reinvent the wheel. Any queries that provide useful data such as instanceid's and their OS versions, tags, patches installed, etc. Could be anything that you find useful.

Also looking at using Quicksight to view the data and looking for examples there as well.

All AWS and non-AWS articles I have found all show how to implement this system and state then to use Athena and Quicksight to view your data, but I have not found many good examples of the queries or reports. I can spend the time to figure it all out, but would rather get a head start from what others have done.

Thanks!!!

Accepted Answer

Hello,

I was able to find the following resources that could help you build a QuickSight dashboard for your AWS Systems Management Inventory data:
* ***Query and visualize Microsoft SQL Server license utilization using Amazon Athena and Amazon QuickSight*** - https://aws.amazon.com/blogs/mt/query-and-visualize-microsoft-sql-server-license-utilization-using-amazon-athena-and-amazon-quicksight/
* ***New – S3 Sync capability for EC2 Systems Manager: Query & Visualize Instance Software Inventory*** - https://aws.amazon.com/blogs/aws/new-s3-sync-capability-for-ec2-systems-manager-query-visualize-instance-software-inventory/ 
* ***Patch Compliance Analysis Reference Architecture*** - https://github.com/aelivingstone/patch-compliance

Additionally, the following blog articles include QuickSight dashboard examples using data from other AWS services, which you can use as reference when building a QuickSight dashboard for your AWS Systems Management Inventory data:
* ***Visualizing AWS Config data using Amazon Athena and Amazon QuickSight*** - https://aws.amazon.com/pt/blogs/mt/visualizing-aws-config-data-using-amazon-athena-and-amazon-quicksight/ 
* ***Build a resilience reporting dashboard with AWS Resilience Hub and Amazon QuickSight*** - https://aws.amazon.com/blogs/mt/resilience-reporting-dashboard-aws-resilience-hub/ 
* ***A detailed overview of Trusted Advisor Organizational Dashboard*** - https://aws.amazon.com/blogs/mt/a-detailed-overview-of-trusted-advisor-organizational-dashboard/

I hope this information was helpful.

Have a nice day!

Systems Manager inventory data in a resource-synced centralized bucket: Looking for useful Athena or QuickSight example queries or visualizations to create useful reports and dashboards from the data.

Relevant content