Friday, June 2, 2023
HomeBig DataUse AWS CloudWatch as a vacation spot for Amazon Redshift Audit logs

Use AWS CloudWatch as a vacation spot for Amazon Redshift Audit logs

Amazon Redshift is a quick, scalable, safe, and fully-managed cloud knowledge warehouse that makes it easy and cost-effective to investigate your whole knowledge utilizing commonplace SQL. Amazon Redshift has complete safety capabilities to fulfill probably the most demanding necessities. That will help you to observe the database for safety and troubleshooting functions, Amazon Redshift logs details about connections and person actions in your database. This course of known as database auditing.

Amazon Redshift Audit Logging is nice for troubleshooting, monitoring, and safety functions, making it doable to find out suspicious queries by checking the connections and person logs to see who’s connecting to the database. It offers data, such because the IP deal with of the person’s pc, the kind of authentication utilized by the person, or the timestamp of the request. Audit logs make it simple to establish who modified the information. Amazon Redshift logs all the SQL operations, together with connection makes an attempt, queries, and adjustments to your knowledge warehouse. These logs might be accessed by way of SQL queries in opposition to system tables, saved to a safe Amazon Easy Storage Service (Amazon S3) Amazon location, or exported to Amazon CloudWatch. You may view your Amazon Redshift cluster’s operational metrics on the Amazon Redshift console, use CloudWatch, and question Amazon Redshift system tables straight out of your cluster.

This submit will stroll you thru the method of configuring CloudWatch as an audit log vacation spot. It should additionally present you that the latency of log supply to both Amazon S3 or CloudWatch is diminished to lower than a couple of minutes utilizing enhanced Amazon Redshift Audit Logging. You may allow audit logging to Amazon CloudWatch by way of the AWS-Console or AWS CLI & Amazon Redshift API.

Resolution overview

Amazon Redshift logs data to 2 locations-system tables and log information.

  1. System tables: Amazon Redshift logs knowledge to system tables robotically, and historical past knowledge is offered for 2 to 5 days primarily based on log utilization and out there disk area. To increase the log knowledge retention interval in system tables, use the Amazon Redshift system object persistence utility from AWS Labs on GitHub. Analyzing logs by way of system tables requires Amazon Redshift database entry and compute sources.
  2. Log information: Audit logging to CloudWatch or to Amazon S3 is an optionally available course of. Once you activate logging in your cluster, you’ll be able to select to export audit logs to Amazon CloudWatch or Amazon S3. As soon as logging is enabled, it captures knowledge from the time audit logging is enabled to the current time. Every logging replace is a continuation of the earlier logging replace. Entry to audit log information doesn’t require entry to the Amazon Redshift database, and reviewing logs saved in Amazon S3 doesn’t require database computing sources. Audit log information are saved indefinitely in CloudWatch logs or Amazon S3 by default.

Amazon Redshift logs data within the following log information:

  • Connection log – Offers data to observe customers connecting to the database and associated connection data. This data is perhaps their IP deal with.
  • Person log – Logs details about adjustments to database person definitions.
  • Person exercise log – It tracks details about the sorts of queries that each the customers and the system carry out within the database. It’s helpful primarily for troubleshooting functions.

Advantages of enhanced audit logging

For a greater buyer expertise, the prevailing structure of the audit logging resolution has been improved to make audit logging extra constant throughout AWS providers. This new enhancement will scale back log export latency from hours to minutes with a nice grain of entry management. Enhanced audit logging improves the robustness of the prevailing supply mechanism, thus decreasing the chance of information loss. Enhanced audit logging will allow you to export logs both to Amazon S3 or to CloudWatch.

The next part will present you how you can configure audit logging utilizing CloudWatch and its advantages.

Organising CloudWatch as a log vacation spot

Utilizing CloudWatch to view logs is a beneficial different to storing log information in Amazon S3. It’s easy to configure and it might fit your monitoring necessities, particularly if you happen to use it already to observe different providers and software.

To arrange a CloudWatch as your log vacation spot, full the next steps:

  1. On the Amazon Redshift console, select Clusters within the navigation pane.
    This web page lists the clusters in your account within the present Area. A subset of properties of every cluster can be displayed.
  2. Select cluster the place you wish to configure CloudWatch logs.

  3. Choose properties to edit audit logging.
  4. Select Activate configure audit logging, and CloudWatch beneath log export sort.
  5. Choose save adjustments.

Analyzing audit log in close to real-time

To run SQL instructions, we use redshift-query-editor-v2, a web-based device that you should use to discover, analyze, share, and collaborate on knowledge saved on Amazon Redshift. Nonetheless, you should use any shopper instruments of your option to run SQL queries.

