Custom Cloudwatch RDS Monitoring Plugins Part-1

Monitoring the RDS Instances is necessary for detecting the issues, AWS RDS provides outbox metrices i.e. system level metrices. But there are occassions when you want to monitor things like blocked connections, advance queue etc. So you can use the below cloudwatch plugin to monitor anything in RDS based on custom query.

The plugin works on logic that the query which is executed on RDS does not provide any error message than the count would be 0 which means ok and if something is wrong than an error message would be prevented whose count would be not 0 which means alarm.

Than if you are using the sharding than you would need to execute the same query on all your shard databases. The below script can run on 1 database or number of database in case of sharding.



Also in case the output is non-zero than the query output would directly be appended onto the email body and email would be sent to the DBA , Devops group so they can identify whats the problem on the RDS end.

 #!/bin/bash  
 # Include system variables  
 # Creating the alarms through oracle user  
 . /home/oracle/.bash_profile  
 #Setting the AWS Configuration  
 export AWS_SHARED_CREDENTIALS_FILE=/opt/cloudwatch_plugin/aws-config  
 export MONITORING_HOME=/opt/cloudwatch_plugin  
 export PATH=$PATH:/bin:/usr/local/bin  
 # Get Instance ID  
 instanceId=`wget -q -O - http://169.254.169.254/latest/meta-data/instance-id`  
 # Source Proxy Configuration  
 #### Sourcing of the proxy to connect to internet to create alarms  
 source $MONITORING_HOME/etc/proxy.conf;  
 ###Configuration File is passed as an Argument  
 source $MONITORING_HOME/etc/$1;  
 # get length of an array  
 arraylength=${#proc_matrix[@]}  
 let "arylngth=arraylength/2"  
 #echo "Length of array: $arylngth"  
 function usage {  
     echo  
     echo  
     echo "AWS Cloud Watch Process Monitoring Script"  
     echo "All parameters like Metric Name and Service name to be defined in aws-ec2_rds_mon.conf"  
     echo "Usage:"  
     echo "$0 <configfile.cfg>"  
     echo  
     echo "Example:"  
     echo "$0 configfile.cfg"  
     echo  
 }  
 # check if namespace-name parameter is passed  
 if [ -z "$1" ]  
     then  
         echo "There's some error in parameters, follow instructions"  
         usage  
         # Exit with status ERROR  
         exit 1  
 fi  
 ###Setting the Initial Counter to 0  
 counter=0  
 ###Itterating a loop to execute queries on multiple databases  
 for ((i=1;i<=${arylngth};i++)) do  
 rds_connection(){  
 hostname=$1  
 j=$2  
 counter=`expr $counter + 1`  
 if [ $counter -gt 7 ]  
 then  
 counter=1  
 fi  
 ###Executing the SQL on the RDS  
 `/oracle/app/oracle/product/12.1.0/client_1/bin/sqlplus -s $username/$password@$hostname @/opt/cloudwatch_plugin/etc/${proc_matrix[$i,2]} > /tmp/${proc_matrix[$i,2]}_$hostname.txt 2>&1 `  
 Minimum=0  
 ###Counting the Query count   
 count=`cat /tmp/${proc_matrix[$i,2]}_$hostname.txt | grep -v Release| grep -v Copyright | grep -v Successful | grep -v Connected | grep -v Enterprise | grep -v Partitioning | grep -v Disconnected| grep -v OLAP | grep -v no | grep '[^[:blank:]]' | wc -l`  
 ###Comparing the Query to 0 where 0 means ok and anything other than 0 is alarm  
 if [ $count -eq 0 ]  
 then  
     aws cloudwatch put-metric-data --namespace "$namespace" --metric-name "${proc_matrix[$i,1]}" --dimension "DBInstanceIdentifier=${db_connection[$counter,1]}" --value "$count" --unit "Count"  
 else  
 #Exporting Proxy  
     aws cloudwatch put-metric-data --namespace "$namespace" --metric-name "${proc_matrix[$i,1]}" --dimension "DBInstanceIdentifier=${db_connection[$counter,1]}" --value "$count" --unit "Count"  
   cat /tmp/${proc_matrix[$i,2]}_$hostname.txt | /bin/mail -A ses -s "${db_connection[$j,1]} - ${alert_matrix[$i,1]} " [email protected] , [email protected]  
 fi  
 }  
 ###Number of databases on which this query needs to be executed  
 for j in {1..7}  
 do  
 rds_connection ${db_matrix[$j,1]} $j  
 done  
 done  
 # Exit with OK status  
 exit 0  

This forms the executable part of the script. We need to pass the arguments configuration file so that all the variables used in this script are there. The configuration file is different so that in case anything changes in future it would only needs to be modified in the configuration file and your executable file remains the same.