A-view-sql¶

SELECT
 IFNULL(ll.resAlarmCount, 0) alarmCountSum,
 IFNULL(ll.level1, 0) alarmCountL1,
 IFNULL(ll.level2, 0) alarmCountL2,
 IFNULL(ll.level3, 0) alarmCountL3,
 ifnull(ext.P310111, '未知') AS memoryNum,
 IFNULL(hs.ddicName, '优') healthDesc,
 IFNULL(h.health, '3') health, IFNULL(h.score, 100) score, sr.resTypeName, mm.hostType  as resSubType,  mm.* ,
 mm.extendCol3 platform,  ur.nickname adminName, ur.phone adminPhone,  IFNULL(ext.maintenanceProvider,'') maintenanceProvider,  IFNULL(ext.cabinetInfo,'') cabinetInfo FROM
 ( select res.*,
CAST(IFNULL(result.KPI31CB8D97,0) AS SIGNED ) memoryRate,
CAST(IFNULL(case when res.resType LIKE 'DATABASE%' then result.KPI18F18278 else result.KPI7054BC34 end,0) AS SIGNED ) cpuRate,
CAST(IFNULL(result.KPIC40A80AC,0) AS SIGNED ) memorySetRate,
CAST(IFNULL(result.KPIBFA3F5CD,0) AS SIGNED ) cpuSetRate,
CAST(IFNULL(result.KPI46BAA9BE,0) AS SIGNED ) storageSetRate,
CAST(IFNULL(result.KPI5E148DBE,0) AS SIGNED ) storageRate,
IFNULL(result.KPIC70A1E3D,'') resRemark,
CAST(IFNULL(result.KPI98960E55,0) AS SIGNED ) cpuSize,
CAST(IFNULL(result.KPI1584BE1C,0) AS SIGNED ) memorySize,
CAST(IFNULL(result.KPI29D42042,0) AS SIGNED ) diskSize,
IFNULL(result.KPIECA37CB0,'') createTimeStr,
IFNULL(result.KPI1303E638,'') updateTimeStr,
IFNULL(result.KPI72E7FB4B,'异常') pingStatus,
IFNULL(case when res.resType = 'MIDDLEWARE_WEBLOGIC' and ifnull(parentId,'') != '' then result.KPI4DA976AF else result.KPIE13DD9A3 end,'') linkState,
IFNULL(result.KPI1635BB9B,'') hostState,
CAST(IFNULL(result.KPIA55F9590,0) AS SIGNED ) lockNum,
IFNULL(result.KPI64B1610ANAME,'') tableSpaceUseName,
IFNULL(result.KPI64B1610AFLAG,'') tableSpaceUseFlag,
CAST(IFNULL(result.KPI64B1610A,0) AS SIGNED ) tableSpaceUseRate,
IFNULL(DATE_FORMAT(KPIF74D9D2B,'%Y-%m-%d %H:%i:%s'),'') lastColTime,case when result.KPIE13DD9A3 like '%失败%' then result.KPIE13DD9A3 end  message,
CAST(IFNULL(result.KPI449F5365,0) AS SIGNED ) fileSysUseRate,
IFNULL(result.KPI449F5365NAME,'') fileSysUseName,
IFNULL(result.KPI449F5365FLAG,'') fileSysUseFlag,
IFNULL(result.KPI8BCBCA98,'')  powerState,
CAST(IFNULL(result.KPID339D51B,0) AS SIGNED ) maxNetCardUpRate,
IFNULL(result.KPID339D51BNAME,'') maxNetCardUpName,
CAST(IFNULL(result.KPI02062F43,0) AS SIGNED ) maxNetCardDownRate,
IFNULL(result.KPI02062F43NAME,'') maxNetCardDownName,
CAST(IFNULL(result.KPI98183B26,0) AS SIGNED ) diskBusyRate,
IFNULL(result.KPI98183B26NAME,'') diskBusyName,
CAST(IFNULL(result.KPI20352505,0) AS SIGNED ) vmmemoryUseRate,
CAST(IFNULL(result.KPI4A216352,0) AS SIGNED ) memoryUsed,
CAST(IFNULL(result.KPI5B7998C9,0) AS SIGNED ) storageSize,
CAST(IFNULL(result.KPI33D6E74B,0) AS SIGNED ) storageUsed,
CAST(IFNULL(result.KPI5E148DBE,0) AS SIGNED ) storageMaxRate,
IFNULL(result.KPI5E148DBENAME,'') storageMaxRateName,
IFNULL(result.KPIEE008133,'')  clusterState,
CAST(IFNULL(result.KPI63916615,0) AS SIGNED ) netNum,
CAST(IFNULL(result.KPI02EA188F,0) AS SIGNED ) hostNum,
CAST(IFNULL(result.KPI9597B203,0) AS SIGNED ) vhostNum,
IFNULL(result.KPI4EFF9873,'')  ptName,
IFNULL(result.KPID7BE5E90,'')  resourcePool,
IFNULL(result.KPIE4B51D5C,'')  runState,
CAST(IFNULL(result.KPICFA785F5,0) AS SIGNED ) activeSessionNum,
CAST(IFNULL(result.KPI18F18278,0) AS SIGNED ) sessionRate,
IFNULL(result.KPI69E67C68,'')  bakLogState,
IFNULL(result.KPIDF0E1A44,'')  racState,
CAST(IFNULL(result.KPI922F8FF7,0) AS SIGNED ) asmDiskUsedRate,
IFNULL(result.KPI922F8FF7NAME,'') asmDiskUsedRateName,
IFNULL(result.KPI922F8FF7FLAG,'') asmDiskUsedRateFlag,
IFNULL(result.KPI1F9DB62C,'')  logOpenState,
CAST(IFNULL(result.KPI8914AC3B,0) AS SIGNED ) maxLinkNum,
IFNULL(result.KPI4FA6C9E5,'')  linkRate,
IFNULL(result.KPI474A7DD3,'')  slaveQueryState,
IFNULL(result.KPI54855B9D,'')  queryState,
IFNULL(result.KPI4DA976AF,'')  serverState,
IFNULL(result.KPI8F3747C0,'')  serverName,
IFNULL(result.KPI6D99C9E0,'')  listenAddr,
IFNULL(result.KPI98F4266F,'')  listenPort,
IFNULL(result.KPIC18DC28A,'')  queueLength,
IFNULL(result.KPI41A1D9E9,'')  errpt,
IFNULL(result.KPI1F5D2F9D,'')  cacheUsed, IFNULL(result.KPIBF24B5F9,'')  threadTotal, IFNULL(result.KPI13329E0E,'')  exclusiveTotal, IFNULL(result.KPIFB54AE94,'')  throughput, IFNULL(result.KPI5F910316,'')  requestFinished, IFNULL(result.KPI38B2287A,'')  heapRamPercent, IFNULL(result.KPI6546E739,'')  heapRam, IFNULL(result.KPIC624B305,'')  heapRamMax, CAST(IFNULL(result.KPIBDB60856,0) AS SIGNED ) cpuCores,
CAST(IFNULL(result.KPID3EBE40F,0) AS SIGNED ) cpuNum
from  ( select *  from (  select _monitor_a0.* from b_resource      _monitor_a0 )  rmt  where rmt.resType != 'platform' ) res left join b_result_realtime_base result on res.resId = result.resId where 1=1 
 and res.resType like 'HOST_X86%'  )  mm
