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 '应用名称',
	`alarm_count` decimal(58,4) DEFAULT NULL COMMENT '告警次数',
	`collection_frequency`  decimal(29,8) DEFAULT NULL COMMENT '采集次数',
	`severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警等级',
	`alarm_duration` decimal(58,4) DEFAULT NULL COMMENT '告警时长',
	`ITM_APP_ID` varchar(30) DEFAULT NULL comment '应用id',
	`alarm_duration_sum` decimal(58,4) DEFAULT NULL COMMENT '告警总时长'
	
	)ENGINE = InnoDB DEFAULT CHARSET = utf8;



INSERT INTO `bi_alm_yingyong_gaojing`
SELECT
   'HOUR' dtype,
 t4.thedvalue 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.avg_alarm_count) as alarm_count,
sum(t4.collection_frequency) as collection_frequency,
	case t4.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	abs(t4.alarm_duration) as alarm_duration,
	t1.ITM_APP_ID as ITM_APP_ID,
	B.alarm_duration_sum as alarm_duration_sum 
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
	left JOIN tmp_v_bi_alm_all_alarm_hour t4 ON t4.alarm_object_id = t1.ITM_APP_ID LEFT join (select thedvalue,sum(abs(alarm_duration)) as alarm_duration_sum from tmp_v_bi_alm_all_alarm_hour group by thedvalue) as B
	on t4.thedvalue = B.thedvalue
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,t1.PRIORITY,t1.app_type,t1.ITM_APP_NAME,t4.severity_id,thedvalue,t4.alarm_object_id,t1.ITM_APP_ID,t4.thedvalue;






INSERT INTO `bi_alm_yingyong_gaojing`
SELECT
   'DAY' dtype,
 t4.thedvalue 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.avg_alarm_count) as alarm_count,
sum(t4.collection_frequency) as collection_frequency,
	case t4.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t4.alarm_duration as alarm_duration,
	t1.ITM_APP_ID as ITM_APP_ID,
	B.alarm_duration_sum as alarm_duration_sum 
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_day t4 ON t4.alarm_object_id = t1.ITM_APP_ID join (select sum(alarm_duration) as alarm_duration_sum from v_bi_alm_all_alarm_day group by thedvalue) as B
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,t1.PRIORITY,t1.app_type,t1.ITM_APP_NAME,t4.severity_id,thedvalue,t4.alarm_object_id,t1.ITM_APP_ID,t4.thedvalue;



INSERT INTO `bi_alm_yingyong_gaojing`
SELECT
   'MONTH' dtype,
 t4.thedvalue 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.avg_alarm_count) as alarm_count,
sum(t4.collection_frequency) as collection_frequency,
	case t4.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	abs(t4.alarm_duration) as alarm_duration,
	t1.ITM_APP_ID as ITM_APP_ID,
	B.alarm_duration_sum as alarm_duration_sum 
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_month t4 ON t4.alarm_object_id = t1.ITM_APP_ID 
	left join (select thedvalue,sum(abs(alarm_duration)) as alarm_duration_sum 
	from v_bi_alm_all_alarm_month group by thedvalue) as B on t4.thedvalue = B.thedvalue
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,t1.PRIORITY,t1.app_type,t1.ITM_APP_NAME,t4.severity_id,thedvalue,t4.alarm_object_id,t1.ITM_APP_ID,t4.thedvalue;



INSERT INTO `bi_alm_yingyong_gaojing`
SELECT
   'MONTH' dtype,
 t4.thedvalue 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.avg_alarm_count) as alarm_count,
sum(t4.collection_frequency) as collection_frequency,
	case t4.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t4.alarm_duration as alarm_duration,
	t1.ITM_APP_ID as ITM_APP_ID,
	B.alarm_duration_sum as alarm_duration_sum 
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_year t4 ON t4.alarm_object_id = t1.ITM_APP_ID join (select sum(alarm_duration) as alarm_duration_sum 
	from v_bi_alm_all_alarm_year group by thedvalue) as B
	group by t2.ITM_APPCATAGORY_NAME,t1.ORGAN_ID,t3.SHORT_NAME,t1.PRIORITY,t1.app_type,t1.ITM_APP_NAME,t4.severity_id,thedvalue,t4.alarm_object_id,t1.ITM_APP_ID,t4.thedvalue;




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

	

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 );

-- 基础设施告警


DROP TABLE IF EXISTS `bi_jichu_sheshi_gaojing`;
CREATE TABLE `bi_jichu_sheshi_gaojing` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`ITM_HARDWARE_RESOURCES_ID` varchar(255) 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 '设备名称/资产名称',
	`alarm_count`  decimal(29,8) DEFAULT NULL COMMENT '告警次数',
	`collection_frequency`  decimal(29,8) DEFAULT NULL COMMENT '采集次数',
	`severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警等级',
	`alarm_duration` decimal(58,4) DEFAULT NULL COMMENT '告警时长',
	`alarm_duration_sum` decimal(58,4) DEFAULT NULL COMMENT '告警总时长'
	)ENGINE = InnoDB DEFAULT CHARSET = utf8;


