use lawe; -- （1）基础设施 -- 建表语句 DROP TABLE IF EXISTS `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; -- （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', `cloud` varchar(255) DEFAULT NULL COMMENT '云平台' ) 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` 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; -- （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; -- （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; -- 建表语句 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; -- (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 ( 50, 20 ) DEFAULT NULL, `group_agg` VARCHAR ( 10 ) NOT NULL DEFAULT '', `unit` VARCHAR ( 10 ) NOT NULL DEFAULT '单位' , `cloud` varchar(255) DEFAULT NULL COMMENT '云平台' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; -- （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'; -- （8）应用系统监控覆盖率趋势 -- 建表语句 DROP TABLE IF EXISTS 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, `cloud` varchar(255) DEFAULT NULL COMMENT '云平台' ) ENGINE = InnoDB DEFAULT CHARSET = utf8; -- （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'; -- （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; -- （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; 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; -- （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; -- （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; -- （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; -- （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; -- （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; -- （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; -- （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(); -- （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; -- 结构化数据容量分析 -- 建表语句 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; -- 存储使用率占比分析 -- 建表语句 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; -- 应用数据分析 -- 建表语句 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; -- 应用备份覆盖率分析 -- 建表语句 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; 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` ( `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型', `thedvalue` varchar(15) DEFAULT NULL COMMENT '日期', `dhour` varchar(15) DEFAULT NULL comment '小时', `ITM_APP_ID` varchar(255) NOT NULL COMMENT '应用ID', `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(100) DEFAULT NULL COMMENT '', `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, `data_type` varchar(50) DEFAULT NULL, `login_action` text, `localize_flag` varchar(32) DEFAULT NULL, `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:未接入统一桌面', `img_url` varchar(50) DEFAULT NULL, `dataType` text, `on_line` mediumtext, `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 '网系', `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 '资源利用率' )ENGINE = InnoDB DEFAULT CHARSET = utf8; 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;