使用SQL查询Zabbix所监控主机的磁盘空间使用率

本文主要描述使用自定义的SQL查询Zabbix所监控主机的磁盘使用率。虽然zabbix能单独监控每台主机的相关信息，但是缺乏对所有主机磁盘空间信息的直观展示，因此本人提供一个SQL语句完成此功能。

欢迎转载，请注明作者、出处。

作者：张正

blog：http://space.itpub.net/26355921

QQ：176036317

如有疑问，欢迎联系。

Zabbix 2.0
一共有109张表：
mysql> show tables;
+-----------------------+
| Tables_in_zabbix |
+-----------------------+
| acknowledges |
| actions |
| alerts |
| application_template |
| applications |
| auditlog |
| auditlog_details |
| autoreg_host |
| conditions |
| config |
| dbversion |
| dchecks |
| dhosts |
| drules |
| dservices |
| escalations |
| events |
| expressions |
| functions |
| globalmacro |
| globalvars |
| graph_discovery |
| graph_theme |
| graphs |
| graphs_items |
| group_discovery |
| group_prototype |
| groups |
| history |
| history_log |
| history_str |
| history_str_sync |
| history_sync |
| history_text |
| history_uint |
| history_uint_sync |
| host_discovery |
| host_inventory |
| hostmacro |
| hosts |
| hosts_groups |
| hosts_templates |
| housekeeper |
| httpstep |
| httpstepitem |
| httptest |
| httptestitem |
| icon_map |
| icon_mapping |
| ids |
| images |
| interface |
| interface_discovery |
| item_discovery |
| items |
| items_applications |
| maintenances |
| maintenances_groups |
| maintenances_hosts |
| maintenances_windows |
| mappings |
| media |
| media_type |
| node_cksum |
| nodes |
| opcommand |
| opcommand_grp |
| opcommand_hst |
| opconditions |
| operations |
| opgroup |
| opmessage |
| opmessage_grp |
| opmessage_usr |
| optemplate |
| profiles |
| proxy_autoreg_host |
| proxy_dhistory |
| proxy_history |
| regexps |
| rights |
| screens |
| screens_items |
| scripts |
| service_alarms |
| services |
| services_links |
| services_times |
| sessions |
| slides |
| slideshows |
| sysmap_element_url |
| sysmap_url |
| sysmaps |
| sysmaps_elements |
| sysmaps_link_triggers |
| sysmaps_links |
| timeperiods |
| tmp3 |
| trends |
| trends_uint |
| trigger_depends |
| trigger_discovery |
| triggers |
| user_history |
| users |
| users_groups |
| usrgrp |
| valuemaps |
+-----------------------+
109 rows in set (0.00 sec)

select a1.host,
a1.g_name,
round((1 - a1.value / a2.value) * 100, 2) pct_used,
round(a2.value / 1024 / 1024 / 1024, 2) "total_size(G)"
from (select host, value, graphid, g_name
from (select t1.itemid,
t1.i_name,
t1.g_name,
t1.host,
from_unixtime(h.clock),
h.value,
t1.graphid,
t1.type
from (select i.itemid,
i.name i_name,
g.graphid,
g.name g_name,
h.hostid,
h.host,
gi.type
from items i
join graphs_items gi
on i.itemid = gi.itemid
join graphs g
on gi.graphid = g.graphid
join hosts h
on i.hostid = h.hostid
where h.name like '192.168.%'
and g.name like 'Disk space usage %'
and g.name not like '%{#FSNAME}') t1,
history_uint h,
(select itemid, max(clock) clock
from history_uint
group by itemid) t2
where t1.itemid = h.itemid
and t1.itemid = t2.itemid
and h.clock = t2.clock) b1
where type = 0) a1
join (select host, value, graphid, g_name
from (select t1.itemid,
t1.i_name,
t1.g_name,
t1.host,
from_unixtime(h.clock),
h.value,
t1.graphid,
t1.type
from (select i.itemid,
i.name i_name,
g.graphid,
g.name g_name,
h.hostid,
h.host,
gi.type
from items i
join graphs_items gi
on i.itemid = gi.itemid
join graphs g
on gi.graphid = g.graphid
join hosts h
on i.hostid = h.hostid
where h.name like '192.168.%'
and g.name like 'Disk space usage %'
and g.name not like '%{#FSNAME}') t1,
history_uint h,
(select itemid, max(clock) clock
from history_uint
group by itemid) t2
where t1.itemid = h.itemid
and t1.itemid = t2.itemid
and h.clock = t2.clock) b2
where type = 2) a2
on a1.host = a2.host
and a1.graphid = a2.graphid
order by 3;

