Get processlist from any RDS(mysql) while a cloudwatch alarm triggers.

multi-rds-lambda.py

# Run any query on any RDS(MYSQL) while a cloudwatch alarm triggers.
# Version 1.0
# Author: SqlAdmin
# Twitter: https://twitter.com/SqlAdmin
# Blog: www.sqlgossip.com
#----------------------------------------------------------------------------------------------------------------
# Make a note:
# In this function we used to get the RDS instance Identifier from the SNS, It won't give the complete endpoint
# So in your account go and see the endpoint, it should like identifier+some-random-string+region+amazon.com
# The random string which is next to the Identifier is differ from other account. 
# So just copy the entire string which is coming after the instance identifier.
# for us, identifier.abcedfg.ap-south-1.rds.amazonaws.com
#----------------------------------------------------------------------------------------------------------------

# import the libraries 
import sys
import json
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')

def handler(event, context):
    
    ###Get RDS Endpoint from CloudWatch
    message = event['Records'][0]['Sns']['Message']
    if isinstance(message, str):
        try:
            message = json.loads(message)
        except Exception as e:
            print(e)
    elif isinstance(message, list):
        message = message[0]
    
    Endpoint =  message['Trigger']['Dimensions'][0]['value'] if isinstance(message, dict) else message
    
    # Use your endpoint string whcich is coming after the identifier
    DB_HOST = Endpoint+'.abcedfg.ap-south-1.rds.amazonaws.com' 
    
    #AWS KMS Encrypted Parameters
    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)
    
    #Check Mysql Connectivity 
    try:
        conn = pymysql.connect(DB_HOST, 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")
    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, so we disable this in this function
            #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 = ['to-address1@domain.com','to-address2@domain.com']
            msg = MIMEMultipart()
            msg['Subject'] = "RDS Current ProcessList"
            msg['From'] = "ses-verified-sender@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('ses-verified-sender@domain.com',['to-address1@domain.com','to-address2@domain.com'],msg.as_string())
            s.quit()