评论

收藏

[MySQL] Python+Mysql生成zabbix统计数据

数据库 数据库 发布于:2021-07-04 09:07 | 阅读数:230 | 评论:0

  先大概了解一下zabbix数据库结构:
  1、groups表
DSC0000.jpg

  可以根据组名查到组ID

  

  2、找到组ID就可以根据组ID找出这个组下面的所有服务器的ID,这个关系在hosts_groups表里面:
DSC0001.jpg

  

  3、有了hostid就可以在hosts表里查看这台机器的基本信息了:
DSC0002.jpg

  items表则可以根据hostid查出这台服务器的所有监控项:
DSC0003.jpg

  

  4、终于在items表查到itemid,利用这个itemid在trends和trends_uint这两个表中统计出我们需要的数据
DSC0004.jpg

  

  我python水平挺菜的,很多面向对象的功能都不知道咋用,求大神教育
  0804编辑:重新整理了一份新的:http://lihuipeng.blog.51cto.com/3064864/1535454
#!/usr/bin/python
#coding:utf-8
import MySQLdb
import time,datetime

#zabbix数据库信息:
zdbhost = '192.168.1.1'
zdbuser = 'zabbix'
zdbpass = 'zabbixreport'
zdbport = 3306
zdbname = 'zabbix'
#需要查询的key列表
keys = {
    'trends_uint':[
      'net.if.in[eth0]',
      'net.if.out[eth0]',
      'vfs.fs.size[/,used]',
      'vm.memory.size[available]',
    ],
    'trends':[
      'system.cpu.load[percpu,avg5]',
      'system.cpu.util[,idle]',
    ],
  }

class ReportForm:
  def __init__(self):
    '''打开数据库连接'''
    self.conn = MySQLdb.connect(host=zdbhost,user=zdbuser,passwd=zdbpass,port=zdbport,db=zdbname)
    self.cursor = self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    #生成zabbix哪个分组报表
    self.groupname = 'qjsh'
    #获取IP信息:
    self.IpInfoList = self.__getHostList()
  def __getHostList(self):
    '''根据zabbix组名获取该组所有IP'''
    #查询组ID:
    sql = '''select groupid from groups where name = '%s' ''' % self.groupname
    self.cursor.execute(sql)
    groupid = self.cursor.fetchone()['groupid']
    #根据groupid查询该分组下面的所有主机ID(hostid):
    sql = '''select hostid from hosts_groups where groupid = %s''' % groupid
    self.cursor.execute(sql)
    hostlist = self.cursor.fetchall()
    #生成IP信息字典:结构为{'119.146.207.19':{'hostid':10086L,},}
    IpInfoList = {}
    for i in hostlist:
      hostid = i['hostid']
      sql = '''select host from hosts where status = 0 and hostid = %s''' % hostid
      ret = self.cursor.execute(sql)
      if ret:
        IpInfoList[self.cursor.fetchone()['host']] = {'hostid':hostid}
    return IpInfoList
  def __getItemid(self,hostid,itemname):
    '''获取itemid'''
    sql = '''select itemid from items where hostid = %s and key_ = '%s' ''' % (hostid, itemname)
    if self.cursor.execute(sql):
      itemid = self.cursor.fetchone()['itemid']
    else:
      itemid = None
    return itemid
  def getTrendsValue(self,itemid, start_time, stop_time):
    '''查询trends_uint表的值,type的值为min,max,avg三种'''
    resultlist = {}
    for type in ['min','max','avg']:
      sql = '''select %s(value_%s) as result from trends where itemid = %s and clock >= %s and clock <= %s''' % (type, type, itemid, start_time, stop_time)
      self.cursor.execute(sql)
      result = self.cursor.fetchone()['result']
      if result == None:
        result = 0
      resultlist[type] = result
    return resultlist
  def getTrends_uintValue(self,itemid, start_time, stop_time):
    '''查询trends_uint表的值,type的值为min,max,avg三种'''
    resultlist = {}
    for type in ['min','max','avg']:
      sql = '''select %s(value_%s) as result from trends_uint where itemid = %s and clock >= %s and clock <= %s''' % (type, type, itemid, start_time, stop_time)
      self.cursor.execute(sql)
      result = self.cursor.fetchone()['result']
      if result:
        resultlist[type] = int(result)
      else:
        resultlist[type] = 0
    return resultlist

  def getLastMonthData(self,hostid,table,itemname):
    '''根据hostid,itemname获取该监控项的值'''
    #获取上个月的第一天和最后一天
    ts_first = int(time.mktime(datetime.date(datetime.date.today().year,datetime.date.today().month-1,1).timetuple()))
    lst_last = datetime.date(datetime.date.today().year,datetime.date.today().month,1)-datetime.timedelta(1)
    ts_last = int(time.mktime(lst_last.timetuple()))
    itemid = self.__getItemid(hostid, itemname)
    function = getattr(self,'get%sValue' % table.capitalize())
    return  function(itemid, ts_first, ts_last)
  def getInfo(self):
    #循环读取IP列表信息
    for ip,resultdict in  zabbix.IpInfoList.items():
      print "正在查询 IP:%-15s hostid:%5d 的信息!" % (ip, resultdict['hostid'])
      #循环读取keys,逐个key统计数据:
      for table, keylists in keys.items():
        for key in keylists:
          print "\t正在统计 key_:%s" % key
          data =  zabbix.getLastMonthData(resultdict['hostid'],table,key)
          zabbix.IpInfoList[ip][key] = data
  def writeToXls(self):
    '''生成xls文件'''
    try:
      import xlsxwriter
      #创建文件
      workbook = xlsxwriter.Workbook('damo.xls')
      #创建工作薄
      worksheet = workbook.add_worksheet()
      #写入标题(第一行)
      i = 0
      for value in ["主机","CPU平均空闲值","CPU最小空闲值","可用平均内存(单位M)","可用最小内存(单位M)","CPU5分钟负载","进入最大流量(单位Kbps)","进入平均流量(单位Kbps)","出去最大流量(单位Kbps)","出去平均流量(单位Kbps)"]:
        worksheet.write(0,i, value.decode('utf-8'))
        i = i + 1
      #写入内容:
      j = 1
      for ip,value in self.IpInfoList.items():
        worksheet.write(j,0, ip)
        worksheet.write(j,1, '%.2f' % value['system.cpu.util[,idle]']['avg'])
        worksheet.write(j,2, '%.2f' % value['system.cpu.util[,idle]']['min'])
        worksheet.write(j,3, '%dM' % int(value['vm.memory.size[available]']['avg'] / 1024 / 1024))
        worksheet.write(j,4, '%dM' % int(value['vm.memory.size[available]']['min'] / 1024 / 1024))
        worksheet.write(j,5, '%.2f' % value['system.cpu.load[percpu,avg5]']['avg'])
        worksheet.write(j,6, value['net.if.in[eth0]']['max']/1000)
        worksheet.write(j,7, value['net.if.in[eth0]']['avg']/1000)
        worksheet.write(j,8, value['net.if.out[eth0]']['max']/1000)
        worksheet.write(j,9, value['net.if.out[eth0]']['avg']/1000)
        j = j + 1
      workbook.close()
    except Exception,e:
      print e

  def __del__(self):
    '''关闭数据库连接'''
    self.cursor.close()
    self.conn.close()
if __name__ == "__main__":
  zabbix = ReportForm()
  zabbix.getInfo()
  zabbix.writeToXls()
  生成xls文件我用了一个叫xlsxwriter的第三方库,这个库只能写不能读,感觉还可以,生成出来的效果:
DSC0005.jpg

  


  
关注下面的标签,发现更多相似文章