1、通过阿里sdk获取慢查询列表,格式化。 2、企业微信报警接口 3、deamon
#!/usr/bin/python #-*- conding:utf-8 -*- from aliyunsdkcore.client import AcsClient from aliyunsdkrds.request.v20140815 import DescribeDBInstanceAttributeRequest from aliyunsdkrds.request.v20140815 import DescribeSlowLogRecordsRequest import requests import json import os import time import logging import subprocess #ali key client = AcsClient( "", "", "" ); log_file = "/tmp/rdsslowinfo.log" logging.basicConfig(filename=log_file,filemode='a',level=logging.DEBUG) def WARNING(*objs): print("[%s] : " % time.strftime("%y-%m-%d %H:%M:%S", time.localtime()), *objs, file=sys.stderr) class rdsInfo(object): ''' ali rds slow log get''' def __init__(self,dbId,startTime,endTime): self.dbId = dbId self.startTime = startTime self.endTime = endTime def slowlogGet(self): request = DescribeSlowLogRecordsRequest.DescribeSlowLogRecordsRequest() request.set_accept_format('json') request.set_DBInstanceId(self.dbId) request.set_StartTime(self.startTime) request.set_EndTime(self.endTime) response = client.do_action_with_exception(request) data = json.loads(response) return data def instanceGet(self): request = DescribeDBInstanceAttributeRequest.DescribeDBInstanceAttributeRequest() request.set_accept_format('json') request.set_DBInstanceId(self.dbId) response = client.do_action_with_exception(request) data = json.loads(response) #print (data['Items']) return data class WeChat: '''微信接口''' def __init__(self,user): self.CORPID = '' #企业ID, 登陆企业微信,在我的企业-->企业信息里查看 self.CORPSECRET = '' #自建应用,每个自建应用里都有单独的secret self.AGENTID = '' #应用代码 self.TOUSER = user # 接收者用户名, @all 全体成员 def _get_access_token(self): url = 'https://qyapi.weixin.qq.com/cgi-bin/gettoken' values = {'corpid': self.CORPID, 'corpsecret': self.CORPSECRET, } req = requests.post(url, params=values) data = json.loads(req.text) # print (data) return data["access_token"] def get_access_token(self): try: with open('access_token.conf', 'r') as f: t, access_token = f.read().split() except: with open('access_token.conf', 'w') as f: access_token = self._get_access_token() cur_time = time.time() f.write('\t'.join([str(cur_time), access_token])) return access_token else: cur_time = time.time() if 0 < cur_time - float(t) < 7200: #token的有效时间7200s return access_token else: with open('access_token.conf', 'w') as f: access_token = self._get_access_token() f.write('\t'.join([str(cur_time), access_token])) return access_token def send_data(self, msg): send_url = 'https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=' + self.get_access_token() send_values = { "touser": self.TOUSER, "msgtype": "text", "agentid": self.AGENTID, "text": { "content": msg }, "safe": "0" } send_msges=(bytes(json.dumps(send_values), 'utf-8')) respone = requests.post(send_url, send_msges) respone = respone.json()#当返回的数据是json串的时候直接用.json即可将respone转换成字典 # print (respone["errmsg"]) return respone["errmsg"] def dataInit(): ''' get data info''' instanceInfo = { 'rm-阿里域名':'业务线', 'rm-':'', 'rm-':'', 'rm-':'', 'rm-':'', } alarmList = ['','','','','']#收报警人列表 for dbUrl in instanceInfo.keys(): nowTime = time.strftime("%Y-%m-%dT%H:%MZ") second = time.time() - 24*60*60 #查询间隔 自己定义 tago = time.strftime('%Y-%m-%dT%H:%MZ', time.localtime(second)) rdsinfo = rdsInfo(dbUrl,tago,nowTime) slowlog_data = rdsinfo.slowlogGet() instance_data = rdsinfo.instanceGet() try: for recode in slowlog_data['Items']['SQLSlowRecord']: #print(recode) ParseRowCounts = recode['ParseRowCounts'] ReturnRowCounts = recode['ReturnRowCounts'] QueryTimes = recode['QueryTimes'] HostAddress = recode['HostAddress'] LockTimes = recode['LockTimes'] ExecutionStartTime = recode['ExecutionStartTime'] SQLText = recode['SQLText'] DBName = recode['DBName'] content = ''' 业务线 :{url} SQL来源:{HostAddress} 执行时间:{ExecutionStartTime} 数据库名:{DBName} 执行时长:{QueryTimes} 锁定时长:{LockTimes} 解析行数:{ParseRowCounts} 返回行数:{ReturnRowCounts} SQL详情:{SQLText} ''' .format(url=instanceInfo[dbUrl],HostAddress=HostAddress,ExecutionStartTime\ =ExecutionStartTime,DBName=DBName,QueryTimes=QueryTimes,LockTimes=LockTimes,\ ParseRowCounts=ParseRowCounts,ReturnRowCounts=ReturnRowCounts,SQLText=SQLText) #print(content) logging.info(content) for alarm in alarmList: wx = WeChat(alarm) wx.send_data(msg=content) except Exception as e: WARNING('[%s]')%(e) class Daemon: ''' Daemon ''' def createDaemon(self): try: if os.fork() > 0: os._exit(0) # exit father… except OSError as error: print ('fork #1 failed: %d (%s)' % (error.errno, error.strerror)) os._exit(1) # it separates the son from the father os.chdir('/') os.setsid() os.umask(0) # create - fork 2 try: pid = os.fork() if pid > 0: print ('Daemon PID %d' % pid) logging.info('Daemon PID %d' % pid) os._exit(0) except OSError as error: print ('fork #2 failed: %d (%s)' % (error.errno, error.strerror)) os._exit(1) self.run() # function demo def run(self): while True: dataInit() time.sleep(24*60*60) #deamon运行间隔时间 #time.sleep(5) #except Exception,e: # traceback.print_exc() if __name__ == '__main__': daemon = Daemon() daemon.createDaemon()