langchao_fayuan/fayuan_zp

--应用系统资源利用率--
DROP TABLE IF EXISTS `bi_app_resource_group_by_time2`;
CREATE TABLE `bi_app_resource_group_by_time2` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`dhour` varchar(15) DEFAULT NULL comment '小时',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`PRIORITY` varchar(10) DEFAULT NULL comment '应用等级',
	`app_type` varchar(10) DEFAULT NULL comment '应用分类',
	`app_type_name` varchar(200) DEFAULT NULL comment '应用名称',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`IND_VALUE_HOST_CPU_UTILIZATION` decimal(20,6) DEFAULT NULL comment 'cpu利用率（原始）',
	`IND_VALUE_HOST_CPU_TOP` decimal(16,2) DEFAULT NULL comment 'cpu利用率峰值（原始）',
	`IND_VALUE_HOST_MEMORY_UTILIZATION` decimal(20,6) DEFAULT NULL comment '内存利用率（原始）',
	`IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH` decimal(16,2) DEFAULT NULL comment '内存利用率峰值（原始）',
	`IND_VALUE_HOST_DISK_UTILIZATION`decimal(16,4) 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_HIGH` decimal(16,4) DEFAULT NULL comment '磁盘利用率峰值（原始）'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into bi_app_resource_group_by_time2
SELECT 
	 'HOUR' AS dtype,
		DATE_FORMAT(t4.CREATE_TIME,'%Y%m%d%H') as thedvalue,
		DATE_FORMAT(t4.CREATE_TIME,'%H') as dhour,
	 t3.ORGAN_ID,
	 t7.SHORT_NAME as organ_name,
	CASE
		t3.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	case t3.app_type when '1' then '智慧服务'  when '2' then '智慧管理'  when '3' then '智慧审判' when '4' then '智慧执行' ELSE '其他' end as app_type,
	t3.ITM_APP_NAME as app_type_name,
	t6.ITM_APPCATAGORY_NAME as NET_NAME,
	t4.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t4.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t4.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t4.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t4.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t4.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t4.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN itm_app t3 ON t3.ruuid = t2.itm_hardware_resources_value
	inner JOIN app_host_stat t4 ON t1.itm_hardware_resources_id = t4.INT_ID
	inner JOIN itm_appcatagory AS t6 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t6.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t7 on t1.ORGAN_ID = t7.code

WHERE
		t1.itm_hardware_resources_type IN ( '000100001', '000100002' )  
		AND t2.ITM_HARDWARE_RESOURCES_PROCODE IN ( 'related_app_id','service' ) 
		group by 
		DATE_FORMAT(t4.CREATE_TIME,'%Y%m%d%H'),
		DATE_FORMAT(t4.CREATE_TIME,'%H'),
		t3.ORGAN_ID,
		t3.PRIORITY ,
		t3.ITM_APP_NAME,
		t6.ITM_APPCATAGORY_NAME,
		t4.IND_VALUE_HOST_DISK_UTILIZATION,
		t4.IND_VALUE_HOST_CPU_UTILIZATION,
		t4.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t4.IND_VALUE_HOST_DISK_TOTAL,
		t7.SHORT_NAME,
		t3.app_type;

insert into bi_app_resource_group_by_time2
SELECT 
	 'DAY' AS dtype,
		DATE_FORMAT(t4.CREATE_TIME,'%Y%m%d') as thedvalue,
		DATE_FORMAT(t4.CREATE_TIME,'%H') as dhour,
	 t3.ORGAN_ID,
	 t7.SHORT_NAME as organ_name,
	CASE
		t3.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	case t3.app_type when '1' then '智慧服务'  when '2' then '智慧管理'  when '3' then '智慧审判' when '4' then '智慧执行' ELSE '其他' end as app_type,
	t3.ITM_APP_NAME as app_type_name,
	t6.ITM_APPCATAGORY_NAME as NET_NAME,
	t4.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t4.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t4.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t4.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t4.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t4.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t4.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN itm_app t3 ON t3.ruuid = t2.itm_hardware_resources_value
	inner JOIN app_host_day_stat t4 ON t1.itm_hardware_resources_id = t4.INT_ID
	inner JOIN itm_appcatagory AS t6 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t6.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t7 on t1.ORGAN_ID = t7.code

WHERE
		t1.itm_hardware_resources_type IN ( '000100001', '000100002' )  
		AND t2.ITM_HARDWARE_RESOURCES_PROCODE IN ( 'related_app_id','service' ) 
		group by 
		DATE_FORMAT(t4.CREATE_TIME,'%Y%m%d'),
		DATE_FORMAT(t4.CREATE_TIME,'%H'),
		t3.ORGAN_ID,
		t3.PRIORITY ,
		t3.ITM_APP_NAME,
		t6.ITM_APPCATAGORY_NAME,
		t4.IND_VALUE_HOST_DISK_UTILIZATION,
		t4.IND_VALUE_HOST_CPU_UTILIZATION,
		t4.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t4.IND_VALUE_HOST_DISK_TOTAL,
		t7.SHORT_NAME,
		t3.app_type;

insert into bi_app_resource_group_by_time2
SELECT 
	 'MONTH' AS dtype,
		DATE_FORMAT(t4.CREATE_TIME,'%Y%m') as thedvalue,
		DATE_FORMAT(t4.CREATE_TIME,'%H') as dhour,
	 t3.ORGAN_ID,
	 t7.SHORT_NAME as organ_name,
	CASE
		t3.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	case t3.app_type when '1' then '智慧服务'  when '2' then '智慧管理'  when '3' then '智慧审判' when '4' then '智慧执行' ELSE '其他' end as app_type,
	t3.ITM_APP_NAME as app_type_name,
	t6.ITM_APPCATAGORY_NAME as NET_NAME,
	t4.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t4.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t4.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t4.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t4.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t4.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t4.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN itm_app t3 ON t3.ruuid = t2.itm_hardware_resources_value
	inner JOIN app_host_month_stat t4 ON t1.itm_hardware_resources_id = t4.INT_ID
	inner JOIN itm_appcatagory AS t6 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t6.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t7 on t1.ORGAN_ID = t7.code

WHERE
		t1.itm_hardware_resources_type IN ( '000100001', '000100002' )  
		AND t2.ITM_HARDWARE_RESOURCES_PROCODE IN ( 'related_app_id','service' ) 
		group by 
		DATE_FORMAT(t4.CREATE_TIME,'%Y%m'),
		DATE_FORMAT(t4.CREATE_TIME,'%H'),
		t3.ORGAN_ID,
		t3.PRIORITY ,
		t3.ITM_APP_NAME,
		t6.ITM_APPCATAGORY_NAME,
		t4.IND_VALUE_HOST_DISK_UTILIZATION,
		t4.IND_VALUE_HOST_CPU_UTILIZATION,
		t4.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t4.IND_VALUE_HOST_DISK_TOTAL,
		t7.SHORT_NAME,
		t3.app_type;

insert into bi_app_resource_group_by_time2
SELECT 
	 'YEAR' AS dtype,
		DATE_FORMAT(t4.CREATE_TIME,'%Y') as thedvalue,
		DATE_FORMAT(t4.CREATE_TIME,'%H') as dhour,
	 t3.ORGAN_ID,
	 t7.SHORT_NAME as organ_name,
	CASE
		t3.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
	case t3.app_type when '1' then '智慧服务'  when '2' then '智慧管理'  when '3' then '智慧审判' when '4' then '智慧执行' ELSE '其他' end as app_type,
	t3.ITM_APP_NAME as app_type_name,
	t6.ITM_APPCATAGORY_NAME as NET_NAME,
	t4.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t4.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t4.IND_VALUE_HOST_MEMORY_UTILIZATION as ND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t4.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t4.IND_VALUE_HOST_DISK_UTILIZATION,
	t4.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t4.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN itm_app t3 ON t3.ruuid = t2.itm_hardware_resources_value
	inner JOIN app_host_month_stat t4 ON t1.itm_hardware_resources_id = t4.INT_ID
	inner JOIN itm_appcatagory AS t6 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t6.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t7 on t1.ORGAN_ID = t7.code

WHERE
		t1.itm_hardware_resources_type IN ( '000100001', '000100002' )  
		AND t2.ITM_HARDWARE_RESOURCES_PROCODE IN ( 'related_app_id','service' ) 
		group by 
		DATE_FORMAT(t4.CREATE_TIME,'%Y'),
		DATE_FORMAT(t4.CREATE_TIME,'%H'),
		t3.ORGAN_ID,
		t3.PRIORITY ,
		t3.ITM_APP_NAME,
		t6.ITM_APPCATAGORY_NAME,
		t4.IND_VALUE_HOST_DISK_UTILIZATION,
		t4.IND_VALUE_HOST_CPU_UTILIZATION,
		t4.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t4.IND_VALUE_HOST_DISK_TOTAL,
		t7.SHORT_NAME,
		t3.app_type;




---基础设施资源利用率---

DROP TABLE IF EXISTS `bi_app_resource_group_by_time3`;
CREATE TABLE `bi_app_resource_group_by_time3` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`dhour` varchar(15) DEFAULT NULL comment '小时',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`cloud` varchar(255) DEFAULT NULL COMMENT '云平台',
	`itm_hardware_resources_cntype` varchar(100) DEFAULT NULL COMMENT '设备型号/资产类型',
	`ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称/资产名称',
	`IND_VALUE_HOST_CPU_UTILIZATION` decimal(20,6) DEFAULT NULL comment 'cpu利用率（原始）',
	`IND_VALUE_HOST_CPU_TOP` decimal(16,2) DEFAULT NULL comment 'cpu利用率峰值（原始）',
	`IND_VALUE_HOST_MEMORY_UTILIZATION` decimal(20,6) DEFAULT NULL comment '内存利用率（原始）',
	`IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH` decimal(16,2) DEFAULT NULL comment '内存利用率峰值（原始）',
	`IND_VALUE_HOST_DISK_UTILIZATION`decimal(16,4) 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_HIGH` decimal(16,4) DEFAULT NULL comment '磁盘利用率峰值（原始）'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into bi_app_resource_group_by_time3
SELECT 
	 'HOUR' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d%H') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
	 t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
	case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' 
	when substr(t1.itm_hardware_resources_type,1,4) = '0001' then '其他主机' 
	when t1.itm_hardware_resources_type = '000200001' then '交换机' 
	when t1.itm_hardware_resources_type = '000200002' then '路由器' 
	when t1.itm_hardware_resources_type = '000300001' then '存储设备' 
	when substr(itm_hardware_resources_type,1,4) = '0004' then '安全设备'
	else '非资产设备' end as itm_hardware_resources_cntype,
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(t3.IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	left join pub_organ as t5 on t1.ORGAN_ID = t5.code 
		group by 
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d%H'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t4.ITM_APPCATAGORY_NAME,
		t1.ORGAN_ID,
		t1.cloud, 
		t1.itm_hardware_resources_type,
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;

insert into bi_app_resource_group_by_time3
SELECT 
	 'DAY' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
	 t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
		case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' 
	when substr(t1.itm_hardware_resources_type,1,4) = '0001' then '其他主机' 
	when t1.itm_hardware_resources_type = '000200001' then '交换机' 
	when t1.itm_hardware_resources_type = '000200002' then '路由器' 
	when t1.itm_hardware_resources_type = '000300001' then '存储设备' 
	when substr(itm_hardware_resources_type,1,4) = '0004' then '安全设备'
	else '非资产设备' end as itm_hardware_resources_cntype, 
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_day_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	left join pub_organ as t5 on t1.ORGAN_ID = t5.code 
	
	GROUP BY 
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t1.ORGAN_ID,
		t4.ITM_APPCATAGORY_NAME,
		t1.cloud,
		t1.itm_hardware_resources_type,
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;
insert into bi_app_resource_group_by_time3
SELECT 
	 'MONTH' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
		t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
		case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' 
	when substr(t1.itm_hardware_resources_type,1,4) = '0001' then '其他主机' 
	when t1.itm_hardware_resources_type = '000200001' then '交换机' 
	when t1.itm_hardware_resources_type = '000200002' then '路由器' 
	when t1.itm_hardware_resources_type = '000300001' then '存储设备' 
	when substr(itm_hardware_resources_type,1,4) = '0004' then '安全设备'
	else '非资产设备' end as itm_hardware_resources_cntype,
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_month_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t5 on t1.ORGAN_ID = t5.code
		group by 
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t1.ORGAN_ID,
		t4.ITM_APPCATAGORY_NAME,
		t1.cloud,
		t1.itm_hardware_resources_type, 
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;


insert into bi_app_resource_group_by_time3
SELECT 
	 'YEAR' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
	 t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
		case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' 
	when substr(t1.itm_hardware_resources_type,1,4) = '0001' then '其他主机' 
	when t1.itm_hardware_resources_type = '000200001' then '交换机' 
	when t1.itm_hardware_resources_type = '000200002' then '路由器' 
	when t1.itm_hardware_resources_type = '000300001' then '存储设备' 
	when substr(itm_hardware_resources_type,1,4) = '0004' then '安全设备'
	else '非资产设备' end as itm_hardware_resources_cntype,
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as ND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_month_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t5 on t1.ORGAN_ID = t5.code 
		group by 
		DATE_FORMAT(t3.CREATE_TIME,'%Y'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t1.ORGAN_ID,
		t4.ITM_APPCATAGORY_NAME,
		t1.cloud,
		t1.itm_hardware_resources_type, 
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;






----基础设施：服务器负载趋势 ----云主机  宿主机  物理机----
DROP TABLE IF EXISTS `bi_app_resource_group_by_time4`;
CREATE TABLE `bi_app_resource_group_by_time4` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`dhour` varchar(15) DEFAULT NULL comment '小时',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`cloud` varchar(255) DEFAULT NULL COMMENT '云平台',
	`itm_hardware_resources_cntype` varchar(100) DEFAULT NULL COMMENT '设备型号/资产类型',
	`ITM_HARDWARE_RESOURCES_NAME` varchar(255) DEFAULT NULL COMMENT '设备名称/资产名称',
	`IND_VALUE_HOST_CPU_UTILIZATION` decimal(20,6) DEFAULT NULL comment 'cpu利用率（原始）',
	`IND_VALUE_HOST_CPU_TOP` decimal(16,2) DEFAULT NULL comment 'cpu利用率峰值（原始）',
	`IND_VALUE_HOST_MEMORY_UTILIZATION` decimal(20,6) DEFAULT NULL comment '内存利用率（原始）',
	`IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH` decimal(16,2) DEFAULT NULL comment '内存利用率峰值（原始）',
	`IND_VALUE_HOST_DISK_UTILIZATION`decimal(16,4) 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_HIGH` decimal(16,4) DEFAULT NULL comment '磁盘利用率峰值（原始）'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into bi_app_resource_group_by_time4
