zabbix调用mysql接口_zabbix_api的调用(curl测试)和SQL一些解答

获取session id

curl -i -X POST -H 'Content-Type: application/json' -d '{"jsonrpc":

"2.0","method":"user.login","params":{"user":"Admin","password":"admin"},"auth":

null,"id":0}' http://172.31.xxx.200/zabbix/api_jsonrpc.php;

得到的session结果如下:

{"jsonrpc":"2.0","result":"ce697eeba1143b88223798e752dd66e4","id":0}

运用session idce697eeba1143b88223798e752dd66e4。通过认证获取数据。

获取指定主机的host id

curl -i -X POST -H 'Content-Type: application/json' -d '{"jsonrpc": "2.0",

"method":"host.get","params":{"output":["hostid"],"filter": {"host":"172.30.xxx.30"}},

"auth": "ce697eeba1143b88223798e752dd66e4","id": 0}' http://172.31.xxx.200/zabbix/api_jsonrpc.php;

得到的host id结果如下:

{"jsonrpc":"2.0","result":[{"hostid":"10110"}],"id":0}

基于hostid获取itemsid和key

curl -i -X POST -H "Content-Type: application/json" -d '{"jsonrpc":"2.0",

"method":"item.get","params":{"output":["itemids","key_"],"hostids":"10110"},

"auth": "ce697eeba1143b88223798e752dd66e4","id": 0}' http://172.31.xxx.200/zabbix/api_jsonrpc.php

结果可以看到item对应哪个监控指标:

{"jsonrpc":"2.0","result":[{"itemid":"23902","key_":"agent.hostname"},{"itemid":"23903","key_":"agent.ping"},{"itemid":"23904","key_":"agent.version"},{"itemid":"23905","key_":"kernel.maxfiles"},{"itemid":"23906","key_":"kernel.maxproc"},{"itemid":"24042","key_":"net.if.in[eth0]"},{"itemid":"24043","key_":"net.if.out[eth0]"},{"itemid":"23910","key_":"proc.num[,,run]"},{"itemid":"23911","key_":"proc.num[]"},{"itemid":"23912","key_":"system.boottime"},{"itemid":"23913","key_":"system.cpu.intr"},{"itemid":"23914","key_":"system.cpu.load[percpu,avg15]"},{"itemid":"23915","key_":"system.cpu.load[percpu,avg1]"},{"itemid":"23916","key_":"system.cpu.load[percpu,avg5]"},{"itemid":"23917","key_":"system.cpu.switches"},{"itemid":"23918","key_":"system.cpu.util[,idle]"},{"itemid":"23919","key_":"system.cpu.util[,interrupt]"},{"itemid":"23920","key_":"system.cpu.util[,iowait]"},{"itemid":"23921","key_":"system.cpu.util[,nice]"},{"itemid":"23922","key_":"system.cpu.util[,softirq]"},{"itemid":"23923","key_":"system.cpu.util[,steal]"},{"itemid":"23924","key_":"system.cpu.util[,system]"},{"itemid":"23925","key_":"system.cpu.util[,user]"},{"itemid":"23926","key_":"system.hostname"},{"itemid":"23927","key_":"system.localtime"},{"itemid":"23928","key_":"system.swap.size[,free]"},{"itemid":"23929","key_":"system.swap.size[,pfree]"},{"itemid":"23930","key_":"system.swap.size[,total]"},{"itemid":"23931","key_":"system.uname"},{"itemid":"23932","key_":"system.uptime"},{"itemid":"23933","key_":"system.users.num"},{"itemid":"23934","key_":"vfs.file.cksum[\/etc\/passwd]"},{"itemid":"24044","key_":"vfs.fs.inode[\/,pfree]"},{"itemid":"24045","key_":"vfs.fs.inode[\/boot,pfree]"},{"itemid":"24046","key_":"vfs.fs.inode[\/opt,pfree]"},{"itemid":"24047","key_":"vfs.fs.size[\/,free]"},{"itemid":"24050","key_":"vfs.fs.size[\/,pfree]"},{"itemid":"24053","key_":"vfs.fs.size[\/,total]"},{"itemid":"24056","key_":"vfs.fs.size[\/,used]"},{"itemid":"24048","key_":"vfs.fs.size[\/boot,free]"},{"itemid":"24051","key_":"vfs.fs.size[\/boot,pfree]"},{"itemid":"24054","key_":"vfs.fs.size[\/boot,total]"},{"itemid":"24057","key_":"vfs.fs.size[\/boot,used]"},{"itemid":"24049","key_":"vfs.fs.size[\/opt,free]"},{"itemid":"24052","key_":"vfs.fs.size[\/opt,pfree]"},{"itemid":"24055","key_":"vfs.fs.size[\/opt,total]"},{"itemid":"24058","key_":"vfs.fs.size[\/opt,used]"},{"itemid":"23941","key_":"vm.memory.size[available]"},{"itemid":"23942","key_":"vm.memory.size[total]"}],"id":0}

