需求:
对 mysql 输出进行 groug by 排序并执行 top 10 返回
前提:
当前需对 zabbix 执行自定义监控数据返回.
涉及到的表比较多, 下面作出简单的说明
hosts_groups 用于记录主机与组的 id 关系
mysql> desc hosts_groups;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| hostgroupid | bigint(20) unsigned | NO | PRI | NULL | |
| hostid | bigint(20) unsigned | NO | MUL | NULL | |
| groupid | bigint(20) unsigned | NO | MUL | NULL | |
+-------------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> select * from hosts_groups limit 1, 2;
+-------------+--------+---------+
| hostgroupid | hostid | groupid |
+-------------+--------+---------+
| 47 | 10047 | 1 |
| 98 | 10048 | 1 |
+-------------+--------+---------+
2 rows in set (0.00 sec)groups 表用于记录主机组信息
mysql> desc groups;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| groupid | bigint(20) unsigned | NO | PRI | NULL | |
| name | varchar(64) | NO | MUL | | |
| internal | int(11) | NO | | 0 | |
| flags | int(11) | NO | | 0 | |
+----------+---------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> select * from groups where name in ( 'QA','DEV','MOBILE','OPS') ;
+---------+--------+----------+-------+
| groupid | name | internal | flags |
+---------+--------+----------+-------+
| 13 | QA | 0 | 0 |
| 14 | DEV | 0 | 0 |
| 15 | MOBILE | 0 | 0 |
| 20 | OPS | 0 | 0 |
+---------+--------+----------+-------+
4 rows in set (0.00 sec)
hosts 表用于记录主机信息
mysql> desc hosts;
+--------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| hostid | bigint(20) unsigned | NO | PRI | NULL | |
| proxy_hostid | bigint(20) unsigned | YES | MUL | NULL | |
| host | varchar(128) | NO | MUL | | |
| status | int(11) | NO | MUL | 0 | |
| disable_until | int(11) | NO | | 0 | |
| error | varchar(128) | NO | | | |
| available | int(11) | NO | | 0 | |
| errors_from | int(11) | NO | | 0 | |
| lastaccess | int(11) | NO | | 0 | |
| ipmi_authtype | int(11) | NO | | 0 | |
| ipmi_privilege | int(11) | NO | | 2 | |
| ipmi_username | varchar(16) | NO | | | |
| ipmi_password | varchar(20) | NO | | | |
| ipmi_disable_until | int(11) | NO | | 0 | |
| ipmi_available | int(11) | NO | | 0 | |
| snmp_disable_until | int(11) | NO | | 0 | |
| snmp_available | int(11) | NO | | 0 | |
| maintenanceid | bigint(20) unsigned | YES | MUL | NULL | |
| maintenance_status | int(11) | NO | | 0 | |
| maintenance_type | int(11) | NO | | 0 | |
| maintenance_from | int(11) | NO | | 0 | |
| ipmi_errors_from | int(11) | NO | | 0 | |
| snmp_errors_from | int(11) | NO | | 0 | |
| ipmi_error | varchar(128) | NO | | | |
| snmp_error | varchar(128) | NO | | | |
| jmx_disable_until | int(11) | NO | | 0 | |
| jmx_available | int(11) | NO | | 0 | |
| jmx_errors_from | int(11) | NO | | 0 | |
| jmx_error | varchar(128) | NO | | | |
| name | varchar(128) | NO | MUL | | |
| flags | int(11) | NO | | 0 | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
| description | text | NO | | NULL | |
+--------------------+---------------------+------+-----+---------+-------+
33 rows in set (0.00 sec)
mysql> select name, hostid from hosts where name = 'tps_mq-awdpn.vclound.com';
+--------------------------+--------+
| name | hostid |
+--------------------------+--------+
| tps_mq-awdpn.vclound.com | 10685 |
+--------------------------+--------+
1 row in set (0.00 sec)
item 用于记录监控项
mysql> desc 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(255) | NO | | | |
| hostid | bigint(20) unsigned | NO | MUL | NULL | |
| name | varchar(255) | NO | | | |
| key_ | varchar(255) | NO | | | |
| delay | int(11) | NO | | 0 | |
| history | int(11) | NO | | 90 | |
| trends | int(11) | NO | | 365 | |
| status | int(11) | NO | MUL | 0 | |
| value_type | int(11) | NO | | 0 | |
| trapper_hosts | varchar(255) | NO | | | |
| units | varchar(255) | NO | | | |
| multiplier | int(11) | NO | | 0 | |
| delta | int(11) | NO | | 0 | |
| snmpv3_securityname | varchar(64) | NO | | | |
| snmpv3_securitylevel | int(11) | NO | | 0 | |
| snmpv3_authpassphrase | varchar(64) | NO | | | |
| snmpv3_privpassphrase | varchar(64) | NO | | | |
| formula | varchar(255) | NO | | | |
| error | varchar(2048) | NO | | | |
| lastlogsize | bigint(20) unsigned | NO | | 0 | |
| logtimefmt | varchar(64) | NO | | | |
| templateid | bigint(20) unsigned | YES | MUL | NULL | |
| valuemapid | bigint(20) unsigned | YES | MUL | NULL | |
| delay_flex | varchar(255) | NO | | | |
| params | text | NO | | NULL | |
| ipmi_sensor | varchar(128) | NO | | | |
| data_type | int(11) | NO | | 0 | |
| authtype | int(11) | NO | | 0 | |
| username | varchar(64) | NO | | | |
| password | varchar(64) | NO | | | |
| publickey | varchar(64) | NO | | | |
| privatekey | varchar(64) | NO | | | |
| mtime | int(11) | NO | | 0 | |
| flags | int(11) | NO | | 0 | |
| interfaceid | bigint(20) unsigned | YES | MUL | NULL | |
| port | varchar(64) | NO | | | |
| description | text | NO | | NULL | |
| inventory_link | int(11) | NO | | 0 | |
| lifetime | varchar(64) | NO | | 30 | |
| snmpv3_authprotocol | int(11) | NO | | 0 | |
| snmpv3_privprotocol | int(11) | NO | | 0 | |
| state | int(11) | NO | | 0 | |
| snmpv3_contextname | varchar(255) | NO | | | |
| evaltype | int(11) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------+-------+
47 rows in set (0.00 sec)
mysql> select a.key_, a.itemid, a.hostid from items a, hosts b where a.hostid = b.hostid and b.name='tps_mq-awdpn.vclound.com' and a.key_ in ( 'net.if.in[eth0]', 'net.if.out[eth0]', 'vm.memory.size[total]', 'vm.memory.size[available]' );
+---------------------------+--------+--------+
| key_ | itemid | hostid |
+---------------------------+--------+--------+
| net.if.in[eth0] | 90380 | 10685 |
| net.if.out[eth0] | 90382 | 10685 |
| vm.memory.size[available] | 90340 | 10685 |
| vm.memory.size[total] | 90341 | 10685 |
+---------------------------+--------+--------+
4 rows in set (0.00 sec)
history 与 history_uint 表结构一样, 都是用于记录 items 的记录, 但 history_unit 记录的是通过 discovery 方式发现的 items. (具体信息参考 www.zabbix.com 官方资料)
mysql> desc 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)
mysql> select * from history_uint where itemid in ( 90380,90340 ) and clock > 1416278920;
+--------+------------+------------+-----------+
| itemid | clock | value | ns |
+--------+------------+------------+-----------+
| 90340 | 1416278980 | 3972816896 | 508372588 |
| 90380 | 1416278960 | 8096 | 188420279 |
+--------+------------+------------+-----------+
2 rows in set (0.00 sec)
如果觉得复杂, 可以直接创建下面的表进行测试.
select b.name project, d.name hostname, c.key_, e.value value
from hosts_groups a, groups b, items c, hosts d, history e
where
b.groupid = a. groupid
and b.name in ( 'QA','DEV','MOBILE','OPS')
and c.hostid = d.hostid
and c.hostid = a.hostid
and e.itemid = c.itemid
and c.key_ = 'net.if.out[eth0]'
and e.clock >= ( select unix_timestamp() - (60 * 60 ))
order by value desc
limit 1;
+---------+------------------------------------------------+------------------+----------+
| project | hosts | mkey | value |
+---------+------------------------------------------------+------------------+----------+
| DEV | test_project-wezqp.vclound.com | net.if.out[eth0] | 3848928 |
| DEV | lamcloud-xmcds.vclound.com | net.if.out[eth0] | 2846344 |
| DEV | seven-test-server-9srvb.vclound.com | net.if.out[eth0] | 1031008 |
| DEV | test_1-eyhxk.vclound.com | net.if.out[eth0] | 906168 |
| DEV | query-uk6hq.vclound.com | net.if.out[eth0] | 564352 |
| DEV | bmqiu-pcmhw.vclound.com | net.if.out[eth0] | 447984 |
| DEV | chao-data01.vclound.com | net.if.out[eth0] | 353504 |
| DEV | linqu-qu4pt.vclound.com | net.if.out[eth0] | 300120 |
| DEV | jenkins_server-vduzh.vclound.com | net.if.out[eth0] | 293976 |
| DEV | bmqiu-4p59i.vclound.com | net.if.out[eth0] | 248040 |
| MOBILE | bencui-hh-dev-web-xunqy.vclound.com | net.if.out[eth0] | 283720 |
| MOBILE | mobile_configcenter_dev-vvcuf.vclound.com | net.if.out[eth0] | 146368 |
| MOBILE | stonexie_test-nf7qi.vclound.com | net.if.out[eth0] | 141656 |
| MOBILE | bendevota-xjraf.vclound.com | net.if.out[eth0] | 137200 |
| MOBILE | mobile_configcenter_data_dev-tsgw9.vclound.com | net.if.out[eth0] | 121096 |
| MOBILE | test_01-czths.vclound.com | net.if.out[eth0] | 108960 |
| MOBILE | mobile_test_automation-xztwz.vclound.com | net.if.out[eth0] | 107872 |
| MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 103304 |
| MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 103304 |
| MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 103304 |
| OPS | db_li-wkq54.vclound.com | net.if.out[eth0] | 180656 |
| OPS | pp-pe5vs.vclound.com | net.if.out[eth0] | 153704 |
| OPS | pp-aytgj.vclound.com | net.if.out[eth0] | 140104 |
| OPS | pp-vqjky.vclound.com | net.if.out[eth0] | 139544 |
| OPS | db_li-nrziy.vclound.com | net.if.out[eth0] | 125160 |
| OPS | chima-hh-dev-web-tivix.vclound.com | net.if.out[eth0] | 125152 |
| OPS | fei-usv76.vclound.com | net.if.out[eth0] | 106448 |
| OPS | data_mining_web-kfjhn.vclound.com | net.if.out[eth0] | 104776 |
| OPS | data_mining_file-532ev.vclound.com | net.if.out[eth0] | 96656 |
| OPS | johnny_liu-vtceh.vclound.com | net.if.out[eth0] | 90816 |
| QA | voptest3-dbkfr.vclound.com | net.if.out[eth0] | 37872920 |
| QA | b2c-zhongbo-xdmpm.vclound.com | net.if.out[eth0] | 2131664 |
| QA | voptest6-spupv.vclound.com | net.if.out[eth0] | 756000 |
| QA | backendtest-ihuri.vclound.com | net.if.out[eth0] | 640128 |
| QA | backend-sd66e.vclound.com | net.if.out[eth0] | 637856 |
| QA | voptest-3mspf.vclound.com | net.if.out[eth0] | 604704 |
| QA | dev_pay_app-xg3gb.vclound.com | net.if.out[eth0] | 266360 |
| QA | voptest4-rwikf.vclound.com | net.if.out[eth0] | 252808 |
| QA | vop_test_02-k7jum.vclound.com | net.if.out[eth0] | 243696 |
| QA | dev_vis_app02-rkbux.vclound.com | net.if.out[eth0] | 208536 |
+---------+------------------------------------------------+------------------+----------+
真实数据超过 10 万条, 上述数据只为显示而已. 如有兴趣, 执行创建数据自行进行测试
看下面的方法, (以真实数据进行测试咯) 所以返回值与上述表不一样
1. 获取一行的 top 数据
select b.name project,
substring_index( group_concat( d.name order by e.value desc),',',1) as hostname
from zabbix.hosts_groups a, zabbix.groups b, zabbix.items c, zabbix.hosts d, zabbix.history_uint e
where
b.groupid = a. groupid
and b.name in ( 'QA','DEV','MOBILE','OPS')
and c.hostid = d.hostid
and c.hostid = a.hostid
and e.itemid = c.itemid
and c.key_ = 'net.if.out[eth0]'
and e.clock >= ( select unix_timestamp() - 60)
group by project;
+---------+------------------------------------------+
| project | hostname |
+---------+------------------------------------------+
| DEV | iclub-8y9hy.vclound.com |
| MOBILE | mobile_test_automation-xztwz.vclound.com |
| OPS | db_li-nrziy.vclound.com |
| QA | b2c-zhongbo-xdmpm.vclound.com |
+---------+------------------------------------------+
4 rows in set, 2 warnings (0.11 sec)
2. 把其他列的数据也添加上去
select b.name project,
substring_index( group_concat( d.name order by e.value desc),',',1) as hostname,
substring_index( group_concat( c.key_ order by e.value desc),',',1) as newkey,
substring_index( group_concat( e.value order by e.value desc),',',1) as value
from zabbix.hosts_groups a, zabbix.groups b, zabbix.items c, zabbix.hosts d, zabbix.history_uint e
where
b.groupid = a. groupid
and b.name in ( 'QA','DEV','MOBILE','OPS')
and c.hostid = d.hostid
and c.hostid = a.hostid
and e.itemid = c.itemid
and c.key_ = 'net.if.out[eth0]'
and e.clock >= ( select unix_timestamp() - 60)
group by project;
+---------+------------------------------------------------+------------------+--------+
| project | hostname | newkey | value |
+---------+------------------------------------------------+------------------+--------+
| DEV | simanhu-hh-dev-web-puzys.vclound.com | net.if.out[eth0] | 94528 |
| MOBILE | mobile_configcenter_data_dev-tsgw9.vclound.com | net.if.out[eth0] | 4672 |
| OPS | db_li-nrziy.vclound.com | net.if.out[eth0] | 4552 |
| QA | dev_ebs_app_db-uya9f.vclound.com | net.if.out[eth0] | 671960 |
+---------+------------------------------------------------+------------------+--------+
4 rows in set, 3 warnings (0.12 sec)
3. 要创建 group by 方式的 top 10 , 需要创建一个临时表.
CREATE TABLE tinyint_asc (
zhi tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (zhi)
) ;
INSERT INTO tinyint_asc VALUES (0),(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),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);
4. 最终结果, 按照 group by 输出 top 10 信息
select b.name project,
substring_index(substring_index( group_concat( d.name order by e.value desc),',',f.zhi),',',-1) as hostname,
substring_index(substring_index( group_concat( c.key_ order by e.value desc),',',f.zhi),',',-1) as newkey,
substring_index(substring_index( group_concat( e.value order by e.value desc),',',f.zhi),',',-1) as value
from zabbix.hosts_groups a, zabbix.groups b, zabbix.items c, zabbix.hosts d, zabbix.history_uint e, tinyint_asc f
where
b.groupid = a. groupid
and b.name in ( 'QA','DEV','MOBILE','OPS')
and c.hostid = d.hostid
and c.hostid = a.hostid
and e.itemid = c.itemid
and c.key_ = 'net.if.out[eth0]'
and e.clock >= ( select unix_timestamp() - 60)
and f.zhi >= 1 and f.zhi <= 10
group by project, zhi;
+---------+------------------------------------------------+------------------+--------+
| project | hostname | newkey | value |
+---------+------------------------------------------------+------------------+--------+
| DEV | chao-data01.vclound.com | net.if.out[eth0] | 9152 |
| DEV | garnettwang-hh-qa-web-xrw6d.vclound.com | net.if.out[eth0] | 6656 |
| DEV | lamcloud-xmcds.vclound.com | net.if.out[eth0] | 6160 |
| DEV | ckjiang-hh-qa-web-ry4jw.vclound.com | net.if.out[eth0] | 5824 |
| DEV | waf-1-satp3.vclound.com | net.if.out[eth0] | 5504 |
| DEV | waf-1-satp3.vclound.com | net.if.out[eth0] | 5504 |
| DEV | test-pq9ms.vclound.com | net.if.out[eth0] | 5072 |
| DEV | ashen-yjnrc.vclound.com | net.if.out[eth0] | 5000 |
| DEV | ftp_server-rtnhd.vclound.com | net.if.out[eth0] | 4992 |
| DEV | jessenli-4535z.vclound.com | net.if.out[eth0] | 4680 |
| MOBILE | mobile_configcenter_data_dev-tsgw9.vclound.com | net.if.out[eth0] | 4864 |
| MOBILE | bendevota-xjraf.vclound.com | net.if.out[eth0] | 4568 |
| MOBILE | bencui-hh-dev-web-xunqy.vclound.com | net.if.out[eth0] | 4296 |
| MOBILE | test_01-czths.vclound.com | net.if.out[eth0] | 4144 |
| MOBILE | stonexie_test-nf7qi.vclound.com | net.if.out[eth0] | 4008 |
| MOBILE | mobile_configcenter_dev-vvcuf.vclound.com | net.if.out[eth0] | 3600 |
| MOBILE | mobile_test_automation-xztwz.vclound.com | net.if.out[eth0] | 3480 |
| MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 3472 |
| MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 3472 |
| MOBILE | mobiletest_webpage-9vs38.vclound.com | net.if.out[eth0] | 3472 |
| OPS | data_mining_file-532ev.vclound.com | net.if.out[eth0] | 74104 |
| OPS | db_li-wkq54.vclound.com | net.if.out[eth0] | 4568 |
| OPS | db_li-nrziy.vclound.com | net.if.out[eth0] | 4560 |
| OPS | data_mining_file-qfctf.vclound.com | net.if.out[eth0] | 4088 |
| OPS | kingtest-k56ne.vclound.com | net.if.out[eth0] | 3864 |
| OPS | chima-hh-dev-web-tivix.vclound.com | net.if.out[eth0] | 3816 |
| OPS | pp-aytgj.vclound.com | net.if.out[eth0] | 3736 |
| OPS | pp-pe5vs.vclound.com | net.if.out[eth0] | 3624 |
| OPS | pp-vqjky.vclound.com | net.if.out[eth0] | 3584 |
| OPS | fei-usv76.vclound.com | net.if.out[eth0] | 3512 |
| QA | dev_ebs_app_db-uya9f.vclound.com | net.if.out[eth0] | 549448 |
| QA | b2c-zhongbo-xdmpm.vclound.com | net.if.out[eth0] | 529344 |
| QA | zhaoantan-hh-qa-web-ar5tx.vclound.com | net.if.out[eth0] | 67784 |
| QA | dev_vis_app02-rkbux.vclound.com | net.if.out[eth0] | 63560 |
| QA | voptest-3mspf.vclound.com | net.if.out[eth0] | 40232 |
| QA | vop_test_02-k7jum.vclound.com | net.if.out[eth0] | 35632 |
| QA | vop_test_03-gdqb2.vclound.com | net.if.out[eth0] | 30120 |
| QA | voptest8-jnusa.vclound.com | net.if.out[eth0] | 29648 |
| QA | voptest7-zbvxy.vclound.com | net.if.out[eth0] | 27152 |
| QA | dev_fcs_app02-mpa9a.vclound.com | net.if.out[eth0] | 27072 |
+---------+------------------------------------------------+------------------+--------+
40 rows in set, 30 warnings (1.17 sec)
扫一扫
6672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
