SELECT 
	mc.target_name 
	, mc.key_value 
	, ROUND( 100 - mc.value, 2 ) current_val 
	, hist.day7 prev_week_val
	, hist.day30 prev_month_val
	, prop.contact
	, mc.collection_timestamp
FROM sysman.mgmt$metric_current mc, (
	SELECT 
		target_guid
		, MAX( CASE WHEN prop.property_name = 'orcl_gtp_contact' THEN prop.property_value END ) contact
		, MAX( CASE WHEN prop.property_name = 'orcl_gtp_lifecycle_status' THEN prop.property_value END ) lifecycle
	FROM sysman.mgmt$target_properties prop
	GROUP BY target_guid 
) prop, ( 
	SELECT DISTINCT host_name FROM sysman.mgmt$target db WHERE target_type IN ( 'rac_database', 'oracle_database' ) 
) db, (
	SELECT 
		target_guid
		, key_value
		, SUM( case when rollup_timestamp = trunc(sysdate)-7 then average else null end ) day7
		, SUM( case when rollup_timestamp = trunc(sysdate)-30 then average else null end ) day30
	FROM (
		SELECT 
			md.target_guid
			, md.key_value
			, ROUND( 100 - md.average, 2 ) average
			, md.rollup_timestamp
		FROM sysman.mgmt$metric_daily md
		WHERE md.target_type = 'host' 
		AND md.metric_name = 'Filesystems' 
		AND md.metric_column = 'pctAvailable' 
		AND md.rollup_timestamp BETWEEN TRUNC(sysdate-30) AND TRUNC(sysdate - 7)  
	) GROUP BY target_guid, key_value 
) hist
WHERE mc.target_type = 'host' 
AND mc.metric_name = 'Filesystems' 
AND mc.metric_column = 'pctAvailable' 
AND mc.key_value != '/proc'
AND ROUND( 100 - mc.value, 2 ) > 90
AND mc.target_name = db.host_name
AND mc.target_guid = prop.target_guid
AND mc.target_guid = hist.target_guid
AND mc.key_value = hist.key_value
AND prop.lifecycle IN ('MissionCritical','Production')
ORDER BY 1, 2, 4 ;