SELECT 
	 'HOUR' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d%H') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
	 t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
	case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' else '' end as itm_hardware_resources_cntype, 
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(t3.IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	left join pub_organ as t5 on t1.ORGAN_ID = t5.code where t1.itm_hardware_resources_type IN ( '000100001', '000100002','000100004' ) 
		group by 
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d%H'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t4.ITM_APPCATAGORY_NAME,
		t1.ORGAN_ID,
		t1.cloud, 
		t1.itm_hardware_resources_type,
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;

insert into bi_app_resource_group_by_time4
SELECT 
	 'DAY' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
	 t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
	case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机'  else '' end as itm_hardware_resources_cntype, 
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_day_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	left join pub_organ as t5 on t1.ORGAN_ID = t5.code where t1.itm_hardware_resources_type IN ( '000100001', '000100002','000100004' )
	
	GROUP BY 
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m%d'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t1.ORGAN_ID,
		t4.ITM_APPCATAGORY_NAME,
		t1.cloud,
		t1.itm_hardware_resources_type,
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;
insert into bi_app_resource_group_by_time4
SELECT 
	 'MONTH' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
		t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
	case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' else '' end as itm_hardware_resources_cntype, 
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as IND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION as IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_month_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t5 on t1.ORGAN_ID = t5.code where t1.itm_hardware_resources_type IN ( '000100001', '000100002','000100004' ) 
		group by 
		DATE_FORMAT(t3.CREATE_TIME,'%Y%m'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t1.ORGAN_ID,
		t4.ITM_APPCATAGORY_NAME,
		t1.cloud,
		t1.itm_hardware_resources_type, 
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;


insert into bi_app_resource_group_by_time4
SELECT 
	 'YEAR' AS dtype,
		DATE_FORMAT(t3.CREATE_TIME,'%Y') as thedvalue,
		DATE_FORMAT(t3.CREATE_TIME,'%H') as dhour,
	 t1.ORGAN_ID,
	 t5.SHORT_NAME as organ_name,
	t4.ITM_APPCATAGORY_NAME as NET_NAME,
	t1.cloud as cloud,
	case 
	when t1.itm_hardware_resources_type = '000100001' then '物理机'
	when t1.itm_hardware_resources_type = '000100002' then '云主机' 
	when t1.itm_hardware_resources_type = '000100004' then '宿主机' else '' end as itm_hardware_resources_cntype, 
	t1.ITM_HARDWARE_RESOURCES_NAME as ITM_HARDWARE_RESOURCES_NAME,
	t3.IND_VALUE_HOST_CPU_UTILIZATION as IND_VALUE_HOST_CPU_UTILIZATION,
	max(t3.IND_VALUE_HOST_CPU_TOP) as IND_VALUE_HOST_CPU_TOP,
	t3.IND_VALUE_HOST_MEMORY_UTILIZATION as ND_VALUE_HOST_MEMORY_UTILIZATION,
	max(t3.IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH) as IND_VALUE_HOST_MEMORY_UTILIZATION_HIGH,
	t3.IND_VALUE_HOST_DISK_UTILIZATION,
	t3.IND_VALUE_HOST_DISK_TOTAL as IND_VALUE_HOST_DISK_TOTAL,
	max(t3.IND_VALUE_HOST_DISK_USED) as IND_VALUE_HOST_DISK_USED,
	max(IND_VALUE_HOST_DISK_UTILIZATION_HIGH) as IND_VALUE_HOST_DISK_UTILIZATION_HIGH
FROM
	itm_hardware_resources t1
	inner JOIN itm_hardware_resources_value t2 ON t1.itm_hardware_resources_id = t2.itm_hardware_resources_id
	inner JOIN app_host_month_stat t3 ON t1.itm_hardware_resources_id = t3.INT_ID
	inner JOIN itm_appcatagory AS t4 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t4.ITM_APPCATAGORY_CODE 
	inner join pub_organ as t5 on t1.ORGAN_ID = t5.code where t1.itm_hardware_resources_type IN ( '000100001', '000100002','000100004' ) 
		group by 
		DATE_FORMAT(t3.CREATE_TIME,'%Y'),
		DATE_FORMAT(t3.CREATE_TIME,'%H'),
		t1.ORGAN_ID,
		t4.ITM_APPCATAGORY_NAME,
		t1.cloud,
		t1.itm_hardware_resources_type, 
		t1.ITM_HARDWARE_RESOURCES_NAME,
		t3.IND_VALUE_HOST_DISK_UTILIZATION,
		t3.IND_VALUE_HOST_CPU_UTILIZATION,
		t3.IND_VALUE_HOST_MEMORY_UTILIZATION,
		t3.IND_VALUE_HOST_DISK_TOTAL,
		t5.SHORT_NAME;



----应用系统告警趋势----

DROP TABLE if EXISTS `bi_alm_yingyong_gaojing`;
CREATE TABLE  `bi_alm_yingyong_gaojing`(
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`PRIORITY` varchar(10) DEFAULT NULL comment '应用等级',
	`app_type` varchar(10) DEFAULT NULL comment '应用分类',
	`app_type_name` varchar(200) DEFAULT NULL comment '应用名称',
	`title` VARCHAR(256) DEFAULT NULL COMMENT '告警标题',
	`alarm_text` VARCHAR(256) DEFAULT NULL COMMENT '告警内容',
	`alarm_count` decimal(58,4) DEFAULT NULL COMMENT '告警次数',
	`alarm_time` decimal(58,4) DEFAULT NULL COMMENT '告警时长(h)',
	`alarm_object_ip` VARCHAR(256) DEFAULT NULL COMMENT '设备名称',
	`total_zy_num`  decimal(29,8) DEFAULT NULL COMMENT '总资产数（取平均）',
	 `severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警等级'
	)ENGINE = InnoDB DEFAULT CHARSET = utf8;


INSERT INTO  `bi_alm_yingyong_gaojing`
SELECT
   'HOUR' dtype,
  DATE_FORMAT(t4.occur_time,'%Y%m%d%H') as thedvalue,
	t2.ITM_APPCATAGORY_NAME AS NET_NAME,
	t1.ORGAN_ID AS ORGAN_ID,
	t3.SHORT_NAME AS organ_name,
CASE
		t1.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
CASE
		t1.app_type 
		WHEN '1' THEN
		'智慧服务' 
		WHEN '2' THEN
		'智慧管理' 
		WHEN '3' THEN
		'智慧审判' 
		WHEN '4' THEN
		'智慧执行' ELSE '其他' 
	END AS app_type,
	t1.ITM_APP_NAME AS app_type_name,
	t4.title AS title,
	t4.alarm_text AS alarm_text,
	sum(t4.alarm_count) AS alarm_count,
	sum(timestampdiff( MINUTE, t4.occur_time, t4.end_time )) AS alarm_time,
	t4.alarm_object_ip as alarm_object_ip,
	(select count(1) from itm_app) as total_zy_num,
	case t4.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type
FROM
	itm_app t1
	LEFT JOIN itm_appcatagory AS t2 ON t2.ITM_APPCATAGORY_CODE= t1.ITM_NETGROUP_ID
	LEFT JOIN pub_organ AS t3 ON t1.organ_id=t3.code
	inner JOIN v_bi_alm_all_alarm t4 ON t4.alarm_object_id = t1.ITM_APP_ID 
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,	t1.PRIORITY ,t1.app_type,t1.ITM_APP_NAME,t4.title,t4.alarm_object_ip,t4.alarm_text,DATE_FORMAT(t4.occur_time,'%Y%m%d%H'),t4.severity_id;
	
	
	
INSERT INTO  `bi_alm_yingyong_gaojing`
	SELECT
  'DAY' dtype,
  substring(thedvalue,1,8) AS thedvalue,
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	title,
	alarm_text,
	sum(alarm_count) ,
	sum(alarm_time),
	alarm_object_ip,
	sum(total_zy_num),
	severity_type
FROM bi_alm_yingyong_gaojing  where dtype = 'HOUR' 
group by 
	substring(thedvalue,1,8),
	net_name,
	ORGAN_ID,
	ORGAN_name, 
	PRIORITY,
	app_type,
	app_type_name,
	title,
	alarm_text,
	alarm_object_ip,
	severity_type; 	
	
	
	
INSERT INTO  `bi_alm_yingyong_gaojing`
SELECT
  'MONTH' dtype,
 substring(thedvalue,1,6) AS thedvalue,
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	title,
	alarm_text,
	sum(alarm_count) ,
	sum(alarm_time),
	alarm_object_ip,
	sum(total_zy_num),
	severity_type
FROM bi_alm_yingyong_gaojing  where dtype = 'DAY' 
group by 
	substring(thedvalue,1,6),
	net_name,
	ORGAN_ID,
	ORGAN_name, 
	PRIORITY,
	app_type,
	app_type_name,
	title,
	alarm_text,
	alarm_object_ip,
	severity_type;
	
	
INSERT INTO  `bi_alm_yingyong_gaojing`	
	SELECT
  'YEAR' dtype,
   substring(thedvalue,1,4) AS thedvalue,
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	title,
	alarm_text,
	sum(alarm_count) ,
	sum(alarm_time),
	alarm_object_ip,
	sum(total_zy_num),
	severity_type
FROM bi_alm_yingyong_gaojing  where dtype = 'MONTH' 
group by 
	substring(thedvalue,1,4),
	net_name,
	ORGAN_ID,
	ORGAN_name, 
	PRIORITY,
	app_type,
	app_type_name,
	title,
	alarm_text,
	alarm_object_ip,
	severity_type;




	----基础设置应用运行----

	

DROP TABLE if EXISTS `bi_alm_yingyong_yunxing`;
CREATE TABLE  `bi_alm_yingyong_yunxing`(
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`PRIORITY` varchar(10) DEFAULT NULL comment '应用等级',
	`app_type` varchar(10) DEFAULT NULL comment '应用分类',
	`app_type_name` varchar(200) DEFAULT NULL comment '应用名称',
	`vist_count` varchar(10) DEFAULT NULL COMMENT '访客数',
	`slow_count` varchar(10) DEFAULT NULL COMMENT '慢操作量',
  `oper_count` varchar(10) DEFAULT NULL COMMENT '操作次数/访问量',
	`resp_time` varchar(32) DEFAULT NULL COMMENT '响应时间',
	`render_time` varchar(32) DEFAULT NULL COMMENT '渲染时间'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into bi_alm_yingyong_yunxing
SELECT
   'HOUR' dtype,
	 DATE_FORMAT(t4.time_mark,'%Y%m%d%H') as thedvalue,
	t2.ITM_APPCATAGORY_NAME AS NET_NAME,
	t1.ORGAN_ID AS ORGAN_ID,
	t3.SHORT_NAME AS organ_name,
CASE
		t1.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
CASE
		t1.app_type 
		WHEN '1' THEN
		'智慧服务' 
		WHEN '2' THEN
		'智慧管理' 
		WHEN '3' THEN
		'智慧审判' 
		WHEN '4' THEN
		'智慧执行' ELSE '其他' 
	END AS app_type,
	t1.ITM_APP_NAME AS app_type_name,
	sum(t4.visit_count) as vist_count,
	sum(t4.slow_count) as  slow_count,
	sum(t4.operate_count) as oper_count,
	t4.resp_time as resp_time,
	t4.render_time as render_time
	FROM
	itm_app t1
	LEFT JOIN itm_appcatagory AS t2 ON t2.ITM_APPCATAGORY_CODE= t1.ITM_NETGROUP_ID
	LEFT JOIN pub_organ AS t3 ON t1.organ_id=t3.code 
	inner join app_visit_log t4 on  t1.ITM_APP_id = t4.app_id
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,	t1.PRIORITY ,t1.app_type,t1.ITM_APP_NAME,t4.resp_time,t4.render_time,DATE_FORMAT(t4.time_mark,'%Y%m%d%H');
	
insert into bi_alm_yingyong_yunxing
SELECT
  'DAY' dtype,
	substring(thedvalue,1,8) as thedvalue,
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(vist_count), 
	sum(slow_count),
	sum(oper_count),
	resp_time,
	render_time
	FROM  bi_alm_yingyong_yunxing where dtype = 'HOUR'
	group by app_type_name,ORGAN_ID,	organ_name,net_NAME,PRIORITY,app_type,app_type_name,resp_time,render_time,substring(thedvalue,1,8);
	
	
	
insert into bi_alm_yingyong_yunxing
SELECT
  'MONTH' dtype,
	substring(thedvalue,1,6) as thedvalue,
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(vist_count), 
	sum(slow_count),
	sum(oper_count),
	resp_time,
	render_time
	FROM  bi_alm_yingyong_yunxing where dtype = 'DAY'
	group by app_type_name,ORGAN_ID,	organ_name,net_NAME,PRIORITY,app_type,app_type_name,resp_time,render_time,substring(thedvalue,1,6);
	
	
	
insert into bi_alm_yingyong_yunxing
SELECT
  'YEAR' dtype,
	substring(thedvalue,1,4) as thedvalue,
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(vist_count), 
	sum(slow_count),
	sum(oper_count),
	resp_time,
	render_time
	FROM  bi_alm_yingyong_yunxing where dtype = 'MONTH'
	group by app_type_name,ORGAN_ID,organ_name,net_NAME,PRIORITY,app_type,app_type_name,resp_time,render_time,substring(thedvalue,1,4);
		



----应用系统用户趋势分析----

drop table if EXISTS bi_alm_yingyong_yonghu;
CREATE table bi_alm_yingyong_yonghu(
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`PRIORITY` varchar(10) DEFAULT NULL comment '应用等级',
	`app_type` varchar(10) DEFAULT NULL comment '应用分类',
	`app_type_name` varchar(200) DEFAULT NULL comment '应用名称',
	`visit_count` varchar(10) DEFAULT NULL COMMENT '用户访问',
	`operate_count` varchar(10) DEFAULT NULL COMMENT '用户操作',
  `login_user_count` varchar(10) DEFAULT NULL COMMENT '活跃用户'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into bi_alm_yingyong_yonghu
SELECT
   'HOUR' dtype,
	 DATE_FORMAT(t4.create_time,'%Y%m%d%H') as thedvalue,
	t2.ITM_APPCATAGORY_NAME AS NET_NAME,
	t1.ORGAN_ID AS ORGAN_ID,
	t3.SHORT_NAME AS organ_name,
CASE
		t1.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
CASE
		t1.app_type 
		WHEN '1' THEN
		'智慧服务' 
		WHEN '2' THEN
		'智慧管理' 
		WHEN '3' THEN
		'智慧审判' 
		WHEN '4' THEN
		'智慧执行' ELSE '其他' 
	END AS app_type,
	t1.ITM_APP_NAME,
	sum(t4.visit_count) as visit_count,
	sum(t4.operate_count) as operate_count,
	sum(t4.login_user_count) as login_user_count
FROM
	itm_app t1
	LEFT JOIN itm_appcatagory AS t2 ON t2.ITM_APPCATAGORY_CODE= t1.ITM_NETGROUP_ID
	LEFT JOIN pub_organ AS t3 ON t1.organ_id=t3.code 
	inner join app_visit_log_region t4 on t1.ORGAN_ID = t4.organ_id
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,	t1.PRIORITY ,t1.app_type,t1.ITM_APP_NAME,DATE_FORMAT(t4.create_time,'%Y%m%d%H') ;
	
insert into bi_alm_yingyong_yonghu

select 
 'DAY' dtype,
	SUBSTRING(thedvalue,1,8),
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(visit_count),
	sum(operate_count),
	sum(login_user_count) from bi_alm_yingyong_yonghu where dtype = 'HOUR'
	group by SUBSTRING(thedvalue,1,8),
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name;
		
insert into bi_alm_yingyong_yonghu
SELECT
   'MONTH' dtype,
	  DATE_FORMAT(t4.create_time,'%Y%m') as thedvalue,
	t2.ITM_APPCATAGORY_NAME AS NET_NAME,
	t1.ORGAN_ID AS ORGAN_ID,
	t3.SHORT_NAME AS organ_name,
CASE
		t1.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
CASE
		t1.app_type 
		WHEN '1' THEN
		'智慧服务' 
		WHEN '2' THEN
		'智慧管理' 
		WHEN '3' THEN
		'智慧审判' 
		WHEN '4' THEN
		'智慧执行' ELSE '其他' 
	END AS app_type,
	t1.ITM_APP_NAME,
	sum(t4.visit_count) as visit_count,
	sum(t4.operate_count) as operate_count,
	sum(t4.login_user_count) as login_user_count
FROM
	itm_app t1
	LEFT JOIN itm_appcatagory AS t2 ON t2.ITM_APPCATAGORY_CODE= t1.ITM_NETGROUP_ID
	LEFT JOIN pub_organ AS t3 ON t1.organ_id=t3.code 
	inner join app_visit_log_region_month t4 on t1.ORGAN_ID = t4.organ_id
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,	t1.PRIORITY ,t1.app_type,t1.ITM_APP_NAME,DATE_FORMAT(t4.create_time,'%Y%m');	
	
	
insert into bi_alm_yingyong_yonghu
select 
 'YEAR' dtype,
	SUBSTRING(thedvalue,1,4),
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(visit_count),
	sum(operate_count),
	sum(login_user_count) from bi_alm_yingyong_yonghu where dtype = 'MONTH'
	group by SUBSTRING(thedvalue,1,4),
	NET_NAME,
	ORGAN_ID,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name;


	
----重写应用时间响应时间趋势----
DROP TABLE if EXISTS `bi_app_response_time_stat`;
CREATE TABLE  `bi_app_response_time_stat`(
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`dhour` varchar(15) DEFAULT NULL COMMENT '小时',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`ORGAN_ID` varchar(30) DEFAULT NULL comment '区域id/数据来源',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`PRIORITY` varchar(10) DEFAULT NULL comment '应用等级',
	`app_type` varchar(10) DEFAULT NULL comment '应用分类',
	`app_type_name` varchar(200) DEFAULT NULL comment '应用名称',
	`response_time` decimal(20,10) DEFAULT NULL COMMENT '平均响应时间',
	`response_time_max` decimal(20,10) DEFAULT NULL COMMENT '最大响应时间'
	)ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into bi_app_response_time_stat
SELECT
   'HOUR' dtype,
	 DATE_FORMAT(t4.create_time,'%Y%m%d%H') as thedvalue,
	 date_format(t4.CREATE_TIME,'%H') as dhour,
	t2.ITM_APPCATAGORY_NAME AS NET_NAME,
	t1.ORGAN_ID AS ORGAN_ID,
	t3.SHORT_NAME AS organ_name,
CASE
		t1.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
CASE
		t1.app_type 
		WHEN '1' THEN
		'智慧服务' 
		WHEN '2' THEN
		'智慧管理' 
		WHEN '3' THEN
		'智慧审判' 
		WHEN '4' THEN
		'智慧执行' ELSE '其他' 
	END AS app_type,
	t1.ITM_APP_NAME as app_type_name,
	avg(t4.resp_time) as response_time,
	max(t4.resp_time) as response_time_max
FROM
	itm_app t1
	LEFT JOIN itm_appcatagory AS t2 ON t2.ITM_APPCATAGORY_CODE= t1.ITM_NETGROUP_ID
	LEFT JOIN pub_organ AS t3 ON t1.organ_id=t3.code 
	inner join app_app_detail_stat t4 on t4.app_id = t1.itm_app_id
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,	t1.PRIORITY ,t1.app_type,t1.ITM_APP_NAME,DATE_FORMAT(t4.create_time,'%Y%m%d%H'),date_format(t4.CREATE_TIME,'%H');

INSERT into bi_app_response_time_stat

select 
	'DAY' as dtype,
	substring(thedvalue,1,8),
	dhour,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	PRIORITY,
	app_type,
	app_type_name,
	avg(response_time),
	max(response_time_max) 
from bi_app_response_time_stat where dtype = 'HOUR' 
group by 
	dhour,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	PRIORITY,
	app_type,
	app_type_name,
	substring(thedvalue,1,8);
	
	
INSERT into bi_app_response_time_stat
select 
	'MONTH' as dtype,
	substring(thedvalue,1,6),
	dhour,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	PRIORITY,
	app_type,
	app_type_name,
	avg(response_time),
	max(response_time_max)
from bi_app_response_time_stat where dtype = 'DAY' 
group by 
	dhour,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	PRIORITY,
	app_type,
	app_type_name,
	substring(thedvalue,1,6);


	
INSERT into bi_app_response_time_stat
select 
	'YEAR' as dtype,
	substring(thedvalue,1,4),
	dhour,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	PRIORITY,
	app_type,
	app_type_name,
	avg(response_time),
	max(response_time_max) 
from bi_app_response_time_stat where dtype = 'MONTH' 
group by 
	dhour,
	net_name,
	ORGAN_ID,
	ORGAN_name,
	PRIORITY,
	app_type,
	app_type_name,
	substring(thedvalue,1,4);


---应用访问分析----

DROP TABLE IF EXISTS `bi_app_access_stat`;
CREATE TABLE `bi_app_access_stat` (
  `dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`net_name` varchar(200) DEFAULT NULL comment '网系',
	`ORGAN_name`  varchar(200) DEFAULT NULL comment '区域名称',
	`PRIORITY` varchar(10) DEFAULT NULL comment '应用等级',
	`app_type` varchar(10) DEFAULT NULL comment '应用分类',
	`app_type_name` varchar(200) DEFAULT NULL comment '应用名称',
	`visit_count` decimal(20,10) DEFAULT NULL COMMENT '备份数据容量',
	`operate_count` decimal(20,10) DEFAULT NULL COMMENT '总容量'
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into bi_app_access_stat
SELECT
	'HOUR' AS dtype,
  DATE_FORMAT(t4.time_mark,'%Y%m%d%H') as thedvalue,
	t2.ITM_APPCATAGORY_NAME AS NET_NAME,
	t3.SHORT_NAME AS organ_name,
CASE
		t1.PRIORITY 
		WHEN 'low' THEN
		'一般应用' 
		WHEN 'important' THEN
		'重要应用' 
		WHEN 'high' THEN
		'核心应用' ELSE '未知' 
	END AS PRIORITY,
CASE
		t1.app_type 
		WHEN '1' THEN
		'智慧服务' 
		WHEN '2' THEN
		'智慧管理' 
		WHEN '3' THEN
		'智慧审判' 
		WHEN '4' THEN
		'智慧执行' ELSE '其他' 
	END AS app_type,
	t1.ITM_APP_NAME AS app_type_name,
	sum(t4.visit_count) as visit_count,
	sum(t4.operate_count) as operate_count 
FROM app_visit_log_region as t4 inner join itm_app t1 on t4.app_id=t1.itm_app_id 
	inner join itm_appcatagory AS t2 ON t1.ITM_NETGROUP_ID=t2.ITM_APPCATAGORY_CODE
	inner JOIN pub_organ AS t3 ON t1.organ_id=t3.code where t4.time_mark !=''
GROUP BY
t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,t1.PRIORITY,t1.app_type,t1.ITM_APP_NAME,
	DATE_FORMAT(t4.time_mark,'%Y%m%d%H');
  

insert into bi_app_access_stat
SELECT
	'DAY' AS dtype,
  substring(thedvalue,1,8) AS thedvalue, 
	NET_NAME,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(visit_count) as visit_count,
	sum(operate_count) as operate_count 
FROM bi_app_access_stat WHERE dtype = 'HOUR'
GROUP BY
app_type_name,
organ_name,
net_NAME,
PRIORITY,
app_type,
app_type_name,
substring( thedvalue, 1, 8 );
  

insert into bi_app_access_stat
SELECT
	'MONTH' AS dtype,
  substring(thedvalue,1,6) AS thedvalue, 
	NET_NAME,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(visit_count) as visit_count,
	sum(operate_count) as operate_count 
FROM bi_app_access_stat WHERE dtype = 'DAY'
GROUP BY
app_type_name,
organ_name,
net_NAME,
PRIORITY,
app_type,
app_type_name,
substring( thedvalue, 1, 6 );

 
insert into bi_app_access_stat
SELECT
	'YEAR' AS dtype,
  substring(thedvalue,1,4) AS thedvalue, 
	NET_NAME,
	organ_name,
	PRIORITY,
	app_type,
	app_type_name,
	sum(visit_count) as visit_count,
	sum(operate_count) as operate_count 
FROM bi_app_access_stat WHERE dtype = 'MONTH'
GROUP BY
app_type_name,
organ_name,
net_NAME,
PRIORITY,
app_type,
app_type_name,
substring( thedvalue, 1, 4 );