Amazon RDS database monitoring with Amazon CloudWatch Logs, AWS Lambda, and Amazon SNS to send logs over email

Posted on by Mohite.Pramod

Hi All,

Today, we are going to learn “how to get amazon RDS database logs notifications to our personal or official email id.

Suppose Dev team is working on amazon rds db to utilize the db functionality with CRUD operations and same will be applicable while it’s in-use for running applications.

But sometimes what happens that there could be errors occurs during the insert, update, delete the data into the databases and may be we could missed out those.

When we are using amazon rds db, then aws have provided logging mechanism for the same and we can find the db logs inside aws cloudwatch. But those logs are available only in aws management console. To see the amazon rds db logs,everytime we need to login to aws console and check those logs. This is not a good solution for production databases. There should be automated way to get the logs whenever there is some operations happens at db end.

To get the logs with automation by using aws services and sends db logs to personal or official email ids. here is the step by step guide to achieve the same.

In general, Database administrators generally monitor for keywords like ORA- errors in Oracle databases and ERROR in PostgreSQL, Aurora, MySQL, SQL Server, and MariaDB databases. When the database error occurs, DBAs need to be notified of the alert to acknowledge the seriousness of the error and take appropriate action.

The solution in this post addresses two issues:

  • Monitoring the RDS database for database errors that appear in the logs
  • Streaming the database logs for RDS databases without having to read the whole file every time to monitor the database errors

This post details the steps to implement proactive monitoring of alerts for an RDS database by streaming the database logs based on the keywords of ORA- errors in an Oracle database or ERROR in PostgreSQL, Aurora, MySQL, MariaDB, or SQL Server databases and send a notification by email to the database administrator to take necessary action to fix the issue. The following diagram illustrates our solution architecture.

We provide two methods to configure this solution: set up the resources manually through various AWS services, or deploy the resources with an AWS CloudFormation template. Both methods complete the following high-level steps:

  1. Create an SNS topic.
  2. Create AWS Identity and Access Management (IAM) roles and policies needed for the solution.
  3. Create a Lambda function with the provided code and assign the appropriate IAM roles.

We then walk you through identifying the log group to monitor, creating a trigger in the Lambda function, and testing the error notification system.

Prerequisites

For this walkthrough, the following prerequisites are necessary:

  • An AWS account with RDS instances running.
  • An RDS instance with logs exported to Amazon CloudWatch. To verify this, on the Amazon RDS console, navigate to your instance and choose Configuration. Under the published logs, you see PostgreSQL (when using PostgreSQL or Aurora), alert (when using Oracle), or error (when using MariaDB, MySQL, or SQL Server).

Set up proactive monitoring and alerting in Amazon RDS manually

In this section, we walk through the process to set up the resources for active monitoring and alerting using various AWS services. To deploy these resources via AWS CloudFormation, you can skip to the next section.

Create an SNS topic

We first create a standard SNS topic and subscribe to it in order to receive email notifications. For instructions, see To create a topic using the AWS Management Console and Email notifications, respectively.

If you already have an existing SNS topic that you want to use, you can skip to the next step.

Set up an IAM role and policies

This step creates a role to create the Lambda function and grant it appropriate permissions. We start by creating our policy.

  1. On the IAM console, under Access management, choose Policies.
  2. Choose Create policy.
  3. Choose JSON.
  4. Enter the following JSON code, providing your Region, account ID, SNS topic name, and the name of the function you’re creating:

================================================

{

“Version”: “2012-10-17”,

“Statement”: [

{

“Effect”: “Allow”,

“Action”: [

“logs:CreateLogGroup”,

“sns:Publish”

],

“Resource”: [

“arn:aws:logs:<region>:<account number>:*”,

“arn:aws:sns:<region>:<account number>:<sns topic name>”,

“arn:aws:lambda:<region>:<account_number>:function:<lambda function name>”

]

},

{

“Effect”: “Allow”,

“Action”: [

“logs:CreateLogStream”,

“logs:PutLogEvents”

],

“Resource”: [

“arn:aws:logs:<region>:<account number>:log-group:/aws/lambda/<lambda function name>:*”

]

}

]

}

========================================================

  1. Choose Review policy.
  2. Enter a policy name, such as AWSLambdaExecutionRole-ErrorNotification.
  3. Choose Create policy.You now create a role and attach your policy.
  4. In the navigation pane, under Access management, choose Roles.
  5. Choose Create role.
  6. Choose Lambda.
  7. Choose Next: Permissions.
Find and choose the policy you just created.
Choose Next: Tags.
Choose Next: Review.
Enter a name for the role, such as LambdaRoleErrorNotification.
Choose Create role.

Create a Lambda function

This steps illustrates how to create the Lambda function that is used to process the CloudWatch logs and send notifications using the Amazon SNS ARN of the topic you created.

  1. On the Lambda function, choose Create function.You need to create the function in the same Region as that of the RDS database server you want to monitor.
  2. Select Author from scratch.
  3. For Function name, enter a name, such as RDSErrorsNotification.
  4. For Runtime, choose Python 3.8.
  5. For Execution role¸ select Use an existing role.
  6. For Existing role, choose the role you created.
  7. Enter the following code:

==============================================================

import sys

import re

import boto3

import math, time

import datetime

import base64

import json

import gzip

import os

def lambda_handler(event, context):

# Reading the cloudwatch log data

cloud_log_data= event[‘awslogs’][‘data’]

message=””

compressed_data= base64.b64decode(cloud_log_data)

uncompressed_data = gzip.decompress(compressed_data)

