This script searches for all auto scaling group that has the world "database" in their names, collects their Ip (private address only) and try a connection on to the Mysql server using a default configuration Mysql file. This is meant to work on different deployment and different environment, in my case a single environment can have multiple deployment id. The connection to the database is done by pymysql module and it does a query to the heartbeat table in the monitoring table. ( this is not configurable via parameters)
python3 --version => 3.4.3 Modules needed
- click
- boto3
- pymysql
- pprint
- texttable
help
./heartbeat_list.py --help
Usage: heartbeat_list.py [OPTIONS]
Options:
--profile [bp|bs|bt] Aws Profile, default babel-production
--deploymentid INTEGER Deployment ID
--threshold INTEGER Threshold of the heartbeat
--rds Check the RDS single instance
--config TEXT Configuration file
--help Show this message and exit.
Script
/heartbeat_list.py --help
#!/usr/bin/env python3
# You need to have configured in your .my.cnf file a user able to query monitoring.heartbeat table
# You need to have a Aws discolabel-production profile
import click
import boto3
import pymysql
from pprint import pprint
import texttable
@click.command()
@click.option('--profile', default='bp', help='Aws Profile, default discolabel-production', type=click.Choice(['bp','bs','bt']))
@click.option('--deploymentid', default=0, help='Deployment ID',type=click.INT)
@click.option('--threshold', default=10, help='Threshold of the heartbeat',type=click.INT)
@click.option('--rds', default=False, help='Check the RDS single instance', is_flag=True)
@click.option('--config', default='/etc/mysql_dbstats_auth.cnf' , help='Configuration file')
# - click.BOOL default for flag option
# - is_flag=True
# Some options
# Feature Switches - Same option name, with different flag_value
# Prompting - parameters that can be provided from the command line
# Prompting hidden (for password)
# Yes Parameters
# Values from Environment Variables ( export GREETER_USERNAME=john; ./call_script.py; > hello john!)
# Why do I have to pass the list of args?
def main(profile,deploymentid,threshold,rds,config):
# Color??
print('profile {}, deplyoment-id {}, threshold {}, rds? {}, config-file {}'.format(profile, deploymentid, threshold, rds, config))
s = boto3.Session(profile_name=profile)
r53 = s.client('route53')
domain=''
# Check if the domain exists in the passed Aws profile
for hosted_zone in r53.list_hosted_zones()['HostedZones']:
# domain=hosted_zone['Name'] if hosted_zone['Name'].find('discolabel') == 0 else ''
if 'discolabel' in hosted_zone['Name']:
domain=hosted_zone['Name']
if len(domain) == 0:
print('discolabel domain not found or profile {} doesn\'t exist'.format(profile))
raise SystemExit
print('Domain {}'.format(domain))
# Server id of the master writer instance
cursor_db=pymysql.connect(
host='discolabel-database-{}-internal.{}'.format(deploymentid,domain),
read_default_file=config,
connect_timeout=2).cursor()
try:
cursor_db.execute('select @@server_id')
server_id=cursor_db.fetchone()[0]
except:
print('Error')
raise SystemExit
cursor_db.close()
# Print information detail
print("""
Heartbeat in
- Profile {}
- Deployment {}
- Server_id {}
- Threshold {}
""".format(profile, deploymentid, server_id, threshold))
asg = s.client('autoscaling')
asg_list=[]
for asg in asg.describe_auto_scaling_groups()['AutoScalingGroups']:
asg_list.append(asg['AutoScalingGroupName'])
ec2 = s.client('ec2')
asg_db_list=[]
for asg in asg_list:
if asg.find('database') > -1:
# print('{}: '.format(asg) + str(asg.find('database'))) ## str function is for "TypeError: Can't convert 'int' object to str implicitly"
asg_db_list.append(asg)
db_instances=ec2.describe_instances(Filters=
[
{'Name':'tag:DeploymentId','Values':[str(deploymentid)]},
{'Name':'tag:aws:autoscaling:groupName','Values':asg_db_list}
])['Reservations']
sql="select rpad(timediff(now(),str_to_date(ts,'%Y-%c-%dT%T.%f')),16,' '), now(),ts, if (abs(timediff(now(),str_to_date(ts,'%Y-%c-%dT%T.%f')))<{},'OK ','*NO*'), position from monitoring.heartbeat where server_id = '{}' union select '999:99:99 ',now(),'9999-12-31 99:99:99.999999','NONE', '000000' order by 1 limit 1;".format(threshold,server_id)
heartbeat_values=[]
for inst in db_instances:
for i in inst['Instances']:
for tag in i['Tags']:
if tag['Key'] == 'aws:autoscaling:groupName':
inst_asg=tag['Value']
cursor_db=pymysql.connect(
host=i['PrivateIpAddress'],
read_default_file=config,
connect_timeout=2).cursor()
try:
cursor_db.execute(sql)
result_list=list(cursor_db.fetchone())
result_list.insert(0,i['PrivateIpAddress'])
result_list.append(inst_asg.split('-')[0]+'-'+inst_asg.split('-')[1])
heartbeat_values.append(result_list)
except pymysql.Error as e:
print('Error running sql')
print(e)
# pprint(heartbeat_values)
# Printing heartbeat in table
# https://github.com/foutaise/texttable
tab = texttable.Texttable()
headings = ['server', 'asg', 'now', 'ts', 'difference', 'status']
tab.header(headings)
tab.set_cols_dtype(['i','t','t','t','t','t'])
tab.set_cols_width([14,20,24,27,17,7])
tab.set_cols_align(['c','c','c','c','c','c'])
server=[]
difference=[]
now=[]
ts=[]
status=[]
asg_c=[]
for row in heartbeat_values:
server.append(row[0])
difference.append(row[1])
now.append(row[2])
ts.append(row[3])
status.append(row[4])
asg_c.append(row[6])
# Zip Returns an iterator of tuples,
for row in zip(server,asg_c,now,ts,difference,status):
tab.add_row(row)
s = tab.draw()
print(s)
# if __name__ == '__main__':. This is traditionally how a standalone Python file looks like. With Click you can continue doing that,
if __name__ == '__main__':
main()
sys.exit()