周末重新整理了一下，把有些不合理的地方改了下，自我感觉好多了
ZabbixReport.py
|
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
167
168
169
170
171
172
173
174
175
176
|
#!/usr/bin/python
#coding:utf-8
import
MySQLdb
import
time,datetime
#zabbix数据库信息：
zdbhost
=
'192.168.1.100'
zdbuser
=
'zabbix'
zdbpass
=
'zabbixreport'
zdbport
=
3306
zdbname
=
'zabbix'
#生成文件名称：
xlsfilename
=
'damo.xls'
#需要查询的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
],
[
'CPU5分钟负载'
,
'trends'
,
'system.cpu.load[percpu,avg5]'
,
'avg'
,
'%.2f'
,
1
],
[
'物理内存大小(单位G)'
,
'trends_uint'
,
'vm.memory.size[total]'
,
'avg'
,'',
1048576000
],
[
'可用平均内存(单位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
],
[
'进入最大流量（单位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
=
'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
,
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获取该监控项的值'''
#获取上个月的第一天和最后一天
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(
type
,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
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文件'''
try
:
import
xlsxwriter
#创建文件
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
except
Exception,e:
print
e
def
__del__(
self
):
'''关闭数据库连接'''
self
.cursor.close()
self
.conn.close()
if
__name__
=
=
"__main__"
:
zabbix
=
ReportForm()
zabbix.getInfo()
zabbix.writeToXls2()
本文转自运维笔记博客51CTO博客，原文链接http://blog.51cto.com/lihuipeng/1535454如需转载请自行联系原作者 lihuipeng
|
