I want the number of configuration items recorded by AWS Config.

To help you identify previous and current billing trends, use Amazon Athena to identify the number of configuration items per month for your account.

Note: Be sure the AWS Config service is able to successfully deliver configuration history files to your Amazon Simple Storage Service (Amazon S3) bucket.

1.  Open the AWS Config console, and then choose Settings from the navigation pane.

2.  In the Amazon S3 bucket section, note the Bucket name.

3.  Sign in to the Amazon S3 console, and then for Bucket name, choose your S3 bucket. Be sure that the S3 bucket you choose contains files. Note: If you are missing configuration files, your role might be missing permissions. For more information, see Setting Bucket and Object Access Permissions.

Create a table in Athena

1.  Sign in to the Athena console, and then follow the instructions at To create a table using the wizard.

2.  Use the following table syntax. For LOCATION, use the location and region for the AWS Config items stored in your Amazon Simple Storage Service (Amazon S3) bucket.

CREATE EXTERNAL TABLE awsconfig (
         fileversion string,
         configSnapshotId string,
         configurationitems ARRAY < STRUCT < configurationItemVersion : STRING,
         configurationItemCaptureTime : STRING,
         configurationStateId : BIGINT,
         awsAccountId : STRING,
         configurationItemStatus : STRING,
         resourceType : STRING,
         resourceId : STRING,
         resourceName : STRING,
         ARN : STRING,
         awsRegion : STRING,
         availabilityZone : STRING,
         configurationStateMd5Hash : STRING,
         resourceCreationTime : STRING > > 
) 
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION 's3://<BUCKET-NAME>/AWSLogs/<ACCOUNT-ID>/Config/<REGION>/';

Sample Athena queries

The following sample query retrieves the number of configuration items per day in February 2018:

SELECT result.configurationitemcapturetime,
         count(result.configurationitemcapturetime) AS NumberOfChanges
FROM 
    (SELECT regexp_replace(configurationItem.configurationItemCaptureTime,
         '(.+)(T.+)', '$1') AS configurationitemcapturetime
    FROM default.awsconfig
    CROSS JOIN UNNEST(configurationitems) AS t(configurationItem)
    WHERE "$path" LIKE '%ConfigHistory%'
            AND configurationItem.configurationItemCaptureTime >= '2018-02-01T%'
            AND configurationItem.configurationItemCaptureTime <= '2018-02-28T%') result
GROUP BY  result.configurationitemcapturetime
ORDER BY  result.configurationitemcapturetime

The results are similar to this:

configurationitemcapturetime    NumberOfChanges
2018-02-02    7
2018-02-03    3
2018-02-07   11
...

The following sample query retrieves the number of changes per resource in February 2018, sorted by most frequently changed:

SELECT configurationItem.resourceType,
         configurationItem.resourceId,
         COUNT(configurationItem.resourceId) AS NumberOfChanges
FROM default.awsconfig
CROSS JOIN UNNEST(configurationitems) AS t(configurationItem)
WHERE "$path" LIKE '%ConfigHistory%'
        AND configurationItem.configurationItemCaptureTime >= '2018-02-01T%'
        AND configurationItem.configurationItemCaptureTime <= '2018-02-28T%'
GROUP BY  configurationItem.resourceType, configurationItem.resourceId
ORDER BY  NumberOfChanges DESC

The results are similar to this:
resourcetype              resourceid        NumberOfChanges
AWS::EC2::VPC             vpc-9ed00bfa        7
AWS::EC2::Subnet          subnet-4472e248     5
AWS::EC2::SecurityGroup   sg-450c6531         4

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-06-08