+----------------+----------------------------------+----------+---------------+
| host | g_name | pct_used | total_size(G) |
+----------------+----------------------------------+----------+---------------+
| 192.168.44.151 | Disk space usage /oacle | 5.60 | 503.97 |
| 192.168.44.132 | Disk space usage / | 7.31 | 200.81 |
| 192.168.44.135 | Disk space usage / | 7.58 | 200.81 |
| 192.168.44.153 | Disk space usage /oot | 8.08 | 0.19 |
| 192.168.44.53 | Disk space usage /oracle | 8.58 | 362.70 |
| 192.168.44.250 | Disk space usage / | 8.99 | 192.25 |
| 192.168.44.249 | Disk space usage / | 10.01 | 192.25 |
| 192.168.241.6 | Disk space usage /oracle/u1 | 10.27 | 916.89 |
| 192.168.44.130 | Disk space usage / | 10.33 | 200.81 |
| 192.168.44.134 | Disk space usage /oracle | 10.84 | 3347.70 |
| 192.168.241.3 | Disk space usage /boot | 11.26 | 0.19 |
| 192.168.241.2 | Disk space usage /boot | 11.26 | 0.19 |
| 192.168.241.10 | Disk space usage /boot | 11.26 | 0.19 |
| 192.168.44.55 | Disk space usage /boot | 11.30 | 0.19 |
| 192.168.44.151 | Disk space usage /boot | 11.30 | 0.19 |
| 192.168.241.6 | Disk space usage /boot | 11.30 | 0.19 |
| 192.168.44.154 | Disk space usage /boot | 11.30 | 0.19 |
| 192.168.44.53 | Disk space usage /boot | 11.36 | 0.19 |
| 192.168.44.135 | Disk space usage /oracle | 11.38 | 3347.70 |
| 192.168.44.52 | Disk space usage /boot | 11.39 | 0.19 |
| 192.168.44.134 | Disk space usage / | 11.39 | 200.81 |
| 192.168.44.54 | Disk space usage /boot | 11.41 | 0.19 |
| 192.168.44.134 | Disk space usage /boot | 11.45 | 0.19 |
| 192.168.44.132 | Disk space usage /boot | 11.45 | 0.19 |
| 192.168.44.130 | Disk space usage /boot | 11.45 | 0.19 |
| 192.168.44.131 | Disk space usage /boot | 11.45 | 0.19 |
| 192.168.44.133 | Disk space usage /boot | 11.45 | 0.19 |
| 192.168.44.135 | Disk space usage /boot | 11.45 | 0.19 |
| 192.168.44.56 | Disk space usage /boot | 11.85 | 0.19 |
| 192.168.44.102 | Disk space usage /boot | 11.98 | 0.19 |
| 192.168.44.57 | Disk space usage /boot | 12.12 | 0.19 |
| 192.168.44.131 | Disk space usage / | 13.47 | 200.81 |
| 192.168.44.53 | Disk space usage / | 14.86 | 28.38 |
| 192.168.44.132 | Disk space usage /oracle | 17.42 | 3347.70 |
| 192.168.241.10 | Disk space usage / | 17.73 | 28.38 |
| 192.168.44.131 | Disk space usage /oracle | 18.01 | 3347.70 |
| 192.168.44.151 | Disk space usage / | 18.07 | 28.38 |
| 192.168.241.3 | Disk space usage / | 19.98 | 28.38 |
| 192.168.44.250 | Disk space usage /boot | 20.93 | 0.19 |
| 192.168.44.249 | Disk space usage /boot | 20.94 | 0.19 |
| 192.168.44.154 | Disk space usage /oracle | 23.45 | 491.22 |
| 192.168.44.154 | Disk space usage / | 23.51 | 28.38 |
| 192.168.44.55 | Disk space usage / | 24.07 | 28.38 |
| 192.168.44.52 | Disk space usage / | 24.62 | 28.38 |
| 192.168.241.6 | Disk space usage / | 26.20 | 28.38 |
| 192.168.44.249 | Disk space usage /oracle | 26.49 | 1761.90 |
| 192.168.44.153 | Disk space usage / | 28.37 | 28.38 |
| 192.168.44.57 | Disk space usage /oracle | 28.96 | 232.28 |
| 192.168.44.54 | Disk space usage / | 29.40 | 28.38 |
| 192.168.44.56 | Disk space usage / | 30.41 | 28.83 |
| 192.168.44.250 | Disk space usage /oracle | 32.92 | 1761.90 |
| 192.168.241.2 | Disk space usage / | 32.99 | 28.38 |
| 192.168.44.133 | Disk space usage / | 39.06 | 200.81 |
| 192.168.44.54 | Disk space usage /oracle | 43.43 | 486.09 |
| 192.168.241.3 | Disk space usage /oracle | 54.91 | 250.75 |
| 192.168.44.102 | Disk space usage / | 56.31 | 108.40 |
| 192.168.44.130 | Disk space usage /oracle | 56.55 | 3347.70 |
| 192.168.44.52 | Disk space usage /oracle | 57.65 | 626.13 |
| 192.168.241.6 | Disk space usage /oracle | 61.26 | 503.97 |
| 192.168.44.153 | Disk space usage /oracle | 62.64 | 1021.01 |
| 192.168.44.151 | Disk space usage /oracle | 66.70 | 916.89 |
| 192.168.44.55 | Disk space usage /oracle | 72.50 | 491.22 |
| 192.168.44.56 | Disk space usage /oracle | 76.33 | 368.56 |
| 192.168.44.133 | Disk space usage /oracle | 82.00 | 3347.70 |
| 192.168.44.102 | Disk space usage /oracle_oradata | 83.86 | 98.43 |
| 192.168.241.2 | Disk space usage /oracle | 91.82 | 250.75 |
| 192.168.44.57 | Disk space usage / | 94.83 | 28.83 |
| 192.168.241.10 | Disk space usage /oracle | 96.30 | 250.75 |
+----------------+----------------------------------+----------+---------------+
68 rows in set (0.77 sec)

本文转自ITPUB博客84223932的博客，原文链接：使用SQL查询Zabbix所监控主机的磁盘空间使用率，如需转载请自行联系原博主。