Tags

, , ,

Some of our notification rules use the ‘Life Cyce Status’ property to determine whether a page or an email is required.

Things won’t work for targets where we’ve forgotten to set the Life Cycle Status, so we need to find to buggers starting with this query:

SELECT CASE INSTR ( cm_target_name, ‘_’ )
WHEN 0 THEN cm_target_name
ELSE SUBSTR ( cm_target_name, 0, INSTR ( cm_target_name, ‘_’ ) – 1 )
END
database_name,
property_value
FROM sysman.cm$em_tprops_ecm_view
WHERE cm_target_type = ‘oracle_database’
AND property_name = ‘orcl_gtp_lifecycle_status’

UNION ALL
SELECT DISTINCT CASE INSTR ( cm_target_name, ‘_’ )
WHEN 0 THEN cm_target_name
ELSE SUBSTR ( cm_target_name, 0, INSTR ( cm_target_name, ‘_’ ) – 1 )
END
database_name,
‘Not defined’
FROM sysman.cm$em_tprops_ecm_view
WHERE cm_target_name NOT IN
(SELECT cm_target_name
FROM sysman.cm$em_tprops_ecm_view
WHERE cm_target_type = ‘oracle_database’
AND property_name = ‘orcl_gtp_lifecycle_status’)
AND cm_target_type = ‘oracle_database’
ORDER BY database_name;

I know, I know – NOT IN is bad form but it works for this limited dataset where I want to combine a list of databases that have the property with a list of databases that lack that same property.