DB系統預警連絡人API,db預警api
Author:Skate
Time:2014/12/16
DB系統預警連絡人API
在我們維護系統時,需要把系統的警示資訊即時傳遞給相應同學,如果把連絡方式直接寫到指令碼裡,對以後的維護變更將埋下禍根,尤其是成百上千的系統。
為此這裡寫了個擷取連絡人資訊的API
資料庫配置中心表:
CREATE TABLE `db_alertcontact` (
`id` INT(11) NULL DEFAULT NULL,
`levelid` INT(11) NULL DEFAULT NULL COMMENT 'contact level',
`contact` VARCHAR(50) NULL DEFAULT NULL COMMENT 'email or phone information',
`type` VARCHAR(50) NULL DEFAULT NULL COMMENT 'phone/email',
`username` VARCHAR(100) NULL DEFAULT NULL,
`group` VARCHAR(80) NULL DEFAULT NULL COMMENT 'contact group'
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
CREATE TABLE `db_alertlevel` (
`id` INT(11) NULL DEFAULT NULL,
`levelname` VARCHAR(50) NULL DEFAULT NULL COMMENT 'info/warn/err'
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
用法協助:
[root@skatedb55 pytest]# python contactlist.py --help
usage: Contanct API v0.1 ,(C) Copyright Skate 2014 [-h] --group GROUP --type
TYPE --level LEVEL
[--interval INTERVAL]
[--load LOAD]
optional arguments:
-h, --help show this help message and exit
--group GROUP = The contact group
--type TYPE = The mode of contact
--level LEVEL = alarm level,info/warn/err
--interval INTERVAL = Database query interval(s)
--load LOAD = The configure center database,eg:
load=user/pass@ip:port:dbname
[root@skatedb55 pytest]#
例子:
INSERT INTO `db_alertcontact` (`id`, `levelid`, `contact`, `type`, `username`, `group`) VALUES
(1, 1, 'skate1@163.com', 'email', 'skate1', 'p1'),
(2, 2, 'skate2@163.com', 'email', 'skate2', 'p2'),
(3, 1, '1300000000', 'phone', 'skate3', 'p2'),
(4, 1, '1311111111', 'phone', 'skate4', 'p2'),
(5, 1, '1322222222', 'phone', 'skate5', 'p2'),
(6, 2, 'skate6@163.com', 'email', 'skate6', 'p2');
INSERT INTO `db_alertlevel` (`id`, `levelname`) VALUES
(1, 'info'),
(2, 'warn'),
(3, 'error');
[root@skatedb55 pytest]# python contactlist.py --group=p2 --type=phone --level=info --interval=10--load=root/root@10.20.0.55:3306:test6
1300000000,1311111111,1322222222,
[root@skatedb55 pytest]#
[root@skatedb55 pytest]# python contactlist.py --group=p2 --type=email --level=warn --interval=10--load=root/root@10.20.0.55:3306:test6
skate2@163.com,skate6@163.com,
[root@skatedb55 pytest]#
優點:
1.在變更連絡人或連絡方式不需要修改代碼
2.連絡人的相關資訊儲存在配置中樞資料庫,為了減少對資料庫的查詢,預設每天查一次資料庫(自己可以指定),把聯絡資訊放在本地,既提高了速度,也減少了對配置中心的依賴
3.如果想在變更聯絡資訊及時生效,只需把本地的臨時檔案"/tmp/contact_dbinfo"刪除即可
contactlist.py:
# -*- coding: utf-8 -*-#!/usr/bin/python## Author:Skate# Time:2014/12/10# Function: Contact APIimport MySQLdb,sysimport argparseimport osimport datetimeclass database: def __int__(self,host,user,passwd,port,dbname): self.conn = None pass def conn(self,host,user,passwd,port,dbname): self.host=host self.user=user self.passwd=passwd self.port=port self.dbname=dbname try: self.conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbname,port=self.port) except MySQLdb.Error, e: print "MySQL Connect Error: %s" % (e.args[1]) return self.conn def closeConn(self): self.conn.close() def execute(self,sql,param): if self.conn==None or self.conn.open==False : return -1 sys.exit cur = self.conn.cursor() cur.execute(sql,param) self.closeConn() return curdef contactlist(group,type,level,host,user,passwd,port,dbname,interval=86400): tfile='/tmp/contact_dbinfo' list='' if os.path.isfile(tfile): a1=datetime.datetime.fromtimestamp(os.path.getctime(tfile)) a2=datetime.datetime.now() diffsec = (a2 - a1).seconds if diffsec > interval: os.remove(tfile) f=open(tfile,'a') db=database() db.conn(host,user,passwd,port,dbname) sql="select t.contact,t.username,t.group,t.`type`,l.levelname from db_alertcontact t , db_alertlevel l where t.levelid=l.id and l.levelname=%s and t.group=%s and t.`type`=%s" param=(level,group,type) cur=db.execute(sql,param) results=cur.fetchall() for row in results: if row[3] =='phone': #for r in row: list = list + row[0] + ',' elif row[3] == 'email': #for r in row: list = list + row[0] + ',' if type =='phone': f.write('phonelist='+ group + ':' + list + '\n') f.close() elif type == 'email': f.write('emaillist='+ group + ':' +list + '\n') f.close() else: strsearch = type + 'list='+ group istype = os.popen('cat '+ tfile +' | grep ' + strsearch + ' | wc -l').readline().strip() if int(istype) > 0: line = os.popen('cat '+ tfile +' | grep ' + strsearch).readline().strip() b=line.split('=') a=b[1].split(":") if b[0]=='phonelist': list=a[1] elif b[0]=='emaillist': list=a[1] elif int(istype) < 1: f=open(tfile,'a') db=database() db.conn(host,user,passwd,port,dbname) sql="select t.contact,t.username,t.group,t.`type`,l.levelname from db_alertcontact t , db_alertlevel l where t.levelid=l.id and l.levelname=%s and t.group=%s and t.`type`=%s" param=(level,group,type) cur=db.execute(sql,param) results=cur.fetchall() #list='' for row in results: if row[3] =='phone': list = list + row[0] + ',' elif row[3] == 'email': list = list + row[0] + ',' if type =='phone': f.write('phonelist='+ group + ':' + list + '\n') f.close() elif type == 'email': f.write('emaillist='+ group + ':' + list + '\n') f.close() else: f=open(tfile,'a') db=database() db.conn(host,user,passwd,port,dbname) sql="select t.contact,t.username,t.group,t.`type`,l.levelname from db_alertcontact t , db_alertlevel l where t.levelid=l.id and l.levelname=%s and t.group=%s and t.`type`=%s" param=(level,group,type) cur=db.execute(sql,param) results=cur.fetchall() for row in results: if row[3] =='phone': #for r in row: list = list + row[0] + ',' elif row[3] == 'email': #for r in row: list = list + row[0] + ',' if type =='phone': f.write('phonelist='+ group + ':' + list + '\n') f.close() elif type == 'email': f.write('emaillist='+ group + ':' + list + '\n') f.close() return listif __name__ == "__main__": parser = argparse.ArgumentParser("Contanct API v0.1 ,(C) Copyright Skate 2014") parser.add_argument('--group', action='store', dest='group',required=True, help=" = The contact group") parser.add_argument('--type', action='store', dest='type',required=True, help=" = The mode of contact") parser.add_argument('--level', action='store', dest='level',required=True, help=" = alarm level,info/warn/err") parser.add_argument('--interval', action='store', dest='interval',type=int,default=86400, help=" = Database query interval") parser.add_argument('--load', action='store', dest='load',default='', help=" = The configure center database,eg: \n load=user/pass@ip:port:dbname") results = parser.parse_args() load = results.load group = results.group type = results.type level = results.level interval = results.interval if (load !=''): user_info,url = load.split("@") host,port,db = url.split(":") port=int(port) user,passwd = user_info.split("/",1) str = contactlist(group,type,level,host,user,passwd,port,db,interval) print str
大家有好的意見,歡迎提出
------end-------