基于hostid获取itemsid和key,value_tpye

curl -i -X POST -H "Content-Type: application/json" -d '{"jsonrpc":"2.0",

"method":"item.get","params":{"output":["itemids","key_","value_type"],"hostids":"10110"},

"auth": "ce697eeba1143b88223798e752dd66e4","id": 0}' http://172.31.xxx.200/zabbix/api_jsonrpc.php

这里我们取简单的load负载作为测试，

{"itemid":"23915","key_":"system.cpu.load[percpu,avg1]"}这个就是load负载，item号为23915。

获取cpu负载的值

curl -i -X POST -H 'Content-Type:application/json' -d '{"jsonrpc":"2.0","method":"history.get",

"params":{"history":0,"itemids":"47616","output":"extend","limit":10},

"auth":"ce697eeba1143b88223798e752dd66e4","id": 0}' http://172.31.xxx.200/zabbix/api_jsonrpc.php

这里调用了history.get,limit代表只取10行数据。

最终效果如下:

{"jsonrpc":"2.0","result":[{"itemid":"47616","clock":"1589249616","value":"0.0095","ns":"936829877"},{"itemid":"47616","clock":"1589249676","value":"0.0108","ns":"932621456"},{"itemid":"47616","clock":"1589249736","value":"0.0104","ns":"963143320"},{"itemid":"47616","clock":"1589249796","value":"0.0105","ns":"397346143"},{"itemid":"47616","clock":"1589249856","value":"0.0101","ns":"700117592"},{"itemid":"47616","clock":"1589249916","value":"0.0093","ns":"823939597"},{"itemid":"47616","clock":"1589249976","value":"0.0093","ns":"335143020"},{"itemid":"47616","clock":"1589250036","value":"0.0095","ns":"977860048"},{"itemid":"47616","clock":"1589250096","value":"0.0119","ns":"922312243"},{"itemid":"47616","clock":"1589250156","value":"0.0105","ns":"481857044"}],"id":0}

上述相当于调用:

mysql> select * from history where itemid='47616' and clock='1589250156';

+--------+------------+--------+-----------+

| itemid | clock | value | ns |

+--------+------------+--------+-----------+

| 47616 | 1589250156 | 0.0105 | 481857044 |

+--------+------------+--------+-----------+

1 row in set (0.00 sec)

为什么history表，可以秒级响应

个人观点，首先，我们可以观察history的表结构。

mysql> show create table history;

| history | CREATE TABLE `history` (

`itemid` bigint(20) unsigned NOT NULL,

`clock` int(11) NOT NULL DEFAULT '0',

`value` double(16,4) NOT NULL DEFAULT '0.0000',

`ns` int(11) NOT NULL DEFAULT '0',

KEY `history_1` (`itemid`,`clock`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

可以看到起各个属性值都是数字类型，所以占用内存的字节数是很小的，相对于(char类型)来说。

同时，它建立了一个组合索引.根据zabbix的逻辑规则。

itemid和clock的组合代表了唯一值。即使这张表是千万数据。直接根据itemid和clock的查询也是超快速响应。

如下:

1.history的数据量

目前的数据量为4800w，7分钟。

mysql> select count(*) from history;

+----------+

| count(*) |

+----------+

| 48154320 |

+----------+

1 row in set (7 min 34.54 sec)

2.查询itemid和clock的值，快速获得

mysql> select * from history where itemid='47616' and clock='1589250156';

+--------+------------+--------+-----------+

| itemid | clock | value | ns |

+--------+------------+--------+-----------+

| 47616 | 1589250156 | 0.0105 | 481857044 |

+--------+------------+--------+-----------+

1 row in set (0.00 sec)

3.查看执行计划:

mysql> explain select * from history where itemid='47616' and clock='1589250156'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: history

type: ref

possible_keys: history_1

key: history_1

key_len: 12

ref: const,const

rows: 1

Extra: Using index condition

1 row in set (0.00 sec)

可以看到获取的数据为1行，引用的都是const,const所以其效率很高。

小结：组合索引，用到索引的条件是。(itemid,clock)组合可以用到，和itemid会用到。