Python+mysql Generating Zabbix statistics (optimization)

The weekend was re-organized, some unreasonable places to change, feel better about themselves

zabbixreport.py

#!/usr/bin/python#coding:utf-8import mysqldbimport time,datetime#zabbix Database Information:zdbhost =  ' 192.168.1.100 ' zdbuser =  ' Zabbix ' zdbpass =  ' Zabbixreport ' zdbport = 3306zdbname  =  ' Zabbix ' #生成文件名称:xlsfilename =  ' Damo.xls ' #需要查询的key列表  [name, table name, key value, value, format, data divisible processing]keys  = [    [' CPU cores ', ' trends_uint ', ' system.cpu.num ', ' avg ', ',1],     [' CPU average idle value ', ' trends ', ' system.cpu.util[,idle ', ' avg ', '%.2f ', 1],    [' CPU min idle value ', ' Trends ', ' system.cpu.util[,idle] ', ' min ', '%.2f ', 1],    [' CPU5 minute load ', ' trends ', ' system.cpu.load [Percpu,avg5] ', ' avg ', '%.2f ', 1],    [' Physical memory size (unit g) ', ' trends_uint ', ' vm.memory.size[total ', ' Avg ', ', 1048576000],    [' usable average memory (unit g) ', ' trends_uint ', ' vm.memory.size[available ', ' avg ', ', 1048576000],    [' Available minimum memory (unit g) ', ' trends_uint ', ' vm.memory.size[available ', ' min ', ', 1048576000],    [' Swap total size (unit g) ', ' trends_uint ', ' system.swap.size[,total ', ' avg ', ' ', 1048576000],    [' Swap average remainder (unit g) ', ' trends_uint ', ' system.swap.size[,free ', ' avg ', ' ', 1048576000],    [' root partition total size ( Unit g) ', ' trends_uint ', ' vfs.fs.size[/,total ', ' avg ', ' ', 1073741824],    [' root partition average remainder (unit g) ', ' Trends_uint ', ' vfs.fs.size[/,free] ', ' avg ', ', 1073741824],    [' enter maximum flow (in Kbps) ', ' Trends_uint ', ' net.if.in[eth0] ', ' Max ', ', 1000],    [' Enter the average flow (in Kbps) ', ' trends_uint ', ' net.if.in[eth0 ', ' Avg ', ', 1000],    [' out maximum flow (in Kbps) ', ' trends_uint ', ' Net.if.out[eth0 ', ' Max ', ',1000],     [' out average flow (in Kbps) ', ' trends_uint ', ' net.if.out[eth0 ', ' avg ', ', 1000],]class reportform:     def __init__ (self):         ' Open database connection '         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):          "Get all IP '         # of this group according to Zabbix group name Query group id:        sql =  "' select groupid from  groups where name =  '%s '   '  % self.groupname         self.cursor.execute (SQL)         groupid =  Self.cursor.fetchone () [' GroupID ']&NBSP;&NBSP;&NBSP;&Nbsp;     #根据groupid查询该分组下面的所有主机ID (HostID):         sql =  ' select hostid from hosts_groups where groupid = %s '  % groupid        self.cursor.execute (SQL)          hostlist = self.cursor.fetchall ()           #生成IP信息字典: The structure is {' 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):         "Get 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,type, itemid, start_time, stop_time):          ' Query the value of the Trends_uint table, the value of type Min,max,avg three '         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        return  result    def gettrends_uintvalue (Self,type, itemid, start_time,  stop_time):         "Query the value of the Trends_uint table, the value of type Min,max,avg three"         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:             Result = int (Result)         else:             result = 0        return result     def getlastmonthdata (Self,type,hostid,table,itemname):          "Get the value of the monitoring item according to 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)      &Nbsp;  function = getattr (self, ' get%svalue '  % table.capitalize ())          return  function (type,itemid, ts_first, ts_last)      def getinfo (self):         #循环读取IP列表信息          for ip,resultdict in  zabbix. Ipinfolist.items ():            print  "querying  IP:%-15s hostid:%5d  Information! " %  (ip, resultdict[' HostID ')               #循环读取keys, data by key:             for  value in keys:                 print  "\ t is counting  key_:%s"  % value[2]                 if not value[2] in zabbix. ipinfolist[ip]:                     zabbix. ipinfolist[ip][value[2]] = {}                 data =  zabbix.getlastmonthdata (value[3], resultdict[' HostID '],value[1],value[2])                  zabbix. IPINFOLIST[IP][VALUE[2]][VALUE[3]]&NBSP;=&NBSP;DATA&NBSP;&NBSP;&NBSP;&NBSP;DEF&NBSP;WRITETOXLS2 (self):          ' Generate XLS file '         try:             import xlsxwriter              #创建文件 &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSp;     workbook = xlsxwriter. Workbook (xlsfilename)              #创建工作薄              worksheet = workbook.add_worksheet ()              #写入第一列:             worksheet.write (0,0, "host". Decode (' Utf-8 '))              i = 1             for ip in self. ipinfolist:                 Worksheet.write (I,0,IP)                  i = i + 1              #写入其他列: &NBsp;           i = 1             for value in keys:                 worksheet.write (0,i,value[0].decode (' Utf-8 '))                 # Write the column contents:                j  = 1                for  ip,result in self. Ipinfolist.items ():                     if value[4]:                         worksheet.write (J,I,&NBSP;VALUE[4]&NBSP;%&NBSP;RESULT[VALUE[2]][VALUE[3]])                      else:                          Worksheet.write (j,i, result[value[2]][value[3]] / value[5])                      j = j +  1                i  = i + 1        except exception,e:             print e    def __del __ (self):         "Close database Connection"          self.cursor.close ()        self.conn.close () if __name__ ==  "__main__":     zabbix = reportform ()     zabbix.getinfo ()    &NBSP;&NBSP;ZABBIX.WRITETOXLS2 ()

This article is from the "Operations Notes" blog, make sure to keep this source http://lihuipeng.blog.51cto.com/3064864/1535454