logdataload = json.loads(uncompressed_data)

# Getting the log group name that needs processing LogGroupName = logdataload[‘logGroup’]

# Debug output of logEvents

print(logdataload[“logEvents”])

# Get the environment variables in Lambda

Region=os.environ.get(‘Region’)

SNSArn=os.environ.get(‘SNSArn’)

SNSRegion=os.environ.get(‘SNSRegion’)

ExcludeFilterPattern=os.environ.get(‘ExcludeFilterPattern’)

if os.environ.get(‘ExcludeFilterPattern’) is None:

ExcludeFilterPattern=”password”

else:

ExcludeFilterPattern=ExcludeFilterPattern+”,password”

ExcludeFilterPattern=ExcludeFilterPattern.split(“,”) IncludeFilterPattern=os.environ.get(‘IncludeFilterPattern’)

# The script works for Oracle/PostgreSQL/Aurora, the condition checks which database and assigns a pattern

if os.environ.get(‘IncludeFilterPattern’) is None:

if “oracle” in LogGroupName.lower():

IncludeFilterPattern=”ORA-”

if “postgres” in LogGroupName.lower():

IncludeFilterPattern=”ERROR”

if “aurora” in LogGroupName.lower():

IncludeFilterPattern=”ERROR”

if “maria” in LogGroupName.lower():

IncludeFilterPattern=”ERROR”

if “sqlserver” in LogGroupName.lower():

IncludeFilterPattern=”ERROR”

if “mysql” in LogGroupName.lower():

IncludeFilterPattern=”ERROR”

IncludeFilterPattern=IncludeFilterPattern.split(“,”)

# Checking if errors exist which match the pattern

errors_exist = len(logdataload[“logEvents”])

if errors_exist == 0:

print(“No errors exist”)

else:

for record in logdataload[“logEvents”]:

# checks if the error is in exclude list or need to be filtered

if re.compile(‘|’.join(ExcludeFilterPattern),re.IGNORECASE).search(record[“message”]): print(‘Error is ignored for {0}’.format(record[“message”]) )

else:

if re.compile(‘|’.join(IncludeFilterPattern),re.IGNORECASE).search(record[“message”]):

message=”Errors in logfile are:n” + record[“message”] + “\n”

else:

print(“No errors match IncludeFilterPattern list”)

# Sends an SNS notification with the error information

if len(message) > 0:

SNSClient = boto3.client(‘sns’, region_name=SNSRegion)

response = SNSClient.publish( TopicArn=SNSArn, Message=str(message), Subject=’Errors exists in RDS database log group’+LogGroupName )

print(‘\t Response:{xyz} \n’.format(xyz=response[‘ResponseMetadata’][‘HTTPStatusCode’]))

====================================================

  1. Choose Deploy.
  2. On your Lambda function page, choose Edit environment variables and input the following keys with corresponding values.
KeyValueAdditional Information
SNSArnThe ARN of the SNS topic you created.This variable is mandatory.
SNSRegionThe Region of SNS topic which you created.This variable is mandatory.
RegionThe Region of Lambda and the RDS database CloudWatch logs.This variable is mandatory.
IncludeFilterPatternSpace-separated patterns for errors that you want to be alterted of.For example, if the RDS database is Oracle, you could only be notified of errors like ORA-00600,ORA-07445.
for ex: “?ERROR(space)?FATAL(space)?LOG“
where, ?-means whatever the logs coming after above pattern, that sentence will be part of email body which we are sending over email.		You can use this parameter to filter any pattern (not just errors) that need to be monitored in the database.This variable is optional.
ExcludeFilterPattern(optional)Space-separated patterns for errors that you don’t want to be alterted of.For example, if the RDS database is Oracle, the value can be ORA-12560,ORA-12152.This variable is optional.

By default, if no filter patterns are mentioned, all ORA- errors in the Oracle RDS alert.log and ERROR messages in the PostgreSQL or Aurora postgresql.log are alerted.

  1. Choose Save.

Create a CloudWatch trigger in the Lambda function

The database logs stored in CloudWatch need to be streamed to the Lambda function for it to process in order to send notifications. You can stream CloudWatch logs by creating a CloudWatch trigger in the function.

  1. On the Lambda console, choose the function you created (or the CloudFormation stack created for you).
  2. In the Designer section, choose Add trigger.
  3. On the drop-down menu, choose CloudWatch Logs.
  4. For Log group, choose the log group corresponding to the RDS database you want to monitor.You can add additional log groups by creating additional triggers.
  5. For Filter name, enter a name.
  6. For Filter pattern, enter a pattern (ORA- for Oracle or ERROR for PostgreSQL, Aurora, MariaDB, MySQL, or SQL Server).If you don’t provide a filter pattern, the function is triggered for every log data written to the database logs, which we don’t want because it increases costs. Because we just want to be notified for alerts, we need to stream just the ERROR or ORA- keywords to the Lambda function.
  7. Choose Add.

To add more triggers, repeat these steps for other database logs.

Test the error notification

Now we’re ready to test the notification when an error occurs in the database. You can generate a fake alert in the RDS database.

For this post, we create an alert for an RDS for PostgreSQL database.

You should receive an email to the email subscribed to the SNS topic.

Once we are able to receive such notifications, it means it’s grabbing all the recent logs from cloudwatch and sending the same over email.

References:

https://aws.amazon.com/blogs/database/build-proactive-database-monitoring-for-amazon-rds-with-amazon-cloudwatch-logs-aws-lambda-and-amazon-sns/

We are successfully completed our Goal…Cheers and Enjoy !!!!

Leave a comment