zabbix2.2からSQLでディスク使用率の高いホスト情報を検索してみました。
apiで頑張ったらいいという話もあるけどちょっと調べたくらいではよくわからなかったのでとりあえず。
重いSQLなようなのでホストを絞るためにグループIDを指定するサブクエリを仕込みます。
where句には左から順に検索結果行数が最も少なくなるように書いてくといいはず。たぶん。
まずグループidを特定
mysql> select * from groups where name like 'グループ名1%' and name != '除外するグループ名2' and name !='除外するグループ名3';
+---------+-------------------+----------+-------+
| groupid | name | internal | flags |
+---------+-------------------+----------+-------+
| 10 | samplegrp3-hoge6 | 0 | 0 |
| 11 | samplegrp3-hoge1 | 0 | 0 |
| 12 | samplegrp1-piyo | 0 | 0 |
| 14 | samplegrp2-fuga1 | 0 | 0 |
| 21 | samplegrp3-piyo | 0 | 0 |
| 25 | samplegrp3-hoge4 | 0 | 0 |
| 26 | samplegrp3-hoge5a | 0 | 0 |
| 28 | samplegrp2-hoge5 | 0 | 0 |
| 29 | samplegrp2-hoge7 | 0 | 0 |
| 30 | samplegrp2-fuga3 | 0 | 0 |
| 31 | samplegrp1-hoge3 | 0 | 0 |
| 32 | samplegrp1-fuga2 | 0 | 0 |
| 41 | samplegrp3-hoge5b | 0 | 0 |
+---------+-------------------+----------+-------+
13 rows in set (0.00 sec)
出したいホスト数をみる。
かならず自分が書くSQLがどのくらいの行の規模になるのかをカウントしておく。
（2000件弱ならまあいいかなと思った）
mysql> select count(h.host) from hosts_groups hg join hosts h on hg.hostid = h.hostid where hg.groupid in (10, 11, 12, 14, 21, 25, 26, 28, 29, 30, 31, 32, 41);
+---------------+
| count(h.host) |
+---------------+
| 1861 |
+---------------+
1 row in set (0.00 sec)
つぎに見たいアイテムのkey_を特定する
mysql> select distinct name, key_ from items where name like '%HDD%';
+-----------------------------------+----------------------------------+
| name | key_ |
+-----------------------------------+----------------------------------+
| 03_01.HDD使用率(/) | vfs.fs.size[/,pused] |
| 03_02.HDD使用量(/) | vfs.fs.size[/,used] |
+-----------------------------------+----------------------------------+
2 rows in set (0.09 sec)
いくつかのテーブルからそれぞれjoinとサブクエリでselectする。
（たぶんインデックスがないとかキャッシュに載ってない関係で約2分くらいかかった。たぶん環境に依存する）
key_に見たいディスク使用率のキーを指定。
hostにinでさっきのgroupidからのサブクエリを指定する(countじゃないやつ)。
mysql> select
-> f.ip AS IP,
-> h.name AS HOST_NAME,
-> i.name AS ITEM_NAME,
-> i.key_ as ITEM_KEY,
-> FROM_UNIXTIME(ELT(i.value_type+1, hy1.clock)) AS clock ,
-> Replace(Replace( ELT(i.value_type+1, hy1.value) , Char(13), ""),Char(10), "") AS value
-> from items i
-> join hosts h on h.hostid = i.hostid
-> left join interface f on f.hostid = h.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 h.host from hosts_groups hg
-> join hosts h on hg.hostid = h.hostid
-> where hg.groupid in (10, 11, 12, 14, 21, 25, 26, 28, 29, 30, 31, 32, 41)) and
-> i.status = 0 and
-> i.key_ = 'vfs.fs.size[/,pused]' and
-> hy1.value >= 50
-> order by hy1.value desc
-> limit 30 ;
+---------------+-------------------------------------+-----------------------+----------------------+---------------------+---------+
| IP | HOST_NAME | ITEM_NAME | ITEM_KEY | clock | value |
+---------------+-------------------------------------+-----------------------+----------------------+---------------------+---------+
| xx.xx0.47.198 | role123456-env1-xxxxxxxxxxxx-47-198 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:52:25 | 78.7767 |
| xx.xx0.47.198 | role123456-env1-xxxxxxxxxxxx-47-198 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:52:25 | 78.7767 |
| xx.xx1.64.129 | role1-env2b-xxxxxxxxxxxx-64-129 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:02 | 77.7183 |
| xx.xx1.64.129 | role1-env2b-xxxxxxxxxxxx-64-129 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:02 | 77.7183 |
| xx.xx1.69.46 | role1-env2b-xxxxxxxxxxxx-69-46 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:56:23 | 75.2243 |
| xx.xx1.69.46 | role1-env2b-xxxxxxxxxxxx-69-46 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:56:23 | 75.2243 |
| xx.xx0.42.215 | role1234-env1-xxxxxxxxxxxx-42-215 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:45 | 67.3197 |
| xx.xx0.42.215 | role1234-env1-xxxxxxxxxxxx-42-215 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:45 | 67.3197 |
| xx.xx0.46.187 | role1234-env1-xxxxxxxxxxxx-46-187 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:27 | 67.1965 |
| xx.xx0.46.187 | role1234-env1-xxxxxxxxxxxx-46-187 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:27 | 67.1965 |
| xx.xx0.42.213 | role1234-env1-xxxxxxxxxxxx-42-213 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:33 | 66.9246 |
| xx.xx0.42.213 | role1234-env1-xxxxxxxxxxxx-42-213 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:33 | 66.9246 |
| xx.xx0.37.248 | role1234-env1-xxxxxxxxxxxx-37-248 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:52:44 | 66.8608 |
| xx.xx0.37.248 | role1234-env1-xxxxxxxxxxxx-37-248 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:52:44 | 66.8608 |
| xx.xx0.37.247 | role1234-env1-xxxxxxxxxxxx-37-247 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:56 | 66.6505 |
| xx.xx0.37.247 | role1234-env1-xxxxxxxxxxxx-37-247 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:56 | 66.6505 |
| xx.xx0.34.4 | role3-env1-xxxxxxxxxxxx-34-4 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:55:42 | 66.6130 |
| xx.xx0.34.4 | role3-env1-xxxxxxxxxxxx-34-4 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:55:42 | 66.6130 |
| xx.xx0.37.249 | role1234-env1-xxxxxxxxxxxx-37-249 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:57:21 | 66.5555 |
| xx.xx0.37.249 | role1234-env1-xxxxxxxxxxxx-37-249 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:57:21 | 66.5555 |
| xx.xx1.0.240 | role2-dev6-xxxxxxxxxxxx-0-240 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:19 | 61.6173 |
| xx.xx1.0.240 | role2-dev6-xxxxxxxxxxxx-0-240 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:53:19 | 61.6173 |
| xx.xx1.64.143 | role2-env2b-xxxxxxxxxxxx-64-143 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:52 | 55.7228 |
| xx.xx1.64.143 | role2-env2b-xxxxxxxxxxxx-64-143 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:52 | 55.7228 |
| xx.xx1.51.103 | role1-env2a-xxxxxxxxxxxx-51-103 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:57:10 | 52.6348 |
| xx.xx1.51.103 | role1-env2a-xxxxxxxxxxxx-51-103 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:57:10 | 52.6348 |
| xx.xx1.55.52 | role1-env2a-xxxxxxxxxxxx-55-52 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:30 | 52.0777 |
| xx.xx1.55.52 | role1-env2a-xxxxxxxxxxxx-55-52 | 03_01.HDD使用率(/) | vfs.fs.size[/,pused] | 2017-02-06 01:54:30 | 52.0777 |
+---------------+-------------------------------------+-----------------------+----------------------+---------------------+---------+
28 rows in set (18.41 sec)
ということでディスク使用率が多いのが見られた。わーい。
ちなみに、速度があがるかと思ってitemidで絞ろうとしたらデータが取れなかった。key_じゃないとダメでした。
（itemidで取れるデータは存在しなかった）
速度を上げるには複合インデックスつけるとたぶん上がる気がするが、インデックスを付ける分のデータが増えてその分ディスク容量が必要でデータサイズがオンメモリにならなくなると遅くなったり更新が遅くなったりする模様。
あとたまにしか発行しないSQLにインデックスつけるのはわりとアレだった気がします。
http://qiita.com/katsukii/items/3409e3c3c96580d37c2b
それとバックアップslaveとかがあるならそっちからデータ取ったほうがよさそう。
SQL久しぶりすぎて思ったとおりのが出るまで大変だったけど出ると嬉しい。
where句は多少いじりまわしたけどselectからjoinのあたりまではIP出るようにinterfaceテーブルをjoinしたくらいでほとんど参考サイトの一番上のURLのそのままです。
apiで同じことする方法をご存知の方がいたらゼヒご教示いただきたく。。
参考：
http://nsb.homeip.net/wp/?p=632
http://www.pursue.ne.jp/jouhousyo/SQLDoc/select08.html
http://labs.timedia.co.jp/2014/10/selecting-max-record-in-group-by.html
http://qiita.com/usiusi360/items/ee3e6e5cbf489db3ea38
http://dev.classmethod.jp/server-side/db/mysql_sub_query/
http://qiita.com/katsukii/items/3409e3c3c96580d37c2b