INSERT into bi_jichu_sheshi_gaojing	
SELECT 
	 'HOUR' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	sum(t2.avg_alarm_count) alarm_count,
	sum(t2.collection_frequency) as collection_frequency,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	
	t2.alarm_duration,						
	B.alarm_duration_sum
	FROM itm_hardware_resources t1 
	inner join tmp_v_bi_alm_all_alarm_hour t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code left join 
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from tmp_v_bi_alm_all_alarm_hour
group by 
thedvalue) B on t2.thedvalue = B.thedvalue 
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;




INSERT into bi_jichu_sheshi_gaojing	
SELECT 
	 'DAY' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	sum(t2.avg_alarm_count) alarm_count,
	sum(t2.collection_frequency) as collection_frequency,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t2.alarm_duration,
	B.alarm_duration_sum
	FROM itm_hardware_resources t1 
	inner join v_bi_alm_all_alarm_day t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code left join 
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from v_bi_alm_all_alarm_day
group by 
thedvalue) B on t2.thedvalue = B.thedvalue 
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;

	
INSERT into bi_jichu_sheshi_gaojing	
SELECT 
	 'MONTH' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	sum(t2.avg_alarm_count) alarm_count,
	sum(t2.collection_frequency) as collection_frequency,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t2.alarm_duration,
	B.alarm_duration_sum
	FROM itm_hardware_resources t1 
	inner join v_bi_alm_all_alarm_month t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code left join 
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from v_bi_alm_all_alarm_month
group by 
thedvalue) B on t2.thedvalue = B.thedvalue 
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;
	
	
	
INSERT into bi_jichu_sheshi_gaojing	
SELECT 
	 'YEAR' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	sum(t2.avg_alarm_count) alarm_count,
	sum(t2.collection_frequency) as collection_frequency,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t2.alarm_duration,
	B.alarm_duration_sum
	FROM itm_hardware_resources t1 
	inner join v_bi_alm_all_alarm_year t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code left join 
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from v_bi_alm_all_alarm_year
group by 
thedvalue) B on t2.thedvalue = B.thedvalue 
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;
	
	
-- 基础设施故障

DROP TABLE IF EXISTS `bi_jichu_sheshi_guzhang`;
CREATE TABLE `bi_jichu_sheshi_guzhang` (
	`dtype` varchar(5) NOT NULL DEFAULT '' COMMENT '日期类型',
	`thedvalue` varchar(15) DEFAULT NULL COMMENT '日期',
	`ITM_HARDWARE_RESOURCES_ID` varchar(255) 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 '设备名称/资产名称',
	`total_zy_num`  decimal(29,8) DEFAULT NULL COMMENT '总资产数',
	`severity_type` varchar(2) NOT NULL DEFAULT '' COMMENT '告警等级',
	`alarm_duration` decimal(58,4) DEFAULT NULL COMMENT '故障时长(h)',
	`alarm_object_sum`  decimal(29,8) DEFAULT NULL COMMENT '故障数',
	`alarm_duration_sum` decimal(58,4) DEFAULT NULL COMMENT '故障总时长(h)'
	)ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into bi_jichu_sheshi_guzhang

