数据库sql脚本¶

综合监控常用业务相关sql

> 资源授权-虚拟化资源授权¶

-- 给当前还未授权给业务系统“虚拟化平台”的资源授权，该授权脚本sql可以重复执行，每次会都会查询未授权的资源进行授权操作
insert into b_resource_bustype
select MD5(CONCAT(resId,'d75205d9ca124b7ab85f9e4ef57aff3b')), resId, 'd75205d9ca124b7ab85f9e4ef57aff3b' 
  from b_resource r
 where resType like '%VIRTUALIZATION_%'
   and not EXISTS(select 1 from b_resource_bustype rb where rb.resId = r.resId);

> 采集指标及相关信息查询¶

-- 获取所有在采资源类型、指标、采集协议、采集频率等信息
select restypeName, 
       -- resTypeCode, 
       kpiName, 
       -- kpiId, 
             kpiIdent, 
             case when kpiName in ('最近采集时间','PING状态','监控连接状态') then 'BASE' 
                  when restypeName = '华为云' then 'HTTPS' 
                        when kpiName in ('应用端口状态', '端口号', '端口描述', '端口类型') then 'SSH' 
                        when restypeName in ('列头柜', '配电柜', 'UPS', '输出柜', '精密空调', '温湿度感应器', '漏水') then 'HTTP/JDBC/SNMP' 
                        else tempType end tempType, 
             collFrequency 
  from (
select rt.resTypeCode, rt.restypeName, rt.sort, tab.kpiId, k.kpiName, LEFT(tab.flag, 5) flag, IFNULL(c.tempType, (select collScope from b_coll_dirctive bcd where bcd.dirctiveId = tab.directiveId)) tempType, CONCAT(IFNULL(cd.collFrequency, '5'), '分钟') collFrequency, 
       case when k.kpiIdent = '0' then '基本指标' when k.kpiIdent = '1' then '性能指标' when k.kpiIdent = '2' then '状态指标' when k.kpiIdent = '3' then '告警指标' else '' end kpiIdent
  from (select case when resType like 'HOST_MINICOMPUTER_%' then 'HOST_MINICOMPUTER_PARTITION' else resType end resType, kpiId, max(flag) flag, templateId, directiveId
          from b_result_realtime_collector rrc1 group by resType,kpiId
         union
        select case when resType like 'HOST_MINICOMPUTER_%' then 'HOST_MINICOMPUTER_PARTITION' else resType end resType, kpiId, max(flag) flag, templateId, directiveId
          from b_result_realtime_collector_tree rrc2 group by resType,kpiId) tab 
    left join b_kpi k on k.kpiId = tab.kpiId
  left join sys_resourcetype rt on rt.resTypeCode = tab.resType
  left join b_checktemplate c on c.checkTempId = tab.templateId
  left join b_checktemplate_dirctive cd on cd.checkTempId = tab.templateId and cd.dirctiveId = tab.directiveId
-- where resType like '%TOMCAT%'
 group by tab.resType, tab.kpiId, k.kpiName) t
 order by sort, kpiIdent, flag, kpiName;

> 快照数据查询¶

-- 获取资源cpu内存使用率采集结果
select r.resName 名称, r.ip IP地址, rrc1.kpiValue 监控连接状态, CONCAT(rrc2.kpiValue, '%') CPU使用率, CONCAT(rrc3.kpiValue,'%') 内存使用率
 from b_resource r
 left join b_result_realtime_collector rrc1 on rrc1.resId = r.resId and rrc1.kpiId = 'KPIE13DD9A3'
 left join b_result_realtime_collector rrc2 on rrc2.resId = r.resId and rrc2.kpiId = 'KPI7054BC34'
 left join b_result_realtime_collector rrc3 on rrc3.resId = r.resId and rrc3.kpiId = 'KPI31CB8D97'
 where r.resType like '%X86%'
 order by r.ip;

-- 获取数据库的asm采集结果
select distinct tab.resName 名称, tab.ip IP地址, tab.flag, rpp.paramValue 实例名, rrc1.kpiValue 磁盘组名称, CONCAT(rrc2.kpiValue,'%') ASM磁盘使用率, rrc3.kpiValue 磁盘组总容量, rrc4.kpiValue 磁盘组已使用容量, rrc5.kpiValue 磁盘组空闲容量
    from (select r.resName, r.ip, r.resId, rrc.flag, rrc.kpiValue fname
          from b_resource r
                    left join b_result_realtime_collector rrc on rrc.resId = r.resId 
         where r.resType = 'DATABASE_ORACLE' and kpiId in ('KPI50267025','KPI922F8FF7','KPI4D710FDA','KPI811E751E','KPIF7B8841E')) tab
                left join b_result_realtime_collector rrc1 on rrc1.resId = tab.resId and rrc1.flag = tab.flag and rrc1.kpiId = 'KPI50267025'
                left join b_result_realtime_collector rrc2 on rrc2.resId = tab.resId and rrc2.flag = tab.flag and rrc2.kpiId = 'KPI922F8FF7'
                left join b_result_realtime_collector rrc3 on rrc3.resId = tab.resId and rrc3.flag = tab.flag and rrc3.kpiId = 'KPI4D710FDA'
                left join b_result_realtime_collector rrc4 on rrc4.resId = tab.resId and rrc4.flag = tab.flag and rrc4.kpiId = 'KPI811E751E'
                left join b_result_realtime_collector rrc5 on rrc5.resId = tab.resId and rrc5.flag = tab.flag and rrc5.kpiId = 'KPIF7B8841E'
                left join b_resource_protocol_param_values rpp on rpp.resId = tab.resId and rpp.paramCode = 'SIDName'
 where 1=1
 order by tab.ip , tab.resName;

