Zabbix 2.2.1で各アイテムの値をデータとして取得するときは、
「監視データ」－＞「最新データ」－＞「グラフ」or「ヒストリ」から「値」を選択すると
「プレーンテキスト」ボタンでTSV形式で取得できるようですが、なぜか1000個ぐらいまでしか
取得できませんでした。（1分ごとの取得するアイテムだと16.6時間分しか取得できない）
しょうがないので、mysqlのDBにから直接情報ｗ取得出来ないかを調べてみました。
本来は、Zabbix API使うのが正当なんだろうけど、現状うまく使いこなせません。
まずはmysqlの使い方を確認
mysqlクライアントで実行（表形式で出力されます）
|
1 2 |
# mysql zabbix > (ここにSQLを入力。最後に;) |
直接SQL分を指定して実行するには、以下のように指定します。
|
1 2 3 4 5 |
直接SQL分を記述 # mysql zabbix -e "(SQL文)" SQLを記述したファイルを指定して実行 # mysql zabbix -e "`cat outcsv.sql`" |
ファイルにｔｓｖ出力するには、単純に標準出力をリダイレクトすればよいようです。
|
1 2 3 4 |
# mysql zabbix -e "(SQL文)" > /tmp/test2.tsv 例 # mysql zabbix -e "select h.host, h.name, i.name, i.description, i.key_ from items i join hosts h on h.hostid = i.hostid where h.name = 'N54L' order by i.key_ " > /tmp/test2.tsv |
ファイルにcsv出力するには、以下のようにtabを’,’に変換して出力します。
|
1 |
# mysql zabbix -e "(SQL文)" | sed -e 's/\t/,/g' > test.csv |
実際に、Zabbix 2.2.1のDBから情報を取得してみます。
- ホスト一覧取得
- ホストごとの監視項目一覧取得
- 指定ホストのアイテム最新値一覧取得
- アイテム値全取得
ホスト一覧を取得
|
1 2 3 4 5 6 7 8 9 |
SELECT host, name, status, flags FROM hosts WHERE STATUS <> 3 AND flags <> 2 order by name; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
+--------------------------------------+--------------------------+--------+-------+ | host | name | status | flags | +--------------------------------------+--------------------------+--------+-------+ | 192.168.29.18 | 192.168.29.18 | 1 | 0 | | 192.168.29.19 | 192.168.29.19 | 1 | 0 | | 192.168.29.81 | N54L | 0 | 0 | | 192.168.29.200 | NS001 | 0 | 0 | | 420c8e7f-4f1a-d635-6278-334d351ff20b | NS001_onESXi | 0 | 4 | | 192.168.29.201 | NS002 | 0 | 0 | | 564d632b-457a-9773-3019-ba1a68e2c7b5 | NS002_onESXi | 0 | 4 | | 192.168.29.241 | VMSV01 | 0 | 0 | | 4c49f06c-6c06-2003-0001-005800232010 | VMSV01.nsb.homeip.net | 0 | 4 | | 192.168.29.242 | VMSV02 | 0 | 0 | | 1c6f65e1-b358-1128-0023-004800282010 | VMSV02.nsb.homeip.net | 0 | 4 | | 192.168.29.150 | Walbrix | 0 | 0 | | 564da1e0-77d9-b8e2-c6eb-0ecf979b0db5 | Walbrix_onESXi | 0 | 4 | | 192.168.29.151 | wordpress | 0 | 0 | | Zabbix server | Zabbix server | 0 | 0 | | 192.168.29.153 | zabbix2 | 0 | 0 | +--------------------------------------+--------------------------+--------+-------+ 68 rows in set (0.00 sec) |
|
1 2 3 4 5 6 |
たぶん以下の意味 status=0 : 有効なホスト status=1 : 無効化されたホスト status=3 : テンプレート flags=2 : ディスカバリ用定義 flags=4 : ディスカバリで自動登録されたホスト |
ホストごとの監視項目一覧を表示
|
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 |
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 = '192.168.29.81' and i.status = 0 and i.flags <> 1 and i.flags <> 2 order by ap.name, i.name,i.key_; |
|
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 |
+---------------+------+--------------------------------------------------------------------------------+-------+---------+--------+--------+--------+--------------------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | host | name | key_ | delay | history | trends | type | units | application_name | item_name | description | +---------------+------+--------------------------------------------------------------------------------+-------+---------+--------+--------+--------+--------------------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 192.168.29.81 | N54L | system.cpu.load[percpu,avg1] | 60 | 7 | 365 | Double | | CPU | Processor load (1 min average) | | | 192.168.29.81 | N54L | system.cpu.load[percpu,avg15] | 60 | 7 | 365 | Double | | CPU | Processor load (15 min average) | | | 192.168.29.81 | N54L | system.cpu.load[percpu,avg5] | 60 | 7 | 365 | Double | | CPU | Processor load (5 min average) | | | 192.168.29.81 | N54L | vm.memory.size[total] | 3600 | 7 | 365 | Uint | B | Filesystems | Total memory | | | 192.168.29.81 | N54L | sysContact | 3600 | 7 | 365 | Str | | General | Device contact details | The textual identification of the contact person for this managed node, together with information on how to contact this person. If no contact information is known, the value is the zero-length string. | | 192.168.29.81 | N54L | sysDescr | 3600 | 7 | 365 | Str | | General | Device description | A textual description of the entity. This value should include the full name and version identification of the system's hardware type, software operating-system, and networking software. | | 192.168.29.81 | N54L | sysLocation | 3600 | 7 | 365 | Str | | General | Device location | The physical location of this node (e.g., `telephone closet, 3rd floor'). If the location is unknown, the value is the zero-length string. | | 192.168.29.81 | N54L | sysName | 3600 | 7 | 365 | Str | | General | Device name | An administratively-assigned name for this managed node. By convention, this is the node's fully-qualified domain name. If the name is unknown, the value is the zero-length string. | | 192.168.29.81 | N54L | sysUpTime | 60 | 7 | 365 | Uint | uptime | General | Device uptime | The time since the network management portion of the system was last re-initialized. | | 192.168.29.81 | N54L | system.uname | 3600 | 7 | 365 | Str | | General | System information | | | 192.168.29.81 | N54L | system.uptime | 60 | 7 | 365 | Uint | uptime | General | System uptime | | | 192.168.29.81 | N54L | icmppingloss | 120 | 7 | 365 | Double | % | ICMP | ICMP loss | | | 192.168.29.81 | N54L | icmpping | 120 | 7 | 365 | Uint | | ICMP | ICMP ping | | | 192.168.29.81 | N54L | icmppingsec | 120 | 7 | 365 | Double | s | ICMP | ICMP response time | | | 192.168.29.81 | N54L | vm.memory.size[free] | 60 | 7 | 365 | Uint | B | Memory | Free memory | | | 192.168.29.81 | N54L | system.swap.size[,free] | 60 | 7 | 365 | Uint | B | Memory | Free swap space | | | 192.168.29.81 | N54L | system.swap.size[,total] | 3600 | 7 | 365 | Uint | B | Memory | Total swap space | | | 192.168.29.81 | N54L | proc.num[] | 60 | 7 | 365 | Uint | | Processes | Number of processes | | | 192.168.29.81 | N54L | hrProcessorLoad[3] | 60 | 90 | 365 | Uint | % | Processors | Utilization of processor #$1 | The average, over the last minute, of the percentage of time that this processor was not idle. Implementations may approximate this one minute smoothing period if necessary. | | 192.168.29.81 | N54L | hrProcessorLoad[4] | 60 | 90 | 365 | Uint | % | Processors | Utilization of processor #$1 | The average, over the last minute, of the percentage of time that this processor was not idle. Implementations may approximate this one minute smoothing period if necessary. | | 192.168.29.81 | N54L | agent.ping | 60 | 7 | 365 | Uint | | Zabbix agent | Agent ping | The agent always returns 1 for this item. It could be used in combination with nodata() for availability check. | | 192.168.29.81 | N54L | agent.hostname | 3600 | 7 | 365 | Str | | Zabbix agent | Host name of zabbix_agentd running | | | 192.168.29.81 | N54L | agent.version | 3600 | 7 | 365 | Str | | Zabbix agent | Version of zabbix_agent(d) running | | +---------------+------+--------------------------------------------------------------------------------+-------+---------+--------+--------+--------+--------------------+------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 293 rows in set (10 min 9.60 sec) |
|
1 2 3 4 5 |
たぶん以下の意味 status=0 : 有効なアイテム status=1 : 無効化されたアイテム flags=1 : ディスカバリ用インデックス定義 flags=2 : ディスカバリ用項目定義 |
指定ホストのアイテム最新値一覧
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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 = '192.168.29.81' and i.status = 0 and i.name like '%' and i.flags <> 1 and i.flags <> 2 and i.key_ like '%'; |
|
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 |
+---------------+-----------+------------------------------------+--------------------------------------------------------------------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | HOST | HOST_NAME | ITEM_NAME | ITEM_KEY | clock | value | +---------------+-----------+------------------------------------+--------------------------------------------------------------------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 192.168.29.81 | N54L | Host name of zabbix_agentd running | agent.hostname | 2014-02-03 23:58:40 | 192.168.29.81 | | 192.168.29.81 | N54L | Agent ping | agent.ping | 2014-02-04 00:38:41 | 1 | | 192.168.29.81 | N54L | Version of zabbix_agent(d) running | agent.version | 2014-02-03 23:58:42 | 2.2.1 | | 192.168.29.81 | N54L | Utilization of processor #$1 | hrProcessorLoad[3] | 2014-02-04 00:38:54 | 35 | | 192.168.29.81 | N54L | Utilization of processor #$1 | hrProcessorLoad[4] | 2014-02-04 00:38:55 | 45 | | 192.168.29.81 | N54L | ICMP ping | icmpping | 2014-02-04 00:38:22 | 1 | | 192.168.29.81 | N54L | ICMP loss | icmppingloss | 2014-02-04 00:38:24 | 0.0000 | | 192.168.29.81 | N54L | ICMP response time | icmppingsec | 2014-02-04 00:38:24 | 0.0011 | | 192.168.29.81 | N54L | Number of processes | proc.num[] | 2014-02-04 00:38:48 | 53 | | 192.168.29.81 | N54L | Device contact details | sysContact | 2014-02-04 00:34:22 | | | 192.168.29.81 | N54L | Device description | sysDescr | 2014-02-04 00:34:23 | Hardware: AMD64 Family 16 Model 6 Stepping 3 AT/AT COMPATIBLE - Software: Windows Version 6.1 (Build 7601 Multiprocessor Free) | | 192.168.29.81 | N54L | Device location | sysLocation | 2014-02-04 00:34:24 | | | 192.168.29.81 | N54L | Device name | sysName | 2014-02-04 00:34:25 | N54L | | 192.168.29.81 | N54L | Processor load (15 min average) | system.cpu.load[percpu,avg15] | 2014-02-04 00:38:49 | 1.1439 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:38:50 | 0.2083 | | 192.168.29.81 | N54L | Processor load (5 min average) | system.cpu.load[percpu,avg5] | 2014-02-04 00:38:51 | 0.3100 | | 192.168.29.81 | N54L | Free swap space | system.swap.size[,free] | 2014-02-04 00:38:52 | 14834892800 | | 192.168.29.81 | N54L | Total swap space | system.swap.size[,total] | 2014-02-03 23:58:53 | 16907833344 | | 192.168.29.81 | N54L | System information | system.uname | 2014-02-03 23:58:54 | Windows N54L 6.1.7601 Microsoft Windows 7 Ultimate Edition Service Pack 1 x64 | | 192.168.29.81 | N54L | System uptime | system.uptime | 2014-02-04 00:38:55 | 148811 | | 192.168.29.81 | N54L | Device uptime | sysUpTime | 2014-02-04 00:38:26 | 148761 | | 192.168.29.81 | N54L | Free memory | vm.memory.size[free] | 2014-02-04 00:38:56 | 6625419264 | | 192.168.29.81 | N54L | Total memory | vm.memory.size[total] | 2014-02-03 23:58:57 | 8454860800 | +---------------+-----------+------------------------------------+--------------------------------------------------------------------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
アイテム値全取得（特定項目,全項目も表示できるが、非常に時間がかかります）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 = '192.168.29.81' and i.status = 0 and i.name like '%' and i.key_ like 'system.cpu.load[percpu,avg1]%'; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
+---------------+-----------+--------------------------------+------------------------------+---------------------+--------+ | HOST | HOST_NAME | ITEM_NAME | ITEM_KEY | clock | value | +---------------+-----------+--------------------------------+------------------------------+---------------------+--------+ | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:47:50 | 0.3917 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:48:50 | 0.3250 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:49:50 | 0.7083 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:50:50 | 0.0833 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:51:50 | 0.1833 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:52:50 | 0.3500 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:53:50 | 0.5583 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:54:50 | 0.7667 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:55:50 | 0.5000 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:56:50 | 0.1750 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:57:50 | 0.1833 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:58:50 | 1.4250 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 00:59:50 | 2.3167 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 01:00:50 | 1.0167 | | 192.168.29.81 | N54L | Processor load (1 min average) | system.cpu.load[percpu,avg1] | 2014-02-04 01:01:50 | 0.1083 | +---------------+-----------+--------------------------------+------------------------------+---------------------+--------+ 10121 rows in set (58 min 12.63 sec) |
こんな感じにすれば、情報は取得できそうですが、
Zabbix のバージョンが上がった時に対応できなくなる可能性が高いのが残念。
あと、mysqlの設定が標準だと検索に時間がかかります。
実際にはチューニング必須なようです（チューニング方法はまた今度）