Visualizing AWS Config data using Amazon Athena and Amazon QuickSight

In this guest post, Henrik André Olsen, Solutions Architect, discusses how he visualized AWS Config data in Amazon QuickSight dashboards with a high value for the Danish insurance company Topdanmark. 

If you are an AWS Config user, you are probably already familiar with how to use the AWS Config console to access data, but it’s also possible to visualize the resource and compliance status of AWS resources deployed across multiple AWS accounts and Regions. Figures 1 and 2 show examples. This capability can be of enormous help to development teams, managers, operators, and security officers, who have the proper authorization to access the AWS Config data.

Figure 1: Summary of AWS Config resources

Figure 2: Summary of AWS Config resource compliance

Overview

By default, AWS Config stores data in an Amazon Simple Storage Service (Amazon S3) bucket. You can centralize all AWS account and Region data in this one location and visualize it by using configuration snapshots and history data.  For reference and for setting up Config, please see a previously published blog on AWS Config best practices.

Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. When configuration snapshots and configuration history data are aggregated in Amazon S3, you can use Athena to query the JSON data directly using SQL statements. You can then visualize your Athena SQL views and queries in Amazon QuickSight, which lets you easily create and publish interactive BI dashboards by creating data sets.

Prerequisites

To follow the steps in this blog post, you need the following:

• Prior to starting, we recommend reviewing the up to date pricing for Amazon S3, AWS Config, Amazon Athena, and Amazon Quicksight.
• An AWS account for hosting the S3 bucket, Amazon Athena, and Amazon QuickSight. I recommend that you use a dedicated shared services account. To avoid inter-region Amazon S3 data transfer charges, put the bucket in the same AWS Region where you are using Amazon Athena and Amazon QuickSight.
• Set up AWS Config in one or more accounts. For information, see Getting Started with AWS Config in the AWS Config Developer Guide. If you use AWS Organizations, you can enable AWS Config for all accounts using StackSets with all features enabled.
• Set up Amazon Athena. For information, see Setting Up in the Amazon Athena User Guide.
• Sign up for an Amazon QuickSight subscription in the same AWS account as where you set up Amazon Athena.

Walkthrough steps

1. Create and configure the S3 bucket to receive the AWS Config data.
2. Enable Amazon S3 configuration snapshot delivery in AWS Config.
3. Set up Amazon Athena to query the AWS Config data in Amazon S3.
4. Create SQL views in Amazon Athena.
5. Join AWS account data with AWS Config data in Amazon Athena. (Optional)
6. Set up Amazon QuickSight to import Amazon Athena data sources.
7. Create Amazon QuickSight analysis and dashboards.
8. Visual Examples.

Step 1: Create and configure the S3 bucket to receive the AWS Config data

Follow the steps in Creating a bucket in the Amazon S3 Getting Started Guide.  We recommend securing your bucket with encryption for the data at rest, to do so, complete the steps listed in the  Setting default server-side encryption behavior for Amazon S3 buckets documentation.  Alternatively, you can also secure your bucket using your own AWS Key Management Service (KMS) keys.  However, you will also need to configure an appropriate bucket policy prior to using your bucket.  See the Protecting data with server-side encryption using AWS KMS CMKs (SSE-KMS) documentation for additional details.

Decide on a prefix path for the AWS Config data. You can use the root of the bucket, but you might want to create a specific prefix path.

Finally, edit the S3 bucket policy by granting AWS Config access to the S3 bucket. Your bucket policy should like the following. Replace with your-bucket-name and your-prefix with your bucket name and prefix.

