今回、ZabbixのDBからCPU使用率やメモリ使用率といったパフォーマンスデータをスクリプトなどで自動エクスポートできるよう、DBの内容調査を行い、データ抽出のためのSQL文を作ってみることにした。
Zabbix環境
- OS : CentOS
- Zabbix : Zabbix 4.0
- DB : MariaDB
ZabbixのDB構造を確認する
まずは、ZabbixのDBの後続を把握するため、存在するテーブルを確認し、個々のテーブルの中身を確認してみることにする。DB内のテーブル一覧を確認
MariaDBにログインして、Zabbixのテーブル一覧を確認してみる。どうやらZabbix 4.0では、144個のテーブルが存在するようだ。MariaDB [zabbix]> show tables;
+----------------------------+
| Tables_in_zabbix |
+----------------------------+
| acknowledges |
| actions |
| alerts |
～(中略)～
| valuemaps |
| widget |
| widget_field |
+----------------------------+
144 rows in set (0.00 sec)
登録されているホスト一覧を確認
Zabbixに登録されているホストは「hosts」テーブルに存在する。テンプレートもホストとして登録されており、後半に実際の監視対象としてのホストが表示される。MariaDB [zabbix]> select hostid, host from hosts;
+--------+-------------------------------------------------------------------------------+
| hostid | host |
+--------+-------------------------------------------------------------------------------+
| 10168 | Template App Apache Tomcat JMX |
| 10093 | Template App FTP Service |
| 10169 | Template App Generic Java JMX |
～(中略)～
| 10262 | t1081w219 |
| 10260 | t1082w216 |
| 10176 | {#HV.UUID} |
| 10177 | {#VM.UUID} |
+--------+-------------------------------------------------------------------------------+
アイテム一覧を確認
アイテムは「items」テーブルに登録されている。アイテムは一覧取得すると大量に出力されるため、とりあえず存在するカラムを確認しておく。MariaDB [zabbix]> show columns from items;
+-----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | PRI | NULL | |
| type | int(11) | NO | | 0 | |
| snmp_community | varchar(64) | NO | | | |
| snmp_oid | varchar(512) | NO | | | |
| hostid | bigint(20) unsigned | NO | MUL | NULL | |
～(中略)～
| verify_peer | int(11) | NO | | 0 | |
| verify_host | int(11) | NO | | 0 | |
| allow_traps | int(11) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------+-------+
63 rows in set (0.00 sec)
MariaDB [zabbix]> select itemid, hostid, name,key_ from items where hostid='10262';
+--------+--------+------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| itemid | hostid | name | key_ |
+--------+--------+------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 28658 | 10262 | Host name of zabbix_agentd running | agent.hostname |
| 28659 | 10262 | Agent ping | agent.ping |
| 28660 | 10262 | Version of zabbix_agent(d) running | agent.version |
～(中略)～
| 28671 | 10262 | Processor load (15 min average) | system.cpu.load[percpu,avg15] |
| 28672 | 10262 | Processor load (1 min average) | system.cpu.load[percpu,avg1] |
| 28673 | 10262 | Processor load (5 min average) | system.cpu.load[percpu,avg5] |
| 28674 | 10262 | Free swap space | system.swap.size[,free] |
| 28675 | 10262 | Total swap space | system.swap.size[,total] |
| 28676 | 10262 | System information | system.uname |
| 28677 | 10262 | System uptime | system.uptime |
| 28657 | 10262 | Mounted filesystem discovery | vfs.fs.discovery |
| 28741 | 10262 | Free disk space on $1 | vfs.fs.size[C:,free] |
| 28742 | 10262 | Free disk space on $1 (percentage) | vfs.fs.size[C:,pfree] |
| 28743 | 10262 | Total disk space on $1 | vfs.fs.size[C:,total] |
| 28744 | 10262 | Used disk space on $1 | vfs.fs.size[C:,used] |
| 28683 | 10262 | Free disk space on $1 | vfs.fs.size[{#FSNAME},free] |
| 28684 | 10262 | Free disk space on $1 (percentage) | vfs.fs.size[{#FSNAME},pfree] |
| 28685 | 10262 | Total disk space on $1 | vfs.fs.size[{#FSNAME},total] |
| 28686 | 10262 | Used disk space on $1 | vfs.fs.size[{#FSNAME},used] |
| 28678 | 10262 | Free memory | vm.memory.size[free] |
| 28679 | 10262 | Total memory | vm.memory.size[total] |
| 28680 | 10262 | Free virtual memory, in % | vm.vmemory.size[pavailable] |
+--------+--------+------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
89 rows in set (0.00 sec)
ヒストリを確認
各ホストのパフォーマンスデータは「history」テーブルに保存されている。このテーブルのカラムはシンプルに4つとなっている。MariaDB [zabbix]> show columns from history;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO | MUL | NULL | |
| clock | int(11) | NO | | 0 | |
| value | double(16,4) | NO | | 0.0000 | |
| ns | int(11) | NO | | 0 | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
特定の日付のパフォーマンスデータを出力する
ひねりのないSQL文
DBのテーブル構成の確認ができたので、historyテーブルから以下内容のパフォーマンスデータをひねりのない単純なSQL文で出力してみる。- 取得対象アイテム : Processor load (5 min average) (itemid=28673)
- 取得開始日時 : 2018-10-08 00:00:00
- 取得終了日時 : 2018-10-09 00:00:00
MariaDB [zabbix]> select from_unixtime(clock), value from history where itemid='28673' and from_unixtime(clock) between '2018-10-08 00:00:00' and '2018-10-09 00:00:00';
+----------------------+--------+
| from_unixtime(clock) | value |
+----------------------+--------+
| 2018-10-08 21:49:53 | 0.0093 |
| 2018-10-08 21:50:53 | 0.0111 |
| 2018-10-08 21:51:53 | 0.0100 |
| 2018-10-08 21:52:53 | 0.0128 |
| 2018-10-08 21:53:53 | 0.0128 |
| 2018-10-08 21:54:53 | 0.0122 |
| 2018-10-08 21:55:53 | 0.0144 |
| 2018-10-08 21:56:53 | 0.0128 |
| 2018-10-08 21:57:53 | 0.0100 |
～(中略)～
| 2018-10-08 23:55:53 | 0.0000 |
| 2018-10-08 23:56:53 | 0.0000 |
| 2018-10-08 23:57:53 | 0.0000 |
| 2018-10-08 23:58:53 | 0.0000 |
| 2018-10-08 23:59:53 | 0.0000 |
+----------------------+--------+
111 rows in set (0.00 sec)
テーブルを結合して出力するSQL文
「ひねりのないSQL文」では、事前にhostidやitemidを調べておく必要があり、実用的ではない。そこで、INNER JOIN句を使いテーブルを内部結合することで、hostidやitemidを調べることなく「ホスト名」や「アイテム名」そのものを条件としてデータ出力できるようにしてみた。MariaDB [zabbix]> select from_unixtime(history.clock), hosts.host, items.name, history.value from history
-> inner join items using( itemid )
-> inner join hosts using( hostid )
-> where hosts.host='t1081w219' and items.name='Processor load (5 min average)' and from_unixtime(clock) between '2019-01-02 00:00:00' and '2019-01-03 00:00:00';
+------------------------------+-----------+--------------------------------+--------+
| from_unixtime(history.clock) | host | name | value |
+------------------------------+-----------+--------------------------------+--------+
| 2018-10-08 21:49:53 | t1081w219 | Processor load (5 min average) | 0.0093 |
| 2018-10-08 21:50:53 | t1081w219 | Processor load (5 min average) | 0.0111 |
| 2018-10-08 21:51:53 | t1081w219 | Processor load (5 min average) | 0.0100 |
～(中略)～
| 2018-10-08 23:57:53 | t1081w219 | Processor load (5 min average) | 0.0000 |
| 2018-10-08 23:58:53 | t1081w219 | Processor load (5 min average) | 0.0000 |
| 2018-10-08 23:59:53 | t1081w219 | Processor load (5 min average) | 0.0000 |
+------------------------------+-----------+--------------------------------+--------+
111 rows in set (0.00 sec)
CSVで出力する
最後に出力したデータをCSVでファイル出力する。INTO OUTFILE句にて出力ファイル名を指定し、「,」区切り指定をすればよい。MariaDB [zabbix]> select from_unixtime(history.clock), hosts.host, items.name, history.value from history
-> inner join items using( itemid )
-> inner join hosts using( hostid )
-> where hosts.host='Zabbix server' and items.name='CPU user time' and from_unixtime(clock) between '2019-01-02 00:00:00' and '2019-01-03 00:00:00'
-> into outfile 'zabbix_history_data.csv' fields terminated by ',';
# ls -l /var/lib/mysql/zabbix/ | grep csv
-rw-rw-rw- 1 mysql mysql 79200 1月 6 15:04 zabbix_history_data.csv
