Understanding Redshift Audit Logs: Types, Need & 5 Easy Steps

• September 21st, 2021

Most businesses are moving their data to Cloud storage. This can be attributed to the many benefits offered by Cloud storage. The Cloud is very elastic and it scales well to meet the changing storage needs of businesses. It also offers adequate security to your data. Amazon Redshift is a popular Cloud storage platform. It provides individuals and businesses with a Cloud platform where they can store their data. Redshift users are assured of the security of their data and scalability when necessary.

When using Redshift, you will need to monitor your Database for troubleshooting and security purposes. That’s why every Amazon Redshift user should be familiar with the Redshift Audit Logs. You will be able to know which user modified which data, the computer from which the modification was done, the time at which it was done, and more. This is possible by looking into the Logs. In this article, we will be discussing Redshift Audit Logs in detail.

Table of Contents

Prerequisites

To set up Amazon Redshift Audit Logs, you need to fulfil the following requirements:

  • Basic understanding of Amazon Redshift.
  • An AWS Account.

Introduction to Amazon Redshift

Redshift Audit Logs - Amazon Redshift Logo
Image Source

Amazon Redshift is a managed, petabyte-scale Cloud Data Warehouse platform that makes the larger part of the AWS Cloud platform. Amazon Redshift provides its users with a platform where they can store all their data and analyze it to extract deep business insights. 

Traditionally, businesses had to make Sales predictions and other forecasts manually. Amazon Redshift does the largest part of the work of analyzing the data to give you time to focus on something else. It also gives you an opportunity to analyze your business data using the latest predictive analytics. This way, you can make smart decisions that can drive the growth of your business.

You can learn more about Amazon Redshift from the official documentation

Importance of Amazon Redshift Audit Logs

Redshift Audit Logs - Logs
Image Source

AWS Redshift offers its users a feature to help them get user Activity Logs after enabling Audit Logging in the configuration settings. 

This feature is good for troubleshooting purposes. It tracks information about the queries executed by both users and the system against the Database. Thus, the Redshift Audit Logs help Redshift users to monitor the Database for troubleshooting purposes. 

Audit Monitoring is also good for monitoring purposes, like determining when and the Database on which a user executed a query. This makes it easy to know who owns which query might accidentally modify the data or blocks other queries, which is helpful for unblocking other users and resolving issues. This is also good for ensuring that there is accountability in the organization. 

Finally, Redshift Audit Logs are good for security purposes. They make it possible to determine suspicious queries by checking the connections and User Logs to see the users connecting to the Database. It gives information such as the IP address of the user’s computer, the type of Authentication used by the user, or the Timestamp of the request. Redshift Audit Logs are stored in the Amazon S3 buckets. 

Simplify Data Analysis with Hevo’s No-code Data Pipeline

Redshift Audit Logs - Hevo Banner

Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources including 30+ Free Sources. It is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo loads the data onto the desired Data Warehouse/destination like Amazon Redshift and enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

GET STARTED WITH HEVO FOR FREE

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Simplify your Data Analysis with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Types of Amazon Redshift Audit Logs

The types of Amazon Redshift Audit Logs are:

  • Connection  Log: It logs information about Connections, Logs Authentication attempts, and Disconnections.
  • User Log: It logs information about changes and modifications made to the Database user definitions. 
  • User Activity Log: It logs every query before running on the Database.

The Connection and User Logs are normally used for security purposes. The Connection Log can help you to monitor information about users who are connecting to Redshift. It gives you information such as the IP address of the user’s machine, the type of Authentication the user used, when the request was made, and more. The log can also show you any changes made to the Database users definition.  It tracks changes to a Database user such as Create User, Drop User, and Alter User. 

The User Activity Log is normally used for troubleshooting purposes. It is the one that logs information about the different types of queries run on the database.

The Connection and the User Logs store the same information as the one stored in the system tables of your Database. Although you can get the same information from the system tables, the Logs provide you with an easier way for retrieving and viewing the Logs. The Log files depend on Amazon S3 permissions instead of Database permissions to run queries against the tables. Again, when you view information stored in the Log files rather than the system tables, you will avoid any impact associated with interaction with the Database. 

Steps to Enable Amazon Redshift Audit Logs

By default, Redshift Audit Logging is not enabled. When you enable it, Redshift starts to upload Logs to Amazon S3. The Logs show the data from the time Redshift Audit Logging was enabled.

To enable Audit Logging in Redshift, you should have permission to write files to S3 and modify your Redshift clusters. The following steps can help you to configure Redshift to start writing Logs to S3:

Step 1: Select “CLUSTERS” on the navigation menu, then select the cluster to be updated.

Step 2: Click the “Properties” tab, then choose “Audit Logging” from the “Database Configurations” section.

Step 3: Click “Edit”, and then “Edit Audit Logging”.

Step 4: The “Edit Audit Logging” page will be opened. Select “Enable” to enable Audit Logging as shown below and enter your choices in terms of where you need to store the Redshift Audit Logs.

Redshift Audit Logs - Enable Audit Logging
Image Source

Step 5: Click “Save Changes” to save the choices you have selected.

After enabling this option, you will have to wait for some time to have the Redshift Audit Logs written to the destination S3 bucket. This may take a number of hours, but you will later see the Logs in the bucket. 

The Logs normally take the following format:

AWSLogs/AccountID/ServiceName/Region/Year/Month/Day/AccountID_ServiceName_Region_ClusterName_LogType_Timestamp.gz

Thus, Redshift Audit Logs are very useful for Monitoring and Troubleshooting purposes.

Conclusion

In this article, you have learnt more about Amazon Redshift. In addition, you understood the importance of Redshift Audit Logs. Moreover, you learnt the different types of Redshift Audit Logs and also the steps to enable Redshift Audit Logging.

VISIT OUR WEBSITE TO EXPLORE HEVO

In case you wish to extract complex data from a diverse set of data sources like CRMs, Marketing Platforms, Streaming Services, Hevo is the right choice for you! Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources including 30+ Free Sources, into your Data Warehouse like Amazon Redshift to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience with Amazon Redshift Audit Logs in the comments section below!

No-Code Data Pipeline For Amazon Redshift