Query RDS from lambda and save the result as CSV, Sent the result in Email, Save the Result in S3

rds-lambda-s3.py

# Query RDS From Lambda and Send Results in Email and Save it to S3
# Blog Post: https://blog.powerupcloud.com/automate-rds-and-aurora-mysql-processes-list-in-lambda-with-kms-736b2878349
# Verson 1.1
# Bug fix1 : Mulitple email receipts 


import sys
import logging
import pymysql
import os
import csv
import boto3
import smtplib
import email.utils
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from base64 import b64decode

#S3 CONNECTIVITY
s3_client = boto3.client('s3')

#AWS KMS Encrypted Parameters
EN_HOSTNAME = os.environ['DB_HOST']
DE_HOSTNAME = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_HOSTNAME))['Plaintext']
EN_UN = os.environ['DB_USERNAME']
DE_UN = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_UN))['Plaintext']
EN_PW = os.environ['DB_PASSWORD']
DE_PW = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_PW))['Plaintext']
EN_DB = os.environ['DB_NAME']
DE_DB = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_DB))['Plaintext']
EN_SMTP = os.environ['SES_SMTP']
DE_SMTP = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_SMTP))['Plaintext']
EN_SES_USER = os.environ['SES_USER']
DE_SES_USER = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_SES_USER))['Plaintext']
EN_SES_PW = os.environ['SES_PW']
DE_SES_PW = boto3.client('kms').decrypt(CiphertextBlob=b64decode(EN_SES_PW))['Plaintext']
logger = logging.getLogger()
logger.setLevel(logging.INFO)
try:
    conn = pymysql.connect(DE_HOSTNAME.decode("utf-8"), user=DE_UN.decode("utf-8"), passwd=DE_PW.decode("utf-8") , db=DE_DB.decode("utf-8"), connect_timeout=5)
except:
    logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    sys.exit()
logger.info("SUCCESS: Connection to RDS mysql instance succeeded")
def handler(event, context):
 
    with conn.cursor() as cur:
        # Enter the query that you want to execute
        cur.execute("select * from information_schema.processlist;") 
        for row in cur:
            res = cur.fetchall()
            
            #Save the Query results to a CSV file
            fp = open('/tmp/Processlist.csv', 'w')
            myFile = csv.writer(fp)
            myFile.writerows(res)
            fp.close()
            
            #Add the Header to the CSV file
            os.system("sed -i '1 i\ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO' /tmp/Processlist.csv")
            
            #Upload this file to S3  --Its not mandatory for this process
            #s3_client.upload_file('/tmp/Processlist.csv', 's3-bucket-1', 'Processlist.csv')
            
            # SMTP Credentials for Sending email -- we are using AWS SES
            EMAIL_HOST = DE_SMTP.decode("utf-8")
            EMAIL_HOST_USER = DE_SES_USER.decode("utf-8")
            EMAIL_HOST_PASSWORD = DE_SES_PW.decode("utf-8")
            EMAIL_PORT = 587
            RECIPIENTS = ['bhuvi@sqladmin.com','you@yourdomain.com']
            msg = MIMEMultipart()
            msg['Subject'] = "RDS CPU Alarm"
            msg['From'] = "aws-ses-verified-email@domain.com"
            msg['To'] = ", ".join(RECIPIENTS)             
            mail_file = MIMEBase('application', 'csv')
            mail_file.set_payload(open('/tmp/Processlist.csv', 'rb').read())
            mail_file.add_header('Content-Disposition', 'attachment', filename='Processlist.csv')
            msg.attach(mail_file)
            s = smtplib.SMTP(EMAIL_HOST, EMAIL_PORT)
            s.starttls()
            s.login(EMAIL_HOST_USER, EMAIL_HOST_PASSWORD)
            s.sendmail('me_sender@domain.com',['bhuvi@sqladmin.com','you@yourdomain.com'],msg.as_string())
            s.quit()

Can the same be done in Oracle RDS too? Do we need to do any changes for it in this code?

It doesn't fetch the first row from the sql for me. It fetches all the other rows though. How do I resolve it

Hi pucbabajob, this seems a bit outdated. Some parameters like the KMS part don't work anymore, had to update the code for it to fetch the KMS values. Anyway, I really appreciate this and wondering if you have an updated one? Coz I still can't seem to make it work. Giving me an error on the RDS part with an error "Cannot connect to MySQL Instance". I've checked everything including routes, VPC and sec groups. Even reached out to AWS support but RDS seems fine and I think it's the code itself. Any help will be appreciated. Really want to knock this one out. Hoping for a reply, thanks!

Is there any updates on this code ?