Now we’ll run some easy SQLs and analyze the logs in CloudWatch in close to real-time.

  1. Run take a look at SQLs to create and drop person.
  2. On the AWS Console, select CloudWatch beneath providers, after which choose Log teams from the best panel.
  3. Choose the userlog – person logs created in close to real-time in CloudWatch for the take a look at person that we simply created and dropped earlier.

Advantages of utilizing CloudWatch as a log vacation spot

  • It’s simple to configure, because it doesn’t require you to change bucket insurance policies.
  • It’s simple to view logs and search by way of logs for particular errors, patterns, fields, and so on.
  • You may have a centralized log resolution throughout all AWS providers.
  • No have to construct a customized resolution reminiscent of AWS Lambda or Amazon Athena to investigate the logs.
  • Logs will seem in close to real-time.
  • It has improved log latency from hours to only minutes.
  • By default, log teams are encrypted in CloudWatch and also you even have the choice to make use of your personal customized key.
  • High quality-granular configuration of what log sorts to export primarily based in your particular auditing necessities.
  • It helps you to export log teams’ logs to Amazon S3 if wanted.

Organising Amazon S3 as a log vacation spot

Though utilizing CloudWatch as a log vacation spot is the beneficial strategy, you even have the choice to make use of Amazon S3 as a log vacation spot. When the log vacation spot is ready as much as an Amzon S3 location, enhanced audit logging logs will likely be checked each quarter-hour and will likely be exported to Amazon S3. You may configure audit logging on Amazon S3 as a log vacation spot from the console or by way of the AWS CLI.

When you save the adjustments, the Bucket coverage will likely be set as the next utilizing the Amazon Redshift service principal.

For added particulars please check with Amazon Redshift audit logging.

For enabling logging by way of AWS CLI – db-auditing-cli-api.


Exporting logs into Amazon S3 might be extra cost-efficient, although contemplating all the advantages which CloudWatch offers concerning search, real-time entry to knowledge, constructing dashboards from search outcomes, and so on., it may well higher go well with those that carry out log evaluation.

For additional particulars, check with the next:

Greatest practices

Amazon Redshift makes use of the AWS safety frameworks to implement industry-leading safety within the areas of authentication, entry management, auditing, logging, compliance, knowledge safety, and community safety. For extra data, check with Safety in Amazon Redshift.

Audit logging to CloudWatch or to Amazon S3 is an optionally available course of, however to have the entire image of your Amazon Redshift utilization, we at all times suggest enabling audit logging, significantly in instances the place there are compliance necessities.

Log knowledge is saved indefinitely in CloudWatch Logs or Amazon S3 by default. This may occasionally incur excessive, sudden prices. We suggest that you simply configure how lengthy to retailer log knowledge in a log group or Amazon S3 to steadiness prices with compliance retention necessities. Apply the best compression to cut back the log file dimension.


This submit demonstrated how you can get close to real-time Amazon Redshift logs utilizing CloudWatch as a log vacation spot utilizing enhanced audit logging. This new performance helps make Amazon Redshift Audit logging simpler than ever, with out the necessity to implement a customized resolution to investigate logs. We additionally demonstrated how the brand new enhanced audit logging reduces log latency considerably on Amazon S3 with fine-grained entry management in comparison with the earlier model of audit logging.

Unauthorized entry is a major problem for many methods. As an administrator, you can begin exporting logs to stop any future incidence of issues reminiscent of system failures, outages, corruption of data, and different safety dangers.

Concerning the Authors

Nita Shah is an Analytics Specialist Options Architect at AWS primarily based out of New York. She has been constructing knowledge warehouse options for over 20 years and makes a speciality of Amazon Redshift. She is concentrated on serving to prospects design and construct enterprise-scale well-architected analytics and determination help platforms.

Evgenii Rublev is a Software program Improvement Engineer on the Amazon Redshift crew. He has labored on constructing end-to-end purposes for over 10 years. He’s obsessed with improvements in constructing high-availability and high-performance purposes to drive a greater buyer expertise. Outdoors of labor, Evgenii enjoys spending time together with his household, touring, and studying books.

Yanzhu Ji is a Product supervisor on the Amazon Redshift crew. She labored on Amazon Redshift crew as a Software program Engineer earlier than changing into a Product Supervisor, she has wealthy expertise of how the shopper dealing with Amazon Redshift options are constructed from planning to launching, and at all times deal with prospects’ necessities as first precedence. In private life, Yanzhu likes portray, images and taking part in tennis.

Ryan Liddle is a Software program Improvement Engineer on the Amazon Redshift crew. His present focus is on delivering new options and behind the scenes enhancements to finest service Amazon Redshift prospects. On the weekend he enjoys studying, exploring new working trails and discovering native eating places.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments