需求:
对 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)
