zabbix 表结构和应用
zabbix 表结构hosts->hostid->templateid
hosts表
hostidhost1v1关系
hosts_templates
hostidtemplateid多v1关系
alerts表是记录已经发送邮件的记录
所以界面上是从哪个表取出来的。
events
SELECT * FROM information_schema.`KEY_COLUMN_USAGE`
WHERE referenced_table_name='events'
因为zabbix有很多外键，所以说这个设计很不错。尤其要注意子表与主表的关联，比如events
所以分析zabbix表结构，首先从外键看起，谁的外键多，谁就是主表，那么就是源头了。
events acknowledged
select * from events where eventid='4516146';
eventid source object objectidclock valueacknowledgedns
45161460 013791 14708154251 1 125453205
5 3 013477 14659622841 097450859
hosts.hostid->hosts_groups.hostid
hosts_groups.groupid->groups.groupid
hosts.hostid->items.hostid
items.itemid->functions.itemid
functions.triggerid->triggers.triggerid
triggers.triggerid->events.objectid
functionid itemid triggerid function parameter
101991001910016diff0
{functionid}>100 意味着itemid(10019)的values>100
意思就是items的值关联function方式，然后triggers是否为1还是0(正常)
sql语法1：
select ht.templateid from hosts_templates ht, hosts h whereh.hostid = ht.hostid
-- and h.hostid = '10084';
解析 hosts的hostid去匹配hosts_templates，所以记录总数为hosts_templates,有重复.
sql语法2: 找出那些有应用模板但是没有items的主机，那些是有故障的
select * from hosts where hostid in
(
SELECT
htt.hostid
FROM
hosts_templates htt
WHERE
htt.templateid = '10143'
AND htt.hostid NOT IN (
SELECT
ii.hostid
FROM
items ii
WHERE
ii.`name` LIKE '%tomcat%'
AND ii.hostid IN (
SELECT
htt.hostid
FROM
hosts_templates htt
WHERE
htt.templateid = '10143'
)
GROUP BY
ii.hostid
)
GROUP BY
htt.hostid
)
sql语法3:查看items中停用的项目
SELECT
b. HOST,
a.itemid,
a.hostid,
a. NAME,
a.key_
FROM
items a,
HOSTS b
WHERE
b.hostid = a.hostid
AND a. STATUS = 1
sql语法4：
create table tmp1 as
(SELECT
`hosts`.`host`,
`triggers`.triggerid,
`triggers`.description,
`triggers`.priority,
`events`.`value`,
FROM_UNIXTIME(`events`.clock) time
FROM
`hosts`,
`triggers`,
`events`,
`items`,
`functions`,
`groups`,
`hosts_groups`
WHERE
`hosts`.hostid = `hosts_groups`.hostid
AND `hosts_groups`.groupid = `groups`.groupid
AND `triggers`.triggerid = `events`.objectid
AND `hosts`.hostid = `items`.hostid
AND `items`.itemid = `functions`.itemid
AND `functions`.triggerid = `triggers`.triggerid);
--告警数据alter表
select FROM_UNIXTIME(clock),sendto,`subject` from alerts
-- where `subject` like '%磁盘%' and
where DATE_FORMAT(FROM_UNIXTIME(clock),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d');
select FROM_UNIXTIME(clock),hh.* from alerts hh where status != 1
and TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME(clock)) < 365;
select FROM_UNIXTIME(clock),hh.* from alerts hh where status != 1
and DATE_FORMAT(NOW(),'%Y-%m-%d') - DATE_FORMAT(FROM_UNIXTIME(clock),'%Y-%m-%d') < 30;
events
select FROM_UNIXTIME(h.clock),h.subject,FROM_UNIXTIME(f.clock),f.* from events f,
(select clock,`subject`,eventid from alerts
where DATE_FORMAT(FROM_UNIXTIME(clock),'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')
) as h
where f.eventid = h.eventid
(32723
select * from functions where itemid = '32723'
15497
select * from `triggers` where triggerid = '15497';
select * from `events`
select 1504618584
select from_unixtime(1504618584,'%Y%m%d %H:%i:%S')) 得到的时间为20170905 21:36:24
select from_unixtime(tt.clock,'%Y%m%d %H:%i:%S'),tt.* from events tt
where eventid in (
select * from alerts order by from_unixtime(clock,'%Y%m%d %H:%i:%S') desc)
order by eventid desc limit 1,5000
hosts
hostid 是ip和模板
select i.itemid,h.host from items i,hosts h where i.hostid=h.hostid and h.host='xxxx' and i.name in ('regionserver writeRequestsCount','regionserver requests');
详细步骤
select * from hosts where host='192.1.1.206';hostid
select * from items where hostid='10084';
select * from items where hostid='10084' and name like '%war%';itemid
总体
hosts,items 联合查询itemid,然后通过itemid到history_unit查询更新记录
报警trigger条件，记录到triggers表中，然后符合条件的过滤到alerts，就会发送出去
主屏幕最近20个问题，表示的是获取到的值一直没有改变。去striggers查询
如果一直没发邮件出去，那么就会挂在web界面上，然后即使你action了，也不会去从数据库执行那套操作。就会一直挂着，除非你改变状态。
问题
1.为什么在主屏幕上显示，而不是发送邮件。
triggers表，alters表没有数据
CREATE TABLE `triggers` (
`triggerid` bigint(20) unsigned NOT NULL,
`expression` varchar(2048) NOT NULL DEFAULT '',
`description` varchar(255) NOT NULL DEFAULT '',
`url` varchar(255) NOT NULL DEFAULT '',
`status` int(11) NOT NULL DEFAULT '0',
`value` int(11) NOT NULL DEFAULT '0',
`priority` int(11) NOT NULL DEFAULT '0',
`lastchange` int(11) NOT NULL DEFAULT '0',
`comments` text NOT NULL,
`error` varchar(128) NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned DEFAULT NULL,
`type` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`triggerid`),
KEY `triggers_1` (`status`),
KEY `triggers_2` (`value`,`lastchange`),
KEY `triggers_3` (`templateid`),
CONSTRAINT `c_triggers_1` FOREIGN KEY (`templateid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
页:
[1]