监控GreenPlum数据量超出阈值使用zabbix发送企业微信报警
代码语言：javascript复制# coding=utf-8
from __future__ import division
import sys
reload(sys)
sys.setdefaultencoding( "utf-8" )
import psycopg2
import datetime
import pymysql
import requests
import time
from prettytable import PrettyTable
# 今日日期
dt = datetime.date.today()
print(dt)
# 昨日日期
ydt = datetime.date.today() datetime.timedelta(-1)
print(ydt)
# 开始时间
begtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print(begtime)
# 发送到zabbix
# zabbix config
tokenUrl = 'your tokenUrl '
sendMsg = 'your sendMsg '
corpid = 'your corpid '
corpsecret = 'your corpsecret '
agentid = '1000010'
def get_token():
values = {'corpid': corpid, 'corpsecret': corpsecret}
req = requests.post(tokenUrl, params=values).json()
return req["access_token"]
def send_msg(msg):
url = sendMsg get_token()
# print url
values = """{"touser" : "your work wechat name" ,
"msgtype":"text",
"agentid":"%s",
"text":{
"content": "%s"
},
"safe":"0"
}""" % (agentid, msg)
requests.post(url, values)
s=0
flag='true'
while(flag):
#连接MySQL
db1 = pymysql.connect(host="ip",port=port, user="user",password="password",database="database",charset="utf8")
cursor = db1.cursor()
#查询状态
sql3="SELECT status FROM tb1 WHERE project='1'"
cursor.execute(sql3)
db1.close
data=cursor.fetchone()
if(data[0]!="SUCCEEDED"):
time.sleep(180)
print("查询中...")
s=s 180
if(s==18000):
sys.exit()
elif(data[0]=="SUCCEEDED"):
print(data[0])
break
# 连接Greenplum
conn = psycopg2.connect(dbname="dbname", user="user", password="password", port="port", host="ip")
cur = conn.cursor()
sql = """
select count(1) from order
"""
cur.execute(sql)
res = cur.fetchall()
# 更新时间
update_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
# 连接MySQL
db = pymysql.connect(host="ip",port=port, user="user",password="password",database="database",charset="utf8")
cursor = db.cursor()
#统计数据插入到监控查询表中
sql = 'insert into monitor(dt,prov,tbl,t_rownum,y_rownum,inc_percent,status,update_time) values (%s,%s,%s,%s,%s,%s,%s,%s)'
#查询昨天的数据量
sql2='select count(1) from order where dt=date_sub(current_date,interval 1 day))'
cursor.execute(sql2)
data=cursor.fetchall()
#各省公司数据较昨日差值
inc_percent0=str(round((int(res[0][2])-int(data[0][1]))/int(res[0][2]),4)*100)
if (inc_percent0>'0.75' or inc_percent0<'0.11'):
status0=0
else:
status0=1
print(inc_percent0)
print(status0)
inc_percent1=str(round((int(res[1][2])-int(data[1][1]))/int(res[1][2]),4)*100)
if (inc_percent1>'1.74' or inc_percent1<'0.18'):
status1=0
else:
status1=1
print(status1)
inc_percent2=str(round((int(res[2][2])-int(data[2][1]))/int(res[2][2]),4)*100)
if (inc_percent2>'1.23' or inc_percent2<'0.20'):
status2=0
else:
status2=1
inc_percent3=str(round((int(res[3][2])-int(data[3][1]))/int(res[3][2]),4)*100)
if (inc_percent3>'0.71' or inc_percent3<'0.10'):
status3=0
else:
status3=1
inc_percent4=str(round((int(res[4][2])-int(data[4][1]))/int(res[4][2]),4)*100)
if (inc_percent4>'0.74' or inc_percent4<'0.13'):
status4=0
else:
status4=1
inc_percent5=str(round((int(res[5][2])-int(data[5][1]))/int(res[5][2]),4)*100)
if (inc_percent5>'0.56' or inc_percent5<'0.06'):
status5=0
else:
status5=1
inc_percent6=str(round((int(res[6][2])-int(data[6][1]))/int(res[6][2]),4)*100)
if (inc_percent6>'0.40' or inc_percent6<'0.12'):
status6=0
else:
status6=1
inc_percent7=str(round((int(res[7][2])-int(data[7][1]))/int(res[7][2]),4)*100)
if (inc_percent7>'0.18' or inc_percent7<'0.03'):
status7=0
else:
status7=1
inc_percent8=str(round((int(res[8][2])-int(data[8][1]))/int(res[8][2]),4)*100)
if (inc_percent8>'0.22' or inc_percent8<'0.09'):
status8=0
else:
status8=1
inc_percent9=str(round((int(res[9][2])-int(data[9][1]))/int(res[9][2]),4)*100)
if (inc_percent9>'0.27' or inc_percent9<'0.11'):
status9=0
else:
status9=1
inc_percent10=str(round((int(res[10][2])-int(data[10][1]))/int(res[10][2]),4)*100)
if (inc_percent10>'0.17' or inc_percent10<'0.05'):
status10=0
else:
status10=1
inc_percent11=str(round((int(res[11][2])-int(data[11][1]))/int(res[11][2]),4)*100)
if (inc_percent11>'0.22' or inc_percent11<'0.06'):
status11=0
else:
status11=1
inc_percent12=str(round((int(res[12][2])-int(data[12][1]))/int(res[12][2]),4)*100)
if (inc_percent12>'0.20' or inc_percent12<'0.06'):
status12=0
else:
status12=1
inc_percent13=str(round((int(res[13][2])-int(data[13][1]))/int(res[13][2]),4)*100)
if (inc_percent13>'0.31' or inc_percent13<'0.08'):
status13=0
else:
status13=1
inc_percent14=str(round((int(res[14][2])-int(data[14][1]))/int(res[14][2]),4)*100)
if (inc_percent14>'0.37' or inc_percent14<'0.13'):
status14=0
else:
status14=1
inc_percent15=str(round((int(res[15][2])-int(data[15][1]))/int(res[15][2]),4)*100)
if (inc_percent15>'0.89' or inc_percent15<'0.24'):
status15=0
else:
status15=1
inc_percent16=str(round((int(res[16][2])-int(data[16][1]))/int(res[16][2]),4)*100)
if (inc_percent16>'0.34' or inc_percent16<'0.15'):
status16=0
else:
status16=1
inc_percent17=str(round((int(res[17][2])-int(data[17][1]))/int(res[17][2]),4)*100)
if (inc_percent17>'0.28' or inc_percent17<'0.13'):
status17=0
else:
status17=1
val = [
(dt, res[0][0], res[0][1], res[0][2],data[0][1],inc_percent0 "%", status0,update_time),
(dt, res[1][0], res[1][1], res[1][2],data[1][1],inc_percent1 "%", status1,update_time),
(dt, res[2][0], res[2][1], res[2][2],data[2][1],inc_percent2 "%", status2,update_time),
(dt, res[3][0], res[3][1], res[3][2],data[3][1],inc_percent3 "%", status3,update_time),
(dt, res[4][0], res[4][1], res[4][2],data[4][1],inc_percent4 "%", status4,update_time),
(dt, res[5][0], res[5][1], res[5][2],data[5][1],inc_percent5 "%", status5,update_time),
(dt, res[6][0], res[6][1], res[6][2],data[6][1],inc_percent6 "%", status6,update_time),
(dt, res[7][0], res[7][1], res[7][2],data[7][1],inc_percent7 "%", status7,update_time),
(dt, res[8][0], res[8][1], res[8][2],data[8][1],inc_percent8 "%", status8,update_time),
(dt, res[9][0], res[9][1], res[9][2],data[9][1],inc_percent9 "%", status9,update_time),
(dt, res[10][0], res[10][1], res[10][2],data[10][1],inc_percent10 "%", status10,update_time),
(dt, res[11][0], res[11][1], res[11][2],data[11][1],inc_percent11 "%", status11,update_time),
(dt, res[12][0], res[12][1], res[12][2],data[12][1],inc_percent12 "%", status12,update_time),
(dt, res[13][0], res[13][1], res[13][2],data[13][1],inc_percent13 "%", status13,update_time),
(dt, res[14][0], res[14][1], res[14][2],data[14][1],inc_percent14 "%", status14,update_time),
(dt, res[15][0], res[15][1], res[15][2],data[15][1],inc_percent15 "%", status15,update_time),
(dt, res[16][0], res[16][1], res[16][2],data[16][1],inc_percent16 "%", status16,update_time),
(dt, res[17][0], res[17][1], res[17][2],data[17][1],inc_percent17 "%", status17,update_time)
]
try:
cursor.executemany(sql, val)
db.commit()
print(cursor.rowcount, "inserted successfully")
except:
# 回滚事务
db.rollback()
#查询输出数据
sql3="select prov,inc_percent from gp_data_monitor where dt=current_date and update_time in (select max(update_time) as update_time from gp_data_monitor where dt=current_date) and status='0' "
cursor.execute(sql3)
results=cursor.fetchall()
db.close()
len1=len(results)
print(len1)
if len1==0:
send_msg("Greenplum monitor done" "nTime : " update_time "n订单量正常")
else:
row = PrettyTable()
row.field_names = ["prov","inc_percent"]
for r in results:
row.add_row(r)
row_fmt=row.get_string(sortby="prov", reversesort=True)
print(row_fmt)
send_msg("Greenplum monitor done" "nTime : " update_time "n以下订单异常，请验证nn" str(row_fmt))整体思路
首先连接GP查询当前的数据量，然后从库中查出昨日数据量，运用公式进行阈值判断，将超出阈值的记录打标签，并筛选出有标记的记录。 使用python的表格模块将记录格式化，最终通过zabbix发送到企业微信通知，将脚本打包上传至服务器，通过定时任务每天自动跑批。 至此python监控小脚本完毕。 如有开发不足之处，欢迎各位大神指正。
我的博客即将同步至腾讯云 社区，邀请大家一同入驻：https://cloud.tencent.com/developer/support-plan?invite_code=16iwj2gzw9fi5