SELECT 
	 'HOUR' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	D.total_zy_num as total_zy_num,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t2.alarm_duration,
	B.alarm_object_sum,
	C.alarm_duration_sum 
	FROM itm_hardware_resources t1 
	inner join tmp_v_bi_alm_all_alarm_hour t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code 
		left join
(select thedvalue,count(DISTINCT alarm_object_id) as alarm_object_sum from tmp_v_bi_alm_all_alarm_hour group by thedvalue
) as B on B.thedvalue = t2.thedvalue left join
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from tmp_v_bi_alm_all_alarm_hour group by thedvalue) C on 
C.thedvalue = t2.thedvalue  join (select count(1) as total_zy_num from itm_hardware_resources where ITM_MONITOR_FLAG = '0') D
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;


	
insert into bi_jichu_sheshi_guzhang
SELECT 
	 'MONTH' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	D.total_zy_num as total_zy_num,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t2.alarm_duration,
	B.alarm_object_sum,
	C.alarm_duration_sum 
	FROM itm_hardware_resources t1 
	inner join v_bi_alm_all_alarm_month t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code 
		left join
(select thedvalue,count(DISTINCT alarm_object_id) as alarm_object_sum from v_bi_alm_all_alarm_month group by thedvalue
) as B on B.thedvalue = t2.thedvalue left join
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from v_bi_alm_all_alarm_month group by thedvalue) C on 
C.thedvalue = t2.thedvalue  join (select count(1) as total_zy_num from itm_hardware_resources where ITM_MONITOR_FLAG = '0') D
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;









insert into bi_jichu_sheshi_guzhang
SELECT 
	 'YEAR' AS dtype,
	 t2.thedvalue as thedvalue,
	 t1.ITM_HARDWARE_RESOURCES_ID as ITM_HARDWARE_RESOURCES_ID,
	 t1.ORGAN_ID,
	 t4.SHORT_NAME,
	t3.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,
	D.total_zy_num as total_zy_num,
	case t2.severity_id when 1 then '致命' when 2 then '严重' when 3 then '一般' else '未知' end as severity_type,
	t2.alarm_duration,
	B.alarm_object_sum,
	C.alarm_duration_sum 
	FROM itm_hardware_resources t1 
	inner join v_bi_alm_all_alarm_year t2 
	  ON t2.alarm_object_id = t1.ITM_HARDWARE_RESOURCES_ID
	left JOIN itm_appcatagory AS t3 
	  ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	left join pub_organ as t4 
	  on t1.ORGAN_ID = t4.code 
		left join
(select thedvalue,count(DISTINCT alarm_object_id) as alarm_object_sum from v_bi_alm_all_alarm_year group by thedvalue
) as B on B.thedvalue = t2.thedvalue left join
(select thedvalue,sum(alarm_duration) as alarm_duration_sum from v_bi_alm_all_alarm_year group by thedvalue) C on 
C.thedvalue = t2.thedvalue  join (select count(1) as total_zy_num from itm_hardware_resources where ITM_MONITOR_FLAG = '0') D
	group by  
	 t1.ITM_HARDWARE_RESOURCES_ID
	,t3.ITM_APPCATAGORY_NAME
	,t1.cloud
	,t1.itm_hardware_resources_type
	,t2.thedvalue;



-- 基础设备运维分析


	`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_DISK_USED` decimal(29,8) DEFAULT NULL COMMENT '已使用设备容量',
	`itm_hardware_resources_id` varchar(100) DEFAULT NULL COMMENT '设备ID',
	`IND_VALUE_NUM` decimal(29,8) DEFAULT NULL COMMENT '总设备数'
	) ENGINE = InnoDB DEFAULT CHARSET = utf8;