LEFT JOIN ( SELECT resId,max(case when propKey = 'P310110' then propValue end  ) as 'P310110',
max(case when propKey = 'P310111' then propValue end  ) as 'P310111',
max( CASE WHEN propKey = 'maintenanceProvider' THEN propValue END ) maintenanceProvider,
max( CASE WHEN propKey = 'cabinetPosition' THEN propValue END ) cabinetInfo 
FROM b_resource_property_values group by resId ) ext ON  mm.resId = ext.resId
LEFT JOIN (SELECT tt.resId,sum(case when tt.alarmLevel = '1' THEN 1 else 0 end) as level1,
     sum(case when tt.alarmLevel = '2' THEN 1 else 0 end) as level2,
     sum(case when tt.alarmLevel = '3' THEN 1 else 0 end) as level3,
     sum(case when tt.alarmLevel is not null then 1 else 0 end) as resAlarmCount
    FROM b_alarm tt group by tt.resId
) ll ON  mm.resId = ll.resId
 left join b_health h on mm.resId=h.resId LEFT JOIN (
 SELECT ddicCode, ddicName FROM sys_ddic WHERE  ddicCategory = 'health_state') hs ON  h.health = hs.ddicCode
 left join sys_resourcetype sr on mm.resType=sr.resTypeCode LEFT JOIN `user-center`.sys_user ur ON ur.username = mm.admin where 1=1  and mm.resType like 'HOST_X86%'  ORDER BY health,resType,resName 

-- 全量将资源的所属域改为空
update cloud_backend_monitor.b_resource r set r.extendCol1 = '';
-- 全量根据IP地址更新资源所属域
-- 更新资源所属域信息，A类
update cloud_backend_monitor.b_resource r 
   set r.extendCol1 = (select domainId from cloud_backend_monitor.b_domain_ip di where di.ip = CONCAT(SUBSTRING_INDEX(r.ip,'.',1),'.*.*.'))
 where IFNULL(r.extendCol1, '') = '';
-- 更新资源所属域信息，B类
update cloud_backend_monitor.b_resource r 
   set r.extendCol1 = (select domainId from cloud_backend_monitor.b_domain_ip di where di.ip = CONCAT(SUBSTRING_INDEX(r.ip,'.',2),'.*.'))
 where IFNULL(r.extendCol1, '') = '';
-- 更新资源所属域信息，C类
update cloud_backend_monitor.b_resource r 
   set r.extendCol1 = (select domainId from cloud_backend_monitor.b_domain_ip di where di.ip = CONCAT(SUBSTRING_INDEX(r.ip,'.',3),'.'))
 where IFNULL(r.extendCol1, '') = '';