常用信息

h1. 20220315alarmcount

* 请各现场同事将以下sql执行并叫结果导出成excel作为附件上传（如果sql错误请联系我）

* 另外将本地的生产环境告警策略表的数据全部导出成excel上传附件

* 每个地方只上传一个excel，不同数据放到不同呢的sheet即可

<pre><code class="sql">

-- 活动告警信息

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 告警持续时长, 

       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;

</code></pre>