使用python调去zabbix数据库groups,hosts_groups,hosts,items,trends,trends_uint表格中的数据组个而成的字典形式，并将其写入excel表格中。
其实，核心思想就是用了下面这5个sql语句，获取的数据以字典，列表，元组的格式进行组合，最后形成的返回格式:
{u'10.1.12.33':{'vfs.fs.size[/,total]':'188.65','vm.memory.size[total]':'7.69','system.swap.size[,total]':'8.00','net.if.out[eth0]':'69.96','system.cpu.util[,idle]':'99.72','net.if.in[eth0]':'65.26','system.swap.size[,free]':'8.00','vfs.fs.size[/,free]':'177.47','vm.memory.size[used]':'1.30','system.cpu.load[percpu,avg5]':'0.08','vm.memory.size[available]':'6.90'}}
sql="selectgroupidfromgroupswherename='%s'"%groupnamesql="selecthostidfromhosts_groupswheregroupid='%s'"%groupidsql="selecthostfromhostswherestatus=0andflags=0andhostid=%s"%hostidsql="selectitemidfromitemswherehostid='%s'andkey_='%s'"%(hostid,itemname)sql="selectavg(value_%s)/%sresultfrom%swhereitemid=%sandclock<=%dandclock>=%d"%(value,ratio,table,itemid,startime,stoptime)
将关注超过阀值的ip的cpu、内存、磁盘单独放在一个sheet中，以邮件的形式定期发送到邮箱。
目前只是实现了各种功能，但是代码还很是粗糙，有些重复冗余的函数，尤其是生成excel和阀值计算这块，后续会继续改进，同时也存在很多bug，一点点找吧
大体思路是抄袭别人的，但是后续在此基础上进行了修改，站在巨人的肩膀上。
zabbix用的是zabbix3.2，同事愿意用新的东西，没办法，就升级了咯，这段代码只提供大体思路，不能保证能在其他环境下运行，这次写的不太成功，移植性太差，看看就行了。
代码部分
#!/usr/bin/envpython#_*_coding:utf-8_*_#Author:wangpengtai#date:/7/10importsysimporttime,datetimeimportMySQLdb.cursorsimportxlsxwriterimportsmtplibfromemail.mime.textimportMIMETextfromemail.mime.multipartimportMIMEMultipartfromemail.mime.applicationimportMIMEApplicationreload(sys)sys.setdefaultencoding('utf-8')zbx_host='10.1.xxx.xxx'zbx_port=3306zbx_username='zabbix'zbx_password='xxxx'zbx_dbname='zabbix'#需要查询的服务器所在组名groupname='xxx_xxx_Linux_Servers'#文件产生日期date=time.strftime("%Y%m%d",time.localtime())#文件名称fname='Zabbix_Report_weekly_%s.xls'%date#groupname='Templates'keys=[[u'CPU平均空闲值','trends','system.cpu.util[,idle]','avg','%.2f',1,''],[u'CPU最小空闲值','trends','system.cpu.util[,idle]','min','%.2f',1,''],[u'CPU5分钟负载','trends','system.cpu.load[percpu,avg5]','avg','%.2f',1,''],[u'物理内存大小(单位G)','trends_uint','vm.memory.size[total]','avg','',1073741824,'G'],[u'物理内存可用(单位G)','trends_uint','vm.memory.size[available]','avg','',1073741824,'G'],[u'物理内存已使用(单位G)','trends_uint','vm.memory.size[used]','avg','',1073741824,'G'],[u'swap总大小(单位G)','trends_uint','system.swap.size[,total]','avg','',1073741824,'G'],[u'swap平均剩余(单位G)','trends_uint','system.swap.size[,free]','avg','',1073741824,'G'],[u'根分区总大小(单位G)','trends_uint','vfs.fs.size[/,total]','avg','',1073741824,'G'],[u'根分区剩余(单位G)','trends_uint','vfs.fs.size[/,free]','avg','',1073741824,'G'],[u'网卡进口流量(单位Kbps)','trends_uint','net.if.in[eth0]','avg','',1024,'Kbps'],[u'网卡进口流量(单位Kbps)','trends_uint','net.if.in[em1]','avg','',1024,'Kbps'],[u'网卡出口流量(单位Kbps)','trends_uint','net.if.out[eth0]','avg','',1024,'Kbps'],[u'网卡出口流量(单位Kbps)','trends_uint','net.if.out[em1]','avg','',1024,'Kbps'],]Title=[u'CPU5分钟负载',u'CPU平均空闲值',u'CPU最小空闲值',u'swap平均剩余(单位G)',u'swap总大小(单位G)',u'根分区剩余(单位G)',u'根分区总大小(单位G)',u'物理内存可用(单位G)',u'物理内存大小(单位G)',u'物理内存已使用(单位G)',u'网卡出口流量(单位Kbps)',u'网卡进口流量(单位Kbps)',u'硬盘剩余率(单位%)',u'Swap剩余率(单位%)',u'内存使用率(单位%)']Title_dict={u'CPU5分钟负载':1,u'CPU平均空闲值':2,u'CPU最小空闲值':3,u'swap平均剩余(单位G)':4,u'swap总大小(单位G)':5,u'根分区剩余(单位G)':6,u'根分区总大小(单位G)':7,u'物理内存可用(单位G)':8,u'物理内存大小(单位G)':9,u'物理内存已使用(单位G)':10,u'网卡出口流量(单位Kbps)':11,u'网卡进口流量(单位Kbps)':12,u'硬盘剩余率(单位%)':13,u'Swap剩余率(单位%)':14,u'内存使用率(单位%)':15}disk_full=Title[6]disk_free=Title[5]swap_full=Title[4]swap_free=Title[3]mem_full=Title[8]mem_used=Title[9]keys_disk=Title[12]keys_swap=Title[13]keys_mem=Title[14]keys_cpu=Title[0]classReport(object):def__init__(self):self.conn=MySQLdb.connect(host=zbx_host,port=zbx_port,user=zbx_username,passwd=zbx_password,db=zbx_dbname,charset="utf8",cursorclass=MySQLdb.cursors.DictCursor)self.cursor=self.conn.cursor()defgetgroupid(self):sql="selectgroupidfromgroupswherename='%s'"%groupnameself.cursor.execute(sql)groupid=self.cursor.fetchone()['groupid']#print'groupidis%s'%groupidreturngroupiddefgethostid(self):#sql='''selecthostidfromhosts_groupswheregroupid=(selectgroupidfromgroupswherename='%s')'''%groupnamegroupid=self.getgroupid()#printgroupidsql="selecthostidfromhosts_groupswheregroupid='%s'"%groupidself.cursor.execute(sql)hostid_list=self.cursor.fetchall()#print'hostid_list:',hostid_listreturnhostid_list#元组格式:({'hostid':10322L},{'hostid':10323L},{'hostid':10324L})defgethostlist(self):host_list={}hostid_list=self.gethostid()#printhostid_listforiteminhostid_list:#printitemhostid=item['hostid']#printhostidsql="selecthostfromhostswherestatus=0andflags=0andhostid=%s"%hostidself.cursor.execute(sql)host_ip=self.cursor.fetchone()#printhost_ip#printhost_ip['host']#！！！判断如果主机被zabbix改成disable的状态时，hosts表格里面host变为空后，字典空键赋值报错《TypeError:'NoneType'objecthasnoattribute'__getitem__'》ifhost_ipisNone:continuehost_list[host_ip['host']]={'hostid':hostid}#printhost_listreturnhost_list#字典格式:{u'10.1.12.32':{'hostid':10175L},u'10.1.12.33':{'hostid':10176L}}defgetitemid(self):hostid_list=self.gethostlist()#printhostid_list.items()keys_dict={}forhostiteminhostid_list.items():hostip=hostitem[0]hostid=hostitem[1]['hostid']#printhostitemhost_info=[]foriteminkeys:keys_list=[]keys_list.append({'title':item[0]})keys_list.append({'table':item[1]})keys_list.append({'itemname':item[2]})keys_list.append({'value':item[3]})keys_list.append({'form':item[4]})keys_list.append({'ratio':item[5]})keys_list.append({'unit':item[6]})itemname=item[2]sql="selectitemidfromitemswherehostid='%s'andkey_='%s'"%(hostid,itemname)self.cursor.execute(sql)itemid=self.cursor.fetchone()ifitemidisNone:continuekeys_list.append(itemid)#keys_dict['itemid']=itemid['itemid']#printhostip,keys_dicthost_info.append(tuple(keys_list))#printhost_infokeys_dict[hostip]=host_info#printkeys_dictreturnkeys_dictdefgetvalue(self):#时间区间为7天内stoptime=time.time()-7*3600startime=time.time()host_info_list=self.getitemid()#printhost_info_listhost_dict={}foripinhost_info_list:host_info=host_info_list[ip]length=len(host_info)host_item={}foriinrange(length):value=host_info[i][3]['value']ratio=host_info[i][5]['ratio']table=host_info[i][1]['table']itemid=host_info[i][7]['itemid']#itemname=host_info[i][2]['itemname']unit=host_info[i][6]['unit']title=host_info[i][0]['title']sql="selectavg(value_%s)/%sresultfrom%swhereitemid=%sandclock<=%dandclock>=%d"%(value,ratio,table,itemid,startime,stoptime)self.cursor.execute(sql)result=self.cursor.fetchone()['result']ifresultisNone:continueelse:host_item[title]='%.2f%s'%(result,unit)#硬盘剩余率ifdisk_fullinhost_itemanddisk_freeinhost_item:#print"Disk_percent:%.2f%%"%(float(host_item[disk_free].strip("GKbps"))/float(host_item[disk_full].strip("GKbps"))*100)host_item[keys_disk]="%.2f"%(float(host_item[disk_free].strip("GKbps"))/float(host_item[disk_full].strip("GKbps"))*100)#Swap剩余率ifswap_fullinhost_itemandswap_freeinhost_item:#print"Swap_percent:%.2f%%"%(float(host_item[swap_full].strip("GKbps"))/float(host_item[swap_full].strip("GKbps"))*100)host_item[keys_swap]="%.2f"%(float(host_item[swap_full].strip("GKbps"))/float(host_item[swap_full].strip("GKbps"))*100)#内存使用率ifmem_usedinhost_itemandmem_fullinhost_item:#print"Mem_percent:%.2f%%"%(float(host_item[mem_used].strip("GKbps"))/float(host_item[mem_full].strip("GKbps"))*100)host_item[keys_mem]="%.2f"%(float(host_item[mem_used].strip("GKbps"))/float(host_item[mem_full].strip("GKbps"))*100)host_dict[ip]=host_itemreturnhost_dict#返回格式:{u'10.1.12.33':{'vfs.fs.size[/,total]':'188.65','vm.memory.size[total]':'7.69','system.swap.size[,total]':'8.00','net.if.out[eth0]':'69.96','system.cpu.util[,idle]':'99.72','net.if.in[eth0]':'65.26','system.swap.size[,free]':'8.00','vfs.fs.size[/,free]':'177.47','vm.memory.size[used]':'1.30','system.cpu.load[percpu,avg5]':'0.08','vm.memory.size[available]':'6.90'}}defdispalyvalue(self):value=self.getvalue()foripinvalue:print"\n\rip:%s\n\r获取服务器信息:"%(ip)forkeyinvalue[ip]:print"%s:%s"%(key,value[ip][key])def__del__(self):#关闭数据库连接self.cursor.close()self.conn.close()defcreatereport(self):host_info=self.getvalue()#创建一个excel表格workbook=xlsxwriter.Workbook(fname)#设置第一行标题格式format_title=workbook.add_format()format_title.set_border(1)#边框format_title.set_bg_color('#1ac6c0')#背景颜色format_title.set_align('center')#左右居中format_title.set_bold()#字体加粗format_title.set_valign('vcenter')#上下居中format_title.set_font_size(12)#设置返回值的格式format_value=workbook.add_format()format_value.set_border(1)format_value.set_align('center')format_value.set_valign('vcenter')format_value.set_font_size(12)#创建一个名为Report的工作表worksheet1=workbook.add_worksheet("总览表格")#设置列宽worksheet1.set_column('A:D',15)worksheet1.set_column('E:J',23)worksheet1.set_column('K:P',25)#设置行高worksheet1.set_default_row(25)#冻结首行首列worksheet1.freeze_panes(1,1)#将标题写入第一行i=1fortitleinTitle:worksheet1.write(0,i,title.decode('utf-8'),format_title)i+=1#写入第一列、第一行worksheet1.write(0,0,"主机".decode('utf-8'),format_title)#根据每个ip写入相应的数值j=1foripinhost_info:keys_ip=sorted(host_info[ip])#取出每个ip的键值，并把键值进行排序host_value=host_info[ip]#取出没有排序的键值iflen(host_value)!=0:#如果出现｛'10.1.12.1':{}｝这种情况的时候，需要跳过，不将其写入表格中worksheet1.write(j,0,ip,format_value)else:continue#k=1foriteminkeys_ip:#worksheet1.write(j,k,host_value[item],format_value)worksheet1.write(j,Title_dict[item],host_value[item],format_value)#k+=1j+=1##########################性能报表#############################worksheet2=workbook.add_worksheet("性能报表")#设置列宽worksheet2.set_column('A:E',25)#设置行高worksheet2.set_default_row(25)#冻结首行首列worksheet2.freeze_panes(1,1)#写入第一列、第一行worksheet2.write(0,0,"主机".decode('utf-8'),format_title)worksheet2.write(0,1,keys_cpu,format_title)worksheet2.write(0,2,keys_mem,format_title)worksheet2.write(0,3,keys_swap,format_title)worksheet2.write(0,4,keys_disk,format_title)j=1foripinhost_info:keys_ip=sorted(host_info[ip])#取出每个ip的键值，并把键值进行排序host_value=host_info[ip]#取出没有排序的键值#Title[12]=硬盘剩余率(单位%)iflen(host_value)!=0andkeys_diskinhost_valueandkeys_swapinhost_valueandkeys_meminhost_valueandkeys_cpuinhost_value:#如果出现｛'10.1.12.1':{}｝这种情况的时候，需要跳过，不将其写入表格中worksheet2.write(j,0,ip,format_value)worksheet2.write(j,1,host_value[keys_cpu],format_value)worksheet2.write(j,2,host_value[keys_mem],format_value)worksheet2.write(j,3,host_value[keys_swap],format_value)worksheet2.write(j,4,host_value[keys_disk],format_value)#keys_meminhost_value:#orkeys_swapinhost_valueelse:continuej+=1###############制作内存大于90%的服务器表格########################worksheet3=workbook.add_worksheet("内存大于90%")#设置列宽worksheet3.set_column('A:B',25)#设置行高worksheet3.set_default_row(25)#冻结首行首列worksheet3.freeze_panes(1,1)#写入第一列、第一行worksheet3.write(0,0,"主机".decode('utf-8'),format_title)worksheet3.write(0,1,keys_mem,format_title)j=1foripinhost_info:keys_ip=sorted(host_info[ip])#取出每个ip的键值，并把键值进行排序host_value=host_info[ip]#取出没有排序的键值iflen(host_value)!=0andkeys_meminhost_valueandfloat(host_value[keys_mem])>90.0:#如果出现｛'10.1.12.1':{}｝这种情况的时候，需要跳过，不将其写入表格中#printtype(float(host_value[keys_mem]))#iffloat(host_value[keys_mem])<20.0:worksheet3.write(j,0,ip,format_value)worksheet3.write(j,1,host_value[keys_mem],format_value)else:continuej+=1#######################制作硬盘空间小于20%的服务器表格##########################制作硬盘空间小于20%的服务器表格worksheet4=workbook.add_worksheet("磁盘空间低于20%")#设置列宽worksheet4.set_column('A:B',25)#设置行高worksheet4.set_default_row(25)#冻结首行首列worksheet4.freeze_panes(1,1)#写入第一列、第一行worksheet4.write(0,0,"主机".decode('utf-8'),format_title)worksheet4.write(0,1,keys_disk,format_title)j=1foripinhost_info:keys_ip=sorted(host_info[ip])#取出每个ip的键值，并把键值进行排序host_value=host_info[ip]#取出没有排序的键值)iflen(host_value)!=0andkeys_diskinhost_valueandfloat(host_value[keys_disk])<20.0:#如果出现｛'10.1.12.1':{}｝这种情况的时候，需要跳过，不将其写入表格中#printtype(float(host_value[keys_disk]))#iffloat(host_value[keys_disk])<20.0:worksheet4.write(j,0,ip,format_value)worksheet4.write(j,1,host_value[keys_disk],format_value)else:continuej+=1#######################制作CPU负载大于8的服务器表格##########################制作CPU负载大于8的服务器表格worksheet5=workbook.add_worksheet("CPU负载大于8")#设置列宽worksheet5.set_column('A:B',25)#设置行高worksheet5.set_default_row(25)#冻结首行首列worksheet5.freeze_panes(1,1)#写入第一列、第一行worksheet5.write(0,0,"主机".decode('utf-8'),format_title)worksheet5.write(0,1,keys_cpu,format_title)j=1foripinhost_info:keys_ip=sorted(host_info[ip])#取出每个ip的键值，并把键值进行排序host_value=host_info[ip]#取出没有排序的键值iflen(host_value)!=0andkeys_cpuinhost_valueandfloat(host_value[keys_cpu])>8:#如果出现｛'10.1.12.1':{}｝这种情况的时候，需要跳过，不将其写入表格中#printtype(float(host_value[keys_cpu]))#iffloat(host_value[keys_cpu])<20.0:worksheet5.write(j,0,ip,format_value)worksheet5.write(j,1,host_value[keys_cpu],format_value)else:continuej+=1workbook.close()defsendreport(self):#发件服务器地址mail_host=''#发件邮箱地址sender_user='xxxx_monitor@'#mail_pass='xxxx'#登录密码#邮箱授权码，不是登录密码sender_pass='xxxxx'#收件邮箱地址receivers=['wangpengtai@','wangpeng@']#创建带附件实例message=MIMEMultipart()#邮件内容#message=MIMEText('Python邮件测试发送','plain','utf-8')#发送邮箱地址message['From']=sender_user#群发邮件时会报错message['To']不支持列表，使用join函数把地址合成字符串message['To']=",".join(receivers)#邮件主题subject='一周服务器资源使用情况报表'message['Subject']=subject.decode('utf-8')#邮件正文内容message.attach(MIMEText('一周服务器资源使用情况报表','plain','utf-8'))#构造附件1，传送当前目录下excel=MIMEApplication(open(fname,'rb').read(),'utf-8')excel.add_header('Content-Disposition','p_w_upload',filename=fname)message.attach(excel)try:smtpobj=smtplib.SMTP()#smtpobj.set_debuglevel(1)smtpobj.connect(mail_host,25)smtpobj.login(sender_user,sender_pass)smtpobj.sendmail(sender_user,receivers,message.as_string())smtpobj.close()print'邮件发送成功'except:print"邮件发送失败"if__name__=="__main__":zabbix=Report()zabbix.dispalyvalue()zabbix.createreport()zabbix.sendreport()
参考大神：
/article/detail/126748
