该文章详细介绍了如何使用Python脚本从Zabbix数据库中导出数据并生成xlsx报表，包括在Windows和Linux环境下安装必要的工具和库，以及具体的Python代码实现，涉及数据库连接、查询和数据格式转换等步骤。
zabbix数据导出为xlsx报表文档记录
如在windows运行程序 则安装 pycharm
如在linux运行程序 则安装python3 pip3
windows 安装 pycharm 如果在终端输入命令不生效，则需要重启电脑，使其生效且windows需要添加python环境
变量，如下：
F:\共享文件夹\Pycharm\ziliao\venv\Scripts 内容如下
PyCharm 设置
接着如下配置
输入 python 或 pip 有以下输出即可
Centos 、Rocky9 安装python3 与 php3
安装依赖包
yum install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel
yum install readline-devel tk-devel gcc make
运行这个命令添加epel扩展源
yum -y install epel-release
yum -y install libffi-devel
安装pip
yum install python-pip
用wget下载python3的源码包
wget http://npm.taobao.org/mirrors/python/3.7.5/Python-3.7.5.tar.xz
编译python3源码包
解压
xz -d Python-3.7.5.tar.xz
tar -xf Python-3.7.5.tar
进入解压后的目录，依次执行下面命令进行手动编译
cd Python-3.7.5
./configure prefix=/usr/local/python3
make && make install
如果出现can't decompress data; zlib not available这个错误，则需要安装相关库
安装依赖zlib、zlib-devel
yum install zlib zlib
yum install zlib zlib-devel
添加软链接
ln -s /usr/local/python3/bin/python3.7 /usr/bin/python3
测试是否安装成功了
python -V
python代码如下：
注意：需要修改的地方如下：
①. zabbix数据库信息
②. 需要查询的key列表,有的默认即可，主要对应的key值要和zabbix上键值对应
#!/usr/bin/python
import pymysql
import time, datetime
import xlsxwriter
# import math
####
#import smtplib
#from email.mime.text import MIMEText
#from email.mime.multipart import MIMEMultipart
#from email.header import Header
#zabbix数据库信息：
zdbhost = "192.168.68.101"
zdbuser = 'root'
zdbpass = '2023'
zdbport = 3306
zdbname = 'zabbix'
#生成文件名称：
time_cur = time.strftime("%Y-%m-%d_%H-%M-%S", time.localtime())
xlsfilename = "NBJF_Group_Production_Server." + time_cur + ".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', 'system.cpu.util', 'avg', '%.2f', 1],
['内存大小(单位G)','trends_uint','vm.memory.size[total]','avg','', 1073741824],
['剩余内存(单位G)','trends_uint','vm.memory.size[available]','avg','',1048576000],
['内存使用率(%)', 'trends', 'vm.memory.utilization', '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],
['/data 磁盘总大小(单位G)','trends_uint','vfs.fs.size[/data,total]','avg','',1073741824],
# ['/data 磁盘剩余(单位G)','trends_uint','vfs.fs.size[/data,free]','avg','',1073741824],
# ['/data 磁盘使用率(%)', 'trends', 'vfs.fs.size[/data,user]', 'avg', '%.2f', 1],
['/data 磁盘已使用(单位G)','trends_uint','vfs.fs.size[/data,used]','avg','',1073741824],
# ['进入最大流量（单位Kbps）','trends_uint','net.if.in[eno4]','max','',1000],
# ['进入平均流量（单位Kbps）','trends_uint','net.if.in[eno4]','avg','',1000],
# ['出去最大流量（单位Kbps）','trends_uint','net.if.out[eno4]','max','',1000],
# ['出去平均流量（单位Kbps）','trends_uint','net.if.out[eno4]','avg','',1000],
]
class ReportForm:
def __init__(self):
'''打开数据库连接'''
self.conn = pymysql.connect(host=zdbhost, user=zdbuser, passwd=zdbpass, port=zdbport, db=zdbname)
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
# 生成zabbix哪个分组报表
self.groupname = 'Linux servers'
# 获取IP信息：
self.IpInfoList = self.__getHostList()
def __getHostList(self):
'''根据zabbix组名获取该组所有IP'''
# 查询组ID:
sql = '''select groupid from hstgrp 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获取该监控项的值'''
# 获取这月15号~22号的数据。0代表当月、1代表上月，以此类推。 15是15号，22是22号，要获取哪天 就写哪天
ts_first = int(
time.mktime(datetime.date(datetime.date.today().year, datetime.date.today().month - 0, 15).timetuple()))
lst_last = int(
time.mktime(datetime.date(datetime.date.today().year, datetime.date.today().month - 0, 22).timetuple()))
itemid = self.__getItemid(hostid, itemname)
function = getattr(self, 'get%sValue' % table.capitalize())
return function(type, itemid, ts_first, lst_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, "主机")
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])
# 写入该列内容：
j = 1
for ip, result in self.IpInfoList.items():
if value[4]:
worksheet.write(j, i, value[4] % result[value[2]][value[3]])
else:
# print( result[value[2]][value[3]])
# print(round(result[value[2]][value[3]] / value[5],2))
worksheet.write(j, i, round(result[value[2]][value[3]] / value[5],2))
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 = ['18931306541@163.com'] # 接收邮件，可设置为你的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()
#print(1073741824 // ( 2 ** 30 ))
# zabbix.Send_Email() ```
结果如下
注： 在pycharm 或 centos、Rocky9 上安装
需要安装模块如下：
pycharm安装方式:
python -m pip install pymysql
python -m pip install xlsxwriter
centos、Rocky9 上安装模块：
pip install pymysql
pip install xlsxwriter
扫一扫
1271
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