insert into bi_cunchu_shebei_yunwei_fenxi
select 
  'HOUR' AS dtype,
	DATE_FORMAT(t1.CREATE_TIME,'%Y%m%d%H') as thedvalue,
	DATE_FORMAT(t1.CREATE_TIME,'%H') as dhour,
	t1.ORGAN_ID,
	t1.ITM_HARDWARE_RESOURCES_AREACODE as organ_name,
	t3.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,
	0 as IND_VALUE_HOST_DISK_USED,
	t1.itm_hardware_resources_id,
	b.c as IND_VALUE_NUM
from
	itm_hardware_resources  as t1
	left JOIN itm_appcatagory AS t3 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	join (select count(1) c  from itm_hardware_resources where ITM_HARDWARE_RESOURCES_TYPE like '%0003%' ) b 
	where t1.ITM_HARDWARE_RESOURCES_TYPE like '%0003%' group by itm_hardware_resources_id,DATE_FORMAT(t1.CREATE_TIME,'%Y%m%d%H');
	
insert into bi_cunchu_shebei_yunwei_fenxi	
select 
  'HOUR' AS dtype,
	DATE_FORMAT(t1.CREATE_TIME,'%Y%m%d%H') as thedvalue,
	DATE_FORMAT(t1.CREATE_TIME,'%H') as dhour,
	t1.ORGAN_ID,
	t1.ITM_HARDWARE_RESOURCES_AREACODE as organ_name,
	t3.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,
	0 as IND_VALUE_HOST_DISK_USED,
	t1.itm_hardware_resources_id,
	b.c as IND_VALUE_NUM
from
	itm_hardware_resources  as t1
	left JOIN itm_appcatagory AS t3 ON t1.ITM_HARDWARE_RESOURCES_NETCODE = t3.ITM_APPCATAGORY_CODE 
	join (select count(1) c  from itm_hardware_resources where ITM_HARDWARE_RESOURCES_TYPE like '%0003%' ) b 
	where t1.ITM_HARDWARE_RESOURCES_TYPE like '%0003%' group by itm_hardware_resources_id,DATE_FORMAT(t1.CREATE_TIME,'%Y%m%d%H');
	
	
	
insert into bi_cunchu_shebei_yunwei_fenxi
	select 
  'DAY' AS dtype,
	substring(thedvalue,1,8) as thedvalue,
	substring(thedvalue,9,10) as dhour,
	ORGAN_ID,
	organ_name,
	NET_NAME,
	cloud,
	itm_hardware_resources_cntype,
	ITM_HARDWARE_RESOURCES_NAME,
	0 as IND_VALUE_HOST_DISK_USED,
	itm_hardware_resources_id,
	IND_VALUE_NUM 
from
	bi_cunchu_shebei_yunwei_fenxi where dtype = 'HOUR'
 group by itm_hardware_resources_id,substring(thedvalue,1,8);
 
 
 
 
 
insert into bi_cunchu_shebei_yunwei_fenxi
	select 
  'MONTH' AS dtype,
	substring(thedvalue,1,6) as thedvalue,
	substring(thedvalue,9,10) as dhour,
	ORGAN_ID,
	organ_name,
	NET_NAME,
	cloud,
	itm_hardware_resources_cntype,
	ITM_HARDWARE_RESOURCES_NAME,
	0 as IND_VALUE_HOST_DISK_USED,
	itm_hardware_resources_id,
	IND_VALUE_NUM 
from
	bi_cunchu_shebei_yunwei_fenxi where dtype = 'DAY'
 group by itm_hardware_resources_id,substring(thedvalue,1,6);
 
 
 
   
insert into bi_cunchu_shebei_yunwei_fenxi
	select 
  'YEAR' AS dtype,
	substring(thedvalue,1,6) as thedvalue,
	substring(thedvalue,9,10) as dhour,
	ORGAN_ID,
	organ_name,
	NET_NAME,
	cloud,
	itm_hardware_resources_cntype,
	ITM_HARDWARE_RESOURCES_NAME,
	0 as IND_VALUE_HOST_DISK_USED,
	itm_hardware_resources_id,
	IND_VALUE_NUM 
from
	bi_cunchu_shebei_yunwei_fenxi where dtype = 'MONTH'
 group by itm_hardware_resources_id,substring(thedvalue,1,4);