Actions
20220315alarmcount¶
- 请各现场同事将以下sql执行并叫结果导出成excel作为附件上传(如果sql错误请联系我)
- 另外将本地的生产环境告警策略表的数据全部导出
- 每个地方只上传一个excel,不同数据放到不同呢的sheet即可
-- 活动告警信息
select * from b_alarm order by kpiId, flag;
-- 指标告警量统计
select kpiId, kpiName, count(1) alarmCnt
from cloud_backend_monitor.b_alarm a
group by kpiId
order by count(1) desc;
-- 资源类型告警量统计
select resType, resTypeName, count(1) alarmCnt
from cloud_backend_monitor.b_alarm a
left join sys_resourcetype rt on rt.resTypeCode = a.resType
group by resType
order by count(1) desc;
-- 获取活动告警情况,按照告警级别和告警次数排序
select a.alarmId, resType, resTypeName, a.resId, a.resName, a.kpiId, a.kpiName, a.flag, a.alarmLevel, a.alarmRepeatCnt,
case when TIMESTAMPDIFF(HOUR, a.alarmTime, a.updateTime) < 24 then CONCAT(TIMESTAMPDIFF(HOUR, a.alarmTime, a.updateTime),'小时')
else CONCAT(ROUND(TIMESTAMPDIFF(HOUR, a.alarmTime, a.updateTime)/24),'天') end alarmTimes,
a.alarmTime, a.updateTime, a.alarmContent
from cloud_backend_monitor.b_alarm a
left join sys_resourcetype rt on rt.resTypeCode = a.resType
order by alarmLevel desc, alarmRepeatCnt desc;
-- 告警主体总告警次数
select alarmId, resTypeName, resId, resName, kpiId, kpiName, flag, count(alarmId) alarmCnt
from (select resType, alarmId, resId, resName, kpiId, kpiName, flag from b_alarm
union all
select resType, alarmId, resId, resName, kpiId, kpiName, flag from b_alarm_his) a
left join sys_resourcetype rt on rt.resTypeCode = a.resType
group by alarmId
order by count(1) desc;
-- 指标告警次数
select resTypeName, resId, resName, kpiId, kpiName, flag, count(alarmId) alarmCnt
from (select resType, alarmId, resId, resName, kpiId, kpiName, flag from b_alarm
union all
select resType, alarmId, resId, resName, kpiId, kpiName, flag from b_alarm_his) a
left join sys_resourcetype rt on rt.resTypeCode = a.resType
group by kpiId
order by count(1) desc;
-- 资源类型告警次数
select resType, resTypeName, count(alarmId) alarmCnt
from (select resType, alarmId, resId, resName, kpiId, kpiName, flag from b_alarm
union all
select resType, alarmId, resId, resName, kpiId, kpiName, flag from b_alarm_his) a
left join sys_resourcetype rt on rt.resTypeCode = a.resType
group by resType
order by count(1) desc;
-- 告警持续时长统计
select a.alarmId, resType, resTypeName, a.resId, a.resName, a.kpiId, a.kpiName, a.flag, a.alarmLevel, a.alarmRepeatCnt,
case when TIMESTAMPDIFF(HOUR, max(a.alarmTime), max(a.clearTime)) < 24 then CONCAT(TIMESTAMPDIFF(HOUR, max(a.alarmTime), max(a.clearTime)),'小时')
else CONCAT(ROUND(TIMESTAMPDIFF(HOUR, max(a.alarmTime), max(a.clearTime))/24),'天') end continueTime,
a.alarmTime, a.updateTime, a.alarmContent
from cloud_backend_monitor.b_alarm_his a
left join sys_resourcetype rt on rt.resTypeCode = a.resType
group by alarmId
order by alarmLevel desc, alarmRepeatCnt desc;