> 资源类型相关信息统计sql¶

-- 资源类型相关信息统计sql
select rt.resTypeCode 资源类型, rt.resTypeName 资源名称, t1.resCount 录入资源量, 
       IFNULL(t2.collCount, 0) 监控资源量, IFNULL(t3.connSuccessCount, 0) 连接成功资源量, 
             IFNULL(t4.connFailCount, 0) 连接失败资源量, IFNULL(t5.lt3rowsCount, 0) 快照记录数少于等于三条资源量,
             IFNULL(collTime6hAgoCount, 0) 最近采集时间在六小时前的资源量,
             IFNULL(alarmCount, 0) 活动告警量, IFNULL(alarmHisCount, 0) 历史告警量,
             IFNULL(alarmNoticeCount, 0) 待发送通知量, IFNULL(alarmNoticeHisCount, 0) 已发送通知量
    from sys_resourcetype rt
  left join (select resType, count(resId) resCount from b_resource r group by r.resType) t1 on t1.resType = rt.resTypeCode
  left join (select resType, count(distinct resId) collCount from b_result_realtime_collector rrc where exists(select 1 from b_resource r where r.resId = rrc.resId) group by rrc.resType) t2 on t2.resType = rt.resTypeCode
  left join (select resType, count(distinct resId) connSuccessCount from b_result_realtime_collector rrc where kpiId = 'KPIE13DD9A3' and kpiValue = '连接成功' and exists(select 1 from b_resource r where r.resId = rrc.resId) group by rrc.resType) t3 on t3.resType = rt.resTypeCode
  left join (select resType, count(distinct resId) connFailCount from b_result_realtime_collector rrc where kpiId = 'KPIE13DD9A3' and kpiValue = '连接失败' and exists(select 1 from b_resource r where r.resId = rrc.resId) group by rrc.resType) t4 on t4.resType = rt.resTypeCode
  left join (select resType, count(1) lt3rowsCount from (select resId, (select resType from b_resource rr where rr.resId = a.resId) resType, count(1) from b_result_realtime_collector a group by resId having count(1) <= 3) t0 where resType is not null group by resType) t5 on t5.resType = rt.resTypeCode
  left join (select resType, count(distinct resId) collTime6hAgoCount from b_result_realtime_collector rrc where kpiId = 'KPIF74D9D2B' and kpiValue < (now() - interval 6 hour) and exists(select 1 from b_resource r where r.resId = rrc.resId) group by rrc.resType) t51 on t51.resType = rt.resTypeCode
    left join (select resType, count(alarmId) alarmCount from b_alarm r group by r.resType) t6 on t6.resType = rt.resTypeCode
  left join (select resType, count(alarmId) alarmHisCount from b_alarm_his r group by r.resType) t7 on t7.resType = rt.resTypeCode
    left join (select resType, count(noticeId) alarmNoticeCount from b_alarm_notice r group by r.resType) t8 on t8.resType = rt.resTypeCode
  left join (select resType, count(noticeId) alarmNoticeHisCount from b_alarm_notice_his r group by r.resType) t9 on t9.resType = rt.resTypeCode
 where t1.resCount is not null
 order by rt.sort;

> 业务相关信息统计sql¶

select b.busId 业务id, busTypeName 业务名称, IFNULL(t1.resCount, 0) 业务资源量,
       IFNULL(t2.connSuccessCount, 0) 连接成功资源量, IFNULL(t3.connFailCount, 0) 连接失败资源量, 
             IFNULL(alarmCount, 0) 活动告警量, IFNULL(noticeCount, 0) 待发送通知量
    from b_bustype b
  left join (select busId, count(resId) resCount from b_resource_bustype bt group by bt.busId) t1 on t1.busId = b.busId
    left join (select busId, count(distinct rrc.resId) connSuccessCount from b_result_realtime_collector rrc, b_resource_bustype rb where rrc.resId = rb.resId and kpiId = 'KPIE13DD9A3' and kpiValue = '连接成功' and exists(select 1 from b_resource r where r.resId = rrc.resId) group by rb.busId) t2 on t2.busId = b.busId
    left join (select busId, count(distinct rrc.resId) connFailCount from b_result_realtime_collector rrc, b_resource_bustype rb  where rrc.resId = rb.resId and kpiId = 'KPIE13DD9A3' and kpiValue = '连接失败' and exists(select 1 from b_resource r where r.resId = rrc.resId) group by rb.busId) t3 on t3.busId = b.busId
    left join (select busId, count(1) alarmCount from b_alarm a, b_resource_bustype rb where a.resId = rb.resId group by rb.busId) t4 on t4.busId = b.busId
    left join (select busId, count(1) noticeCount from b_alarm_notice a, b_resource_bustype rb where a.resId = rb.resId group by rb.busId) t5 on t5.busId = b.busId
 order by b.sort;