How to Monitor Redshift Logs with Sumo Logic

In the second installment of our Amazon Redshift series, we covered the different ways you can monitor the performance and disk space of your Redshift servers using tools in AWS. In this final post, we will discuss how you can take your monitoring and logging efforts up a couple of notches by using Sumo Logic with Amazon Redshift.

Introduction to Sumo Logic

Sumo Logic helps organizations gain better real-time visibility into their IT infrastructure. Monitoring for both performance and security is top of mind for security analysts, and out-of-the-box tools from cloud server providers are hardly adequate to gain the level of visibility needed to make data-driven decisions.

Sumo Logic integrates with Redshift as well as most cloud services and widely-used cloud-based applications, making it simple and easy to aggregate data across different services, giving users a full view of their operational, business, and security analytics.

Overview of Amazon Redshift

Amazon Redshift is a petabyte-scale cloud-based data warehouse service from Amazon Web Services. It is fully managed and is designed for the storage, migration, and analysis of massive amounts of data sets.

A direct alternative to traditional on-premise data warehousing, Redshift provides organizations with a scalable, cost-efficient, and secure server solution that delivers fast performance and a level of querying efficiency that’s hard for the traditional solution to beat.

Read more: What is Amazon Redshift?

Amazon Redshift users would be remiss to not take advantage of the wealth of information generated by the datasets they keep and process on their Redshift clusters. This is where the integration with a cloud-native analytics platform like Sumo Logic enters the picture.

Redshift Integration Overview

As an AWS service, users of the data warehousing service Redshift have access to a wealth of monitoring and logging tools--but because these tools are wholesale in nature, just using the built-in monitoring tools alone won’t give security analysts the capability to parse through the massive amounts of information in Redshift that would enable them to make decisions founded on data.

Filling this crucial gap is the Sumo Logic App for Amazon Redshift Unified Logs and Metrics (ULM). This app helps users monitor activity in Amazon Redshift with the level of detail and ease of data manipulation required by large-volume data. Sumo Logic’s app for Redshift is armed with preconfigured dashboards that give granular view and crucial insights into database connections, SQL command and statement execution, user events, Amazon CloudTrail events, and resource utilization both on the node and cluster level.

Redshift Installation - Configuring Log and Metric Collection

The first step to using the Sumo Logic for Amazon Redshift ULM app is to set up the collection of logs and metrics from Redshift.

Step 1. Plan source categories

Prior to configuring the log and metric sources for the Sumo Logic Redshift app, you need to decide the source category to be assigned to each source.

Use a descriptive name for the categories. For example, for the AWS CloudTrail source for Redshift CloudTrail Events, you could specify a source category of AWS/CloudTrail. Using a hierarchical approach to naming your source categories enables you to search better and perform wildcards when needed.

Step 2. Enable Amazon Redshift Audit logging

For this step, you need to enable database audit logging and user activity logging. For complete instructions on how to enable database audit logging, see the steps outlined in this document. To enable user activity logging, you must enable the enable_user_activity_logging parameter.

Failing to enable user activity logging after enabling the audit logging feature will result in incomplete logs that only have connection and user logs, and not user activity logs. On Redshift, enable_user_activity_logging is disabled by default, so make sure to double-check before moving forward. Read more about this step here.

Steps 3 to 5: Configure sources for AWS S3, CloudTrail, and CloudWatch

Using the name scheme you’ve chosen on step 1, it’s now time to set up the sources for logs and metrics. You need to set up a collector for each source:

• AWS S3 source for Amazon Redshift Audit logs collection
• AWS CloudTrail source for CloudTrail Events
• AWS CloudWatch source for Redshift metrics

For each one, you need to set up a Hosted Collector. Make sure to fill out all fields and use the categories you’ve decided on in step 1.

Sumo Logic Redshift ULM App Installation

After collection is configured, you can then install the Redshift app from the Sumo Logic app catalog. Follow these steps:

1. Search for the Redshift app in the Sumo Logic App Catalog
2. Click Add to Library and fill out the following fields:
   1. App Name: You can use the existing name or replace with your name of choice.
   2. Data Source: Choose Source Category to pick a data source from the list or Enter a Customer Data Filter.
   3. Advanced: Choose the Location in Library (set to Personal folder by default), or add a New Folder.
3. Click Add to Library to finish installation.

Once the Redshift app is installed, you may now share it with your organization. The panels of the preconfigured dashboards will now be automatically populated with data from the specified sources. Note that it may take a moment to see full graphs and visualizations.

Sumo Logic Redshift Dashboards

There are seven preconfigured dashboards on the Sumo Logic Redshift App that give an incisive view of performance and security metrics of your Redshift clusters. For more in-depth information on the data points on each dashboard, each metric is described in detail in this document.

Read more: Dashboards on the Sumo Logic Redshift ULM App

1. Amazon Redshift - Overview

Covers overviews of connections, user activity, CloudTrail events, and resource utilization.

2. Amazon Redshift - Audit - Connection Log Analysis

Covers information about database connections, including authentication failure counts and trends, session statistics and details, and top remote hosts, users, databases, and applications.

3. Amazon Redshift - Audit - User Activity Log Analysis

Covers information about SQL command and statement execution--including top databases, users, SQL statements and commands, and tabular listings of the top 20 delete, truncate, vacuum, create, grant, drop, revoke, and alter command executions. 

4. Amazon Redshift - Audit - User Log Analysis

Covers information about database user account events, including database user database accounts that were created, dropped, or altered.

5. Amazon Redshift - CloudTrail Events Overview

Covers information about CloudTrail events for Amazon Redshift. This includes event locations, event status, and trends; event counts by event name, cluster, account ID, region, and user agent; and failed event locations, error codes, and details.

6. Amazon Redshift - Resource Utilization by ClusterIdentifier

Covers cluster-level resource utilization metrics, including CPU, network receive, and transmit throughput, database connections, and disk.

7. Amazon Redshift - Resource Utilization by NodeID

Covers node-level resource utilization metrics, including CPU; disk; network; and read/write latency, throughput and I/O operations per second.

Benefits of Using the Sumo Logic Redshift ULM App

While Amazon Redshift provides users a powerful product in its warehousing solution, there are a lot of opportunities that could be lost if the use of Redshift clusters is not paired with a competent analytics solution like Sumo Logic. From identifying and resolving issues faster to proactively monitoring the performance of queries in Redshift, the Sumo Logic Redshift ULM App is the perfect Redshift companion for mission-critical operations, business, and security analytics.