decopingです。
アイテム値を取得する方法としては、通常の方法だと
ZABBIXのWEB画面経由で「プレーンテキスト」をダウンロードする方法
と
ZABBIX APIを使ったデータ取得方法
がありますが、欲しい情報を取得するのは大変！
という事で、ZABBIXのデータが格納されているDB（Mysql）から
直接、値を取得するSQLクエリを調べてみました。
SQLクエリなので、JasperReportと組み合わせて
稼働レポート、設計書の帳票として出力するのに便利。
では、早速。
【SQLクエリ１】ホスト一覧を取得
SELECT
host
FROM
hosts
WHERE
STATUS <> 3 AND
flags <> 2
ORDER BY
name;
【SQLクエリ２】ホスト毎の監視項目一覧を表示
SELECT
h.host,
h.name,
i.key_,
i.delay,
i.history,
i.trends,
ELT(i.value_type+1,'Double','Str','Log','Uint','Text') type,
i.units ,
ifnull(ap.name,'[Undefined]') application_name,
i.name item_name,
Replace(Replace( i.description , Char(13), ""),Char(10), "") AS description
FROM
items i join hosts h on h.hostid = i.hostid
left join items_applications ia on i.itemid=ia.itemid
left join applications ap on h.hostid=ap.hostid and ia.applicationid=ap.applicationid
WHERE
h.host in (
SELECT
host
FROM
hosts
WHERE
STATUS <> 3 AND
flags <> 2
ORDER BY
name
) AND
i.status = 0 AND
i.flags <> 1 AND
i.flags <> 2
ORDER BY
ap.name,
i.name,
i.key_;
【SQLクエリ３】指定ホストの最新アイテム値を取得
select
h.host AS HOST,
h.name AS HOST_NAME,
i.name AS ITEM_NAME,
i.key_ as ITEM_KEY,
FROM_UNIXTIME
(ELT(i.value_type+1, hy1.clock, hy3.clock, hy2.clock, hy5.clock, hy4.clock)) AS clock ,
Replace(Replace(ELT(i.value_type+1, hy1.value, hy3.value, hy2.value, hy5.value, hy4.value) , Char(13), ""),Char(10), "") AS value
from items i
join hosts h on h.hostid = i.hostid
left join
history hy1 on hy1.itemid = i.itemid and hy1.clock =
(select max(hy12.clock) from history hy12 where hy12.itemid = i.itemid)
left join
history_log hy2 on hy2.itemid = i.itemid and hy2.clock =
(select max(hy22.clock) from history_log hy22 where hy22.itemid = i.itemid)
left join
history_str hy3 on hy3.itemid = i.itemid and hy3.clock =
(select max(hy32.clock) from history_str hy32 where hy32.itemid = i.itemid)
left join
history_text hy4 on hy4.itemid = i.itemid and hy4.clock =
(select max(hy42.clock) from history_text hy42 where hy42.itemid = i.itemid)
left join
history_uint hy5 on hy5.itemid = i.itemid and hy5.clock =
(select max(hy52.clock) from history_uint hy52 where hy52.itemid = i.itemid)
where
h.host in
(SELECT host FROM hosts WHERE STATUS <> 3 AND flags <> 2 order by name) and
i.status = 0 and
i.name like '%' and
i.flags <> 1 and
i.flags <> 2 and
i.key_ like '%';
【SQLクエリ４】カレントアイテム値を全て取得（サンプルとしてsystem.cpu.loadの全表示）（特定項目,全項目も表示できるが、非常に時間がかかります）
select
h.host AS HOST,
h.name AS HOST_NAME,
i.name AS ITEM_NAME,
i.key_ as ITEM_KEY,
FROM_UNIXTIME(ELT(i.value_type+1, hy1.clock, hy3.clock, hy2.clock, hy5.clock, hy4.clock)) AS clock ,
Replace(Replace( ELT(i.value_type+1, hy1.value, hy3.value, hy2.value, hy5.value, hy4.value) , Char(13), ""),Char(10), "") AS value
from items i
join hosts h on h.hostid = i.hostid
left join history hy1 on hy1.itemid = i.itemid
left join history_log hy2 on hy2.itemid = i.itemid
left join history_str hy3 on hy3.itemid = i.itemid
left join history_text hy4 on hy4.itemid = i.itemid
left join history_uint hy5 on hy5.itemid = i.itemid
where
h.host in
(SELECT host FROM hosts WHERE STATUS <> 3 AND flags <> 2 order by name) and
i.status = 0 and
i.name like '%' and
i.key_ like 'system.cpu.load[percpu,avg1]%';
では！
