use lawe; -- （1）基础设施 -- 建表语句 DROP TABLE `bi_itm_hardware_resources`; CREATE TABLE `bi_itm_hardware_resources` ( `itm_hardware_resources_id` varchar(100) DEFAULT NULL COMMENT '设备ID', `itm_hardware_resources_type` varchar(100) DEFAULT NULL COMMENT '设备型号ID', `itm_hardware_resources_cntype` varchar(100) DEFAULT NULL COMMENT '设备型号归类', `itm_hardware_resources_areacode` varchar(100) DEFAULT NULL COMMENT '设备区域', `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资码暂时用来存储设备所属应用名称', `ITM_HARDWARE_RESOURCES_CODE` varchar(200) DEFAULT NULL COMMENT '设备编码', `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称', `ITM_HARDWARE_RESOURCES_IP` varchar(64) DEFAULT NULL COMMENT '设备IP', `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '使用状态：在用，停用', `itm_monitor_flag` varchar(10) DEFAULT NULL COMMENT '监控状态：监控，未监控', `create_time` datetime DEFAULT NULL COMMENT '设备创建时间', `NAME` varchar(512) DEFAULT NULL COMMENT '设备名称', `resclasscnname` varchar(255) DEFAULT NULL COMMENT '设备类型', `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系', `ROOM_JIFANG` varchar(64) DEFAULT NULL COMMENT '所属机房', `UID` varchar(100) DEFAULT NULL COMMENT 'UID', `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌', `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号', `mac` varchar(255) DEFAULT NULL COMMENT 'mac地址', `guoziguishurenyuan` varchar(255) DEFAULT NULL COMMENT '国资归属人员', `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门', `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员', `servicename` varchar(255) DEFAULT NULL COMMENT '服务app名', `relatedappid` varchar(255) DEFAULT NULL COMMENT '关联appid', `mantancevendor` varchar(255) DEFAULT NULL COMMENT '运维厂商', `cloud` varchar(255) DEFAULT NULL COMMENT '云平台', KEY `bi_itm_hardware_resources_itm_hardware_resources_id_IDX` (`itm_hardware_resources_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_itm_hardware_resources; insert into bi_itm_hardware_resources SELECT IzGoY.itm_hardware_resources_id, IzGoY.itm_hardware_resources_type, case when IzGoY.itm_hardware_resources_type = '000100001' then '物理机' when IzGoY.itm_hardware_resources_type = '000100002' then '云主机' when IzGoY.itm_hardware_resources_type = '000100004' then '宿主机' when substr(IzGoY.itm_hardware_resources_type,1,4) = '0001' then '其他主机' when IzGoY.itm_hardware_resources_type = '000200001' then '交换机' when IzGoY.itm_hardware_resources_type = '000200002' then '路由器' when IzGoY.itm_hardware_resources_type = '000300001' then '存储设备' when substr(IzGoY.itm_hardware_resources_type,1,4) = '0004' then '安全设备' else '非资产设备' end as itm_hardware_resources_cntype, case when IzGoY.itm_hardware_resources_areacode is null then '无区域' else IzGoY.itm_hardware_resources_areacode end as itm_hardware_resources_areacode, IzGoY.ITM_HARDWARE_RESOURCES_ASSNO, IzGoY.ITM_HARDWARE_RESOURCES_CODE, IzGoY.ITM_HARDWARE_RESOURCES_NAME, IzGoY.ITM_HARDWARE_RESOURCES_IP, IzGoY.ITM_HARDWARE_RESOURCES_STATUS, case when IzGoY.itm_monitor_flag = '1' then '监控' else '未监控' end as itm_monitor_flag, case when IzGoY.create_time is null then IzGoY.update_time else IzGoY.create_time end as create_time, XVuSA. NAME, -- dHSPj.resclasscnname, dHSPj.ITM_HARDWARE_RESOURCES_TYPE_NAME as resclasscnname, AIiKi.ITM_APPCATAGORY_NAME as NET_NAME, case when thd.ITM_HARDWARE_RESOURCES_NAME is null then '无机房' else thd.ITM_HARDWARE_RESOURCES_NAME end as ROOM_JIFANG, Qfqdl.UID, Qfqdl.pinpai, Qfqdl.xinghao, Qfqdl.mac, Qfqdl.guoziguishurenyuan, Qfqdl.guoziguishubumen, case when IzGoY.itm_hardware_resources_type = '000100002' then Qfqdl.business_director else Qfqdl.yunweirenyuan end as yunweirenyuan, Qfqdl.servicename, Qfqdl.relatedappid, case when IzGoY.itm_hardware_resources_type = '000100002' then Qfqdl.contractor else Qfqdl.mantancevendor end as mantancevendor, IzGoY.cloud FROM itm_hardware_resources IzGoY LEFT JOIN (SELECT ITM_HARDWARE_RESOURCES_ID AS UID, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'brand_name') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS pinpai, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'model') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS xinghao, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'macaddress') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS mac, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'manager') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS guoziguishurenyuan, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'department') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS guoziguishubumen, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'mantance_duty') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS yunweirenyuan, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'service_name') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS servicename, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'related_app_id') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS relatedappid, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'mantance_vendor') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS mantancevendor, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'business_director') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS business_director, max((CASE WHEN (ITM_HARDWARE_RESOURCES_PROCODE = 'contractor') THEN ITM_HARDWARE_RESOURCES_VALUE ELSE '' END)) AS contractor FROM itm_hardware_resources_value GROUP BY ITM_HARDWARE_RESOURCES_ID ) Qfqdl ON IzGoY.ITM_HARDWARE_RESOURCES_ID = Qfqdl.UID LEFT JOIN pub_organ XVuSA ON IzGoY.ORGAN_ID = XVuSA. CODE LEFT JOIN itm_appcatagory AIiKi ON IzGoY.ITM_HARDWARE_RESOURCES_NETCODE = AIiKi.ITM_APPCATAGORY_CODE -- LEFT JOIN (select distinct case when substr(resclassenname,1,2) = 'P_' then substr(resclassenname,3) else resclassenname end as resclassenname,resclasscnname from m_resclass) dHSPj ON IzGoY.ne_type = dHSPj.resclassenname left join (select ITM_HARDWARE_RESOURCES_TYPE,ITM_HARDWARE_RESOURCES_TYPE_NAME from itm_hardware_resources_type) dHSPj on IzGoY.ITM_HARDWARE_RESOURCES_TYPE = dHSPj.ITM_HARDWARE_RESOURCES_TYPE left join itm_hardware_resources thd on IzGoY.ITM_HARDWARE_RESOURCES_ROOMCODE = thd.itm_hardware_resources_id; -- （2）基础设施告警、基础设施故障告警、网络中断趋势、应用系统告警趋势、应用系统故障趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_alm_group_by_time`; CREATE TABLE `bi_alm_group_by_time` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期格式', `thedvalue` varchar(17) DEFAULT NULL COMMENT '告警时间', `alarm_object_ip` varchar(300) DEFAULT NULL COMMENT '告警设备IP', `alarm_object_type` varchar(50) NOT NULL DEFAULT '' COMMENT '设备类型', `ITM_HARDWARE_RESOURCES_AREACODE` varchar(64) DEFAULT NULL COMMENT '告警设备区域', `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资编码', `ITM_HARDWARE_RESOURCES_CODE` longtext COMMENT '设备编码', `ITM_HARDWARE_RESOURCES_NAME` varchar(512) DEFAULT NULL COMMENT '设备名称', `ITM_HARDWARE_RESOURCES_IP` varchar(300) DEFAULT NULL COMMENT '设备IP', `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌', `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号', `ITM_HARDWARE_RESOURCES_NETNAME` varchar(255) DEFAULT NULL COMMENT '网系名称', `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '设备状态', `severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警等级', `title` varchar(256) DEFAULT NULL COMMENT '告警标题', `alarm_text` mediumtext COMMENT '告警内容', `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门', `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员', `alarm_count` double DEFAULT NULL COMMENT '告警次数', `alarm_time` decimal(58,4) DEFAULT NULL COMMENT '告警时长(h)', `alarm_zy_num` decimal(42,0) DEFAULT NULL COMMENT '故障次数', `total_zy_num` decimal(29,8) DEFAULT NULL COMMENT '总资产数（取平均）', `process_type_name` varchar(64) DEFAULT NULL COMMENT '故障类型', `app_id` varchar(64) DEFAULT NULL COMMENT '应用ID' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 涉及视图 create or replace view `v_bi_alm_all_alarm` as select `t_alm_active_alarm`.`alarm_object_ip` as `alarm_object_ip`, `t_alm_active_alarm`.`alarm_object_id` as `alarm_object_id`, `t_alm_active_alarm`.`alarm_object_type` as `alarm_object_codetype`, (case when (`t_alm_active_alarm`.`alarm_object_type` = '000100001') then '物理机' when (`t_alm_active_alarm`.`alarm_object_type` = '000100002') then '云主机' when (`t_alm_active_alarm`.`alarm_object_type` = '000100004') then '宿主机' when (substr(`t_alm_active_alarm`.`alarm_object_type`, 1, 4) = '0001') then '其他主机' when (`t_alm_active_alarm`.`alarm_object_type` = '000200001') then '交换机' when (`t_alm_active_alarm`.`alarm_object_type` = '000200002') then '路由器' when (`t_alm_active_alarm`.`alarm_object_type` = '000300001') then '存储设备' when (substr(`t_alm_active_alarm`.`alarm_object_type`, 1, 4) = '0004') then '安全设备' when (substr(`t_alm_active_alarm`.`alarm_object_type`, 1, 4) = '0014') then '网络' when ((`t_alm_active_alarm`.`alarm_object_type` like '%0012%') or (`t_alm_active_alarm`.`alarm_object_type` like '%app%')) then '应用' else '其它未知类型' end) as `alarm_object_type`, `t_alm_active_alarm`.`alarm_object_name` as `alarm_object_name`, (`t_alm_active_alarm`.`alarm_count` + 0) as `alarm_count`, `t_alm_active_alarm`.`severity_id` as `severity_id`, `t_alm_active_alarm`.`occur_time` as `occur_time`, (case when isnull(`t_alm_active_alarm`.`update_time`) then `t_alm_active_alarm`.`insert_time` else `t_alm_active_alarm`.`update_time` end) as `end_time`, `t_alm_active_alarm`.`title` as `title`, `t_alm_active_alarm`.`alarm_text` as `alarm_text`, `t_alm_active_alarm`.`clr_status` as `clr_status`, 1 as `is_active_alarm` from `t_alm_active_alarm` union all select `t_alm_history_alarm`.`alarm_object_ip` as `alarm_object_ip`, `t_alm_history_alarm`.`alarm_object_id` as `alarm_object_id`, `t_alm_history_alarm`.`alarm_object_type` as `alarm_object_codetype`, (case when (`t_alm_history_alarm`.`alarm_object_type` = '000100001') then '物理机' when (`t_alm_history_alarm`.`alarm_object_type` = '000100002') then '云主机' when (`t_alm_history_alarm`.`alarm_object_type` = '000100004') then '宿主机' when (substr(`t_alm_history_alarm`.`alarm_object_type`, 1, 4) = '0001') then '其他主机' when (`t_alm_history_alarm`.`alarm_object_type` = '000200001') then '交换机' when (`t_alm_history_alarm`.`alarm_object_type` = '000200002') then '路由器' when (`t_alm_history_alarm`.`alarm_object_type` = '000300001') then '存储设备' when (substr(`t_alm_history_alarm`.`alarm_object_type`, 1, 4) = '0004') then '安全设备' when (substr(`t_alm_history_alarm`.`alarm_object_type`, 1, 4) = '0014') then '网络' when ((`t_alm_history_alarm`.`alarm_object_type` LIKE '%0012%') or (`t_alm_history_alarm`.`alarm_object_type` like '%app%')) then '应用' else '其它未知类型' end) as `alarm_object_type`, `t_alm_history_alarm`.`alarm_object_name` as `alarm_object_name`, (`t_alm_history_alarm`.`alarm_count` + 0) as `alarm_count`, `t_alm_history_alarm`.`severity_id` as `severity_id`, `t_alm_history_alarm`.`occur_time` as `occur_time`, (case when isnull(`t_alm_history_alarm`.`clear_time`) then `t_alm_history_alarm`.`insert_time` else `t_alm_history_alarm`.`clear_time` end) as `end_time`, `t_alm_history_alarm`.`title` as `title`, `t_alm_history_alarm`.`alarm_text` as `alarm_text`, `t_alm_history_alarm`.`clr_status` as `clr_status`, 0 as `is_active_alarm` from `t_alm_history_alarm`; truncate table bi_alm_group_by_time; insert into bi_alm_group_by_time select 'HOUR' AS dtype, DATE_FORMAT(occur_time,'%Y%m%d%H') as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count, sum(alarm_time) as alarm_time,count(1) as alarm_zy_num, (select count(1) from bi_itm_hardware_resources where itm_hardware_resources_cntype != '非资产设备') as total_zy_num, process_type_name, app_id from ( select a.occur_time, a.alarm_object_id, a.alarm_object_ip, a.alarm_object_type, b.ITM_HARDWARE_RESOURCES_AREACODE, b.ITM_HARDWARE_RESOURCES_ASSNO, b.ITM_HARDWARE_RESOURCES_CODE, b.ITM_HARDWARE_RESOURCES_NAME, b.ITM_HARDWARE_RESOURCES_IP, b.pinpai, b.xinghao,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.ITM_HARDWARE_RESOURCES_STATUS, case a.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type, a.title, a.alarm_text, b.guoziguishubumen, b.yunweirenyuan, a.is_active_alarm, a.alarm_count, timestampdiff(MINUTE,a.occur_time,a.end_time)*1.0/60 as alarm_time, wf.process_type_name,wrr.app_id from (select * from v_bi_alm_all_alarm where alarm_object_type not in ('网络','应用')) a left join bi_itm_hardware_resources b on a.alarm_object_id = b.itm_hardware_resources_id LEFT JOIN icm_bpm.t_work_resource_real wrr on wrr.object_id = a.alarm_object_id left join icm_bpm.t_work_flow wf on wf.id=wrr.work_id) m group by DATE_FORMAT(occur_time,'%Y%m%d%H'), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id; insert into bi_alm_group_by_time select 'DAY' AS dtype, substr(thedvalue,1,8) as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num, process_type_name, app_id from bi_alm_group_by_time where dtype = 'HOUR' and alarm_object_type not in ('网络','应用') group by substr(thedvalue,1,8), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id; insert into bi_alm_group_by_time select 'MONTH' AS dtype, substr(thedvalue,1,6) as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num,process_type_name, app_id from bi_alm_group_by_time where dtype = 'DAY' and alarm_object_type not in ('网络','应用') group by substr(thedvalue,1,6), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id; insert into bi_alm_group_by_time select 'YEAR' AS dtype, substr(thedvalue,1,4) as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num,process_type_name,app_id from bi_alm_group_by_time where dtype = 'MONTH' and alarm_object_type not in ('网络','应用') group by substr(thedvalue,1,4), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id; insert into bi_alm_group_by_time (dtype,thedvalue,itm_hardware_resources_name,itm_hardware_resources_ip,itm_hardware_resources_areacode, pinpai,itm_hardware_resources_status,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,xinghao,alarm_object_type,alarm_count,alarm_time,alarm_zy_num,total_zy_num,process_type_name,app_id ) select 'HOUR' AS dtype, DATE_FORMAT(occur_time,'%Y%m%d%H') as thedvalue, m.itm_app_name as itm_hardware_resources_name, m.itm_app_systemurl as itm_hardware_resources_ip, m.net_name as itm_hardware_resources_areacode, m.priority as pinpai, m.stateflag as itm_hardware_resources_status, m.severity_type as severity_type, m.title as title, m.alarm_text as alarm_text, m.mantancevendor as guoziguishubumen, m.yunweirenyuan as yunweirenyuan, m.app_type_name as xinghao, '应用' as alarm_object_type, sum(alarm_count) as alarm_count, sum(alarm_time) as alarm_time,count(1) as alarm_zy_num, (select count(1) from itm_app) as total_zy_num,process_type_name,app_id from ( select a.occur_time, a.alarm_object_id, case a.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type, a.title, a.alarm_text, a.is_active_alarm, a.alarm_count, timestampdiff(MINUTE,a.occur_time,a.end_time)*1.0/60 as alarm_time, b.itm_app_name as itm_app_name, b.itm_app_systemurl as itm_app_systemurl, case b.stateflag when 0 then '已应用' else '未应用' end as stateflag, case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY, d.name as app_type_name, e.name as net_name, '' as mantancevendor, b.mantance_duty as yunweirenyuan, wf.process_type_name,wrr.app_id from (select * from v_bi_alm_all_alarm where alarm_object_type ='应用') a left join itm_app b on a.alarm_object_id = b.ITM_APP_ID LEFT JOIN icm_bpm.t_work_resource_real wrr on wrr.object_id = a.alarm_object_id left join icm_bpm.t_work_flow wf on wf.id=wrr.work_id LEFT JOIN m_com_dict d on b.app_type=d.value LEFT JOIN m_com_dict e on e.value = b.ITM_NETGROUP_ID WHERE d.dict_index='app_type' and e.dict_index='itm_resources_netcode' ) m group by DATE_FORMAT(occur_time,'%Y%m%d%H'), pinpai,m.itm_app_name,m.itm_app_systemurl,m.net_name,m.priority,m.stateflag,m.severity_type,m.title,m.alarm_text,m.mantancevendor,m.yunweirenyuan,m.app_type_name,process_type_name,app_id; insert into bi_alm_group_by_time select 'DAY' AS dtype, substr(thedvalue,1,8) as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num, process_type_name, app_id from bi_alm_group_by_time where dtype = 'HOUR' and alarm_object_type = '应用' group by substr(thedvalue,1,8), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name,app_id; insert into bi_alm_group_by_time select 'MONTH' AS dtype, substr(thedvalue,1,6) as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num, process_type_name, app_id from bi_alm_group_by_time where dtype = 'DAY' and alarm_object_type = '应用' group by substr(thedvalue,1,6), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name, app_id; insert into bi_alm_group_by_time select 'YEAR' AS dtype, substr(thedvalue,1,4) as thedvalue, alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan, sum(alarm_count) as alarm_count,sum(alarm_time) as alarm_time,sum(alarm_zy_num) as alarm_zy_num,max(total_zy_num) as total_zy_num,process_type_name, app_id from bi_alm_group_by_time where dtype = 'MONTH' and alarm_object_type = '应用' group by substr(thedvalue,1,4), alarm_object_ip,alarm_object_type,ITM_HARDWARE_RESOURCES_AREACODE,ITM_HARDWARE_RESOURCES_ASSNO,ITM_HARDWARE_RESOURCES_CODE, ITM_HARDWARE_RESOURCES_NAME,ITM_HARDWARE_RESOURCES_IP,pinpai,xinghao,ITM_HARDWARE_RESOURCES_NETNAME,ITM_HARDWARE_RESOURCES_STATUS,severity_type,title,alarm_text,guoziguishubumen,yunweirenyuan,process_type_name, app_id; DROP TABLE IF EXISTS `bi_work_flow_group_row`; CREATE TABLE `bi_work_flow_group_row` ( `dtype` varchar(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '', `thedvalue` varchar(15) CHARACTER SET utf8mb3 DEFAULT NULL, `process_type_name` varchar(255) DEFAULT NULL, `app_id` varchar(64) DEFAULT NULL, `app_name` varchar(64) DEFAULT NULL, `serve_area` varchar(32) DEFAULT NULL, `serve_department` varchar(32) DEFAULT NULL, `serve_firm` varchar(32) DEFAULT NULL, `agg` varchar(10) DEFAULT NULL, `unit` varchar(20) DEFAULT NULL, `kind` varchar(40) NOT NULL DEFAULT '', `value` double DEFAULT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8; truncate table bi_work_flow_group_row; insert into bi_work_flow_group_row select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works' as 'kind', total_works as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_end' as 'kind', total_works_end as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_noend' as 'kind', total_works_noend as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_notimeout' as 'kind', total_works_notimeout as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '件', 'total_works_timeout' as 'kind', total_works_timeout as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '小时', 'total_time_consuming' as 'kind', total_time_consuming as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '小时', 'avg_time_consuming' as 'kind', avg_time_consuming as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'AVG', '分', 'satisfaction' as 'kind', satisfaction as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '%', 'total_works_notimeout_pct' as 'kind', 100*total_works_notimeout/total_works as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '%', 'total_works_timeout_pct' as 'kind', 100*total_works_timeout/total_works as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id union all select m.dtype, m.thedvalue,m.process_type_name,m.app_id,ia.ITM_APP_NAME,m.serve_area,m.serve_department,m.serve_firm, 'SUM', '%', 'total_works_end_pct' as 'kind', 100*total_works_end/total_works as 'value' from bi_work_flow_group_by_time m left join itm_app ia on ia.ITM_APP_ID=m.app_id; -- （3）基础设施监控覆盖率趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_hardware_fugai`; CREATE TABLE `bi_hardware_fugai` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `itm_hardware_resources_id` varchar(200) DEFAULT '' COMMENT '告警设备ID', `itm_hardware_resources_type` varchar(64) DEFAULT NULL COMMENT '设备类型', `itm_hardware_resources_cntype` varchar(5) DEFAULT '' COMMENT '设备类型中文', `itm_hardware_resources_areacode` varchar(64) DEFAULT NULL COMMENT '告警设备区域', `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资编码', `ITM_HARDWARE_RESOURCES_CODE` varchar(200) DEFAULT NULL COMMENT '设备编码', `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称', `ITM_HARDWARE_RESOURCES_IP` varchar(64) DEFAULT NULL COMMENT '设备IP', `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '设备状态', `itm_monitor_flag` varchar(64) DEFAULT NULL COMMENT '是否进行监控 0未纳入监控 1纳入监控', `create_time` datetime DEFAULT NULL COMMENT '创建日期', `NAME` varchar(512) DEFAULT NULL COMMENT '所属组织', `resclasscnname` varchar(255) DEFAULT NULL COMMENT '所属类型（网系）', `UID` varchar(100) DEFAULT NULL COMMENT 'UID 同告警设备ID', `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌', `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号', `mac` varchar(255) DEFAULT NULL COMMENT 'MAC地址', `guoziguishurenyuan` varchar(255) DEFAULT NULL COMMENT '国资归属人员', `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门', `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员', `ITM_HARDWARE_RESOURCES_NETNAME` varchar(100) DEFAULT NULL, `cloud` varchar(255) DEFAULT NULL COMMENT '云平台' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_hardware_fugai; insert into bi_hardware_fugai select 'MONTH' AS dtype, a.thedvalue, b.itm_hardware_resources_id,b.itm_hardware_resources_type,b.itm_hardware_resources_cntype,b.itm_hardware_resources_areacode,b.ITM_HARDWARE_RESOURCES_ASSNO,b.ITM_HARDWARE_RESOURCES_CODE ,b.ITM_HARDWARE_RESOURCES_NAME,b.ITM_HARDWARE_RESOURCES_IP,b.ITM_HARDWARE_RESOURCES_STATUS,b.itm_monitor_flag,b.create_time ,b.NAME,b.resclasscnname,b.UID,b.pinpai,b.xinghao,b.mac ,b.guoziguishurenyuan,b.guoziguishubumen,b.yunweirenyuan,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.cloud from ( select distinct date_format(dt_d,'%Y%m') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-12-31') a left join bi_itm_hardware_resources b on a.thedvalue >= date_format(b.create_time,'%Y%m') UNION ALL select 'YEAR' AS dtype, a.thedvalue, b.itm_hardware_resources_id,b.itm_hardware_resources_type,b.itm_hardware_resources_cntype,b.itm_hardware_resources_areacode,b.ITM_HARDWARE_RESOURCES_ASSNO,b.ITM_HARDWARE_RESOURCES_CODE ,b.ITM_HARDWARE_RESOURCES_NAME,b.ITM_HARDWARE_RESOURCES_IP,b.ITM_HARDWARE_RESOURCES_STATUS,b.itm_monitor_flag,b.create_time ,b.NAME,b.resclasscnname,b.UID,b.pinpai,b.xinghao,b.mac ,b.guoziguishurenyuan,b.guoziguishubumen,b.yunweirenyuan,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.cloud from ( select distinct date_format(dt_d,'%Y') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-12-31') a left join bi_itm_hardware_resources b on a.thedvalue >= date_format(b.create_time,'%Y') UNION ALL select 'DAY' AS dtype, a.thedvalue, b.itm_hardware_resources_id,b.itm_hardware_resources_type,b.itm_hardware_resources_cntype,b.itm_hardware_resources_areacode,b.ITM_HARDWARE_RESOURCES_ASSNO,b.ITM_HARDWARE_RESOURCES_CODE ,b.ITM_HARDWARE_RESOURCES_NAME,b.ITM_HARDWARE_RESOURCES_IP,b.ITM_HARDWARE_RESOURCES_STATUS,b.itm_monitor_flag,b.create_time ,b.NAME,b.resclasscnname,b.UID,b.pinpai,b.xinghao,b.mac ,b.guoziguishurenyuan,b.guoziguishubumen,b.yunweirenyuan,b.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME,b.cloud from ( select distinct date_format(dt_d,'%Y%m%d') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-12-31') a left join bi_itm_hardware_resources b on a.thedvalue >= date_format(b.create_time,'%Y%m%d'); -- （4）基础设施资源利用率趋势、服务器资源负载趋势、网络设备带宽利用率趋势、网络设备效能分析、磁盘性能分析、存储设备资源利用率趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_app_stat_group_by_time`; CREATE TABLE `bi_app_stat_group_by_time` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `dhour` varchar(15) DEFAULT NULL COMMENT '小时', `itm_hardware_resources_id` varchar(200) NOT NULL DEFAULT '' COMMENT '告警设备ID', `itm_hardware_resources_type` varchar(64) DEFAULT NULL COMMENT '设备类型', `itm_hardware_resources_cntype` varchar(5) NOT NULL DEFAULT '' COMMENT '设备类型中文', `QGorZG` varchar(10) DEFAULT NULL COMMENT '全国/最高', `itm_hardware_resources_areacode` varchar(64) DEFAULT NULL COMMENT '告警设备区域', `ITM_HARDWARE_RESOURCES_ASSNO` varchar(100) DEFAULT NULL COMMENT '国资编码', `ITM_HARDWARE_RESOURCES_CODE` varchar(200) DEFAULT NULL COMMENT '设备编码', `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称', `ITM_HARDWARE_RESOURCES_IP` varchar(64) DEFAULT NULL COMMENT '设备IP', `ITM_HARDWARE_RESOURCES_STATUS` varchar(64) DEFAULT NULL COMMENT '设备状态', `ITM_HARDWARE_RESOURCES_NETNAME` varchar(255) DEFAULT NULL COMMENT '网系名称', `itm_monitor_flag` varchar(64) DEFAULT NULL COMMENT '是否进行监控 0未纳入监控 1纳入监控', `create_time` datetime DEFAULT NULL COMMENT '设备创建时间', `NAME` varchar(512) DEFAULT NULL COMMENT '所属组织', `resclasscnname` varchar(255) DEFAULT NULL COMMENT '所属类型（网系）', `UID` varchar(100) DEFAULT NULL COMMENT 'UID 同告警设备ID', `pinpai` varchar(255) DEFAULT NULL COMMENT '设备品牌', `xinghao` varchar(255) DEFAULT NULL COMMENT '设备型号', `mac` varchar(255) DEFAULT NULL COMMENT 'MAC地址', `guoziguishurenyuan` varchar(255) DEFAULT NULL COMMENT '国资归属人员', `guoziguishubumen` varchar(255) DEFAULT NULL COMMENT '国资归属部门', `yunweirenyuan` varchar(255) DEFAULT NULL COMMENT '运维人员', `IND_VALUE_Network_Cpurate` decimal(20,6) DEFAULT NULL COMMENT 'cpu利用率（原始）', `IND_VALUE_Network_Cpurate_High` decimal(16,2) DEFAULT NULL COMMENT 'CPU利用率峰值（原始）', `IND_VALUE_Network_Memrate` decimal(20,6) DEFAULT NULL COMMENT '内存利用率（原始）', `IND_VALUE_Network_Memrate_High` decimal(16,2) DEFAULT NULL COMMENT '内存利用率峰值（原始）', `IND_VALUE_HOST_DISK_TOTAL` decimal(38,4) DEFAULT NULL COMMENT '磁盘总大小（原始）', `IND_VALUE_HOST_DISK_USED` decimal(38,4) DEFAULT NULL COMMENT '已使用磁盘大小（原始）', `IND_VALUE_HOST_DISK_UTILIZATION` decimal(16,4) DEFAULT NULL COMMENT '磁盘利用率峰值（原始）', `Band_widthrate` decimal(38,4) DEFAULT NULL COMMENT '实时带宽利用率', `Bandwidthrate_High` decimal(38,4) DEFAULT NULL COMMENT '带宽峰值用率', `Network_delay` decimal(38,4) DEFAULT NULL COMMENT '网络时延', `Network_flow` decimal(38,4) DEFAULT NULL COMMENT '端口流量', `Network_PacketErrorrate` decimal(38,4) DEFAULT NULL COMMENT '丢包率', `Network_PacketLossrate` decimal(38,4) DEFAULT NULL COMMENT '错包率', `Write_rate` decimal(38,4) DEFAULT NULL COMMENT '读写速率', `network_Ifinrate` decimal(38,4) DEFAULT NULL COMMENT '下行利用率', `network_Ifortrate` decimal(38,4) DEFAULT NULL COMMENT '上行利用率', `Device_total` decimal(38,4) DEFAULT NULL COMMENT '设备总量', `Device_used` decimal(38,4) DEFAULT NULL COMMENT '设备已用容量', `Device_UTILIZATION` decimal(38,4) DEFAULT NULL COMMENT '资源利用率', `cloud` varchar(255) DEFAULT NULL COMMENT '云平台', KEY `bi_app_stat_group_by_time_dtype_IDX` (`dtype`,`thedvalue`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `bi_save_tempbgtime`; CREATE TABLE `bi_save_tempbgtime` ( `bg_time` varchar(300) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; drop procedure if exists wk_app_stat_group_by_time; delimiter $$ create procedure wk_app_stat_group_by_time(in bg_time varchar(300), in ed_time varchar(300)) begin delete from bi_app_stat_group_by_time where dtype = 'HOUR' and thedvalue >= concat(bg_time,'00') and thedvalue < concat(ed_time,'00'); delete from bi_app_stat_group_by_time where dtype = 'DAY' and thedvalue >= bg_time and thedvalue < ed_time; delete from bi_app_stat_group_by_time where dtype = 'MONTH' and thedvalue >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y%m') and thedvalue <= date_format(DATE_ADD(STR_TO_DATE(ed_time,'%Y%m%d'), INTERVAL -1 day),'%Y%m'); delete from bi_app_stat_group_by_time where dtype = 'YEAR' and thedvalue >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y') and thedvalue <= date_format(DATE_ADD(STR_TO_DATE(ed_time,'%Y%m%d'), INTERVAL -1 day),'%Y'); insert into bi_app_stat_group_by_time select 'HOUR' as dtype, date_format(b.IND_VALUE_TIME,'%Y%m%d%H') as thedvalue, date_format(b.IND_VALUE_TIME,'%H') as dhour, a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end QGorZG,a.itm_hardware_resources_areacode, a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME ,a.itm_monitor_flag,a.create_time,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac ,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, avg(IND_VALUE_Network_Bandwidthrate) as Band_widthrate, max(IND_VALUE_Network_Bandwidthrate_High) as Bandwidthrate_High, 0 as Network_delay, avg(TRANSSIZE_Up + TRANSSIZE_Down) as Network_flow, avg(IND_VALUE_Network_PacketErrorrate) as Network_PacketErrorrate, avg(IND_VALUE_Network_PacketLossrate) as Network_PacketLossrate, avg(IND_VALUE_Network_Bandwidthrate) as Write_rate, avg(IND_VALUE_Network_Ifinrate) as network_Ifinrate, avg(IND_VALUE_Network_Ifoutrate) as network_Ifortrate, 0.0 as Device_total, 0.0 as Device_used, 0.0 as Device_UTILIZATION, a.cloud from bi_itm_hardware_resources a inner join app_network_stat b on a.itm_hardware_resources_id = b.INT_ID where b.IND_VALUE_TIME >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y-%m-%d') and b.IND_VALUE_TIME < date_format(STR_TO_DATE(ed_time,'%Y%m%d'),'%Y-%m-%d') GROUP BY date_format(b.IND_VALUE_TIME,'%Y%m%d%H'),date_format(b.IND_VALUE_TIME,'%H'), a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end,a.itm_hardware_resources_areacode, a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME ,a.itm_monitor_flag,a.create_time,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac ,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan,a.cloud union all select 'HOUR' as dtype, date_format(b.IND_VALUE_TIME,'%Y%m%d%H') as thedvalue, date_format(b.IND_VALUE_TIME,'%H') as dhour, a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end as QGorZG,a.itm_hardware_resources_areacode, a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME as ITM_HARDWARE_RESOURCES_NETNAME ,a.itm_monitor_flag,a.create_time,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac ,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan, 0.0 as IND_VALUE_Network_Cpurate,0.0 as IND_VALUE_Network_Cpurate_High,0.0 as IND_VALUE_Network_Memrate,0.0 as IND_VALUE_Network_Memrate_High, sum(b.IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(b.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED, max(b.IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION, 0.0 as Band_widthrate, 0.0 as Bandwidthrate_High, 0.0 as Network_delay, 0.0 as Network_flow, 0.0 as Network_PacketErrorrate, 0.0 as Network_PacketLossrate, 0.0 as Write_rate, 0.0 as network_Ifinrate, 0.0 as network_Ifortrate, max(IND_VALUE_HOST_DISK_TOTAL) as Device_total, max(IND_VALUE_HOST_DISK_USED) as Device_used, max(b.IND_VALUE_HOST_DISK_UTILIZATION) as Device_UTILIZATION, a.cloud from bi_itm_hardware_resources a inner join app_host_stat b on a.itm_hardware_resources_id = b.INT_ID where b.IND_VALUE_TIME >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y-%m-%d') and b.IND_VALUE_TIME < date_format(STR_TO_DATE(ed_time,'%Y%m%d'),'%Y-%m-%d') GROUP BY date_format(b.IND_VALUE_TIME,'%Y%m%d%H'),date_format(b.IND_VALUE_TIME,'%H'), a.itm_hardware_resources_id,a.itm_hardware_resources_type,a.itm_hardware_resources_cntype,case when a.NAME = '最高人民法院' then '最高' else '全国' end,a.itm_hardware_resources_areacode, a.ITM_HARDWARE_RESOURCES_ASSNO,a.ITM_HARDWARE_RESOURCES_CODE,a.ITM_HARDWARE_RESOURCES_NAME,a.ITM_HARDWARE_RESOURCES_IP,a.ITM_HARDWARE_RESOURCES_STATUS,a.NET_NAME ,a.itm_monitor_flag,a.CREATE_TIME,a.NAME,a.resclasscnname,a.UID,a.pinpai,a.xinghao,a.mac ,a.guoziguishurenyuan,a.guoziguishubumen,a.yunweirenyuan,a.cloud; insert into bi_app_stat_group_by_time select 'DAY' as dtype, substr(thedvalue,1,8) as thedvalue, dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode , ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS , ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, avg(IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, sum(IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED, max(IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION, avg(Band_widthrate) as Band_widthrate, max(Bandwidthrate_High) as Bandwidthrate_High, 0 as Network_delay, 0 as Network_flow, avg(Network_PacketErrorrate) as Network_PacketErrorrate, avg(Network_PacketLossrate) as Network_PacketLossrate, 0 as Write_rate, avg(network_Ifinrate) as network_Ifinrate, avg(network_Ifortrate) as network_Ifortrate, max(Device_total) as Device_total, max(Device_used) as Device_used, max(Device_UTILIZATION) as Device_UTILIZATION, cloud from bi_app_stat_group_by_time where dtype = 'HOUR' and thedvalue >= concat(bg_time,'00') and thedvalue < concat(ed_time,'00') group by substr(thedvalue,1,8),dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode , ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS , ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, cloud; insert into bi_app_stat_group_by_time select 'MONTH' as dtype, substr(thedvalue,1,6) as thedvalue, dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode , ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS , ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, avg(IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, sum(IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED, max(IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION, avg(Band_widthrate) as Band_widthrate, max(Bandwidthrate_High) as Bandwidthrate_High, 0 as Network_delay, 0 as Network_flow, avg(Network_PacketErrorrate) as Network_PacketErrorrate, avg(Network_PacketLossrate) as Network_PacketLossrate, 0 as Write_rate, avg(network_Ifinrate) as network_Ifinrate, avg(network_Ifortrate) as network_Ifortrate, max(Device_total) as Device_total, max(Device_used) as Device_used, max(Device_UTILIZATION) as Device_UTILIZATION, cloud from bi_app_stat_group_by_time where dtype = 'DAY' and thedvalue >= bg_time and thedvalue < ed_time group by substr(thedvalue,1,6),dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode , ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS , ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, cloud; insert into bi_app_stat_group_by_time select 'YEAR' as dtype, substr(thedvalue,1,4) as thedvalue, dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode , ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS , ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, avg(IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, sum(IND_VALUE_HOST_DISK_TOTAL) as IND_VALUE_HOST_DISK_TOTAL, sum(IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED, max(IND_VALUE_HOST_DISK_UTILIZATION) as IND_VALUE_HOST_DISK_UTILIZATION, avg(Band_widthrate) as Band_widthrate, max(Bandwidthrate_High) as Bandwidthrate_High, 0 as Network_delay, 0 as Network_flow, avg(Network_PacketErrorrate) as Network_PacketErrorrate, avg(Network_PacketLossrate) as Network_PacketLossrate, 0 as Write_rate, avg(network_Ifinrate) as network_Ifinrate, avg(network_Ifortrate) as network_Ifortrate, max(Device_total) as Device_total, max(Device_used) as Device_used, max(Device_UTILIZATION) as Device_UTILIZATION, cloud from bi_app_stat_group_by_time where dtype = 'MONTH' and thedvalue >= date_format(STR_TO_DATE(bg_time,'%Y%m%d'),'%Y%m') and thedvalue <= date_format(DATE_ADD(STR_TO_DATE(ed_time,'%Y%m%d'), INTERVAL -1 day),'%Y%m') group by substr(thedvalue,1,4),dhour ,itm_hardware_resources_id ,itm_hardware_resources_type ,itm_hardware_resources_cntype ,QGorZG ,itm_hardware_resources_areacode , ITM_HARDWARE_RESOURCES_ASSNO ,ITM_HARDWARE_RESOURCES_CODE ,ITM_HARDWARE_RESOURCES_NAME ,ITM_HARDWARE_RESOURCES_IP ,ITM_HARDWARE_RESOURCES_STATUS , ITM_HARDWARE_RESOURCES_NETNAME ,itm_monitor_flag ,create_time ,NAME ,resclasscnname ,UID ,pinpai ,xinghao ,mac ,guoziguishurenyuan ,guoziguishubumen ,yunweirenyuan, cloud; end $$ delimiter ; -- 循环执行（按天循环运行，并将执行完成的结果插入bi_save_tempbgtime） drop procedure if exists wk_app_stat_group_by_time_xunhuan; delimiter $$ create procedure wk_app_stat_group_by_time_xunhuan(in bg_time varchar(300), in ed_time varchar(300)) begin declare i varchar(300); set i = bg_time; while i < ed_time do call wk_app_stat_group_by_time(i, date_format(DATE_ADD(STR_TO_DATE(i,'%Y%m%d'), INTERVAL 1 day),'%Y%m%d')); insert into bi_save_tempbgtime values(i); set i = date_format(DATE_ADD(STR_TO_DATE(i,'%Y%m%d'), INTERVAL 1 day),'%Y%m%d'); end while; end $$ delimiter ; -- 按时间段一次运行（只包含开始时间，不包含结束时间，输入格式为20230801这种格式） truncate table bi_save_tempbgtime; call wk_app_stat_group_by_time_xunhuan('20230101','20231230'); -- （5）四级法院网络中断趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_pub_organ_siji`; CREATE TABLE `bi_pub_organ_siji` ( `ID` varchar(32) DEFAULT NULL, `CODE` varchar(64) DEFAULT NULL, `FYJB1` varchar(512) DEFAULT NULL, `FYJB2` varchar(512) DEFAULT NULL, `FYJB3` varchar(512) DEFAULT NULL, `FYJB4` varchar(512) DEFAULT NULL, `FYJB1_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `FYJB2_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `FYJB3_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `FYJB4_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `organ_type` varchar(6) NOT NULL DEFAULT '', `COURT_NUM` varchar(255) DEFAULT NULL, `COURT_TYPE` varchar(36) DEFAULT NULL, `SHORT_NAME` varchar(255) DEFAULT NULL, `STATUS` char(1) DEFAULT NULL, `PUB_LONGITUDE` varchar(255) DEFAULT NULL, `PUB_LATITUDE` varchar(255) DEFAULT NULL, `PUB_ORGAN_IP` varchar(100) DEFAULT NULL, `FYJB1_JC` varchar(512) DEFAULT NULL, `FYJB2_JC` varchar(512) DEFAULT NULL, `FYJB3_JC` varchar(512) DEFAULT NULL, `FYJB4_JC` varchar(512) DEFAULT NULL, KEY `index_1` (`CODE`) USING BTREE, KEY `index_2` (`FYJB2`) USING BTREE, KEY `index_3` (`FYJB3`) USING BTREE, KEY `index_4` (`FYJB4`) USING BTREE, KEY `index_5` (`FYJB2_JC`) USING BTREE, KEY `index_6` (`FYJB3_JC`) USING BTREE, KEY `index_7` (`FYJB4_JC`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_pub_organ_siji; insert into bi_pub_organ_siji select m.ID,m.CODE,m.NAME AS FYJB1,'-' as FYJB2,'-' as FYJB3,'-' as FYJB4,a.org_code as FYJB1_CODE,'-' as FYJB2_CODE,'-' as FYJB3_CODE,'-' as FYJB4_CODE, 'FYJB.1' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP, m.SHORT_NAME AS FYJB1_JC,'-' as FYJB2_JC,'-' as FYJB3_JC,'-' as FYJB4_JC from pub_organ_tree a left join pub_organ m on a.org_code = m.code where a.parent_code = '0' union all select m.ID,m.CODE,l.NAME AS FYJB1,m.NAME as FYJB2,'-' as FYJB3,'-' as FYJB4,b.org_code as FYJB1_CODE,a.org_code as FYJB2_CODE,'-' as FYJB3_CODE,'-' as FYJB4_CODE, 'FYJB.2' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP, l.SHORT_NAME AS FYJB1_JC,m.SHORT_NAME as FYJB2_JC,'-' as FYJB3_JC,'-' as FYJB4_JC from pub_organ_tree a left join pub_organ_tree b on a.parent_code = b.org_code left join pub_organ l on b.org_code = l.code left join pub_organ m on a.org_code = m.code where b.parent_code = '0' union all select m.ID,m.CODE,k.NAME AS FYJB1,l.NAME as FYJB2,m.NAME as FYJB3,'-' as FYJB4,c.org_code as FYJB1_CODE,b.org_code as FYJB2_CODE,a.org_code as FYJB3_CODE,'-' as FYJB4_CODE, 'FYJB.3' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP, k.SHORT_NAME AS FYJB1_JC,l.SHORT_NAME as FYJB2_JC,m.SHORT_NAME as FYJB3_JC,'-' as FYJB4_JC from pub_organ_tree a left join pub_organ_tree b on a.parent_code = b.org_code left join pub_organ_tree c on b.parent_code = c.org_code left join pub_organ k on c.org_code = k.code left join pub_organ l on b.org_code = l.code left join pub_organ m on a.org_code = m.code where c.parent_code = '0' union all select m.ID,m.CODE,j.NAME AS FYJB1,k.NAME as FYJB2,l.NAME as FYJB3,m.NAME as FYJB4,d.org_code as FYJB1_CODE,c.org_code as FYJB2_CODE,b.org_code as FYJB3_CODE,a.org_code as FYJB4_CODE, 'FYJB.4' as organ_type,m.COURT_NUM,m.COURT_TYPE,m.SHORT_NAME,m.STATUS,m.PUB_LONGITUDE,m.PUB_LATITUDE,m.PUB_ORGAN_IP, j.SHORT_NAME AS FYJB1_JC,k.SHORT_NAME as FYJB2_JC,l.SHORT_NAME as FYJB3_JC,m.SHORT_NAME as FYJB4_JC from pub_organ_tree a left join pub_organ_tree b on a.parent_code = b.org_code left join pub_organ_tree c on b.parent_code = c.org_code left join pub_organ_tree d on c.parent_code = d.org_code left join pub_organ j on d.org_code = j.code left join pub_organ k on c.org_code = k.code left join pub_organ l on b.org_code = l.code left join pub_organ m on a.org_code = m.code where d.parent_code = '0'; -- 建表语句 DROP TABLE IF EXISTS `bi_sijifayuan_alm_group_by_time`; CREATE TABLE `bi_sijifayuan_alm_group_by_time` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `dhour` varchar(7) DEFAULT NULL COMMENT '日期', `alarm_object_id` varchar(64) DEFAULT NULL COMMENT '告警ID（网络CODE）', `alarm_object_ip` varchar(300) DEFAULT NULL COMMENT '告警IP（网络IP）', `alarm_object_type` varchar(6) NOT NULL DEFAULT '' COMMENT '告警对象类型', `severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警级别', `title` varchar(256) DEFAULT NULL COMMENT '告警标题', `alarm_text` mediumtext COMMENT '告警标题', `is_active_alarm` bigint(20) NOT NULL DEFAULT '0' COMMENT '告警正文', `FYJB1` varchar(512) DEFAULT NULL COMMENT '1级法院名称', `FYJB2` varchar(512) DEFAULT NULL COMMENT '2级法院名称', `FYJB3` varchar(512) DEFAULT NULL COMMENT '3级法院名称', `FYJB4` varchar(512) DEFAULT NULL COMMENT '4级法院名称', `FYJB1_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '1级法院CODE', `FYJB2_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '2级法院CODE', `FYJB3_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '3级法院CODE', `FYJB4_CODE` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '4级法院CODE', `organ_type` varchar(6) DEFAULT NULL COMMENT '法院级别', `COURT_NUM` varchar(255) DEFAULT NULL, `COURT_TYPE` varchar(36) DEFAULT NULL, `SHORT_NAME` varchar(255) DEFAULT NULL, `STATUS` char(1) DEFAULT NULL, `PUB_LONGITUDE` varchar(255) DEFAULT NULL, `PUB_LATITUDE` varchar(255) DEFAULT NULL, `PUB_ORGAN_IP` varchar(100) DEFAULT NULL, `alarm_count` double DEFAULT NULL COMMENT '告警次数', `alarm_time` decimal(65,5) DEFAULT NULL COMMENT '告警时长', `alarm_zy_num` decimal(42,0) DEFAULT NULL COMMENT '中断次数（故障次数）', `total_zy_num` decimal(24,4) DEFAULT NULL COMMENT '总资产数（取平均）', `pingall` double(17,0) DEFAULT NULL COMMENT 'ping次数', `pingdown` double(17,0) DEFAULT NULL COMMENT 'ping不通次数' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_sijifayuan_alm_group_by_time; insert into bi_sijifayuan_alm_group_by_time select 'HOUR' AS dtype, date_format(occur_time,'%Y%m%d%H') as thedvalue, date_format(occur_time,'%H') as dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP, sum(alarm_count) as alarm_count, sum(alarm_time) as alarm_time, count(1) as alarm_zy_num, (select count(1) from bi_itm_hardware_resources where itm_hardware_resources_cntype != '非资产设备') as total_zy_num, 0 as pingall, 0 as pingdown from ( select a.occur_time, a.alarm_object_id, a.alarm_object_ip, a.alarm_object_type, case a.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type, a.title, a.alarm_text, a.is_active_alarm, a.alarm_count, timestampdiff(MINUTE,a.occur_time,a.end_time)*1.0/60 as alarm_time, b.FYJB1,b.FYJB2,b.FYJB3,b.FYJB4,b.FYJB1_CODE,b.FYJB2_CODE,b.FYJB3_CODE,b.FYJB4_CODE,b.organ_type,b.COURT_NUM,b.COURT_TYPE,b.SHORT_NAME,b.STATUS,b.PUB_LONGITUDE,b.PUB_LATITUDE,b.PUB_ORGAN_IP from (select * from v_bi_alm_all_alarm where alarm_object_type in ('网络','路由器','交换机')) a left join bi_pub_organ_siji b on a.alarm_object_id = b.CODE) m group by date_format(occur_time,'%Y%m%d%H') , date_format(occur_time,'%H') , alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP; insert into bi_sijifayuan_alm_group_by_time select 'DAY' AS dtype, substring(thedvalue,1,8) AS thedvalue, dhour as dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP, sum(alarm_count) as alarm_count, sum(alarm_time) as alarm_time, SUM(alarm_zy_num) as alarm_zy_num, AVG(total_zy_num) as total_zy_num, SUM(pingall) as pingall, SUM(pingdown) as pingdown FROM bi_sijifayuan_alm_group_by_time where dtype = 'HOUR' GROUP BY substring(thedvalue,1,8), dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP; insert into bi_sijifayuan_alm_group_by_time select 'MONTH' AS dtype, substring(thedvalue,1,6) AS thedvalue, dhour as dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP, sum(alarm_count) as alarm_count, sum(alarm_time) as alarm_time, SUM(alarm_zy_num) as alarm_zy_num, AVG(total_zy_num) as total_zy_num, SUM(pingall) as pingall, SUM(pingdown) as pingdown FROM bi_sijifayuan_alm_group_by_time where dtype = 'DAY' GROUP BY substring(thedvalue,1,6), dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP; insert into bi_sijifayuan_alm_group_by_time select 'YEAR' AS dtype, substring(thedvalue,1,4) AS thedvalue, dhour as dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP, sum(alarm_count) as alarm_count, sum(alarm_time) as alarm_time, SUM(alarm_zy_num) as alarm_zy_num, AVG(total_zy_num) as total_zy_num, SUM(pingall) as pingall, SUM(pingdown) as pingdown FROM bi_sijifayuan_alm_group_by_time where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4), dhour, alarm_object_id, alarm_object_ip, alarm_object_type,severity_type, title, alarm_text, is_active_alarm, FYJB1,FYJB2,FYJB3,FYJB4,FYJB1_CODE,FYJB2_CODE,FYJB3_CODE,FYJB4_CODE,organ_type,COURT_NUM,COURT_TYPE,SHORT_NAME,STATUS,PUB_LONGITUDE,PUB_LATITUDE,PUB_ORGAN_IP; -- (6) 虚拟化资源分配趋势、虚拟化资源使用趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_host_stat`; CREATE TABLE `bi_host_stat` ( `dtype` varchar(10) NOT NULL DEFAULT '', `thedvalue` varchar(10) DEFAULT NULL, `COMPANY` varchar(30) DEFAULT NULL COMMENT '数据来源', `target` varchar(20) NOT NULL DEFAULT '', `dim` varchar(20) NOT NULL DEFAULT '', `dim_value` decimal(20,8) DEFAULT NULL, `group_agg` varchar(3) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; TRUNCATE table bi_host_stat; INSERT INTO bi_host_stat select 'HOUR' as dtype, a.thedvalue,a.COMPANY, case b.mm when 1 then 'CPU' when 2 then '内存' when 3 then '硬盘'when 4 then 'CPU' when 5 then '内存' when 6 then '硬盘'when 7 then 'CPU' when 8 then '内存' when 9 then '硬盘' when 10 then 'CPU' when 11 then '内存' when 12 then '硬盘' else '' end as target, case b.mm when 1 then '额定量' when 2 then '额定量' when 3 then '额定量' when 4 then '分配量' when 5 then '分配量' when 6 then '分配量'when 7 then '剩余可用' when 8 then '剩余可用' when 9 then '剩余可用' when 10 then '分配占比' when 11 then '分配占比' when 12 then '分配占比' else '' end as dim, case b.mm when 1 then dim1 when 2 then dim2 when 3 then dim3 when 4 then dim4 when 5 then dim5 when 6 then dim6 when 7 then dim7 when 8 then dim8 when 9 then dim9 when 10 then IF(dim1<>0,dim4/dim1,0) when 11 then IF(dim2<>0,dim5/dim2,0) when 12 then IF(dim3<>0,dim6/dim3,0) else null end as dim_value, case b.mm when 1 then 'avg' when 2 then 'max' when 3 then 'max'when 4 then 'avg' when 5 then 'max' when 6 then 'max'when 7 then 'avg' when 8 then 'max' when 9 then 'max' else 'avg' end as group_agg from ( select date_format(CREATE_TIME,'%Y%m%d%H') as thedvalue, case COMPANY when 'huawei' then '太极云资源池' when 'huawei-xc' then '信创云资源池' when 'shouxin' then '首信云资源池' when 'unicom' then '联通云资源池' else '其它' end as COMPANY, avg(IND_VALUE_HOST_CPU_UTILIZATION) as dim1,MAX(IND_VALUE_HOST_MEMORY_TOTAL) as dim2,MAX(IND_VALUE_HOST_DISK_TOTAL) as dim3, avg(IND_VALUE_HOST_CPU_TOP) as dim4,MAX(IND_VALUE_HOST_MEMORY_USED) as dim5,MAX(IND_VALUE_HOST_DISK_USED) as dim6, avg(IND_VALUE_HOST_CPU_UTILIZATION_LOW) as dim7,MAX(IND_VALUE_HOST_MEMORY_UNUSED) as dim8,MAX(IND_VALUE_HOST_DISK_UNUSED) as dim9 from app_host_stat where COMPANY is not null group by COMPANY,date_format(CREATE_TIME,'%Y%m%d%H') ) a cross join (select 1 mm union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) b; INSERT INTO bi_host_stat select 'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue,COMPANY,target,dim,AVG(dim_value),group_agg from bi_host_stat where group_agg = 'avg' AND dtype = 'HOUR' GROUP BY substring(thedvalue,1,8),COMPANY,target,dim,group_agg UNION ALL select 'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue,COMPANY,target,dim,MAX(dim_value),group_agg from bi_host_stat where group_agg = 'MAX' AND dtype = 'HOUR' GROUP BY substring(thedvalue,1,8),COMPANY,target,dim,group_agg; INSERT INTO bi_host_stat select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,COMPANY,target,dim,AVG(dim_value),group_agg from bi_host_stat where group_agg = 'avg' AND dtype = 'DAY' GROUP BY substring(thedvalue,1,6),COMPANY,target,dim,group_agg UNION ALL select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue,COMPANY,target,dim,MAX(dim_value),group_agg from bi_host_stat where group_agg = 'MAX' AND dtype = 'DAY' GROUP BY substring(thedvalue,1,6),COMPANY,target,dim,group_agg; INSERT INTO bi_host_stat select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,COMPANY,target,dim,AVG(dim_value),group_agg from bi_host_stat where group_agg = 'avg' AND dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),COMPANY,target,dim,group_agg UNION ALL select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue,COMPANY,target,dim,MAX(dim_value),group_agg from bi_host_stat where group_agg = 'MAX' AND dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),COMPANY,target,dim,group_agg; -- （7）设备回收趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_recycle_trend`; CREATE TABLE `bi_recycle_trend` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `itm_hardware_resources_cntype` varchar(15) DEFAULT NULL COMMENT '设备类型', `NET_NAME` varchar(50) DEFAULT NULL COMMENT '网系', `total` decimal(38,4) DEFAULT NULL COMMENT '设备回收数', `total_recycled` decimal(38,4) DEFAULT NULL COMMENT '已回收数', `total_no_recycled` decimal(38,4) DEFAULT NULL COMMENT '未回收数' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- https://blog.csdn.net/helloxiaozhe/article/details/78570016 set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; TRUNCATE table bi_recycle_trend; insert into bi_recycle_trend SELECT 'HOUR' as dtype, date_format(a.start_time,'%Y%m%d%H') as thedvalue, c.itm_hardware_resources_cntype, c.NET_NAME, count(1) as total, sum(case when a.end_time is not null then 1 else 0 end) as total_recycled, sum(case when a.end_time is null then 1 else 0 end) as total_no_recycled from icm_bpm.t_work_flow a LEFT JOIN icm_bpm.t_work_resource_real b on a.id = b.work_id -- LEFT JOIN v_bi_itm_hardware_resources c on c.itm_hardware_resources_id=b.resource_id left join (select itm_hardware_resources_ip,itm_hardware_resources_cntype,NET_NAME from ( select itm_hardware_resources_ip,itm_hardware_resources_cntype,NET_NAME from bi_itm_hardware_resources) a group by itm_hardware_resources_ip) c on c.itm_hardware_resources_ip = b.object_id WHERE a.process_type_name='基础设施退网' GROUP BY date_format(a.start_time,'%Y%m%d%H') ,c.itm_hardware_resources_cntype,c.NET_NAME; insert into bi_recycle_trend select 'DAY' as dtype, substr(thedvalue,1,8) as thedvalue, itm_hardware_resources_cntype, NET_NAME, sum(total) as total, sum(total_recycled) as total_recycled, sum(total_no_recycled) as total_no_recycled from bi_recycle_trend where dtype = 'HOUR' GROUP BY substr(thedvalue,1,8),itm_hardware_resources_cntype, NET_NAME; insert into bi_recycle_trend select 'MONTH' as dtype, substr(thedvalue,1,6) as thedvalue, itm_hardware_resources_cntype, NET_NAME, sum(total) as total, sum(total_recycled) as total_recycled, sum(total_no_recycled) as total_no_recycled from bi_recycle_trend where dtype = 'DAY' GROUP BY substr(thedvalue,1,6),itm_hardware_resources_cntype, NET_NAME; insert into bi_recycle_trend select 'YEAR' as dtype, substr(thedvalue,1,4) as thedvalue, itm_hardware_resources_cntype, NET_NAME, sum(total) as total, sum(total_recycled) as total_recycled, sum(total_no_recycled) as total_no_recycled from bi_recycle_trend where dtype = 'MONTH' GROUP BY substr(thedvalue,1,4),itm_hardware_resources_cntype, NET_NAME; -- （8）应用系统监控覆盖率趋势 -- 建表语句 DROP TABLE bi_app_fugai; CREATE TABLE `bi_app_fugai` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `ITM_APP_ID` varchar(255) NOT NULL, `ITM_APP_CODE` varchar(45) DEFAULT NULL COMMENT '编码', `ITM_APP_NAME` varchar(200) DEFAULT NULL COMMENT '应用名称', `ITM_APP_SHORTNAME` varchar(100) DEFAULT NULL COMMENT '应用简称', `ITM_APP_DESC` text COMMENT '系统简介', `ITM_APP_DEPT` varchar(300) DEFAULT NULL COMMENT '归口部门', `ITM_APP_COURTCODE` varchar(45) DEFAULT NULL COMMENT '承建单位\n例如：最高法、山东省高法\n取法院系统的组织机构编码', `ITM_NETGROUP_ID` varchar(30) DEFAULT NULL COMMENT '应用所属网系\n更新字典表\nNG000：外部专网\nNG001：法院专网\nNG002：互联网\nNG003：移动专网\nNG004：涉密内网', `ITM_APPCATAGORY_ID` varchar(45) DEFAULT NULL, `ITM_APP_VENDOR` varchar(45) DEFAULT NULL COMMENT '供应商：浪潮、太极等', `ITM_APP_UPTIME` datetime DEFAULT NULL COMMENT '上线时间', `ITM_APP_VERSION` varchar(45) DEFAULT NULL, `ITM_APP_MAINTENANCE_ID` varchar(45) DEFAULT NULL COMMENT '运维团队,关联运维表，使用内码连接\n', `ITM_APP_ORDER` int(45) DEFAULT NULL COMMENT '应用顺序，展示', `ITM_APP_NETID` varchar(45) DEFAULT NULL COMMENT '专网编号', `ITM_APP_ICONSRC` varchar(100) DEFAULT NULL COMMENT '应用分类\n三类和25类字典表表现', `ITM_APP_SYSTEMURL` varchar(300) DEFAULT NULL, `ORGAN_ID` varchar(30) DEFAULT NULL COMMENT '机构id，关联机构表', `IS_GLOBAL` varchar(45) DEFAULT NULL COMMENT '是否是全局\n如果是全局，则拓扑图是全局的\n如果是非全局，需要维护各自的', `ITM_APP_PID` varchar(45) DEFAULT NULL COMMENT '父类系统\n', `PLATFORM` varchar(100) DEFAULT NULL COMMENT '七大平台', `PRIORITY` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low', `ITM_APP_IP` varchar(30) DEFAULT NULL COMMENT '应用所在服务器IP', `ITM_APP_PORT` varchar(10) DEFAULT NULL COMMENT '应用所在服务器端口', `ITM_APP_WEBROOT` varchar(100) DEFAULT NULL COMMENT '应用访问路径根目录', `ITM_LOCATION` varchar(100) DEFAULT NULL COMMENT '位置信息', `ITM_APP_ASSURE_LEVEL` varchar(1) DEFAULT NULL COMMENT '应用保障级别1一级保障', `SHOW_TYPE` varchar(10) DEFAULT NULL, `ITM_SHOW_FLAG` varchar(10) DEFAULT NULL, `int_id` int(22) DEFAULT NULL COMMENT '其他系统关联用ID', `INSERT_TIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录插入时间', `mantance_vendor` varchar(64) DEFAULT NULL, `mantance_duty` varchar(64) DEFAULT NULL, `mantance_duty_phone` varchar(128) DEFAULT NULL, `start_date` datetime DEFAULT NULL, `mantance_year_number` int(16) DEFAULT NULL, `ne_type` varchar(64) DEFAULT NULL, `creator` varchar(64) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `modifier` varchar(64) DEFAULT NULL, `modify_time` datetime DEFAULT NULL, `time_stamp` datetime DEFAULT NULL, `stateflag` mediumtext, `ruuid` varchar(128) NOT NULL, `dataType` varchar(50) DEFAULT NULL, `login_action` text, `localize_flag` varchar(32) DEFAULT NULL COMMENT '是否国产化，1为国产化，0为非国产化', `current_stat` varchar(32) DEFAULT NULL, `contract` varchar(64) DEFAULT NULL, `emergency_flag` varchar(32) DEFAULT NULL, `monitor_flag` varchar(32) DEFAULT NULL, `development_language` varchar(32) DEFAULT NULL, `app_type` varchar(64) DEFAULT NULL, `contract_money` varchar(32) DEFAULT NULL, `unified_login` varchar(12) DEFAULT NULL COMMENT '统一登录:0:以接入统一桌面;1:未接入统一桌面', `stateflag1` varchar(20) DEFAULT NULL COMMENT '应用状态', `PRIORITY1` varchar(20) DEFAULT NULL COMMENT '重要程度', `app_type_name` varchar(20) DEFAULT NULL COMMENT '分类', `net_name` varchar(20) DEFAULT NULL COMMENT '网系', `cloud_flag` varchar(20) DEFAULT NULL COMMENT '应用对应资源是否上云1为上云，0为不上云', `organ_name` varchar(200) DEFAULT NULL COMMENT '区域', `itm_hardware_resources_areacode` varchar(100) DEFAULT NULL COMMENT '设备区域', `on_line` varchar(255) DEFAULT NULL ) ENGINE = InnoDB DEFAULT CHARSET = utf8; TRUNCATE table bi_app_fugai; insert into bi_app_fugai select 'DAY' AS dtype, a.thedvalue, b.ITM_APP_ID ,b.ITM_APP_CODE ,b.ITM_APP_NAME ,b.ITM_APP_SHORTNAME ,b.ITM_APP_DESC ,b.ITM_APP_DEPT ,b.ITM_APP_COURTCODE , b.ITM_NETGROUP_ID ,b.ITM_APPCATAGORY_ID ,b.ITM_APP_VENDOR ,b.ITM_APP_UPTIME ,b.ITM_APP_VERSION ,b.ITM_APP_MAINTENANCE_ID ,b.ITM_APP_ORDER ,b.ITM_APP_NETID , b.ITM_APP_ICONSRC ,b.ITM_APP_SYSTEMURL ,b.ORGAN_ID ,b.IS_GLOBAL ,b.ITM_APP_PID ,b.PLATFORM ,b.PRIORITY ,b.ITM_APP_IP ,b.ITM_APP_PORT ,b.ITM_APP_WEBROOT , b.ITM_LOCATION ,b.ITM_APP_ASSURE_LEVEL ,b.SHOW_TYPE ,b.ITM_SHOW_FLAG ,b.int_id ,b.INSERT_TIME ,b.mantance_vendor ,b.mantance_duty ,b.mantance_duty_phone , b.start_date ,b.mantance_year_number ,b.ne_type ,b.creator ,b.create_time ,b.modifier ,b.modify_time ,b.time_stamp ,b.stateflag ,b.ruuid ,b.data_Type ,b.login_action , b.localize_flag ,b.current_stat ,b.contract ,b.emergency_flag ,b.monitor_flag ,b.development_language ,b.app_type ,b.contract_money ,b.unified_login, case b.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, c.name as app_type_name, d.net_name as net_name, e.cloud_flag as cloud_flag, f.`SHORT_NAME` as organ_name, e.itm_hardware_resources_areacode as itm_hardware_resources_areacode, b.on_line as on_line from ( select distinct date_format(dt_d,'%Y%m%d') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-11-09') a left join itm_app b on a.thedvalue >= date_format(b.create_time,'%Y%m%d') LEFT JOIN m_com_dict c on b.app_type=c.value LEFT JOIN itm_organ_net d on d.NET_ID = b.ITM_NETGROUP_ID -- 只要app的一个资源上云了 就说明上云了 LEFT JOIN (SELECT servicename,itm_hardware_resources_areacode,concat(max(CASE WHEN itm_hardware_resources_cntype = '云主机' then 1 else 0 end),'') as cloud_flag from bi_itm_hardware_resources where servicename is not null and servicename != '' group by servicename,itm_hardware_resources_areacode) e on b.ITM_APP_NAME= e.servicename left join pub_organ f on b.ORGAN_ID = f.`CODE` WHERE c.dict_index='app_type' UNION ALL select 'MONTH' AS dtype, a.thedvalue, b.ITM_APP_ID ,b.ITM_APP_CODE ,b.ITM_APP_NAME ,b.ITM_APP_SHORTNAME ,b.ITM_APP_DESC ,b.ITM_APP_DEPT ,b.ITM_APP_COURTCODE , b.ITM_NETGROUP_ID ,b.ITM_APPCATAGORY_ID ,b.ITM_APP_VENDOR ,b.ITM_APP_UPTIME ,b.ITM_APP_VERSION ,b.ITM_APP_MAINTENANCE_ID ,b.ITM_APP_ORDER ,b.ITM_APP_NETID , b.ITM_APP_ICONSRC ,b.ITM_APP_SYSTEMURL ,b.ORGAN_ID ,b.IS_GLOBAL ,b.ITM_APP_PID ,b.PLATFORM ,b.PRIORITY ,b.ITM_APP_IP ,b.ITM_APP_PORT ,b.ITM_APP_WEBROOT , b.ITM_LOCATION ,b.ITM_APP_ASSURE_LEVEL ,b.SHOW_TYPE ,b.ITM_SHOW_FLAG ,b.int_id ,b.INSERT_TIME ,b.mantance_vendor ,b.mantance_duty ,b.mantance_duty_phone , b.start_date ,b.mantance_year_number ,b.ne_type ,b.creator ,b.create_time ,b.modifier ,b.modify_time ,b.time_stamp ,b.stateflag ,b.ruuid ,b.data_Type ,b.login_action , b.localize_flag ,b.current_stat ,b.contract ,b.emergency_flag ,b.monitor_flag ,b.development_language ,b.app_type ,b.contract_money ,b.unified_login, case b.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, c.name as app_type_name, d.net_name as net_name, e.cloud_flag as cloud_flag, f.`SHORT_NAME` as organ_name, e.itm_hardware_resources_areacode as itm_hardware_resources_areacode, b.on_line as on_line from ( select distinct date_format(dt_d,'%Y%m') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-11-09') a left join itm_app b on a.thedvalue >= date_format(b.create_time,'%Y%m') LEFT JOIN m_com_dict c on b.app_type=c.value LEFT JOIN itm_organ_net d on d.NET_ID = b.ITM_NETGROUP_ID -- 只要app的一个资源上云了 就说明上云了 LEFT JOIN (SELECT servicename,itm_hardware_resources_areacode,concat(max(CASE WHEN itm_hardware_resources_cntype = '云主机' then 1 else 0 end),'') as cloud_flag from bi_itm_hardware_resources where servicename is not null and servicename != '' group by servicename,itm_hardware_resources_areacode) e on b.ITM_APP_NAME= e.servicename left join pub_organ f on b.ORGAN_ID = f.`CODE` WHERE c.dict_index='app_type' UNION ALL select 'YEAR' AS dtype, a.thedvalue, b.ITM_APP_ID ,b.ITM_APP_CODE ,b.ITM_APP_NAME ,b.ITM_APP_SHORTNAME ,b.ITM_APP_DESC ,b.ITM_APP_DEPT ,b.ITM_APP_COURTCODE , b.ITM_NETGROUP_ID ,b.ITM_APPCATAGORY_ID ,b.ITM_APP_VENDOR ,b.ITM_APP_UPTIME ,b.ITM_APP_VERSION ,b.ITM_APP_MAINTENANCE_ID ,b.ITM_APP_ORDER ,b.ITM_APP_NETID , b.ITM_APP_ICONSRC ,b.ITM_APP_SYSTEMURL ,b.ORGAN_ID ,b.IS_GLOBAL ,b.ITM_APP_PID ,b.PLATFORM ,b.PRIORITY ,b.ITM_APP_IP ,b.ITM_APP_PORT ,b.ITM_APP_WEBROOT , b.ITM_LOCATION ,b.ITM_APP_ASSURE_LEVEL ,b.SHOW_TYPE ,b.ITM_SHOW_FLAG ,b.int_id ,b.INSERT_TIME ,b.mantance_vendor ,b.mantance_duty ,b.mantance_duty_phone , b.start_date ,b.mantance_year_number ,b.ne_type ,b.creator ,b.create_time ,b.modifier ,b.modify_time ,b.time_stamp ,b.stateflag ,b.ruuid ,b.data_Type ,b.login_action , b.localize_flag ,b.current_stat ,b.contract ,b.emergency_flag ,b.monitor_flag ,b.development_language ,b.app_type ,b.contract_money ,b.unified_login, case b.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, c.name as app_type_name, d.net_name as net_name, e.cloud_flag as cloud_flag, f.`SHORT_NAME` as organ_name, e.itm_hardware_resources_areacode as itm_hardware_resources_areacode, b.on_line as on_line from ( select distinct date_format(dt_d,'%Y') as thedvalue from bi_m_date where dt_d >= '2021-01-01' and dt_d < '2023-11-09') a left join itm_app b on a.thedvalue >= date_format(b.create_time,'%Y') LEFT JOIN m_com_dict c on b.app_type=c.value LEFT JOIN itm_organ_net d on d.NET_ID = b.ITM_NETGROUP_ID -- 只要app的一个资源上云了 就说明上云了 LEFT JOIN (SELECT servicename,itm_hardware_resources_areacode,concat(max(CASE WHEN itm_hardware_resources_cntype = '云主机' then 1 else 0 end),'') as cloud_flag from bi_itm_hardware_resources where servicename is not null and servicename != '' group by servicename,itm_hardware_resources_areacode) e on b.ITM_APP_NAME= e.servicename left join pub_organ f on b.ORGAN_ID = f.`CODE` WHERE c.dict_index='app_type'; -- （9）基础设施上线趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_online_trend`; CREATE TABLE `bi_online_trend` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `itm_hardware_resources_areacode` varchar(100) DEFAULT NULL COMMENT '区域/机房', `itm_hardware_resources_cntype` varchar(15) DEFAULT NULL COMMENT '设备类型', `itm_hardware_resources_name` varchar(100) DEFAULT NULL COMMENT '设备名称', `NET_NAME` varchar(50) DEFAULT NULL COMMENT '网系', `total` decimal(38,4) DEFAULT NULL COMMENT '上线总量', `ended_total` decimal(38, 4) DEFAULT NULL COMMENT '退网总量', `ending_total` decimal(38, 4) DEFAULT NULL COMMENT '退网中总量' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; truncate table bi_online_trend; insert into bi_online_trend SELECT 'HOUR' as dtype, date_format(a.end_time,'%Y%m%d%H') as thedvalue, c.itm_hardware_resources_areacode, c.itm_hardware_resources_cntype, c.itm_hardware_resources_name, c.NET_NAME, SUM(CASE WHEN a.process_type_name='基础设施入网' and a.end_time is not null THEN 1 ELSE 0 END) as 'total', SUM(CASE WHEN a.process_type_name='基础设施退网' and a.end_time is not null THEN 1 ELSE 0 END) as 'ended_total', SUM(CASE WHEN a.process_type_name='基础设施退网' and a.end_time is null THEN 1 ELSE 0 END) as 'ending_total' from icm_bpm.t_work_flow a LEFT JOIN icm_bpm.t_work_resource_real b on a.id = b.work_id left join ( select itm_hardware_resources_id,itm_hardware_resources_cntype,NET_NAME,itm_hardware_resources_areacode,itm_hardware_resources_name from ( select itm_hardware_resources_id,itm_hardware_resources_cntype,NET_NAME,itm_hardware_resources_areacode, itm_hardware_resources_name from bi_itm_hardware_resources) a group by itm_hardware_resources_id ) c on c.itm_hardware_resources_id = b.object_id GROUP BY date_format(a.end_time,'%Y%m%d%H'),c.itm_hardware_resources_areacode,c.itm_hardware_resources_cntype,c.NET_NAME; insert into bi_online_trend select 'DAY' as dtype, substr(thedvalue,1,8) as thedvalue, itm_hardware_resources_areacode, itm_hardware_resources_cntype, itm_hardware_resources_name, NET_NAME, sum(total) as total, sum(ended_total) as ended_total, sum(ending_total) as ending_total from bi_online_trend where dtype = 'HOUR' GROUP BY substr(thedvalue,1,8),itm_hardware_resources_areacode,itm_hardware_resources_cntype,NET_NAME; insert into bi_online_trend select 'MONTH' as dtype, substr(thedvalue,1,6) as thedvalue, itm_hardware_resources_areacode, itm_hardware_resources_cntype, itm_hardware_resources_name, NET_NAME, sum(total) as total, sum(ended_total) as ended_total, sum(ending_total) as ending_total from bi_online_trend where dtype = 'DAY' GROUP BY substr(thedvalue,1,6),itm_hardware_resources_areacode,itm_hardware_resources_cntype,NET_NAME; insert into bi_online_trend select 'YEAR' as dtype, substr(thedvalue,1,4) as thedvalue, itm_hardware_resources_areacode, itm_hardware_resources_cntype, itm_hardware_resources_name, NET_NAME, sum(total) as total, sum(ended_total) as ended_total, sum(ending_total) as ending_total from bi_online_trend where dtype = 'MONTH' GROUP BY substr(thedvalue,1,4),itm_hardware_resources_areacode,itm_hardware_resources_cntype,NET_NAME; -- （10）操作次数趋势、响应时间趋势、应用系统用户趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_app_log_result`; CREATE TABLE `bi_app_log_result` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `dhour` varchar(15) DEFAULT NULL COMMENT '小时', `PRIORITY` varchar(20) DEFAULT NULL COMMENT '重要程度', `app_type_name` varchar(50) DEFAULT NULL COMMENT '分类', `net_name` varchar(50) DEFAULT NULL COMMENT '网系', `id` bigint(20) NOT NULL DEFAULT '0' COMMENT '自增标记，用于数据同步', `app_id` varchar(200) DEFAULT NULL COMMENT '应用ID', `app_name` varchar(200) DEFAULT NULL COMMENT '应用名称', `orgin_id` varchar(32) DEFAULT NULL COMMENT '组织ID', `net_id` varchar(30) DEFAULT NULL, `independent_visits_num` varchar(10) DEFAULT NULL COMMENT '独立访客数/活跃用户数', `visits_num` varchar(10) DEFAULT NULL COMMENT '访客数', `oper_num` varchar(10) DEFAULT NULL COMMENT '操作次数/访问量', `slow_oper` varchar(10) DEFAULT NULL COMMENT '慢操作占比', `min_resp_time` varchar(10) DEFAULT NULL COMMENT '最小响应时间(毫秒)', `max_resp_time` varchar(10) DEFAULT NULL COMMENT '最大响应时间(毫秒)', `avg_resp_time` varchar(10) DEFAULT NULL COMMENT '平均响应时间(毫秒）', `time_stamp` varchar(32) DEFAULT NULL COMMENT '时间戳', `monitor_time` varchar(32) DEFAULT NULL COMMENT '推送时间', `territory_visitor` varchar(32) DEFAULT NULL COMMENT '地域访问数', `reg_user_num` varchar(32) DEFAULT NULL COMMENT '注册用户数', `sign_num` varbinary(32) DEFAULT NULL COMMENT '登录次数', `new_reg_user_num` varchar(32) DEFAULT NULL COMMENT '新注册用户数', `time_type` varchar(32) DEFAULT NULL COMMENT '类型（hour：小时 day：天 month：月 国内：domestic 国外：foreign）', `create_time` varchar(32) DEFAULT NULL COMMENT '插入时间', `organ_name` varchar(200) DEFAULT NULL COMMENT '区域' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_app_log_result; insert into bi_app_log_result select 'HOUR' as dtype, date_format(a.monitor_time,'%Y%m%d%H') as thedvalue, date_format(a.monitor_time,'%H') as dhour, case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY, d.name as app_type_name, c.net_name as net_name, a.id ,a.app_id ,a.app_name ,a.orgin_id ,a.net_id,sum(a.independent_visits_num) as independent_visits_num,sum(a.visits_num) as visits_num, sum(a.oper_num) as oper_num,avg(a.slow_oper + 0.0) as slow_oper,min(a.min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time, avg(a.avg_resp_time + 0.0) as avg_resp_time,date_format(a.monitor_time,'%H') as time_stamp,'' as monitor_time, sum(a.territory_visitor) as territory_visitor,sum(a.reg_user_num) as reg_user_num,'' as sign_num,sum(a.new_reg_user_num) as new_reg_user_num, '' as time_type,'' as create_time, e.`SHORT_NAME` as organ_name from t_ind_obj_app_log_result a LEFT JOIN itm_app b on a.app_id =b.ITM_APP_ID LEFT JOIN itm_organ_net c on c.NET_ID = a.net_id inner JOIN (select * from m_com_dict where dict_index = 'app_type') d on b.app_type=d.value left join pub_organ e on b.ORGAN_ID = e.`CODE` WHERE time_type in('hour') group by date_format(a.monitor_time,'%Y%m%d%H'),date_format(a.monitor_time,'%H'), case b.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end,d.name,c.net_name,a.id ,a.app_id ,a.app_name ,a.orgin_id ,a.net_id,e.`SHORT_NAME`; insert into bi_app_log_result select 'DAY' as dtype, substring(thedvalue,1,8) AS thedvalue, dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,sum(independent_visits_num) as independent_visits_num,sum(visits_num) as visits_num, sum(oper_num) as oper_num,avg(slow_oper + 0.0) as slow_oper,min(min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time, avg(avg_resp_time + 0.0) as avg_resp_time,dhour as time_stamp,'' as monitor_time, sum(territory_visitor) as territory_visitor,sum(reg_user_num) as reg_user_num,'' as sign_num,sum(new_reg_user_num) as new_reg_user_num, '' as time_type,'' as create_time,organ_name from bi_app_log_result where dtype = 'HOUR' group by substring(thedvalue,1,8),dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,organ_name; insert into bi_app_log_result select 'MONTH' as dtype, substring(thedvalue,1,6) AS thedvalue, dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,sum(independent_visits_num) as independent_visits_num,sum(visits_num) as visits_num, sum(oper_num) as oper_num,avg(slow_oper + 0.0) as slow_oper,min(min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time, avg(avg_resp_time + 0.0) as avg_resp_time,dhour as time_stamp,'' as monitor_time, sum(territory_visitor) as territory_visitor,sum(reg_user_num) as reg_user_num,'' as sign_num,sum(new_reg_user_num) as new_reg_user_num, '' as time_type,'' as create_time,organ_name from bi_app_log_result where dtype = 'DAY' group by substring(thedvalue,1,6),dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,organ_name; insert into bi_app_log_result select 'YEAR' as dtype, substring(thedvalue,1,4) AS thedvalue, dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,sum(independent_visits_num) as independent_visits_num,sum(visits_num) as visits_num, sum(oper_num) as oper_num,avg(slow_oper + 0.0) as slow_oper,min(min_resp_time) as min_resp_time,max(max_resp_time) as max_resp_time, avg(avg_resp_time + 0.0) as avg_resp_time,dhour as time_stamp,'' as monitor_time, sum(territory_visitor) as territory_visitor,sum(reg_user_num) as reg_user_num,'' as sign_num,sum(new_reg_user_num) as new_reg_user_num, '' as time_type,'' as create_time,organ_name from bi_app_log_result where dtype = 'MONTH' group by substring(thedvalue,1,4),dhour,PRIORITY,app_type_name,net_name,id ,app_id ,app_name ,orgin_id ,net_id,organ_name; -- （16）应用漏洞变化趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_panoramic_view_app_ld_trend`; CREATE TABLE `bi_panoramic_view_app_ld_trend` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(8) DEFAULT NULL COMMENT '日期类型', `app_name` varchar(128) DEFAULT NULL COMMENT '应用名称', `score_count` varchar(53) DEFAULT NULL COMMENT '指标值', `NET_NAME` varchar(30) DEFAULT NULL COMMENT '网系名称', `score_name` varchar(25) DEFAULT NULL COMMENT '覆盖率,合格率' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_panoramic_view_app_ld_trend; insert into bi_panoramic_view_app_ld_trend select 'DAY' as dtype,date_format(savi.insert_time,'%Y%m%d') as thedvalue, savi.name as app_name, max(savi.vul_total_count) as score_count, ion.NET_NAME as NET_NAME, mcd.name as score_name from safe_app_vul_info savi LEFT JOIN itm_app ia on savi.name = ia.ITM_APP_NAME LEFT JOIN m_com_dict mcd on ia.app_type = mcd.dispindex and mcd.dict_index='app_type' LEFT JOIN itm_organ_net ion on ia.ITM_NETGROUP_ID = ion.NET_ID GROUP BY date_format(savi.insert_time,'%Y%m%d'),savi.name,ion.NET_NAME,mcd.name; insert into bi_panoramic_view_app_ld_trend select 'MONTH' as dtype, substr(thedvalue,1,6) as thedvalue, app_name, sum(score_count) as score_count, NET_NAME, score_name from bi_panoramic_view_app_ld_trend where dtype = 'DAY' GROUP BY substr(thedvalue,1,6),app_name,NET_NAME,score_name; insert into bi_panoramic_view_app_ld_trend select 'YEAR' as dtype, substr(thedvalue,1,4) as thedvalue, app_name, sum(score_count) as score_count, NET_NAME, score_name from bi_panoramic_view_app_ld_trend where dtype = 'MONTH' GROUP BY substr(thedvalue,1,4),app_name,NET_NAME,score_name; -- 建表语句 DROP TABLE IF EXISTS `bi_panoramic_new_trend`; CREATE TABLE `bi_panoramic_new_trend` ( `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期', `app_name` varchar(50) DEFAULT NULL COMMENT '设备资产名称', `score_name` varchar(50) DEFAULT NULL COMMENT '选择对象', `the_score` double DEFAULT NULL COMMENT '网系名称', `NET_NAME` varchar(100) DEFAULT NULL COMMENT '网系名称', `organ_name` varchar(100) DEFAULT NULL COMMENT '区域' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 用cross join进行的列转行 truncate table bi_panoramic_new_trend; insert into bi_panoramic_new_trend select 'DAY' as dtype, thedvalue, '' as app_name, case b.mm when 1 then '安全设备' when 2 then '网络设备' when 3 then '终端PC' when 4 then '服务器' else '' end as score_name, case b.mm when 1 then security_device_vul_total_count when 2 then network_device_vul_total_count when 3 then terminal_vul_total_count when 4 then server_vul_total_count else 0.0 end as the_score, null as NET_NAME, null as organ_name from (select date_format(insert_time,'%Y%m%d') as thedvalue, max(security_device_vul_total_count) + 0.0 as security_device_vul_total_count, max(network_device_vul_total_count) + 0.0 as network_device_vul_total_count, max(terminal_vul_total_count) + 0.0 as terminal_vul_total_count, max(server_vul_total_count) + 0.0 as server_vul_total_count from safe_index_info_1 group by date_format(insert_time,'%Y%m%d'),id) a cross join (select 1 mm union all select 2 union all select 3 union all select 4) b union all select 'DAY' as dtype, date_format(savi.insert_time,'%Y%m%d') as thedvalue, savi.name as app_name, '应用系统' as score_name, max(savi.vul_total_count) as the_score, ion.NET_NAME as NET_NAME, f.name as organ_name from safe_app_vul_info savi LEFT JOIN itm_app ia on savi.name = ia.ITM_APP_NAME LEFT JOIN m_com_dict mcd on ia.app_type = mcd.dispindex and mcd.dict_index='app_type' LEFT JOIN itm_organ_net ion on ia.ITM_NETGROUP_ID = ion.NET_ID left join pub_organ f on ia.ORGAN_ID = f.`CODE` GROUP BY date_format(savi.insert_time,'%Y%m%d'),savi.name,ion.NET_NAME,f.name; insert into bi_panoramic_new_trend select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, app_name,score_name,avg(the_score) as the_score,NET_NAME,organ_name from bi_panoramic_new_trend where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),app_name,score_name,NET_NAME,organ_name; insert into bi_panoramic_new_trend select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, app_name,score_name,avg(the_score) as the_score,NET_NAME,organ_name from bi_panoramic_new_trend where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),app_name,score_name,NET_NAME,organ_name; -- （20）综合安全评分趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_panoramic_view_safe_trend`; CREATE TABLE `bi_panoramic_view_safe_trend` ( `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期', `id` varchar(50) NOT NULL DEFAULT '0' COMMENT '设备名称暂时没有数据', `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '指标名称', `the_score` double DEFAULT NULL COMMENT '指标值' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_panoramic_view_safe_trend; insert into bi_panoramic_view_safe_trend select 'DAY' as dtype, thedvalue, '' as id, case b.mm when 1 then '综合分' when 2 then '网络资源明晰度评分' when 3 then '资产脆弱性安全指数得分' when 4 then '网络攻击安全指数得分' when 5 then '边界安全指数得分' when 6 then '应用系统合规性得分' when 7 then '运维风险可控度得分' else '' end as score_name, case b.mm when 1 then global_score when 2 then network_resource_score when 3 then asset_vul_score when 4 then network_security_score when 5 then border_security_score when 6 then application_system_score when 7 then operation_risk_score else 0.0 end as the_score from (select date_format(insert_time,'%Y%m%d') as thedvalue, max(global_score) + 0.0 as global_score, max(network_resource_score) + 0.0 as network_resource_score, max(asset_vul_score) + 0.0 as asset_vul_score, max(network_security_score) + 0.0 as network_security_score, max(border_security_score) + 0.0 as border_security_score, max(application_system_score) + 0.0 as application_system_score, max(operation_risk_score) + 0.0 as operation_risk_score from safe_index_score group by date_format(insert_time,'%Y%m%d'),id) a cross join (select 1 mm union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7) b; insert into bi_panoramic_view_safe_trend select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, '' as id,score_name,avg(the_score) as the_score from bi_panoramic_view_safe_trend where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),score_name; insert into bi_panoramic_view_safe_trend select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, '' as id,score_name,avg(the_score) as the_score from bi_panoramic_view_safe_trend where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),score_name; -- （21）网络攻击变化趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_panoramic_view_net_attack_trend`; CREATE TABLE `bi_panoramic_view_net_attack_trend` ( `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期', `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '指标名称', `the_score` double DEFAULT NULL COMMENT '指标值' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_panoramic_view_net_attack_trend; insert into bi_panoramic_view_net_attack_trend select 'DAY' as dtype, thedvalue, case b.mm when 1 then '网络攻击尝试数' when 2 then '网络攻击成功数' when 3 then '网络攻击结果不明数' when 4 then '恶意程序数' else '' end as score_name, case b.mm when 1 then network_attack_try_count when 2 then network_attack_success_count when 3 then network_attack_unknown_count when 4 then server_vul_total_count else 0.0 end as the_score from (select date_format(insert_time,'%Y%m%d') as thedvalue, max(network_attack_try_count) + 0.0 as network_attack_try_count, max(network_attack_success_count) + 0.0 as network_attack_success_count, max(network_attack_unknown_count) + 0.0 as network_attack_unknown_count, max(server_vul_total_count) + 0.0 as server_vul_total_count from safe_index_info_1 group by date_format(insert_time,'%Y%m%d'),id) a cross join (select 1 mm union all select 2 union all select 3 union all select 4) b; insert into bi_panoramic_view_net_attack_trend select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, score_name,avg(the_score) as the_score from bi_panoramic_view_net_attack_trend where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),score_name; insert into bi_panoramic_view_net_attack_trend select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, score_name,avg(the_score) as the_score from bi_panoramic_view_net_attack_trend where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),score_name; -- （22）设备漏洞变化趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_panoramic_view_sb_ld_trend`; CREATE TABLE `bi_panoramic_view_sb_ld_trend` ( `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期', `app_name` varchar(50) NOT NULL DEFAULT '0' COMMENT '设备名称暂时没有数据', `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '', `the_score` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_panoramic_view_sb_ld_trend; insert into bi_panoramic_view_sb_ld_trend select 'DAY' as dtype, thedvalue, '' as app_name, case b.mm when 1 then '安全设备漏洞' when 2 then '网络设备漏洞' when 3 then '终端PC漏洞' when 4 then '服务器漏洞' else '' end as score_name, case b.mm when 1 then security_device_vul_total_count when 2 then network_device_vul_total_count when 3 then terminal_vul_total_count when 4 then server_vul_total_count else 0.0 end as the_score from (select date_format(insert_time,'%Y%m%d') as thedvalue, max(security_device_vul_total_count) + 0.0 as security_device_vul_total_count, max(network_device_vul_total_count) + 0.0 as network_device_vul_total_count, max(terminal_vul_total_count) + 0.0 as terminal_vul_total_count, max(server_vul_total_count) + 0.0 as server_vul_total_count from safe_index_info_1 group by date_format(insert_time,'%Y%m%d'),id) a cross join (select 1 mm union all select 2 union all select 3 union all select 4) b; insert into bi_panoramic_view_sb_ld_trend select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, '' as app_name,score_name,avg(the_score) as the_score from bi_panoramic_view_sb_ld_trend where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),score_name; insert into bi_panoramic_view_sb_ld_trend select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, '' as app_name,score_name,avg(the_score) as the_score from bi_panoramic_view_sb_ld_trend where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),score_name; -- （23）设备准入变化趋势、双因子认证变化趋势、纳入4A变化趋势 -- 建表语句 DROP TABLE IF EXISTS `bi_panoramic_view_shaung_yin_a4_trend`; CREATE TABLE `bi_panoramic_view_shaung_yin_a4_trend` ( `dtype` varchar(10) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '日期', `id` varchar(50) NOT NULL DEFAULT '0' COMMENT '设备名称暂时没有数据', `score_name` varchar(50) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '指标名称', `the_score` double DEFAULT NULL COMMENT '指标值' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_panoramic_view_shaung_yin_a4_trend; insert into bi_panoramic_view_shaung_yin_a4_trend select 'DAY' as dtype, thedvalue, case b.mm when 1 then '终端PC' when 2 then '终端PC' when 3 then '终端PC' when 4 then '' when 5 then '' when 6 then '' when 7 then '' when 8 then '' else '' end as id, case b.mm when 1 then '入网数' when 2 then '准入数' when 3 then '审计数' when 4 then '安全设备双因子认证' when 5 then '网络设备双因子认证' when 6 then '服务器双因子认证' when 7 then '服务器4A' when 8 then '应用4A' else '' end as score_name, case b.mm when 1 then dim1 when 2 then dim2 when 3 then dim3 when 4 then dim4 when 5 then dim5 when 6 then dim6 when 7 then dim7 when 8 then dim8 else 0.0 end as the_score from (select date_format(insert_time,'%Y%m%d') as thedvalue, max(terminal_count) + 0.0 as dim1, max(terminal_admittance_count) + 0.0 as dim2, max(terminal_audit_count) + 0.0 as dim3, max(1.0 - security_device_unused_eactor_percent) + 0.0 as dim4, max(1.0 - network_device_unused_factor_percent) + 0.0 as dim5, max(1.0 - server_unused_factor_percent) + 0.0 as dim6, max(1.0 - case when server_resource_failure_count + server_resource_success_count = 0 then 0.0 else server_resource_failure_count/(server_resource_failure_count + server_resource_success_count) * 1.0 end) + 0.0 as dim7, max(1.0 - application_unused_factor_percent) as dim8 from safe_index_info_1 group by date_format(insert_time,'%Y%m%d'),id) a cross join (select 1 mm union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8) b; insert into bi_panoramic_view_shaung_yin_a4_trend select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, id,score_name,avg(the_score) as the_score from bi_panoramic_view_shaung_yin_a4_trend where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),score_name,id; insert into bi_panoramic_view_shaung_yin_a4_trend select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, id,score_name,avg(the_score) as the_score from bi_panoramic_view_shaung_yin_a4_trend where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),score_name,id; -- （24）运维工单总量趋势、工单按时解决率趋势、各类工单未按时解决排名、工单满意度趋势、工单处理耗时排名、工单平均处理时长 -- 建表语句 DROP TABLE IF EXISTS `bi_work_flow_group_by_time`; CREATE TABLE `bi_work_flow_group_by_time` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `process_type_name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '流程定义类型名称', `serve_area` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '所属区域', `serve_department` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '所属部门', `serve_firm` varchar(32) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '所属厂商', `total_works` bigint(21) NOT NULL DEFAULT '0' COMMENT '工单总量', `total_works_end` decimal(23,0) DEFAULT NULL COMMENT '已解决总量', `total_works_noend` decimal(23,0) DEFAULT NULL COMMENT '未解决总量', `total_works_notimeout` decimal(23,0) DEFAULT NULL COMMENT '未超时总量', `total_works_timeout` decimal(23,0) DEFAULT NULL COMMENT '超时总量', `total_time_consuming` double DEFAULT NULL COMMENT '总耗时', `avg_time_consuming` double DEFAULT NULL COMMENT '平均耗时', `satisfaction` double DEFAULT NULL COMMENT '满意度', `app_id` varchar(20) DEFAULT NULL COMMENT 'appID' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_work_flow_group_by_time; insert into bi_work_flow_group_by_time select 'HOUR' as dtype, date_format(a.start_time,'%Y%m%d%H') as thedvalue, -- str_to_date a.process_type_name, a.serve_area, c.display_name as serve_department, d.manufacturer_name as serve_firm, count(*) as total_works, sum(case when end_time is not null then 1 else 0 end) as total_works_end, sum(case when end_time is not null then 0 else 1 end) as total_works_noend, sum(case when time_out =1 then 1 else 0 end) as total_works_notimeout, sum(case when time_out =0 then 1 else 0 end) as total_works_timeout, sum(time_consuming) as total_time_consuming, avg(time_consuming + 0.0) as avg_time_consuming, avg(satisfaction + 0.0) as satisfaction, b.app_id from icm_bpm.t_work_flow a left join icm_bpm.t_work_resource_real b on a.id = b.work_id left join icm.sys_org c on a.serve_department = c.org_code left join icm.cfg_manufacturer d on a.handler_firm = d.id group by date_format(a.start_time,'%Y%m%d%H'), a.process_type_name, a.serve_area, c.display_name, d.manufacturer_name,b.app_id; insert into bi_work_flow_group_by_time select 'DAY' as dtype,substring(thedvalue,1,8) AS thedvalue, process_type_name, serve_area, serve_department, serve_firm, sum(total_works) as total_works, sum(total_works_end) as total_works_end, sum(total_works_noend) as total_works_noend, sum(total_works_notimeout) as total_works_notimeout, sum(total_works_timeout) as total_works_timeout, sum(total_time_consuming) as total_time_consuming, avg(avg_time_consuming + 0.0) as avg_time_consuming, avg(satisfaction + 0.0) as satisfaction, app_id from bi_work_flow_group_by_time where dtype = 'HOUR' group by substring(thedvalue,1,8), process_type_name, serve_area, serve_department, serve_firm,app_id; insert into bi_work_flow_group_by_time select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, process_type_name, serve_area, serve_department, serve_firm, sum(total_works) as total_works, sum(total_works_end) as total_works_end, sum(total_works_noend) as total_works_noend, sum(total_works_notimeout) as total_works_notimeout, sum(total_works_timeout) as total_works_timeout, sum(total_time_consuming) as total_time_consuming, avg(avg_time_consuming + 0.0) as avg_time_consuming, avg(satisfaction + 0.0) as satisfaction, app_id from bi_work_flow_group_by_time where dtype = 'DAY' group by substring(thedvalue,1,6), process_type_name, serve_area, serve_department, serve_firm,app_id; insert into bi_work_flow_group_by_time select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, process_type_name, serve_area, serve_department, serve_firm, sum(total_works) as total_works, sum(total_works_end) as total_works_end, sum(total_works_noend) as total_works_noend, sum(total_works_notimeout) as total_works_notimeout, sum(total_works_timeout) as total_works_timeout, sum(total_time_consuming) as total_time_consuming, avg(avg_time_consuming + 0.0) as avg_time_consuming, avg(satisfaction + 0.0) as satisfaction, app_id from bi_work_flow_group_by_time where dtype = 'MONTH' group by substring(thedvalue,1,4), process_type_name, serve_area, serve_department, serve_firm,app_id; -- （26）最高法院效能分析排序 -- 建表语句 DROP TABLE IF EXISTS `bi_app_stat_group_transposed`; CREATE TABLE `bi_app_stat_group_transposed` ( `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `itm_hardware_resources_id` varchar(200) NOT NULL DEFAULT '' COMMENT '告警设备ID', `ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称', `col_name` varchar(200) CHARACTER SET utf8mb4 NOT NULL DEFAULT '', `col_value` decimal(24,10) DEFAULT NULL, KEY `idx_1` (`thedvalue`,`col_name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; truncate table bi_app_stat_group_transposed; insert into bi_app_stat_group_transposed select thedvalue,itm_hardware_resources_id,ITM_HARDWARE_RESOURCES_NAME, case b.mm when 1 then 'CPU平均利用率' when 2 then 'CPU峰值利用率' when 3 then '内存平均利用率' when 4 then '内存峰值利用率' when 5 then '磁盘利用率' when 6 then '带宽平均利用率' when 7 then '带宽峰值利用率' when 8 then '交换机错包率' when 9 then '交换机丢包率' else '' end as col_name, case b.mm when 1 then dim1 when 2 then dim2 when 3 then dim3 when 4 then dim4 when 5 then dim5 when 6 then dim6 when 7 then dim7 when 8 then dim8 when 9 then dim9 else 0.0 end as col_value from ( select thedvalue,itm_hardware_resources_id,ITM_HARDWARE_RESOURCES_NAME, avg(IND_VALUE_Network_Cpurate) as dim1,max(IND_VALUE_Network_Cpurate_High) as dim2,avg(IND_VALUE_Network_Memrate) as dim3,avg(IND_VALUE_Network_Memrate_High) as dim4, avg(IND_VALUE_HOST_DISK_UTILIZATION) as dim5,avg(Band_widthrate) as dim6,max(Bandwidthrate_High) as dim7,avg(Network_PacketLossrate) as dim8,avg(Network_PacketErrorrate) as dim9 from bi_app_stat_group_by_time where dtype = 'DAY' and QGorZG = '最高' group by thedvalue,itm_hardware_resources_id,ITM_HARDWARE_RESOURCES_NAME) a cross join (select 1 mm union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b; -- （27）应用系统业务指标 -- 建表语句 DROP TABLE IF EXISTS `bi_app_tsc_stat`; CREATE TABLE `bi_app_tsc_stat` ( `dtype` varchar(10) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(12) DEFAULT NULL COMMENT '日期', `app_id` varchar(20) NOT NULL DEFAULT '' COMMENT 'appID', `app_name` varchar(255) NOT NULL DEFAULT '' COMMENT 'app名称', `dim` varchar(255) NOT NULL DEFAULT '' COMMENT '维度', `dim_tuli` varchar(255) DEFAULT NULL COMMENT '图例', `dim_value` decimal(20,2) DEFAULT NULL COMMENT '指标值', `dim_value_unit` varchar(255) DEFAULT NULL COMMENT '指标单位', `value_calculate` varchar(255) DEFAULT NULL COMMENT '指标计算方式', `dim_tuli_value` decimal(20,2) DEFAULT NULL COMMENT '图例值' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; drop procedure if exists wk_app_new; delimiter $$ create procedure wk_app_new() begin declare i int default 0; declare continue_handler int default 0; declare c_app_id varchar(200); declare c_app_name varchar(200); declare c_fieldname varchar(64); declare c_fielddesc varchar(64); declare c_statmodel varchar(255); declare c_quotaUnit varchar(10); declare c1 cursor for select distinct a.APP_ID,a.APP_NAME,b.fieldname,b.fielddesc,b.statmodel,b.quotaUnit from (select distinct ITM_APP_ID as APP_ID,ITM_APP_NAME as APP_NAME from itm_app) a left join (select a.appid,a.fieldname,a.fielddesc,a.statmodel,a.quotaUnit from itm_app_tsc_set a where transtype = '62' and showType = 'line') b on a.APP_ID = b.appid where (fieldname is not null) and (statmodel is not null and statmodel != ''); declare c2 cursor for select distinct a.APP_ID,a.APP_NAME,b.fieldname,b.fielddesc,b.statmodel,b.quotaUnit from (select distinct ITM_APP_ID as APP_ID,ITM_APP_NAME as APP_NAME from itm_app) a left join (select distinct a.appid,a.fieldname,b.fielddesc,a.statmodel,a.quotaUnit from (select appid,fieldname,fielddesc,statmodel,quotaUnit,showType from itm_app_tsc_set where transtype = '62') a left join (select appid,fieldname,fielddesc,statmodel,quotaUnit,showType from itm_app_tsc_set where transtype = '62') b on a.appid = b.appid and a.fieldname = b.fieldname where a.showType = 'bar' and b.showType = 'line') b on a.APP_ID = b.appid where (fieldname is not null) and (statmodel is not null and statmodel != ''); declare continue handler for not found set i=1; declare continue handler for sqlexception,sqlwarning set continue_handler=1; open c1; while i = 0 do fetch c1 into c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit; set @need_sql=concat(" insert into bi_app_tsc_stat select \'MONTH\' as dtype,date_format(TRANSED_DT,\'%Y%m\') as thedvalue,\'',c_app_id,'\' as app_id,\'',c_app_name,'\' as app_name,\'',c_fielddesc,'\' as dim,\'',c_fielddesc,'\' as dim_tuli,' ,c_statmodel,'(',c_fieldname,') as dim_value,\'',c_quotaUnit,'\' as dim_value_unit,\'',c_statmodel,'\' as value_calculate,null as dim_tuli_value from t_ind_obj_app_tsc_stat where APP_ID = \'',c_app_id, '\' and TRANS_TYPE = \'62\' and (CEXT0 is null or CEXT0 = \'\') group by date_format(TRANSED_DT,\'%Y%m\')"); PREPARE final_sql FROM @need_sql; EXECUTE final_sql; -- if continue_handler = 1 THEN -- insert into bi_app_tsc_default values(c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit); -- end if; end while; close c1; set i = 0; open c2; while i = 0 do fetch c2 into c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit; set @need_sql=concat(" insert into bi_app_tsc_stat select \'MONTH\' as dtype,date_format(TRANSED_DT,\'%Y%m\') as thedvalue,\'',c_app_id,'\' as app_id,\'',c_app_name,'\' as app_name,\'',c_fielddesc,'\' as dim,CEXT0 as dim_tuli, null as dim_value,\'',c_quotaUnit,'\' as dim_value_unit,\'',c_statmodel,'\' as value_calculate,',c_statmodel,'(',c_fieldname,') as dim_value from t_ind_obj_app_tsc_stat where APP_ID = \'',c_app_id, '\' and TRANS_TYPE = \'62\' and CEXT0 is not null and cext0 != \'\' group by date_format(TRANSED_DT,\'%Y%m\'), CEXT0"); PREPARE final_sql FROM @need_sql; EXECUTE final_sql; -- if continue_handler = 1 THEN -- insert into bi_app_tsc_default values(c_app_id,c_app_name,c_fieldname,c_fielddesc,c_statmodel,c_quotaUnit); -- end if; end while; close c2; end $$ delimiter ; -- truncate table bi_app_tsc_default; truncate table bi_app_tsc_stat; call wk_app_new(); delete from bi_app_tsc_stat where dtype = 'YEAR'; insert into bi_app_tsc_stat select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,sum(dim_value) as dim_value,dim_value_unit,value_calculate,sum(dim_tuli_value) as dim_tuli_value from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'SUM' group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate union all select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,avg(dim_value) as dim_value,dim_value_unit,value_calculate,avg(dim_tuli_value) as dim_tuli_value from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'AVG' group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate union all select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,max(dim_value) as dim_value,dim_value_unit,value_calculate,max(dim_tuli_value) as dim_tuli_value from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'MAX' group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate union all select 'YEAR' as dtype,substring(thedvalue,1,4) as thedvalue,app_id,app_name,dim,dim_tuli,min(dim_value) as dim_value,dim_value_unit,value_calculate ,min(dim_tuli_value) as dim_tuli_value from bi_app_tsc_stat where dtype = 'MONTH' and value_calculate = 'MIN' group by substring(thedvalue,1,4), app_id, app_name, dim,dim_tuli,dim_value_unit,value_calculate; -- （28） 工单报表 -- 建表语句 DROP TABLE IF EXISTS `bi_workflow`; CREATE TABLE `bi_workflow` ( `before_handler` varchar(255) DEFAULT NULL, `a_id` varchar(255) NOT NULL COMMENT '主键id', `theme` varchar(255) DEFAULT NULL COMMENT '工单主题', `proposer_phone` varchar(255) DEFAULT NULL COMMENT '申请人联系方式', `proposer_name` varchar(255) DEFAULT NULL COMMENT '申请人姓名', `proposer` varchar(255) DEFAULT NULL COMMENT '申请人id', `serial_number` varchar(255) DEFAULT NULL COMMENT '工单流水号', `process_key` varchar(255) DEFAULT NULL COMMENT '流程定义的key', `serve_type` varchar(255) DEFAULT NULL COMMENT '服务类型', `process_id` varchar(255) DEFAULT NULL COMMENT '流程定义id', `parent_process_type_name` varchar(255) DEFAULT NULL COMMENT '流程定义类型名称', `process_type` varchar(255) DEFAULT NULL COMMENT '流程定义类型名称', `setting_data` longtext COMMENT '流程定义设置信息json格式数据', `process_type_name` varchar(64) DEFAULT NULL, `parent_process_type` varchar(64) DEFAULT NULL, `form_key` varchar(255) DEFAULT NULL, `form_data` longtext COMMENT '对应的自定义表单json格式数据', `urg_degree` varchar(255) DEFAULT NULL COMMENT '工单紧急程度', `start_account` varchar(255) DEFAULT NULL COMMENT '发起人', `start_account_id` varchar(255) DEFAULT NULL COMMENT '发起人id', `start_account_phone` varchar(32) DEFAULT NULL, `expect_time` varchar(255) DEFAULT NULL COMMENT '期望完成时间', `end_time` varchar(255) DEFAULT NULL COMMENT '完成时间', `start_time` varchar(255) DEFAULT NULL COMMENT '开始时间', `process_status` varchar(255) DEFAULT NULL COMMENT '流程状态', `handler` longtext COMMENT '待处理人', `next_handler` varchar(255) DEFAULT NULL COMMENT '下一环节处理人', `attention` varchar(64) DEFAULT NULL COMMENT '关注状态', `node_id` varchar(64) DEFAULT NULL COMMENT '流程节点id', `press` tinyint(4) DEFAULT '0' COMMENT '催办状态 0表示未催办 1表示催办', `opinion` varchar(255) DEFAULT NULL COMMENT '审批意见', `result` int(11) DEFAULT NULL COMMENT '审批结果', `form_id` varchar(255) DEFAULT NULL COMMENT '自定义表单id', `proc_def_id` varchar(255) DEFAULT NULL COMMENT '流程部署id', `relevance` varchar(64) DEFAULT NULL COMMENT '关联状态', `handler_name` varchar(255) DEFAULT NULL COMMENT '待处理人姓名', `next_handler_name` varchar(255) DEFAULT NULL COMMENT '下一节点处理人姓名', `node_type` varchar(255) DEFAULT NULL COMMENT '节点类型', `update_time` varchar(255) DEFAULT NULL COMMENT '更新时间', `appraise` tinyint(4) DEFAULT NULL COMMENT '工单是否已评价 0已评价 1未评价', `failure_type` varchar(255) DEFAULT NULL, `satisfaction` varchar(32) DEFAULT NULL COMMENT '满意度', `serve_department` varchar(32) DEFAULT NULL COMMENT '发起人所属部门', `serve_firm` varchar(32) DEFAULT NULL COMMENT '发起人所属厂商', `serve_area` varchar(32) DEFAULT NULL COMMENT '发起人所属区域', `time_consuming` varchar(32) DEFAULT NULL COMMENT '工单耗时', `time_out` varchar(32) DEFAULT NULL COMMENT '是否超时 0超时 1未超时', `performance` varchar(255) DEFAULT NULL COMMENT '绩效得分', `proposer_serve_department` varchar(255) DEFAULT NULL COMMENT '申请人所属部门', `proposer_serve_firm` varchar(255) DEFAULT NULL COMMENT '申请人所属厂商', `proposer_serve_area` varchar(255) DEFAULT NULL COMMENT '申请人所属区域', `start_user` varchar(255) DEFAULT NULL, `order_desc` varchar(255) DEFAULT NULL COMMENT '工单描述', `cocall` varchar(255) DEFAULT NULL, `handler_firm` longtext, `b_id` int(11) DEFAULT '0', `work_id` varchar(32) DEFAULT NULL COMMENT '工单id', `alarm_uuid` varchar(255) DEFAULT NULL COMMENT '告警uuid', `alarm_type` varchar(32) DEFAULT NULL COMMENT '告警类型', `object_id` varchar(255) DEFAULT NULL, `app_id` varchar(64) DEFAULT NULL, `c` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '设备名称' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; truncate table bi_workflow; insert into bi_workflow select '',a.id as a_id,theme,proposer_phone,proposer_name,proposer,serial_number,process_key,serve_type,process_id,parent_process_type_name,process_type,setting_data,process_type_name,parent_process_type,form_key,form_data,urg_degree,start_account,start_account_id,start_account_phone,expect_time,end_time,start_time,process_status,handler,next_handler,attention,node_id,press,opinion,result,form_id,proc_def_id,relevance,handler_name,next_handler_name,node_type,a.update_time,appraise,failure_type,satisfaction,serve_department,serve_firm,serve_area,time_consuming, time_out,performance,proposer_serve_department,proposer_serve_firm,proposer_serve_area,start_user,order_desc,cocall,handler_firm,b.id as b_id,work_id,alarm_uuid,alarm_type,object_id,app_id,ihr.ITM_HARDWARE_RESOURCES_NAME c from icm_bpm.t_work_flow a left join icm_bpm.t_work_resource_real b on a.id = b.work_id left join itm_hardware_resources ihr on ihr.itm_hardware_resources_id = b.object_id; -- 结构化数据容量分析 -- 建表语句 DROP TABLE IF EXISTS `bi_structured_data_capacity`; CREATE TABLE `bi_structured_data_capacity` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID', `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称', `ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID', `ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称', `totalsize` varchar(255) DEFAULT NULL COMMENT '当前数据库对应数量存储量 单位是GB' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; truncate table bi_structured_data_capacity; insert into bi_structured_data_capacity select 'DAY' as dtype, date_format(IND_VALUE_TIME,'%Y%m%d') as thedvalue, adds.NETID, d.name, adds.ORGAN_ID, po.short_name, SUM(adds.totalsize) from app_db_detail_stat adds left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=adds.NETID left join pub_organ po on po.ID=adds.ORGAN_ID group by date_format(IND_VALUE_TIME,'%Y%m%d'),adds.NETID,adds.ORGAN_ID,d.name,po.short_name; insert into bi_structured_data_capacity select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, NET_ID,NET_NAME, ORGAN_ID,ORGAN_NAME, SUM(totalsize) from bi_structured_data_capacity where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME; insert into bi_structured_data_capacity select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, NET_ID,NET_NAME, ORGAN_ID,ORGAN_NAME, SUM(totalsize) from bi_structured_data_capacity where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME; -- 存储使用率占比分析 -- 建表语句 DROP TABLE IF EXISTS `bi_storage_usage_rate`; CREATE TABLE `bi_storage_usage_rate` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID', `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称', `ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID', `ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称', `used` decimal(20,10) DEFAULT NULL COMMENT '存储使用量', `total` decimal(20,10) DEFAULT NULL COMMENT '存储总量', `usage_rate` decimal(20,10) DEFAULT NULL COMMENT '存储使用率' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; truncate table bi_storage_usage_rate; insert into bi_storage_usage_rate select 'DAY' as dtype, date_format(IND_VALUE_TIME,'%Y%m%d') as thedvalue, asds.NETID, d.name, asds.ORGAN_ID, po.short_name, SUM(asds.IND_VALUE_StorageDevice_UsedSpace), SUM(asds.IND_VALUE_StorageDevice_TotalSpace), SUM(asds.IND_VALUE_StorageDevice_UsedSpace) / SUM(asds.IND_VALUE_StorageDevice_TotalSpace) as 'usage_rate' from app_storage_day_stat asds left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=asds.NETID left join pub_organ po on po.ID=asds.ORGAN_ID group by date_format(IND_VALUE_TIME,'%Y%m%d'),asds.NETID,asds.ORGAN_ID,d.name,po.short_name; insert into bi_storage_usage_rate select 'MONTH' as dtype,substring(thedvalue,1,6) AS thedvalue, NET_ID, NET_NAME,ORGAN_ID,ORGAN_NAME, SUM(used), SUM(total), SUM(used)/SUM(total) from bi_storage_usage_rate where dtype = 'DAY' GROUP BY substring(thedvalue,1,6),NET_ID,ORGAN_ID,NET_NAME,ORGAN_NAME; insert into bi_storage_usage_rate select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, NET_ID, NET_NAME,ORGAN_ID,ORGAN_NAME, SUM(used), SUM(total), SUM(used)/SUM(total) from bi_storage_usage_rate where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),NET_ID,ORGAN_ID,NET_NAME,ORGAN_NAME; -- 应用数据分析 -- 建表语句 DROP TABLE IF EXISTS `bi_app_data_analysis`; CREATE TABLE `bi_app_data_analysis` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID', `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称', `ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID', `ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称', `app_id` varchar(64) DEFAULT NULL COMMENT '应用ID', `app_name` varchar(255) DEFAULT NULL COMMENT '应用名称', `priority` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low', `visit_count` decimal(20,2) DEFAULT NULL COMMENT '访问量', `user_count` decimal(20,2) DEFAULT NULL COMMENT '用户量', `quality` decimal(20,6) DEFAULT NULL COMMENT '数据质量' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; truncate table bi_app_data_analysis; insert into bi_app_data_analysis select 'MONTH' as dtype, date_format(visit_time,'%Y%m') as thedvalue, ia.ITM_NETGROUP_ID, d.name, arl.organ_id, po.short_name, arl.app_id, ia.itm_app_name, ia.PRIORITY, sum(arl.visit_count), sum(arl.user_count), 0.0 from app_region_log arl left join itm_app ia on ia.ITM_APP_ID=arl.app_id left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=ia.ITM_NETGROUP_ID left join pub_organ po on po.id=CAST(arl.organ_id AS CHAR) group by date_format(visit_time,'%Y%m'),ia.ITM_NETGROUP_ID,arl.organ_id,arl.app_id,ia.itm_app_name,ia.PRIORITY,d.name,po.short_name; insert into bi_app_data_analysis select 'MONTH' as dtype, replace(aadq.`month`,'-','') as thedvalue, ia.ITM_NETGROUP_ID, d.name, ia.organ_id, po.name, ia.ITM_APP_ID, ia.itm_app_name, ia.PRIORITY, 0, 0, sum(aadq.quality) from t_asset_app_data_quality aadq left join itm_app ia on ia.ITM_APP_ID=aadq.app_id left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=ia.ITM_NETGROUP_ID left join pub_organ po on po.ID=ia.organ_id group by replace(aadq.`month`,'-',''),ia.ITM_NETGROUP_ID,ia.organ_id,ia.ITM_APP_ID,ia.itm_app_name,ia.PRIORITY,d.name,po.name; insert into bi_app_data_analysis select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority,sum(visit_count),sum(user_count),sum(quality) from bi_app_data_analysis where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority; -- 应用备份覆盖率分析 -- 建表语句 DROP TABLE IF EXISTS `bi_app_backup_coverage`; CREATE TABLE `bi_app_backup_coverage` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID', `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称', `ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID', `ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称', `app_id` varchar(64) DEFAULT NULL COMMENT '应用ID', `app_name` varchar(255) DEFAULT NULL COMMENT '应用名称', `priority` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low', `bak_size` decimal(20,10) DEFAULT NULL COMMENT '备份数据容量', `total_size` decimal(20,10) DEFAULT NULL COMMENT '总容量', `backup_coverage` decimal(20,10) DEFAULT NULL COMMENT '备份覆盖率' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; truncate table bi_app_backup_coverage; insert into bi_app_backup_coverage select 'MONTH' as dtype, replace(abi.`month`,'-','') as thedvalue, ia.ITM_NETGROUP_ID, d.name, ia.ORGAN_ID, po.short_name, abi.app_id, ia.ITM_APP_NAME, ia.PRIORITY, sum(abi.bak_size), sum(abi.total_size), IF(sum(abi.total_size)<>0, sum(abi.bak_size) / sum(abi.total_size), 0) from t_asset_app_bak_info abi left join itm_app ia on ia.ITM_APP_ID=abi.app_id left join (select value,name from m_com_dict where dict_index='itm_resources_netcode') d on d.value=ia.ITM_NETGROUP_ID left join pub_organ po on po.id=ia.ORGAN_ID group by replace(abi.`month`,'-',''),ia.ITM_NETGROUP_ID,ia.ORGAN_ID,abi.app_id,ia.ITM_APP_NAME,ia.PRIORITY,d.name,po.short_name; insert into bi_app_backup_coverage select 'YEAR' as dtype,substring(thedvalue,1,4) AS thedvalue, NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority, sum(bak_size), sum(total_size), IF(sum(total_size)<>0, sum(bak_size) / sum(total_size), 0) from bi_app_backup_coverage where dtype = 'MONTH' GROUP BY substring(thedvalue,1,4),NET_ID,NET_NAME,ORGAN_ID,ORGAN_NAME,app_id,app_name,priority; ----应用系统资源利用率趋势2023-11-20--- --4-10-- set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; DROP TABLE IF EXISTS `bi_app_resource_group_by_time`; create table bi_app_resource_group_by_time select 'HOUR' as dtype, date_format(b.CREATE_TIME,'%Y%m%d%H') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename !='') c on a.ITM_APP_NAME = c.servicename -- inner join bi_itm_hardware_resources f -- on a.ruuid = c.relatedappid inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID inner JOIN m_com_dict d on a.app_type=d.value inner JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d%H'),date_format(b.CREATE_TIME,'%H'),net_name UNION ALL select 'HOUR' as dtype, date_format(b.CREATE_TIME,'%Y%m%d%H') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename = '') c on a.ruuid = c.relatedappid -- inner join bi_itm_hardware_resources f -- on a.ruuid = c.relatedappid inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID inner JOIN m_com_dict d on a.app_type=d.value inner JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d%H'),date_format(b.CREATE_TIME,'%H'),net_name union all select 'DAY' as dtype, date_format(b.CREATE_TIME,'%Y%m%d') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename !='') c on a.ITM_APP_NAME = c.servicename inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d'),date_format(b.CREATE_TIME,'%H') union all select 'DAY' as dtype, date_format(b.CREATE_TIME,'%Y%m%d') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename = '') c on a.ruuid = c.relatedappid -- inner join bi_itm_hardware_resources f -- on a.ruuid = c.relatedappid inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID inner JOIN m_com_dict d on a.app_type=d.value inner JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m%d'),date_format(b.CREATE_TIME,'%H'),net_name union all select 'WEEK' as dtype, date_format(b.CREATE_TIME,'%Y%u') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename != '') c on a.ITM_APP_NAME = c.servicename inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%u'),date_format(b.CREATE_TIME,'%H') union all select 'WEEK' as dtype, date_format(b.CREATE_TIME,'%Y%u') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename = '') c on a.ruuid = c.relatedappid inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%u'),date_format(b.CREATE_TIME,'%H') union all select 'MONTH' as dtype, date_format(b.CREATE_TIME,'%Y%m') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename != '') c on a.ITM_APP_NAME = c.servicename inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m'),date_format(b.CREATE_TIME,'%H') union all select 'MONTH' as dtype, date_format(b.CREATE_TIME,'%Y%m') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename = '') c on a.ruuid = c.relatedappid inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y%m'),date_format(b.CREATE_TIME,'%H') union all select 'YEAR' as dtype, date_format(b.CREATE_TIME,'%Y') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename != '') c on a.ITM_APP_NAME = c.servicename inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y'),date_format(b.CREATE_TIME,'%H') union ALL select 'YEAR' as dtype, date_format(b.CREATE_TIME,'%Y') as thedvalue, date_format(b.CREATE_TIME,'%H') as dhour, a.*, case a.stateflag when 0 then '已应用' else '未应用' end as stateflag1, case a.PRIORITY WHEN 'low' then '一般应用' when 'important' then '重要应用' when 'high' then '核心应用' else '未知' end as PRIORITY1, d.name as app_type_name, e.net_name as net_name, avg(b.IND_VALUE_Network_Cpurate) as IND_VALUE_Network_Cpurate,max(b.IND_VALUE_Network_Cpurate_High) as IND_VALUE_Network_Cpurate_High, avg(IND_VALUE_Network_Memrate) as IND_VALUE_Network_Memrate,max(IND_VALUE_Network_Memrate_High) as IND_VALUE_Network_Memrate_High, 0.0 as IND_VALUE_HOST_DISK_TOTAL, 0.0 as IND_VALUE_HOST_DISK_USED,0.0 as IND_VALUE_HOST_DISK_UTILIZATION, rand(10) * 100 as Band_widthrate, case when rand(10) * 100 + rand(10) * 10 > 100 then 100 else rand(10) * 100 + rand(10) * 10 end as Bandwidthrate_High, rand() * 100 as Network_delay, rand() * 10000 as Network_flow, rand() as Network_PacketErrorrate, rand() as Network_PacketLossrate, rand() * 10000 as Write_rate, rand() as network_Ifinrate, rand() as network_Ifortrate, 0.0 * rand(20) * 10 as Device_total, 0.0 * rand(20) * 10 as Device_used, 0.0 as Device_UTILIZATION from itm_app a inner join (select * from bi_itm_hardware_resources where servicename = '') c on a.ruuid = c.relatedappid inner join app_network_stat b on c.itm_hardware_resources_id = b.INT_ID LEFT JOIN m_com_dict d on a.app_type=d.value LEFT JOIN itm_organ_net e on e.NET_ID = a.ITM_NETGROUP_ID WHERE d.dict_index='app_type' GROUP BY a.ITM_APP_NAME,date_format(b.CREATE_TIME,'%Y'),date_format(b.CREATE_TIME,'%H') alter table bi_app_resource_group_by_time modify `dtype` varchar(5) NOT NULL DEFAULT '' comment '日期类型'; alter table bi_app_resource_group_by_time modify `thedvalue` varchar(15) DEFAULT NULL comment '日期'; alter table bi_app_resource_group_by_time modify `dhour` varchar(15) DEFAULT NULL comment '小时'; alter table bi_app_resource_group_by_time modify `ITM_APP_ID` varchar(255) NOT NULL COMMENT '应用ID'; alter table bi_app_resource_group_by_time modify `ITM_APP_CODE` varchar(45) DEFAULT NULL COMMENT '编码'; alter table bi_app_resource_group_by_time modify `ITM_APP_NAME` varchar(200) DEFAULT NULL COMMENT '应用名称'; alter table bi_app_resource_group_by_time modify `ITM_APP_SHORTNAME` varchar(100) DEFAULT NULL COMMENT '应用简称'; alter table bi_app_resource_group_by_time modify `ITM_APP_DESC` text COMMENT '系统简介'; alter table bi_app_resource_group_by_time modify `ITM_APP_DEPT` varchar(300) DEFAULT NULL COMMENT '归口部门'; alter table bi_app_resource_group_by_time modify `ITM_APP_COURTCODE` varchar(45) DEFAULT NULL COMMENT '承建单位\n例如：最高法、山东省高法\n取法院系统的组织机构编码'; alter table bi_app_resource_group_by_time modify `ITM_NETGROUP_ID` varchar(30) DEFAULT NULL COMMENT '应用所属网系\n更新字典表\nNG000：外部专网\nNG001：法院专网\nNG002：互联网\nNG003：移动专网\nNG004：涉密内网'; alter table bi_app_resource_group_by_time modify `ITM_APPCATAGORY_ID` varchar(45) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `ITM_APP_VENDOR` varchar(45) DEFAULT NULL COMMENT '供应商：浪潮、太极等'; alter table bi_app_resource_group_by_time modify `ITM_APP_UPTIME` datetime DEFAULT NULL COMMENT '上线时间'; alter table bi_app_resource_group_by_time modify `ITM_APP_VERSION` varchar(45) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `ITM_APP_MAINTENANCE_ID` varchar(45) DEFAULT NULL COMMENT '运维团队,关联运维表，使用内码连接\n'; alter table bi_app_resource_group_by_time modify `ITM_APP_ORDER` int(45) DEFAULT NULL COMMENT '应用顺序，展示'; alter table bi_app_resource_group_by_time modify `ITM_APP_NETID` varchar(45) DEFAULT NULL COMMENT '专网编号'; alter table bi_app_resource_group_by_time modify `ITM_APP_ICONSRC` varchar(100) DEFAULT NULL COMMENT '应用分类\n三类和25类字典表表现'; alter table bi_app_resource_group_by_time modify `ITM_APP_SYSTEMURL` varchar(300) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `ORGAN_ID` varchar(30) DEFAULT NULL COMMENT '机构id，关联机构表'; alter table bi_app_resource_group_by_time modify `IS_GLOBAL` varchar(45) DEFAULT NULL COMMENT '是否是全局\n如果是全局，则拓扑图是全局的\n如果是非全局，需要维护各自的'; alter table bi_app_resource_group_by_time modify `ITM_APP_PID` varchar(45) DEFAULT NULL COMMENT '父类系统\n'; alter table bi_app_resource_group_by_time modify `PLATFORM` varchar(100) DEFAULT NULL COMMENT '七大平台'; alter table bi_app_resource_group_by_time modify `PRIORITY` varchar(10) DEFAULT NULL COMMENT '优先级:核心-high;重要-important;一般-low'; alter table bi_app_resource_group_by_time modify `ITM_APP_IP` varchar(30) DEFAULT NULL COMMENT '应用所在服务器IP'; alter table bi_app_resource_group_by_time modify `ITM_APP_PORT` varchar(10) DEFAULT NULL COMMENT '应用所在服务器端口'; alter table bi_app_resource_group_by_time modify `ITM_APP_WEBROOT` varchar(100) DEFAULT NULL COMMENT '应用访问路径根目录'; alter table bi_app_resource_group_by_time modify `ITM_LOCATION` varchar(100) DEFAULT NULL COMMENT '位置信息'; alter table bi_app_resource_group_by_time modify `ITM_APP_ASSURE_LEVEL` varchar(1) DEFAULT NULL COMMENT '应用保障级别1一级保障'; alter table bi_app_resource_group_by_time modify `SHOW_TYPE` varchar(10) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `ITM_SHOW_FLAG` varchar(10) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `int_id` int(22) DEFAULT NULL COMMENT '其他系统关联用ID'; alter table bi_app_resource_group_by_time modify `INSERT_TIME` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录插入时间'; alter table bi_app_resource_group_by_time modify `mantance_vendor` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `mantance_duty` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `mantance_duty_phone` varchar(128) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `start_date` datetime DEFAULT NULL; alter table bi_app_resource_group_by_time modify `mantance_year_number` int(16) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `ne_type` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `creator` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `create_time` datetime DEFAULT NULL; alter table bi_app_resource_group_by_time modify `modifier` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `modify_time` datetime DEFAULT NULL; alter table bi_app_resource_group_by_time modify `time_stamp` datetime DEFAULT NULL; alter table bi_app_resource_group_by_time modify `stateflag` mediumtext; alter table bi_app_resource_group_by_time modify `ruuid` varchar(128) NOT NULL; alter table bi_app_resource_group_by_time modify `dataType` varchar(50) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `login_action` text; alter table bi_app_resource_group_by_time modify `localize_flag` varchar(32) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `current_stat` varchar(32) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `contract` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `emergency_flag` varchar(32) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `monitor_flag` varchar(32) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `development_language` varchar(32) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `app_type` varchar(64) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `contract_money` varchar(32) DEFAULT NULL; alter table bi_app_resource_group_by_time modify `unified_login` varchar(12) DEFAULT NULL COMMENT '统一登录:0:以接入统一桌面;1:未接入统一桌面'; alter table bi_app_resource_group_by_time modify `stateflag1` varchar(20) DEFAULT NULL COMMENT '应用状态'; alter table bi_app_resource_group_by_time modify `PRIORITY1` varchar(20) DEFAULT NULL COMMENT '重要程度'; alter table bi_app_resource_group_by_time modify `app_type_name` varchar(20) DEFAULT NULL COMMENT '分类'; alter table bi_app_resource_group_by_time modify `net_name` varchar(20) DEFAULT NULL COMMENT '网系'; alter table bi_app_resource_group_by_time modify `IND_VALUE_Network_Cpurate` decimal(20 ,6) DEFAULT NULL comment 'cpu利用率（原始）'; alter table bi_app_resource_group_by_time modify `IND_VALUE_Network_Cpurate_High` decimal(16 ,2) DEFAULT NULL comment 'CPU利用率峰值（原始）'; alter table bi_app_resource_group_by_time modify `IND_VALUE_Network_Memrate` decimal(20 ,6) DEFAULT NULL comment '内存利用率（原始）'; alter table bi_app_resource_group_by_time modify `IND_VALUE_Network_Memrate_High` decimal(16 ,2) DEFAULT NULL comment '内存利用率峰值（原始）'; alter table bi_app_resource_group_by_time modify `IND_VALUE_HOST_DISK_TOTAL` decimal(38 ,4) DEFAULT NULL comment '磁盘总大小（原始）'; alter table bi_app_resource_group_by_time modify `IND_VALUE_HOST_DISK_USED` decimal(38 ,4) DEFAULT NULL comment '已使用磁盘大小（原始）'; alter table bi_app_resource_group_by_time modify `IND_VALUE_HOST_DISK_UTILIZATION` decimal(16 ,4) DEFAULT NULL comment '磁盘利用率峰值（原始）'; alter table bi_app_resource_group_by_time modify `Band_widthrate` decimal(38,4) DEFAULT NULL COMMENT '实时带宽利用率'; alter table bi_app_resource_group_by_time modify `Bandwidthrate_High` decimal(38,4) DEFAULT NULL COMMENT '带宽峰值用率'; alter table bi_app_resource_group_by_time modify `Network_delay` decimal(38,4) DEFAULT NULL COMMENT '网络时延'; alter table bi_app_resource_group_by_time modify `Network_flow` decimal(38,4) DEFAULT NULL COMMENT '端口流量'; alter table bi_app_resource_group_by_time modify `Network_PacketErrorrate` decimal(38,4) DEFAULT NULL COMMENT '丢包率'; alter table bi_app_resource_group_by_time modify `Network_PacketLossrate` decimal(38,4) DEFAULT NULL COMMENT '错包率'; alter table bi_app_resource_group_by_time modify `Write_rate` decimal(38,4) DEFAULT NULL COMMENT '读写速率'; alter table bi_app_resource_group_by_time modify `network_Ifinrate` decimal(38,4) DEFAULT NULL COMMENT '下行利用率'; alter table bi_app_resource_group_by_time modify `network_Ifortrate` decimal(38,4) DEFAULT NULL COMMENT '上行利用率'; alter table bi_app_resource_group_by_time modify `Device_total` decimal(38,4) DEFAULT NULL COMMENT '设备总量'; alter table bi_app_resource_group_by_time modify `Device_used` decimal(38,4) DEFAULT NULL COMMENT '设备已用容量'; alter table bi_app_resource_group_by_time modify `Device_UTILIZATION` decimal(38,4) DEFAULT NULL COMMENT '资源利用率'; ----应用系统访问分析2023-11-22--- DROP TABLE IF EXISTS `bi_app_access_stat`; CREATE TABLE `bi_app_access_stat` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(20) DEFAULT NULL COMMENT '日期', `NET_ID` varchar(64) DEFAULT NULL COMMENT '网系ID', `NET_NAME` varchar(64) DEFAULT NULL COMMENT '网系名称', `ORGAN_ID` varchar(100) DEFAULT NULL COMMENT '区域ID/数据来源', `ORGAN_NAME` varchar(100) DEFAULT NULL COMMENT '区域名称', `app_id` varchar(64) DEFAULT NULL COMMENT '应用ID', `app_name` varchar(255) DEFAULT NULL COMMENT '应用名称', `visit_count` decimal(20,10) DEFAULT NULL COMMENT '访问数', `operate_count` decimal(20,10) DEFAULT NULL COMMENT '操作量', `priority` varchar(10) DEFAULT NULL COMMENT '应用等级:核心-high;重要-important;一般-low', `app_type` varchar(255) DEFAULT NULL COMMENT '应用分类' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; insert into bi_app_access_stat SELECT 'HOUR' AS dtype, DATE_FORMAT(A.time_mark,'%Y%m%d%H') as thedvalue, B.ITM_NETGROUP_ID AS NET_ID, C.NAME AS net_name, A.ORGAN_ID AS ORGAN_ID, D.short_name AS ORGAN_name, A.app_id as app_id, B.ITM_APP_NAME AS app_name, sum(A.visit_count) as visit_count, sum(A.operate_count) as operate_count , CASE B.PRIORITY WHEN 'low' THEN '一般应用' WHEN 'important' THEN '重要应用' WHEN 'high' THEN '核心应用' ELSE '未知' END AS PRIORITY, F.NAME FROM app_visit_log_region as A LEFT JOIN itm_app B on A.app_id=B.itm_app_id LEFT JOIN ( SELECT VALUE, NAME FROM m_com_dict WHERE dict_index = 'itm_resources_netcode' ) C ON C.VALUE = B.ITM_NETGROUP_ID LEFT JOIN pub_organ D ON D.id = A.ORGAN_ID LEFT JOIN ( SELECT temp1.ITM_APP_ID, temp2.NAME FROM itm_app temp1 LEFT JOIN m_com_dict temp2 ON temp1.app_type = temp2.VALUE ) AS F ON A.app_id = F.itm_app_id GROUP BY A.app_id, A.ORGAN_ID, B.ITM_NETGROUP_ID, B.ITM_APP_NAME, C.NAME, D.short_name, B.priority, F.NAME, B.PRIORITY, A.time_mark, DATE_FORMAT(A.time_mark,'%Y%m%d%H'); insert into bi_app_access_stat SELECT 'DAY' AS dtype, substring(thedvalue,1,8) AS thedvalue, NET_ID, net_name, ORGAN_ID, ORGAN_name, app_id, app_name, sum(visit_count) as visit_count, sum(operate_count) as operate_count , CASE PRIORITY WHEN 'low' THEN '一般应用' WHEN 'important' THEN '重要应用' WHEN 'high' THEN '核心应用' ELSE '未知' END AS PRIORITY, app_type FROM bi_app_access_stat WHERE dtype = 'HOUR' GROUP BY dtype, thedvalue, NET_ID, net_name, ORGAN_ID, ORGAN_name, app_id, app_name, priority, app_type, substring(thedvalue,1,8); insert into bi_app_access_stat SELECT 'MONTH' AS dtype, substring(thedvalue,1,6) AS thedvalue, NET_ID, net_name, ORGAN_ID, ORGAN_name, app_id, app_name, sum(visit_count) as visit_count, sum(operate_count) as operate_count , CASE PRIORITY WHEN 'low' THEN '一般应用' WHEN 'important' THEN '重要应用' WHEN 'high' THEN '核心应用' ELSE '未知' END AS PRIORITY, app_type FROM bi_app_access_stat WHERE dtype = 'DAY' GROUP BY dtype, thedvalue, NET_ID, net_name, ORGAN_ID, ORGAN_name, app_id, app_name, priority, app_type, substring(thedvalue,1,6); insert into bi_app_access_stat SELECT 'YEAR' AS dtype, substring(thedvalue,1,4) AS thedvalue, NET_ID, net_name, ORGAN_ID, ORGAN_name, app_id, app_name, sum(visit_count) as visit_count, sum(operate_count) as operate_count , CASE PRIORITY WHEN 'low' THEN '一般应用' WHEN 'important' THEN '重要应用' WHEN 'high' THEN '核心应用' ELSE '未知' END AS PRIORITY, app_type FROM bi_app_access_stat WHERE dtype = 'MONTH' GROUP BY dtype, thedvalue, NET_ID, net_name, ORGAN_ID, ORGAN_name, app_id, app_name, priority, app_type, substring(thedvalue,1,4)