python导出zabbix数据并发邮件脚本

Zabbix没有报表导出的功能，于是通过编写脚本导出zabbix数据并发邮件。效果如下：

下面是脚本，可根据自己的具体情况修改：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

#!/usr/bin/python
#coding:utf-8
  
import MySQLdb
import time,datetime
import xlsxwriter
 
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
 
  
  
#zabbix数据库信息：
zdbhost = '127.0.0.1'
zdbuser = 'zabbix'
zdbpass = 'zabbix'
zdbport = 3306
zdbname = 'zabbix'
  
#生成文件名称：
xlsfilename = 'Group_Production_Server.xlsx'
  
#需要查询的key列表 [名称，表名，key值，取值，格式化，数据整除处理]
keys = [
#    ['CPU核心数','trends_uint','system.cpu.num','avg','',1],
    #['CPU平均空闲值','trends','system.cpu.util[,idle]','avg','%.2f',1],
    #['CPU最小空闲值','trends','system.cpu.util[,idle]','min','%.2f',1],
    ['CPU使用率(%)','trends','CPU_used','avg','%.2f',1],
    #['内存大小(单位G)','trends_uint','vm.memory.size[total]','avg','',1048576000],
    #['剩余内存(单位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],
    ['内存使用率(%)','trends','Memory_used','avg','%.2f',1],
#    ['可用平均内存(单位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],
#    ['可用最小内存(单位G)','trends_uint','vm.memory.size[available]','min','',1048576000],
#    ['swap总大小(单位G)','trends_uint','system.swap.size[,total]','avg','',1048576000],
#    ['swap平均剩余(单位G)','trends_uint','system.swap.size[,free]','avg','',1048576000],
#    ['根分区总大小(单位G)','trends_uint','vfs.fs.size[/,total]','avg','',1073741824],
#    ['根分区平均剩余(单位G)','trends_uint','vfs.fs.size[/,free]','avg','',1073741824],
    #['磁盘总大小(单位G)','trends_uint','vfs.fs.size[/fs01,total]','avg','',1073741824],
    #['磁盘剩余(单位G)','trends_uint','vfs.fs.size[/fs01,free]','avg','',1073741824],
    ['磁盘使用率(%)','trends','fs01_used','avg','%.2f',1],
#    ['进入最大流量（单位Kbps）','trends_uint','net.if.in[eth0]','max','',1000],
#    ['进入平均流量（单位Kbps）','trends_uint','net.if.in[eth0]','avg','',1000],
#    ['出去最大流量（单位Kbps）','trends_uint','net.if.out[eth0]','max','',1000],
#    ['出去平均流量（单位Kbps）','trends_uint','net.if.out[eth0]','avg','',1000],
]
  
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 = 'Group_Production_Server'
  
        #获取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,type, itemid, start_time, stop_time):
        '''查询trends_uint表的值,type的值为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
        return result
  
    def getTrends_uintValue(self,type, itemid, start_time, stop_time):
        '''查询trends_uint表的值,type的值为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:
            result = int(result)
        else:
            result = 0
        return result
  
  
    def getLastMonthData(self,type,hostid,table,itemname):
        '''根据hostid,itemname获取该监控项的值'''
        #获取上个月的第20天和最后1天
        ts_first = int(time.mktime(datetime.date(datetime.date.today().year,datetime.date.today().month-1,20).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(type,itemid, ts_first, ts_last)
    def getNowData(self):
        nowtime = datetime.datetime.now().strftime('%Y-%m-%d')
        return nowtime
  
    def getInfo(self):
        #循环读取IP列表信息
        for ip,resultdict in  zabbix.IpInfoList.items():
            print "正在查询 IP:%-15s hostid:%5d 的信息！" % (ip, resultdict['hostid'])
            #循环读取keys，逐个key统计数据：
            for value in keys:
                print "\t正在统计 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]] = data
    def writeToXls2(self):
        '''生成xls文件'''
            #创建文件
        workbook = xlsxwriter.Workbook(xlsfilename)
  
            #创建工作薄
        worksheet = workbook.add_worksheet()
  
            #写入第一列：
        worksheet.write(0,0,"主机".decode('utf-8'))
        i = 1
        for ip in self.IpInfoList:
            worksheet.write(i,0,ip)
            i = i + 1
  
            #写入其他列：
        i = 1
        for value in keys:
            worksheet.write(0,i,value[0].decode('utf-8'))
  
                #写入该列内容：
            j = 1
            for ip,result in self.IpInfoList.items():
                if value[4]:
                    worksheet.write(j,i, value[4] % result[value[2]][value[3]])
                else:
                    worksheet.write(j,i, result[value[2]][value[3]] / value[5])
                j = j + 1
  
            i = i + 1
        workbook.close()
  
  
  
    def __del__(self):
        '''关闭数据库连接'''
        self.cursor.close()
        self.conn.close()
    def Send_Email(self):
        sender = 'from@runoob.com'
    receivers = ['hejianlai@pci.cn']  # 接收邮件，可设置为你的QQ邮箱或者其他邮箱
      
    #创建一个带附件的实例
    message = MIMEMultipart()
    message['From'] = Header("Zabbix_server", 'utf-8')
    message['To'] =  Header("it", 'utf-8')
    subject = '生产环境虚机资源使用情况'
    message['Subject'] = Header(subject, 'utf-8')
      
    #邮件正文内容
    message.attach(MIMEText('生产环境虚机资源使用情况', 'plain', 'utf-8'))
      
    # 构造附件1，传送当前目录下的 test.txt 文件
    att1 = MIMEText(open('Group_Production_Server.xlsx', 'rb').read(), 'base64', 'utf-8')
    att1["Content-Type"] = 'application/octet-stream'
    # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
    att1["Content-Disposition"] = 'attachment; filename="Group_Production_Server.xlsx"'
    message.attach(att1)
      
    try:
        smtpObj = smtplib.SMTP('localhost')
        smtpObj.sendmail(sender, receivers, message.as_string())
        print "邮件发送成功"
    except smtplib.SMTPException:
        print "Error: 无法发送邮件"
  
if __name__ == "__main__":
    zabbix = ReportForm()
    zabbix.getInfo()
    zabbix.writeToXls2()
    zabbix.Send_Email()