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

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

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.

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:

You can look up the sample queries or define your own custom queries.

Try those queries in the query editor to make sure that they return the expected results.

For the purpose of this blog, we will use 3 sample queries from the list:

Now that your CloudTrail Lake is all set, let us move to the next step.

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.

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).

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:

Run your queries from Athena and make sure that they work as expected.

Now, we are ready for 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'

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).

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'.

In the next screen, click on 'Visualize' to create 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.

Use the Properties tab to customize the visual details such as name, format etc.

Once you are happy with the visual, you can publish it to a new dashboard or replace an existing 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:

SQL query for visualizing security group changes:

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)

From within the chosen analysis, click on the drop down in the Dataset section and '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)

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

Once you are happy, publish the sheet to a dashboard to have all visuals in a single dashboard. Mine looks as below.

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.