<\/p>
<\/p>
Before we learn how to automatically Export RDS Audit logs to S3, did you know AWS CloudWatch automatically stores RDS DB logs? Yes, they do, but, they only store the general logs for your DB instance. Now, what if you want to find a specific query or data from the log? Which user performed a particular query or from which IP Address? Who deleted certain records from your DB tables?<\/p>
Also, somehow you have managed to export all audit logs of your RDS instance to CloudWatch, which is great. But, do you know? CloudWatch will retain those logs for 30 days only!! To retain them for a longer period, you need to export CloudWatch logs to the S3 bucket. You can do this manually from the CloudWatch dashboard. But, what if you want to have automation for this?<\/p>
This guide will walk you through all the steps required to log all such events automatically!!<\/p>
This guide includes:<\/b><\/p>
1) Tweak RDS settings to send audit logs to CloudWatch
\n2) Create an S3 Bucket (To Store CloudWatch Audit Logs)
\n3) Create IAM Role (We will use this for Lambda automation)
\n4) Lambda (Function to automate CloudWatch Logs export to S3)<\/p>
Let’s begin!<\/p>
First of all, we need to tweak the RDS to send specific logs to CloudWatch. To do this, we will create one Option Group and one Parameter Group.<\/p>
⇒ Visit your Amazon RDS Dashboard.
\n⇒ Go to the Option Group.
\n⇒ Click on the Create Group button.<\/p>
⇒ Enter the Name and Description for this group. ⇒ Click on the Option Group that you have created. ⇒ Let’s open the Amazon RDS Dashboard. ⇒ Select “Parameter group family”: mysql8.0 ⇒ Now, click on the parameter Group that you have created. Now, we are all set with our Option Group and Parameter Group. Now, let’s allocate these groups to our RDS.<\/p> ⇒ Go to your RDS Dashboard. We are now all set with the RDS. Once you are done with the above steps, kindly allow 20-30 minutes to populate the data in CloudWatch (NOTE: It may take more time depending on the size of the logs).<\/p> Once CloudWatch has gathered all the logs, you will see the audit logs under your CloudWatch Dashboard. It will look like the following image:<\/p> ⇒ Go to Amazon S3 Dashboard. Now, we will create the IAM role that will be used in the Lambda function set up. The AWS Lambda service will require permission to log events and write to the S3 bucket we have created.<\/p> We will create the IAM Role “Export-RDS-CloudWatch-to-S3-Lambda” with “AmazonS3FullAccess”, “CloudWatchLogsFullAccess”, and “CloudWatchEventsFullAccess” policies.<\/p> ⇒ Open your AWS IAM Dashboard. Lambda function allows you to put your code under the function and run it on triggers. You do not need to have any server or set up for this. Very easy and efficient!<\/p> ⇒ Switch to AWS Lambda Dashboard. ⇒ Keep “Author for Scratch” selected. ⇒ Click on the Create function and navigate to Code view and enter the following script:<\/p> ⇒ Now, click on Configuration ⇒ Environment Variables.<\/a><\/p>
\n⇒ Select Engine: mysql and Engine Version: 8.0. Click on Create.<\/p><\/a><\/p>
\n⇒ Under Options section, click on Add option button.
\n⇒ Under “SERVER_AUDIT_EVENTS*” option, enter the values that you want to audit i.e. CONNECT, QUERY, QUERY_DDL, QUERY_DML, QUERY_DCL, QUERY_DML_NO_SELECT. If you want to log all the queries run on your tables, just enter QUERY in this field.
\n⇒ Set “Yes” for Apply Immediately. Click on Add Option.<\/p><\/a><\/p>
<\/div>Looking for AWS EXPERTS? CHECKOUT HERE<\/span><\/a><\/div><\/div>Create Parameter Group<\/h3>
\n⇒ Click on the Parameter Group.
\n⇒ Click on the Create Parameter Group button.<\/p><\/a><\/p>
\n⇒ Select “Type”: DB Parameter Group
\n⇒ Enter Group Name and Description. Click on Create.<\/p><\/a><\/p>
\n⇒ Under Parameter, we will edit the certain parameters and set the following values:
\n⇒ Edit parameter: “log_output” ⇒ Change its value from TABLE to FILE
\n⇒ Edit parameter: “slow_query_log” ⇒ Change its value from BLANK to 1
\n⇒ Edit parameter: “general_log” ⇒ Change its value from BLANK to 1<\/p>
\n⇒ Click on the RDS that you have created.
\n⇒ Click on Modify.
\n⇒ Scroll down the page and go to the “Database options” section.
\n⇒ Under “DB parameter group”, select the parameter group that you have created.
\n⇒ Under “Option group”, select the option group that you have created.<\/p><\/a><\/p>
<\/a><\/p>
<\/div>Looking for AWS EXPERTS? CHECKOUT HERE<\/span><\/a><\/div><\/div>Create an S3 Bucket (To Store CloudWatch Audit Logs)<\/h2>
\n⇒ Create a new Bucket.
\n⇒ Once you have created a bucket, open it and navigate to the Permissions tab.
\n⇒ We will need to allow CloudWatch to put objects to the bucket (Write Access)
\n⇒ Click on Edit button for Bucket policy. Enter the following code:<\/p>{\n\"Version\": \"2012-10-17\",\n\"Statement\": [\n{\n\"Effect\": \"Allow\",\n\"Principal\": {\n\"Service\": \"logs.YOUR-REGION.amazonaws.com\" \n\/\/ i.e. logs.us-east-1.amazonaws.com\n},\n\"Action\": \"s3:GetBucketAcl\",\n\"Resource\": \"arn:aws:s3:::BUCKET_NAME_HERE\"\n},\n{\n\"Effect\": \"Allow\",\n\"Principal\": {\n\"Service\": \"logs.YOUR-REGION.amazonaws.com\"\n},\n\"Action\": \"s3:PutObject\",\n\"Resource\": \"arn:aws:s3:::BUCKET_NAME_HERE\/*\",\n\"Condition\": {\n\"StringEquals\": {\n\"s3:x-amz-acl\": \"bucket-owner-full-control\"\n}\n}\n}\n]\n}<\/pre><\/div>Create IAM Role (We will use this for Lambda automation)<\/h2>
\n⇒ Switch to the Roles and click on the Create Role button.
\n⇒ Under “Use case”, select Lambda and click on Next.
\n⇒ Search for “AmazonS3FullAccess” and select it.
\n⇒ Search for “CloudWatchLogsFullAccess” and select it.
\n⇒ Search for “CloudWatchEventsFullAccess” and select it.
\n⇒ Set Role Name: “Export-RDS-CloudWatch-to-S3-Lambda” and click on Create role.<\/p><\/div>Looking for AWS EXPERTS? CHECKOUT HERE<\/span><\/a><\/div><\/div>Lambda (Function to automate CloudWatch Logs export to S3)<\/h2>
\n⇒ Click on the Functions and then click on the Create function button.<\/p><\/a><\/p>
\n⇒ Set “Function name”: Export-RDS-CloudWatch-Logs-To-S3
\n⇒ Under “Runtime”, select Python 3.x.
\n⇒ Under “Permissions”, select “Use an existing role” and select the IAM role that we created in the previous step.<\/p><\/a><\/p>
import boto3\nimport os\nimport datetime\n\nGROUP_NAME = os.environ['GROUP_NAME']\nDESTINATION_BUCKET = os.environ['DESTINATION_BUCKET']\nPREFIX = os.environ['PREFIX']\nNDAYS = os.environ['NDAYS']\nnDays = int(NDAYS)\n\ncurrentTime = datetime.datetime.now()\nStartDate = currentTime - datetime.timedelta(days=nDays)\nEndDate = currentTime - datetime.timedelta(days=nDays - 1)\n\nfromDate = int(StartDate.timestamp() * 1000)\ntoDate = int(EndDate.timestamp() * 1000)\n\nBUCKET_PREFIX = os.path.join(PREFIX, StartDate.strftime\n('%Y{0}%m{0}%d').format(os.path.sep))\n\ndef lambda_handler(event, context):\nclient = boto3.client('logs')\nclient.create_export_task(\nlogGroupName=GROUP_NAME,\nfromTime=fromDate,\nto=toDate,\ndestination=DESTINATION_BUCKET,\ndestinationPrefix=BUCKET_PREFIX\n)<\/pre><\/div>
\n⇒ We need to create 4 variables:
\n⇒ DESTINATION_BUCKET: <Name of your S3 bucket>
\n⇒ GROUP_NAME: <Log’s group name that you are exporting>
\n⇒ NDAYS: 1
\n⇒ PREFIX: exported-logs<\/p>