先大概了解一下zabbix数据库结构:
1、groups表
可以根据组名查到组ID
2、找到组ID就可以根据组ID找出这个组下面的所有服务器的ID,这个关系在hosts_groups表里面:
3、有了hostid就可以在hosts表里查看这台机器的基本信息了:
items表则可以根据hostid查出这台服务器的所有监控项:
4、终于在items表查到itemid,利用这个itemid在trends和trends_uint这两个表中统计出我们需要的数据
我python水平挺菜的,很多面向对象的功能都不知道咋用,求大神教育
0804编辑:重新整理了一份新的:http://lihuipeng.blog.51cto.com/3064864/1535454
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
|
#!/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的第三方库,这个库只能写不能读,感觉还可以,生成出来的效果:
本文转自运维笔记博客51CTO博客,原文链接http://blog.51cto.com/lihuipeng/1533315如需转载请自行联系原作者
lihuipeng