zabbix 告警数据查询语句

分享一些总结的zabbix告警查询语句
1、查询zabbix过去一周群组告警总数

SELECT
        a.hostid,
        a.`host`,
        a.`status`,
        a.`name` AS hostnname,
        b.`name` AS groupname,
        d.`name` AS item,
        FROM_UNIXTIME( e.clock ) AS eventtime,
        e.`name`,
        e.severity 
FROM
        hosts a
        JOIN hosts_groups c ON a.hostid = c.hostid
        JOIN hstgrp b ON b.groupid = c.groupid 
        AND b.NAME LIKE '%系统组%'
        JOIN items d ON a.hostid = d.hostid 
        AND d.flags <> 1 
        AND d.flags <> 2
        JOIN ( SELECT itemid, triggerid FROM functions GROUP BY itemid, triggerid ) f ON d.itemid = f.itemid
        JOIN events e ON f.triggerid = e.objectid 
        AND e.`value` = 1 
        AND yearweek( FROM_UNIXTIME( e.clock ), 1 )= yearweek( now(), 1 ) 
ORDER BY
        e.clock

2、查询zabbix所有监控指标触发器和告警等级

SELECT * FROM 
(SELECT a.hostid,b.`name` AS 系统名称,a.`host` AS 地址,d.`name` AS 监控项,d.key_  as 键值,d.itemid
   from `hosts` a,hstgrp b,hosts_groups c,items d
      where a.available  = 1 and a.hostid =c.hostid and b.groupid = c.groupid
          AND b.`name` LIKE '%系统%'  
             AND a.hostid = d.hostid AND d.key_ not LIKE '%#%'  AND d.key_ not like '%IPADDRESS%' AND d.name like "%进程%"
                 ORDER BY b.`name`,a.`host`) A 
                    LEFT JOIN (SELECT m.itemid,m.`name` as 触发器函数,m.parameter as 触发器参数,n.expression as 触发器条件,n.recovery_mode AS 恢复模式,n.recovery_expression,
                                   CASE  
                                         WHEN n.priority =5 THEN '一级' 
																				 WHEN n.priority =4 THEN '二级'
                                         WHEN n.priority =3 THEN '三级' 
                                         WHEN n.priority =2 THEN '四级' 
                                         WHEN n.priority =1 THEN '五级' 
                                         WHEN n.priority =0 THEN '六级' 
                                         END AS 告警等级
                                from  functions m,`triggers`n  
                                   where n.triggerid=m.triggerid  
                                     --  GROUP BY itemid
                               ) B ON  A.itemid = B.itemid

3、导出zabbix群组所有监控主机指标

SELECT * FROM 
(SELECT a.hostid,b.`name` AS 系统名称,a.`host` AS 地址,d.`name` AS 监控项,d.key_  as 键值,d.itemid
   from `hosts` a,hstgrp b,hosts_groups c,items d
      where a.available  = 1 and a.hostid =c.hostid and b.groupid = c.groupid
          AND b.`name` LIKE '%系统%'  
             AND a.hostid = d.hostid  AND d.name like "%进程%"
                 ORDER BY b.`name`,a.`host`) A 
                    LEFT JOIN (SELECT m.itemid,m.`name` as 触发器函数,m.parameter as 触发器参数,n.expression as 触发器条件 
                                from  functions m,`triggers`n  
                                   where n.triggerid=m.triggerid  
                                     --  GROUP BY itemid
                               ) B ON  A.itemid = B.itemid
							   

4、导出zabbix群组所有监控主机

SELECT b.`name` AS 系统名称,a.`host` AS 地址
   from `hosts` a,hstgrp b,hosts_groups c
        -- where a.`status`  = 1
        WHERE a.hostid =c.hostid and b.groupid = c.groupid  AND a.`status` <> 3
          AND (b.`name` LIKE '%45-%')
         --    AND A.hostid = D.hostid AND d.key_ not LIKE '%#%'  AND d.key_ not like '%IPADDRESS%'
                 ORDER BY b.`name`,a.`host`

5、查看所有监控项

SELECT
    *
FROM
    (
        SELECT
            a.hostid,
            b.`name` AS 系统名称,
            a.`host` AS 地址,
            d.`name` AS 监控项,
            d.key_ AS 键值,
            d.itemid
        FROM
            `hosts` a,
            hstgrp b,
            hosts_groups c,
            items d
        WHERE
            a.available = 1
        AND a.hostid = c.hostid
        AND b.groupid = c.groupid
        AND b.`name` LIKE '%45-%'
        AND A.hostid = D.hostid
        AND d.key_ NOT LIKE '%#%'
        AND d.key_ NOT LIKE '%IPADDRESS%'
        AND d.key_ NOT LIKE '%discovery%'
        ORDER BY
            b.`name`,
            a.`host`
    ) A
LEFT JOIN (
    SELECT
        m.itemid,
        m.nameparameter AS 触发器函数参数,
        -- m.parameter AS 触发器参数,
        n.expression AS 触发器条件,
        n.recovery_mode AS 恢复模式,
        CASE
    WHEN n.priority = 5 THEN '一级'
    WHEN n.priority = 4 THEN '二级'
    WHEN n.priority = 3 THEN '三级'
    WHEN n.priority = 2 THEN '四级'
    WHEN n.priority = 1 THEN '五级'
    WHEN n.priority = 0 THEN '六级'
    END AS 告警等级
    FROM
        (
            SELECT
                m.itemid,
                m.triggerid,
                CONCAT(GROUP_CONCAT(
                    CONCAT(NAME, '(', parameter, ')')
                )) nameparameter
            FROM
                functions m
            GROUP BY
                m.itemid,
                m.triggerid
        ) m,
        `triggers` n
    WHERE
        n.triggerid = m.triggerid
) B ON A.itemid = B.itemid

另一种写法

select name,count(name) '发送次数',from_unixtime(clock) '发生时间' 
from events where clock in (select clock from events where TIMESTAMPDIFF(day,from_unixtime(clock,'%Y-%m-%d'),current_date)<30) and value=1 and source=0 
group by name order by count(name) desc

6、查询最近30天zabbix告警数据

SELECT
        a.hostid ,
        a.`host`,
        a.`name` AS hostnname,
        b.`name` AS groupname,
        FROM_UNIXTIME( e.clock ) AS eventtime,
        e.`name`,
        e.severity 
FROM
        hosts a
        JOIN hosts_groups c ON a.hostid = c.hostid
        JOIN hstgrp b ON b.groupid = c.groupid 
        AND b.NAME LIKE '%系统组%'
        JOIN items d ON a.hostid = d.hostid 
        AND d.flags <> 1 
        AND d.flags <> 2
        JOIN ( SELECT itemid, triggerid FROM functions GROUP BY itemid, triggerid ) f ON d.itemid = f.itemid
        JOIN events e ON f.triggerid = e.objectid 
        AND e.`value` = 1 
				AND e.clock in (select e.clock from events e where TIMESTAMPDIFF(day,from_unixtime(e.clock,'%Y-%m-%d'),current_date)<30)
        
ORDER BY e.clock

以上均可自行尝试，修改查询但是由于zabbix数据库量比较大，经常会导致查询时长过大，请先测试后使用。