{
    "Version": "2012-10-17",
    "Statement": [
       {
            "Sid": "AWSConfigBucketPermissionsCheck",
            "Effect": "Allow",
            "Principal": {
                "Service": "config.amazonaws.com"
            },
            "Action": "s3:GetBucketAcl",
            "Resource": "arn:aws:s3:::DOC-EXAMPLE-BUCKET"
        },
        {
            "Sid": "AWSConfigBucketExistenceCheck",
            "Effect": "Allow",
            "Principal": {
                "Service": "config.amazonaws.com"
            },
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::DOC-EXAMPLE-BUCKET"
        },
        {
            "Sid": " AWSConfigBucketDelivery",
            "Effect": "Allow",
            "Principal": {
                "Service": "config.amazonaws.com"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::DOC-EXAMPLE-BUCKET/your-prefix/AWSLogs/*/Config/*",
            "Condition": {
                "StringEquals": {
                    "s3:x-amz-acl": "bucket-owner-full-control"
                }
            }
        }
    ]
}

Step 2: Enable Amazon S3 configuration snapshot delivery in AWS Config

For each account that should deliver its configuration snapshots and configuration history data to the centralized S3 bucket, you need to update the AWS Config delivery channel. Use the same method (CloudFormation stacks, StackSets, AWS CLI) that you used to configure AWS Config in your accounts.

Figure 3 shows AWS Config settings in the console, but you might want to use CloudFormation  scripts instead.

Figure 3: Create a bucket in the AWS Config console

Make sure you change the delivery channel in all AWS Regions where you have enabled AWS Config.

Step 3: Set up Amazon Athena to query the AWS Config data in Amazon S3

For instructions, see the How to query your AWS resource configuration states using AWS Config and Amazon Athena blog post, written by Marc Labrecque. Because you already enabled snapshot delivery, skip the first part of the post, but follow up to the point where there are some simple example queries.

When you create the AWS Lambda function in Python, edit the region and dt partition keys, based on the Region and date of the given configuration snapshot files, as shown in Marc’s blog. as appropriate for your configuration snapshot files.

I also encourage you to read the example queries to get a better idea of how to query the AWS Config JSON data. I include some SQL query examples to show how you extract compliance rule evaluations and detailed data from your Amazon EC2, Amazon RDS, Amazon VPC, and IAM resources.

By now you should have the following components in place:

• An S3 bucket that contains your AWS Config data from one or multiple accounts.
• An Amazon Athena database and a table formatted for querying your AWS Config data in S3.
• An AWS Lambda function for automatically updating the table partitioning keys for region and dt.

You are now ready to start creating some advanced SQL views and optionally joining your AWS Organizations account and business data to these views. You can then start visualizing your AWS Config data in Amazon QuickSight.

Step 4: Create SQL views in Amazon Athena

In order to understand how the Config data is structured, each of the sample SQL queries show how the configuration items must be unnested FROM the configurationsItems table.

To better understand how the JSON data is structured, make the following query:

SELECT * FROM "default"."aws_config_configuration_snapshot" limit 10;

The result of the query will give you a sample of 10 AWS Config snapshots to investigate by copying the configurationItem data to a text editor.

Next, try to view the latest snapshots from an specific AWS account and Region. Replace accountId and region with your AWS account number and Region.

SELECT * FROM default.aws_config_configuration_snapshot

WHERE "dt" = 'latest' AND accountId='012345678912' AND region='eu-west-1’

Notice how each snapshot contains AWS Config data for all resources in the account. To display each resource (configurationItem) in separate rows, use the UNNEST function to extract all the individual JSON elements into rows:

SELECT accountId, region, configurationItem.resourcetype, configurationItem FROM

default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE "dt" = 'latest' AND accountId='012345678912' AND region='eu-west-1'

ORDER BY configurationItem.resourcetype

You should now have a better understanding of how the AWS Config data is structured and how to extract each configurationItem into separate rows.

To create SQL views, in the Athena console, open a new query tab in the Query Editor tab and execute the following SQL statements to render some interesting views of your AWS Config data.

Example 1: Create a view of all AWS Config resources
This view will give you a list of all AWS Config resources contained in the latest snapshot.

CREATE OR REPLACE VIEW v_config_resources AS

SELECT DISTINCT

"accountId" "AccountId"

,"region" "Region"

,"configurationItem"."resourceid" "ResourceId"

,"configurationItem"."resourcename" "ResourceName"

,"configurationItem"."resourcetype" "ResourceType"

,"configurationItem"."availabilityzone" "AvailabiltyZone"

,"configurationItem"."resourcecreationtime" "CreationTime"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE ("dt" = 'latest')

Example 2: Create a view of all EC2 instances
This view will give you a list of all EC2 instances. The json_extract_scalar function is used to extract the EC2 instance IDs.

CREATE OR REPLACE VIEW v_config_ec2_instances AS

SELECT DISTINCT

"accountId" "AccountId"

,"region" "Region"

,"json_extract_scalar"("configurationItem"."configuration", '$.instanceid') "InstanceId"

,"configurationItem"."tags"['name'] "TagName"

,"configurationItem"."configurationitemcapturetime" "LastConfigSnapshot"

,"configurationItem"."resourcecreationtime" "CreationTime"

,"json_extract_scalar"("configurationItem"."configuration", '$.launchtime') "LaunchTime"

,"json_extract_scalar"("configurationItem"."configuration", '$.instancetype') "InstanceType"

,"json_extract_scalar"("configurationItem"."configuration", '$.keyname') "KeyName"

,"json_extract_scalar"("configurationItem"."configuration", '$.imageid') "AmiId"

,"json_extract_scalar"("configurationItem"."configuration", '$.privateipaddress') "PrivateIp"

,"json_extract_scalar"("configurationItem"."configuration", '$.publicipaddress') "PublicIp"

,"json_extract_scalar"("configurationItem"."configuration", '$.state.name') "State"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE (("dt" = 'latest') AND ("configurationItem"."resourcetype" = 'AWS::EC2::Instance'))

Example 3: Create a view of all VPCs
This view will give you a list of all VPCs, their names, CIDR blocks, and whether the VPC is the default or non-default.

Not all VPCs use more than one CIDR block, but if they do, you can extract the CIDR blocks from the cidrblockassociationset[n].cidrblock JSON element where n is a number between 0 and 4. (A VPC can use a maximum of 5 CIDR blocks.)

CREATE OR REPLACE VIEW v_config_ec2_vpcs AS

SELECT DISTINCT

"accountId" "AccountId"

, "region" "Region"

, "configurationItem"."resourceid" "ResourceId"

, "configurationItem"."tags"['name'] "TagName"

, "json_extract_scalar"("configurationItem"."configuration", '$.isdefault') "IsDefault"

, "json_extract_scalar"("configurationItem"."configuration", '$.cidrblockassociationset[0].cidrblock') "CidrBlock0"

, "json_extract_scalar"("configurationItem"."configuration", '$.cidrblockassociationset[1].cidrblock') "CidrBlock1"

, "json_extract_scalar"("configurationItem"."configuration", '$.cidrblockassociationset[2].cidrblock') "CidrBlock2"

, "json_extract_scalar"("configurationItem"."configuration", '$.cidrblockassociationset[3].cidrblock') "CidrBlock3"

, "json_extract_scalar"("configurationItem"."configuration", '$.cidrblockassociationset[4].cidrblock') "CidrBlock4"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE (("dt" = 'latest') AND ("configurationItem"."resourcetype" = 'AWS::EC2::VPC'))

Example 4: Create a view of RDS database instances
This view shows you a list of all RDS database instances in your account. You can use the list to check if they are set up with deletion protection, storage encryption, or have public endpoints.

CREATE OR REPLACE VIEW v_config_rds_dbinstances AS

SELECT DISTINCT

"accountId" "AccountId"

, "region" "Region"

, "configurationItem"."tags"['name'] "TagName"

, "configurationItem"."configurationitemcapturetime" "LastConfigSnapshot"

, "configurationItem"."resourcecreationtime" "CreationTime"

, "json_extract_scalar"("configurationItem"."configuration", '$.endpoint.address') "EndPoint"

, "json_extract_scalar"("configurationItem"."configuration", '$.engine') "Engine"

, "json_extract_scalar"("configurationItem"."configuration", '$.deletionprotection') "DeletionProtection"

, "json_extract_scalar"("configurationItem"."configuration", '$.preferredbackupwindow') "BackupWindow"

, "json_extract_scalar"("configurationItem"."configuration", '$.backupretentionperiod') "BackupRetention"

, "json_extract_scalar"("configurationItem"."configuration", '$.dbinstanceclass') "InstanceType"

, "json_extract_scalar"("configurationItem"."configuration", '$.endpoint.port') "Port"

, "json_extract_scalar"("configurationItem"."configuration", '$.publiclyaccessible') "PubliclyAccessible"

, "json_extract_scalar"("configurationItem"."configuration", '$.dbinstancestatus') "Status"

, "json_extract_scalar"("configurationItem"."configuration", '$.storageencrypted') "StorageEncrypted"

"PubliclyAccessible"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE (("dt" = 'latest') AND ("configurationItem"."resourcetype" = 'AWS::RDS::DBInstance'))

Example 5: Create a view of all IAM resources
This view shows you a list of all IAM resources (users, groups, roles, and policies).

If you have configured AWS Config to include IAM resources in more than one Region, your aggregated AWS Config data will contain duplicates. Here, only IAM resources from us-east-1 are queried because I set up AWS Config to capture IAM resources in that Region only.

CREATE OR REPLACE VIEW v_config_iam_resources AS

SELECT DISTINCT

"accountId" "AccountId"

,"configurationItem"."resourcename" "Name"

,"configurationItem"."resourcetype" "Type"

,"configurationItem"."resourcecreationtime" "CreationTime"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE (

(

("dt" = 'latest')

AND ("region" = 'us-east-1')

)

AND (

("configurationItem"."resourcetype" = 'AWS::IAM::User')

OR ("configurationItem"."resourcetype" = 'AWS::IAM::Role')

OR ("configurationItem"."resourcetype" = 'AWS::IAM::Policy')

OR ("configurationItem"."resourcetype" = 'AWS::IAM::Group')

)

)

Advanced queries against AWS Config rules

You can optionally configure AWS Config to run selected rule evaluations against your resources. For information, see the Evaluating Resources with AWS Config Rules in the AWS Config Developer Guide.

AWS Config rule evaluations create compliance data for the resources they evaluate. The data is stored in the AWS Config snapshots as the AWS::Config::ResourceCompliance resource type.

Rules are stored in the JSON data in separate indexes in each configurationItem.configuration as configrulelist[n], where n is an index number between 0 and some upper-limit integer value. The more rules you use, the higher the upper-limit integer value will be.

This makes querying your AWS Config rule compliance evaluations a bit complicated because you don’t know beforehand across how many indexes the data is spread.

However, through trial and error, you can create an SQL view where you include as many of the indexes as there is data. As soon as you hit an index where you no longer return any data, you can stop. Keep in mind, though, that if you add more rules, you will have to add more indexes to the query.

Example 6: Create a view of each AWS Config rule and resource compliance evaluation
Because the following query joins queries against each configrulelist[n] index separately and UNION joins them, depending on the amount of rule data, it might take a while to render the data

To add more indexes, simply UNION a new SELECT and change the index in configrulelist[ ] to the next value. In the following example, I have included up to index 2,  configrulelist[2], so the next index would be configrulelist[3]. Make sure you replace configrulelist[ ] everywhere, including in the WHERE clause.

Each ResourceCompliance item contains the ResourceType and ResourceId separated by a forward slash (/) in the resourceid field. For example, a resourceid value for an IAM user would look like this: AWS::IAM::User/AIDA3ZK7FOVMGCIRKMYL4AIDACKCEVSQ6C2EXAMPLE.

To join other resource views to this view, use the SPLIT_PART function to split the resourceid into the ResourceId and ResourceType constituent parts.

CREATE OR REPLACE VIEW v_config_rules_resource_compliances AS

SELECT

"accountId" "AccountId"

, "configurationItem"."awsregion" "Region"

, "split_part"("configurationItem"."resourceid", '/', 1) "ResourceType"

, "split_part"("configurationItem"."resourceid", '/', 2) "ResourceId"

, "json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[0].configrulename') "ConfigRuleName"

, "json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[0].compliancetype') "ComplianceType"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE ((("configurationItem"."resourcetype" = 'AWS::Config::ResourceCompliance') AND ("json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[0].configrulename') IS NOT NULL)) AND ("dt" = 'latest'))

UNION

SELECT

"accountId" "AccountId"

, "configurationItem"."awsregion" "Region"

, "split_part"("configurationItem"."resourceid", '/', 1) "ResourceType"

, "split_part"("configurationItem"."resourceid", '/', 2) "ResourceId"

, "json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[1].configrulename') "ConfigRuleName"

, "json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[1].compliancetype') "ComplianceType"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE ((("configurationItem"."resourcetype" = 'AWS::Config::ResourceCompliance') AND ("json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[0].configrulename') IS NOT NULL)) AND ("dt" = 'latest'))

UNION

SELECT

"accountId" "AccountId"

, "configurationItem"."awsregion" "Region"

, "split_part"("configurationItem"."resourceid", '/', 1) "ResourceType"

, "split_part"("configurationItem"."resourceid", '/', 2) "ResourceId"

, "json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[2].configrulename') "ConfigRuleName"

, "json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[2].compliancetype') "ComplianceType"

FROM default.aws_config_configuration_snapshot

CROSS JOIN UNNEST("configurationitems") t (configurationItem)

WHERE ((("configurationItem"."resourcetype" = 'AWS::Config::ResourceCompliance') AND ("json_extract_scalar"("configurationItem"."configuration", '$.configrulelist[0].configrulename') IS NOT NULL)) AND ("dt" = 'latest'))

Step 6: Join AWS Organizations data with AWS Config data in Amazon Athena (Optional)

AWS Config data contains the 12-digit AWS account IDs, but you might find it useful to also include the names of your AWS accounts in your Athena views.

If your AWS accounts are joined to AWS Organizations, you can tag each account. For example, you might use BusinessUnit, Owner, CostUnit tags.

For accounts joined to AWS Organizations, you can use a Lambda function to export the account names, root user emails, tags, and so on to a JSON file. If you export the JSON file to the S3 bucket you created for your AWS Config data, you can then create a table for the account JSON data in Athena.

Create a file with content that contains data in your AWS accounts that also exists in your AWS Config data:

{

"AccountId":"012345678912",

"Name":"Account-01",

"Status": “Active”,

"BusinessUnit":"ToysDevelopment”

"CostUnit":"Finance",

"Environment":"Development",

"Solution":"Toddler-Games",

"Owner":"Alejandro",

"ContactEmail":"alejandro@example.com"

},

{

"AccountId":"012345678912",

"Name":"Account-02",

"Status": “Active”,

"BusinessUnit":"HR”

"CostUnit":"Finance",

"Environment":"Production",

"Solution":"Adult-Games",

"Owner":"Jane",

"ContactEmail":"jane@example.com"

},

{

…

}

Next, upload the JSON file to the S3 bucket. To keep data separate, upload the JSON file to Amazon S3 using a prefix path (for example, your-prefix/accounts.json).

Create a table in Athena for the AWS account data

After the JSON file has been uploaded to the S3 bucket, execute the following query to create a table for the data in Amazon Athena:

CREATE EXTERNAL TABLE `aws_org_accounts`(

`accountid` string,

`businessunit` string,

`costunit` string,

`contactemail` string,

`environment` string,

`solution` string,

`name` string,

`status` struct<value:string> )

ROW FORMAT SERDE

'org.openx.data.jsonserde.JsonSerDe'

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION

's3://DOC-EXAMPLE-BUCKET/your-prefix'

You should now have a new table that contains your AWS account data. You might have other fields. If so, just edit the query to match the fields in your JSON file.

Next, you might want to create an SQL view of the table. Perhaps you want just a subset of fields or you want to rename some fields.

CREATE OR REPLACE VIEW v_aws_org_accounts AS

SELECT

accountid

, businessunit

, costunit

, contactemail AS "email"

, environment

, solution

, name

, status

FROM "default"."aws_org_accounts"

Join the AWS Organizations data on the AWS Config views in Amazon Athena

Now to join the v_aws_org_accounts and v_config_e2_instances views, execute the following SQL query in Athena:

CREATE OR REPLACE VIEW v_config_ec2_instances_aws_org_accounts AS

WITH

a AS (

SELECT * FROM default.v_config_ec2_instances

)

, b AS (

SELECT

"accountid"

, "name"

, "businessunit"

, "solutionname"

FROM default.v_aws_org_accounts

)

SELECT

"b"."name" "AccountName"

, "b"."businessunit" "BusinessUnit"

, "b"."solution" "Solution"

, a.*

FROM

(a

LEFT JOIN b ON ("a"."accountid" = "b"."accountid"))

Things to note:

• The query joins the two views using accountid as key.
• The query uses a LEFT join so it shows every row in your v_config_ec2_instances view, but only joins the rows in aws_org_accounts where accountid matches.
• Only some fields from aws_org_accounts are selected (name, businessunit, solution).

For each AWS Config view you create, you can optionally create another view by joining your AWS Organizations account data. This enriches the AWS Config data with meaningful AWS account names and business tags.

Step 9: Set up Amazon QuickSight to import Amazon Athena data sources

When your SQL views are ready, you can start importing the data into Amazon QuickSight. In order to determine if access is configured correctly, follow the instructions included in the Amazon QuickSight documentation.

Authorize Amazon QuickSight to use Amazon Athena and S3
Follow these instructions to authorize Amazon QuickSight to use Athena and the S3 buckets used by Athena.

Create an Athena data source and data set

1. In the Amazon QuickSight console, choose Datasets, and then choose New dataset.
2. Under Create a data set from new data sources, choose Amazon Athena.
3. Enter a name for your Athena data source (for example, aws-config) and choose Create data source.Figure 4: New Athena data source
4. Choose one of the SQL views you created earlier and then choose Edit/Preview data.Figure 5: List of SQL views
5. On the page that opens, customize the query for your data set.

Change query mode to SPICE
Change the query mode to SPICE. This mode will cache the query data and make the dashboard user experience much faster.

Figure 6: Query mode

Change datetime fields from string to date format
AWS Config stores datetime fields as ISO8601 strings: yyyy-MM-ddTHH:mm:ss.SSSZ  (for example, 2020-07-07T12:00:19.412Z).

1. To convert the string field to a real datetime field, in the QuickSight console, in the creationtime field, choose String and then choose Date. (The fields in your SQL query might be different.)Figure 7: creationtime field
2. In Edit date format, enter yyyy-MM-dd’T’HH:mm:ss.SSSZ. Note the T is enclosed in single quotation marks.
3. Choose Validate. If you entered the date correctly, it should be validated. Choose Update.

Figure 8: Edit date format

You should do this for every data set you import from your Athena queries that contain datetime fields stored as ISO8601 strings. Although you could have used CAST or CONVERT on the ISO8601 varchar strings in your Athena queries, some issues occur with the time offset when you have empty strings in your table (and you will). For this reason, I find it better to change the datetime fields in QuickSight. If you don’t plan to use your datetime fields as real dates, you don’t need to convert them.

Step 10: Finish creating the data set

Although I don’t cover it in this blog post, but you can add calculated fields. These fields make it possible to create new fields from the existing data using advanced functions.  If necessary, use the Working with Calculated Fields in Datasets documentation for calculating fields from existing data.

To create the data set, choose Save.

Figure 9: Save button

If you choose Save & visualize, QuickSight will create a visualization (analysis) for your data set. Don’t choose this option because you will create an analysis for all of your AWS Config data sets later on.

Repeat the steps for creating data sets for the rest of the SQL queries (views) that you want to visualize in QuickSight

Create schedules for updating your data sets

By now, you should have created several data sets in Amazon QuickSight that import data from your Amazon Athena queries against AWS Config data located in Amazon S3.

Next, make sure the data sets are updated regularly with the most recent data.

1. In the QuickSight console, on the Data sets page, choose one of your data sets, and then choose Schedule refresh.Figure 10: v_config_resources data set
2. To create a schedule, choose Create.Figure 11: Schedule refresh
3. Choose how often you want the data set to be refreshed and then choose Create.

Figure 12: Create a schedule

Close Schedule refresh. You’ll see that you can initiate a full manual refresh by choosing Refresh Now on Data Set.  This can come in handy when you just have changed a SQL query and want to see the results in QuickSight immediately.

Repeat these steps for all the AWS Config data sets you have imported from Amazon Athena.

You are now ready to create an analysis for your AWS Config data that can be published as a dashboard.

Step 7: Create Amazon QuickSight analysis and dashboards

1. On the QuickSight Analysis page, choose New Analysis.
2. Choose one of your data sets (for example, v_config_resources) and then choose Create analysis.
3. Give your analysis a meaningful name (for example, AWS Config).
4. Rename Sheet 1 to Config Summary or whatever you like. As you add more data sets to your analysis, create new tabs, such as RDS, EC2, VPC, and so on.

Add visuals

The analysis starts with one predefined visual, but to add more, in the upper left, choose Add and then choose Add visual.

Create a donut visual of AWS Config resources by AWS Region

1. Under Visual types, choose the donut, as shown in Figure 13:Figure 13: Visual types
2. Drag the accountid field from the data set to the Field wells section and release it over the Group/Color box.
3. You can also add fields to your visual by dragging them directly onto the visual over the + sign, as shown in Figure 15. To change the field order, you must use the Field wells section.

Figure 14: Fields list

Format your visual

1. To further customize your visual, choose Format visual.Figure 15: Format visual options
2. Expand Data labels and then select Show metric.Figure 16: Label options
3. The values for each Region now appear on the visual:

Figure 17: Regions displayed on the visual

Familiarize yourself with the formatting options on each visual type.

Create a table visual of EC2 instances

1. Before you add a visual, choose the plus sign (+) next to the current sheet to create a new one.
2. Rename the new sheet EC2.
3. Next, choose the pen icon to add the EC2 data set to your analysis.Figure 18: Pen icon
4. Choose New data set and then choose the v_config_ec2_instances data set you created.
5. Select the new data set from the dropdown.Figure 19: v_config_resources
6. To add a new visual, choose Add and then choose Add visual. Remember to add a visual after you’ve selected the data set to use.Figure 20: Add visual
7. Select the empty visual on the new sheet and choose Table.Figure 21: Table selected
8. Drag each field you want into the visual to create something like this:

Figure 22: List of EC2 instances

Publish your analysis to a dashboard

After you have created your analysis, you can export it to a dashboard. Choose Share and then choose Publish dashboard.

Figure 23: Publish dashboard

Visual samples

I hope these dashboard samples will inspire you to create visualizations of your AWS Organizations and AWS Config data in Amazon QuickSight.

The data sets originate from views described in the Examples section, but the views have been joined with the AWS Organizations view from sample 1 below using the AWS account IDs as the joining field. This makes it possible to see the AWS account names and business tags on the dashboards.

Sample 1: AWS Organizations dashboard
This sample shows AWS accounts extracted from AWS Organizations. The AWS accounts were tagged in AWS Organizations with BusinessUnit, Solution, and Last Month’s Cost. This data is not part of AWS Config, but you can use a Lambda function to extract it as a JSON file to S3. Then you can import the JSON data as a table in Amazon Athena and join it to your AWS Config queries using SQL joins.

Figure 24: AWS accounts extracted from AWS Organizations

Sample 2: AWS Config compliance summary

This sample shows resource compliance against AWS Config compliance rules. I based this sample on the Athena view, v_config_rules_resource_compliances, in example 6.

Figure 25: Sample based on v_config_rules_resource_compliances

Sample 3: RDS databases
This dashboard uses data from the v_config_rds_dbinstances view created earlier in example 4.

Figure 26: Sample based on v_config_rds_dbinstances

Sample 4: EC2 instances
This dashboard uses data from the v_config_ec2_instances view created in example 2.

Figure 27: Sample based on v_config_ec2_instances

Sample 5: VPC summary
This dashboard uses data from the v_config_ec2_vpcs views created in example 3.

It’s helpful to see which CIDR blocks are being used by default and non-default VPCs across your accounts. You can extract data from VPC peering connections from your AWS Config data to see which VPCs are peered.

Figure 28: Sample based on v_config_ec2_vpcs

Sample 6: IAM summary
This dashboard uses data from the v_config_iam_resources views created in example 5.

Figure 29: Sample based on v_config_iam_resources

Conclusion

In this post, I showed you how to visualize your AWS Organizations and AWS Config data in Amazon QuickSight dashboards. These dashboards offer insight to the teams and managers using your AWS accounts. If you use AWS Config rules, you can visualize which resources are compliant or noncompliant with your rules and share this data in compliance reviews with your development and operational teams.

About the author

Henrik André Olsen is a Solution Architect who helps enterprises improve cost optimization, security, identity management, network architecture, best practices, governance, and more.

In his free time, Henrik enjoys playing drums in various bands, role-playing games, and geeky board games. He likes learning about quantum physics and cosmological theories.

The content and opinions in this blog are those of the third party author and AWS is not responsible for the content or accuracy of this post.