Python3 获取RDS slowlog+微信接口报警

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": ""

            }

        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()