Walkthrough: Using resource data sync to aggregate inventory data

Open the Amazon S3 console at https://console.amazonaws.cn/s3/.

Create a bucket to store your aggregated inventory data. For more information, see Creating a bucket in the Amazon Simple Storage Service User Guide. Make a note of the bucket name and the Amazon Web Services Region where you created it.

After you create the bucket, choose the Permissions tab, and then choose Bucket Policy.

Copy and paste the following bucket policy into the policy editor. Replace amzn-s3-demo-bucket and account-id with the name of the Amazon S3 bucket you created and a valid Amazon Web Services account ID. When adding multiple accounts, add an additional condition string and ARN for each account. Remove the additional placeholders from the example when adding one account. Optionally, replace bucket-prefix with the name of an Amazon S3 prefix (subdirectory). If you didn't created a prefix, remove bucket-prefix/ from the ARN in the policy.

(Optional) If you want to encrypt the sync, then you must add the following conditions to the policy listed in the previous step. Add these in the StringEquals section.

"s3:x-amz-server-side-encryption":"aws:kms",
"s3:x-amz-server-side-encryption-aws-kms-key-id":"arn:aws-cn:kms:region:account_ID:key/KMS_key_ID"

Here is an example:

"StringEquals": {
          "s3:x-amz-acl": "bucket-owner-full-control",
          "aws:SourceAccount": "account-id",
          "s3:x-amz-server-side-encryption":"aws:kms",
          "s3:x-amz-server-side-encryption-aws-kms-key-id":"arn:aws-cn:kms:region:account_ID:key/KMS_key_ID"
        }

Install and configure the Amazon Command Line Interface (Amazon CLI), if you haven't already.

For information, see Installing or updating the latest version of the Amazon CLI.

(Optional) If you want to encrypt the sync, run the following command to verify that the bucket policy is enforcing the Amazon KMS key requirement. Replace each example resource placeholder with your own information.

Run the following command to create a resource data sync configuration with the Amazon S3 bucket you created at the start of this procedure. This command creates a sync from the Amazon Web Services Region you're logged into.

You can use the region parameter to specify where the sync configuration should be created. In the following example, inventory data from the us-west-1 Region, will be synchronized in the Amazon S3 bucket in the us-west-2 Region.

(Optional) If you want to encrypt the sync by using Amazon KMS, run the following command to create the sync. If you encrypt the sync, then the Amazon KMS key and the Amazon S3 bucket must be in the same Region.

Run the following command to view the status of sync configuration.

aws ssm list-resource-data-sync 

If you created the sync configuration in a different Region, then you must specify the region parameter, as shown in the following example.

aws ssm list-resource-data-sync --region us-west-1

After the sync configuration is created successfully, examine the target bucket in Amazon S3. Inventory data should be displayed within a few minutes.

Open the Athena console at https://console.amazonaws.cn/athena/.

Copy and paste the following statement into the query editor and then choose Run Query.

CREATE DATABASE ssminventory

The system creates a database called ssminventory.

Copy and paste the following statement into the query editor and then choose Run Query. Replace amzn-s3-demo-bucket and bucket_prefix with the name and prefix of the Amazon S3 target.

