zabbix 批量生成聚合图形

 通过插入数据库的方式批量生成 zabbix 聚合图形

原型图形 聚合的 sql 批量操作
1、在聚合图形创建好一个聚合图形A。找出图形A的ID  (创建图形的时候记得填写好行数和列数)
select screenid from screens WHERE  name='A';
2、生成insert语句。
set @mycnt = -1;select max(screenitemid) into @mycnt2  from screens_items;
SELECT concat("INSERT INTO screens_items (resourcetype,width,height,x,y,colspan,rowspan,elements,valign,halign,style,url,dynamic,sort_triggers,max_columns,application,screenid,resourceid,screenitemid) VALUES ('20','220','100','0',",@mycnt := @mycnt + 1,",'1','1','0','0','1','0','','0','0','5','','110',",g.graphid,",",@mycnt2 := @mycnt2 + 1,");")  from  graphs g  where g.name='Memcached {#MCPORT} 连接数' into outfile '/tmp/file.txt';
3、cat /tmp/file.txt ，执行insert语句
4、插入完毕后更新ids。原型图形 聚合的 sql 批量操作
1、在聚合图形创建好一个聚合图形A。找出图形A的ID  (创建图形的时候记得填写好行数和列数)
select screenid from screens WHERE  name='A';
2、生成insert语句。
set @mycnt = -1;select max(screenitemid) into @mycnt2  from screens_items;
SELECT concat("INSERT INTO screens_items (resourcetype,width,height,x,y,colspan,rowspan,elements,valign,halign,style,url,dynamic,sort_triggers,max_columns,application,screenid,resourceid,screenitemid) VALUES ('20','220','100','0',",@mycnt := @mycnt + 1,",'1','1','0','0','1','0','','0','0','5','','110',",g.graphid,",",@mycnt2 := @mycnt2 + 1,");")  from  graphs g  where g.name='Memcached {#MCPORT} 连接数' into outfile '/tmp/file.txt';
3、cat /tmp/file.txt ，执行insert语句
4、插入完毕后更新ids。
UPDATE ids SET nextid=(select max(screenitemid)  from screens_items) WHERE table_name='screens_items' AND field_name='screenitemid';
字段说明：
g.name 是原型图形的名称
resourcetype 20表示 图形原型
x  y 表示生成图形的坐标轴
screenitemid 表示screens_items id号
screenid 表示聚合图形的id，存在于主表 screens
resourceid  表示加入图形的id，存在于graphs的主键。
halign  表示图形的横向对齐 0表示居中，1标志靠左

图形  聚合的 批量操作:
图形聚合是特指到每个主机的某一个监控项目。所以设计到主机的ip，监控项名称。
1、在聚合图形创建好一个聚合图形A。找出图形A的ID  (创建图形的时候记得填写好行数和列数)
select screenid from screens WHERE  name='A';
2、生成insert语句。
SELECT concat("INSERT INTO screens_items (resourcetype,width,height,x,y,colspan,rowspan,elements,valign,halign,style,url,dynamic,sort_triggers,max_columns,application,screenid,resourceid,screenitemid) VALUES ('0','250','100','0',",@mycnt := @mycnt + 1,",'1','1','0','0','1','0','','0','0','3','','106',",g.graphid,",",@mycnt2 := @mycnt2 + 1,");")  from  (select distinct  g.graphid  from graphs g inner join graphs_items  gi on g.graphid=gi.graphid inner join items i on gi.itemid=i.itemid  inner join interface inf on inf.hostid=i.hostid where  g.name like 'Redis 604_ 内存使用'  and i.status=0 and inf.ip like '192.168.1.%' ORDER BY i.hostid) as g into outfile 'cat /tmp/file.txt';
3、cat /tmp/file.txt ，执行insert语句
4、插入完毕后更新ids。
UPDATE ids SET nextid=(select max(screenitemid)  from screens_items) WHERE table_name='screens_items' AND field_name='screenitemid';
字段说明：
i.status  表示监控项的状态 0位启用 1为禁用
resourcetype  0 表示 图形
inf.ip   指定主机ip
width   聚合图形的宽度
height  聚合图形的高度

UPDATE ids SET nextid=(select max(screenitemid)  from screens_items) WHERE table_name='screens_items' AND field_name='screenitemid';
字段说明：
g.name 是原型图形的名称
resourcetype 20表示 图形原型
x  y 表示生成图形的坐标轴
screenitemid 表示screens_items id号
screenid 表示聚合图形的id，存在于主表 screens
resourceid  表示加入图形的id，存在于graphs的主键。
halign  表示图形的横向对齐 0表示居中，1标志靠左

图形  聚合的 批量操作:
图形聚合是特指到每个主机的某一个监控项目。所以设计到主机的ip，监控项名称。
1、在聚合图形创建好一个聚合图形A。找出图形A的ID  (创建图形的时候记得填写好行数和列数)
select screenid from screens WHERE  name='A';
2、生成insert语句。
SELECT concat("INSERT INTO screens_items (resourcetype,width,height,x,y,colspan,rowspan,elements,valign,halign,style,url,dynamic,sort_triggers,max_columns,application,screenid,resourceid,screenitemid) VALUES ('0','250','100','0',",@mycnt := @mycnt + 1,",'1','1','0','0','1','0','','0','0','3','','106',",g.graphid,",",@mycnt2 := @mycnt2 + 1,");")  from  (select distinct  g.graphid  from graphs g inner join graphs_items  gi on g.graphid=gi.graphid inner join items i on gi.itemid=i.itemid  inner join interface inf on inf.hostid=i.hostid where  g.name like 'Redis 604_ 内存使用'  and i.status=0 and inf.ip like '192.168.1.%' ORDER BY i.hostid) as g into outfile 'cat /tmp/file.txt';
3、cat /tmp/file.txt ，执行insert语句
4、插入完毕后更新ids。
UPDATE ids SET nextid=(select max(screenitemid)  from screens_items) WHERE table_name='screens_items' AND field_name='screenitemid';
字段说明：
i.status  表示监控项的状态 0位启用 1为禁用
resourcetype  0 表示 图形
inf.ip   指定主机ip
width   聚合图形的宽度
height  聚合图形的高度

 

转载于:https://www.cnblogs.com/vansky/p/8270382.html