Amazon QuickSight Dashboard for Auditing and Monitoring Operations in your AWS Accounts

Amazon QuickSight Dashboard for Auditing and Monitoring Operations in your AWS Accounts

Step by Step guidance for extracting specific operational events in your AWS Accounts from AWS CloudTrail Lake and creating an Amazon QuickSight dashboard to visualize and monitor them

Anand Malayil Sasi
Amazon Employee
Published Jun 24, 2024


Security and Compliance is an essential topic for almost all customers running their workloads in AWS. Ensuring that your workloads are secure and compliant in AWS is a shared responsibility. There are a number of AWS services that you can leverage for monitoring and auditing your workloads and operations in AWS. In this blog post, we will look at creating a handy Amazon QuickSight Dashboard by leveraging AWS CloudTrail Lake's SQL querying capability for monitoring operations in your AWS accounts.
AWS CloudTrail is being used by millions of customers for operational and risk auditing, governance, and compliance of their AWS accounts. Whenever an activity occurs in your AWS account, it is recorded in a CloudTrail Event. CloudTrail Lake provides us the ability to query these events in familiar SQL query language. This is very handy as you can easily filter the data and metrics that are critical for you by efficiently designing your query. CloudTrail Lake comes pre-built with a fairly large set of sample queries which might already be relevant for you. You can also define custom queries to extract the exact information you need.
In this blog, we will see how we can extract the relevant information from the CloudTrail Lake events using AWS Glue Data Catalog and Amazon Athena and visualize them in an Amazon QuickSight Dashboard

The Dashboard - Step by step

Enabling CloudTrail Lake and running queries from the native query editor are explained in detail in this blog. The steps explained in this blog post assumes that you have completed the initial set up as per the aforementioned blog.

AWS CloudTrail Lake Event Store

If you have enabled CloudTrail Lake as explained in the previously mentioned blog, you should already have an Event data store.
Mine looks as below:
The Event data store
You can look up the sample queries or define your own custom queries.
Sample queries
Try those queries in the query editor to make sure that they return the expected results.
Query Editor
For the purpose of this blog, we will use 3 sample queries from the list:
  • Monitoring Security Group Changes
  • Track Route Table Changes
  • Track Network ACL Configuration Changes
Now that your CloudTrail Lake is all set, let us move to the next step.

AWS Glue Data Catalog

When you created the Event data store, a corresponding table (with an auto-generated alphanumeric string as its name) in a database named 'aws:cloudtrail' was auromatically created in your Glue Data Catalog.
Mine looks as below.
Table in Glue Data Catalog

Amazon Athena

We need to use Athena as the query engine to create the dashboard. Athena natively integrates with AWS Glue.
Go to Amazon Athena in your AWS console. You should see that the Glue Data Catalog table is already available as below. In case you have not already done, you will have to set up an Amazon S3 bucket as query result location (This is a one time activity).
Glue Table in Athena
Try running your CloudTrail Lake SQL queries from the Athena query editor. In my case, the query when used as-is from the CloudTrail Lake does not work as Athena could not figure out the schema. For it to work, I had to provide the table name in the <"database_name"."table_name"> format.
For instance, my table name as you can see from the pictures above is '82b6fde8-e083-4811-b950-060d90d783ff'
My SQL query for selecting all Network ACL changes till date from Athena query editor was:
WHERE eventSource = 'ec2.amazonaws.com' AND eventName in ('CreateNetworkAcl', 'CreateNetworkAclEntry', 'DeleteNetworkAcl', 'DeleteNetworkAclEntry', 'ReplaceNetworkAclEntry', 'ReplaceNetworkAclAssociation')
Run your queries from Athena and make sure that they work as expected.
Now, we are ready for QuickSight.

Amazon QuickSight

If you have not used QuickSight before, please follow this documentation to do the initial setup in the same AWS Region you have done the previous steps.
As the first step, we need to create a corresponding dataset for each SQL query to be visualized in the dashboard. In this blog, we are looking to visualize 3 SQL queries. Hence we need to create 3 datasets.
For creating your first dataset, go to the QuickSight console > Datasets > New Dataset > Athena. Provide a name to your Data source and click on 'Create data source'
Creating Dataset
In the next screen, make sure you have the correct catalog and database from the Glue Data Catalog selected. Click on the button 'Use custom SQL' (The default SQL will generate an exception).
Use custom SQL while creating Dataset
Enter the SQL query in to the field (Please use “database_name”.”table_name” format as mentioned earlier to address the table) and click on 'Confirm query'.
Confirm Query
In the next screen, click on 'Visualize' to create visual.
Creating the Visual
You get many options to format your visual while creating it. I have chosen a table format and the fields I would like to track.
Format and Create visual
Use the Properties tab to customize the visual details such as name, format etc.
Visual Properties
Once you are happy with the visual, you can publish it to a new dashboard or replace an existing dashboard
Publish the visual to new dashboard
You can repeat the steps in this section with each query to create the corresponding dataset, visual and dashboard. In my case I did the same steps two more times with the remaining queries
SQL query for visualizing route table changes:
coalesce(element_at(requestParameters, 'routeTableId'), cast(json_extract(element_at(responseelements, 'routeTable'), '$.routeTableId') as varchar)) as routeTableId, element_at(requestParameters, 'vpcId') as vpcId, eventTime, requestParameters, responseElements
eventsource = 'ec2.amazonaws.com' AND eventName in ('CreateRoute', 'ReplaceRoute', 'DeleteRoute', 'CreateRouteTable', 'DeleteRouteTable', 'DisassociateRouteTable', 'ReplaceRouteTableAssociation')
SQL query for visualizing security group changes:
eventName, userIdentity.arn AS user, sourceIPAddress, eventTime,
element_at(requestParameters, 'groupId') AS securityGroup,
element_at(requestParameters, 'ipPermissions') AS ipPermissions
(element_at(requestParameters, 'groupId') LIKE '%sg-%')
BY eventTime ASC

Creating a single dashboard for all visuals

In the above section, we published each visual to a separate dashboard. You can also publish all the visuals you would like to track to a single dashboard. In order to do that, we need to add all 3 datasets to the same Analysis in QuickSight.
Go to Analyses section in QuickSight console and choose any analysis you would like (we will add all datasets to this analysis)
Choose an Analysis to update
From within the chosen analysis, click on the drop down in the Dataset section and 'Add a new dataset'
Add a new Dataset
Select each dataset you created one by one. In my case, I chose the Network ACL analysis. So I added the Security Group and Route Table dataset to it (the Network ACL datases is already part of it)
Select Datasets
The steps to create and format each visual is the same as we have seen before. The only difference is that you will do this in the same page rather than different ones. My sheet looks as below
All visuals in one sheet
Once you are happy, publish the sheet to a dashboard to have all visuals in a single dashboard. Mine looks as below.
The consolidated QuickSight Dashboard
Now you can monitor, audit and track all relevant metrics you chose from one QuickSight dashboard


In this blog, we saw how we can leverage AWS CloudTrail Lake, AWS Glue Data Catalog and Amazon Athena to extract the critical operations and events in your AWS Accounts and visualize them in Amazon QuickSight. It is important to note that CloudTrail delivers events within an average of about 5 minutes of an API call (This time is not guaranteed) hence, this is not ideal for real-time tracking. You can also leverage the Generative BI capabilities in QuickSight to build powerful, sharable narratives using the data and visuals.

Any opinions in this post are those of the individual author and may not reflect the opinions of AWS.