CREATE EXTERNAL TABLE IF NOT EXISTS ssminventory.AWS_Application (
Name string,
ResourceId string,
ApplicationType string,
Publisher string,
Version string,
InstalledTime string,
Architecture string,
URL string,
Summary string,
PackageId string
) 
PARTITIONED BY (AccountId string, Region string, ResourceType string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://amzn-s3-demo-bucket/bucket_prefix/AWS:Application/'

Copy and paste the following statement into the query editor and then choose Run Query.

MSCK REPAIR TABLE ssminventory.AWS_Application

The system partitions the table.

To preview your data, choose the view icon next to the AWS_Application table.

Copy and paste the following statement into the query editor and then choose Run Query.

SELECT a.name, a.version, count( a.version) frequency 
from aws_application a where
a.name = 'aws-cfn-bootstrap'
group by a.name, a.version
order  by frequency desc

The query returns a count of different versions of aws-cfn-bootstrap, which is an Amazon application present on Amazon Elastic Compute Cloud (Amazon EC2) instances for Linux, macOS, and Windows Server.

Individually copy and paste the following statements into the query editor, replace amzn-s3-demo-bucket and bucket-prefix with information for Amazon S3, and then choose Run Query. These statements set up additional inventory tables in Athena.

CREATE EXTERNAL TABLE IF NOT EXISTS ssminventory.AWS_AWSComponent (
 `ResourceId` string,
  `Name` string,
  `ApplicationType` string,
  `Publisher` string,
  `Version` string,
  `InstalledTime` string,
  `Architecture` string,
  `URL` string
)
PARTITIONED BY (AccountId string, Region string, ResourceType string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://amzn-s3-demo-bucket/bucket-prefix/AWS:AWSComponent/'

MSCK REPAIR TABLE ssminventory.AWS_AWSComponent

CREATE EXTERNAL TABLE IF NOT EXISTS ssminventory.AWS_WindowsUpdate (
  `ResourceId` string,
  `HotFixId` string,
  `Description` string,
  `InstalledTime` string,
  `InstalledBy` string
)
PARTITIONED BY (AccountId string, Region string, ResourceType string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://amzn-s3-demo-bucket/bucket-prefix/AWS:WindowsUpdate/'

MSCK REPAIR TABLE ssminventory.AWS_WindowsUpdate

CREATE EXTERNAL TABLE IF NOT EXISTS ssminventory.AWS_InstanceInformation (
  `AgentType` string,
  `AgentVersion` string,
  `ComputerName` string,
  `IamRole` string,
  `InstanceId` string,
  `IpAddress` string,
  `PlatformName` string,
  `PlatformType` string,
  `PlatformVersion` string
)
PARTITIONED BY (AccountId string, Region string, ResourceType string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://amzn-s3-demo-bucket/bucket-prefix/AWS:InstanceInformation/'

MSCK REPAIR TABLE ssminventory.AWS_InstanceInformation

CREATE EXTERNAL TABLE IF NOT EXISTS ssminventory.AWS_Network (
  `ResourceId` string,
  `Name` string,
  `SubnetMask` string,
  `Gateway` string,
  `DHCPServer` string,
  `DNSServer` string,
  `MacAddress` string,
  `IPV4` string,
  `IPV6` string
)
PARTITIONED BY (AccountId string, Region string, ResourceType string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://amzn-s3-demo-bucket/bucket-prefix/AWS:Network/'

MSCK REPAIR TABLE ssminventory.AWS_Network

CREATE EXTERNAL TABLE IF NOT EXISTS ssminventory.AWS_PatchSummary (
  `ResourceId` string,
  `PatchGroup` string,
  `BaselineId` string,
  `SnapshotId` string,
  `OwnerInformation` string,
  `InstalledCount` int,
  `InstalledOtherCount` int,
  `NotApplicableCount` int,
  `MissingCount` int,
  `FailedCount` int,
  `OperationType` string,
  `OperationStartTime` string,
  `OperationEndTime` string
)
PARTITIONED BY (AccountId string, Region string, ResourceType string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://amzn-s3-demo-bucket/bucket-prefix/AWS:PatchSummary/'

MSCK REPAIR TABLE ssminventory.AWS_PatchSummary

Sign up for Amazon Quick Suite and then log in to the Quick Suite console.

Create a data set from the AWS_Application table and any other tables you created. For more information, see Creating a dataset using Amazon Athena data in the Amazon Quick Suite User Guide.

Join tables. For example, you could join the instanceid column from AWS_InstanceInformation because it matches the resourceid column in other inventory tables. For more information about joining tables, see Joining data in the Amazon Quick Suite User Guide.

Build a visualization. For more information, see Analyses and reports: Visualizing data in Amazon Quick Sight in the Amazon Quick Suite User Guide.