Rem Rem $Header: metric_init.sql 27-aug-2007.11:15:37 rrawat Exp $ Rem Rem metric_init.sql Rem Rem Copyright (c) 2005, 2007, Oracle. All rights reserved. Rem Rem NAME Rem metric_init.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rrawat 08/27/07 - Backport rrawat_bug-6336496 from main Rem rrawat 08/22/07 - Bug-6336496 Rem niramach 08/02/05 - MOved the latest one frm v102010 to v102020 Rem niramach 06/09/05 - Fix for bug 4416854. Rem asaraswa 06/13/05 - fix group_target_compliance Rem pmodi 06/09/05 - Bug:4406767 - Changing call back name Rem asaraswa 06/09/05 - fixing group_target_compliance to handle other Rem aggregate targets Rem pmodi 05/26/05 - Bug:4396320-set is_transposed to 1 for host/UDM Rem and oracle_database/SQLUDM Rem rpinnama 05/13/05 - rpinnama_bug-4331862 Rem rpinnama 05/12/05 - Created Rem -- CREATE_POLICY_TREND_METRICS -- To be called when creating a repository. This creates all the metrics needed to support policy GUI charts for -- tracking policy violations. It creates the metrics and sets the default collections. All of the metrics are -- oracle_emrep metrics. -- DECLARE l_metricColList MGMT_METRIC_COLUMN_ARRAY; l_mco_object_target_guid MGMT_METRIC_COLUMN_OBJ; l_mco_group_target_guid MGMT_METRIC_COLUMN_OBJ; l_mco_member_target_guid MGMT_METRIC_COLUMN_OBJ; l_mco_member_target_type MGMT_METRIC_COLUMN_OBJ; l_mco_category MGMT_METRIC_COLUMN_OBJ; l_mco_compliance_score MGMT_METRIC_COLUMN_OBJ; l_mco_violation_level MGMT_METRIC_COLUMN_OBJ; l_mco_violation_count MGMT_METRIC_COLUMN_OBJ; l_source_query VARCHAR2(4000); BEGIN -- Set up column descriptors l_mco_object_target_guid := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'OBJECT_TARGET_GUID', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_TRUE, p_column_label=>'Object Target Guid', p_description=>'Identifies the target of the metric result row, not the target of the metric itself.', p_column_label_nlsid=>'oracle_emrep_pol_obj_targ_guid', p_description_nlsid=>'oracle_emrep_pol_obj_targ_guid_descr'); l_mco_group_target_guid := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'GROUP_TARGET_GUID', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_TRUE, p_column_label=>'Group Target Guid', p_description=>'Identifies the group target of the metric result row, not the target of the metric itself.', p_column_label_nlsid=>'oracle_emrep_pol_grp_targ_guid', p_description_nlsid=>'oracle_emrep_pol_grp_targ_guid_descr'); l_mco_member_target_guid := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'MEMBER_TARGET_GUID', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_TRUE, p_column_label=>'Member Target Guid', p_description=>'Identifies the member target of the metric result row, not the target of the metric itself.', p_column_label_nlsid=>'oracle_emrep_pol_mem_targ_guid', p_description_nlsid=>'oracle_emrep_pol_mem_targ_guid_descr'); l_mco_member_target_type := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'MEMBER_TARGET_TYPE', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_TRUE, p_column_label=>'Member Target Type', p_description=>'Identifies the target type of the member for this metric result row.', p_column_label_nlsid=>'oracle_emrep_pol_mem_targ_type', p_description_nlsid=>'oracle_emrep_pol_mem_targ_type_descr'); l_mco_category := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'CATEGORY', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_STRING, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_TRUE, p_column_label=>'Category', p_description=>'The category of the policy of which this metric row represents.', p_column_label_nlsid=>'oracle_emrep_pol_category', p_description_nlsid=>'oracle_emrep_pol_category_descr'); l_mco_compliance_score := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'COMPLIANCE_SCORE', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE, p_column_label=>'Compliance Score', p_description=>'The compliance score of the policy-target which this metric row represents.', p_column_label_nlsid=>'oracle_emrep_pol_comp_score', p_description_nlsid=>'oracle_emrep_pol_comp_score_descr'); l_mco_violation_level := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'VIOLATION_LEVEL', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_TRUE, p_column_label=>'Violation Level', p_description=>'The violation level of the policy which this metric row represents.', p_column_label_nlsid=>'oracle_emrep_pol_viol_level', p_description_nlsid=>'oracle_emrep_pol_viol_level_descr'); l_mco_violation_count := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name=>'VIOLATION_COUNT', p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE, p_column_label=>'Violation Count', p_description=>'The violation count of the policy-target which this metric row represents.', p_column_label_nlsid=>'oracle_emrep_pol_viol_count', p_description_nlsid=>'oracle_emrep_pol_viol_count_descr'); -- TARGET_COMPLIANCE l_source_query := 'select a.target_guid as object_target_guid, nvl(c.category_name,''ALL'') as category, ROUND(avg(a.compliance_score)) as compliance_score, rt.target_guid as target_guid from MGMT_POLICY_ASSOC_EVAL_SUMM a, MGMT_CATEGORY_MAP c, MGMT_TARGETS rt where a.policy_guid = c.object_guid and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' group by a.target_guid, rt.target_guid, rollup(category_name)'; -- Create metric l_metricColList := MGMT_METRIC_COLUMN_ARRAY( l_mco_object_target_guid, l_mco_category, l_mco_compliance_score ); MGMT_METRIC.CREATE_METRIC( p_target_type=>MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_metric_name=>'TARGET_COMPLIANCE', p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_metric_label=>'Target Compliance', p_is_repository=>MGMT_GLOBAL.G_TRUE, p_source_type=>MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source=>l_source_query, p_metric_column_list=>l_metricColList, p_metric_label_nlsid=>'oracle_emrep_pol_targ_comp', p_description=>'Measures target policy compliance over time.', p_description_nlsid=>'oracle_emrep_pol_targ_comp_descr', p_is_long_running=>1); COMMIT; -- TARGET_VIOLATIONS -- Create metric l_metricColList := MGMT_METRIC_COLUMN_ARRAY( l_mco_object_target_guid, l_mco_violation_level, l_mco_category, l_mco_violation_count ); l_source_query := 'select a.target_guid as object_target_guid, p.violation_level as violation_level, nvl(c.category_name, ''ALL'') as category, sum(a.non_exempt_violations_logged) as violation_count, rt.target_guid as target_guid from MGMT_POLICY_ASSOC_EVAL_SUMM a, MGMT_CATEGORY_MAP c, MGMT_POLICIES p, mgmt_targets rt where a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and p.policy_guid = a.policy_guid and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' group by a.target_guid, violation_level, rt.target_guid, rollup(category_name)'; MGMT_METRIC.CREATE_METRIC( p_target_type=>MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_metric_name=>'TARGET_VIOLATIONS', p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_metric_label=>'Target Violations', p_is_repository=>MGMT_GLOBAL.G_TRUE, p_source_type=>MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source=>l_source_query, p_metric_column_list=>l_metricColList, p_metric_label_nlsid=>'oracle_emrep_pol_targ_viol', p_description=>'Measure the number of target policy violations over time.', p_description_nlsid=>'oracle_emrep_pol_targ_viol_descr'); COMMIT; -- GROUP_COMPLIANCE -- Create metric l_metricColList := MGMT_METRIC_COLUMN_ARRAY( l_mco_group_target_guid, l_mco_member_target_type, l_mco_category, l_mco_compliance_score ); l_source_query := 'select group_target_guid,nvl(member_target_type,''ALL'') as member_target_type, nvl(category,''ALL'') as category,ROUND(avg(compliance_score)) as compliance_score, target_guid from ( --Member targets compliance score select DISTINCT f.composite_target_guid as group_target_guid, f.member_target_type as member_target_type, f.member_target_name as target_name, c.category_name as category, a.compliance_score as compliance_score, rt.target_guid as target_guid, a.policy_guid from mgmt_flat_target_memberships f, mgmt_policy_assoc_eval_summ a, mgmt_category_map c, mgmt_targets rt where f.member_target_guid = a.target_guid and a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' UNION --Group level compliance score select DISTINCT f.composite_target_guid as group_target_guid, --To facilitate the member target type filter to include group level target type also, --composite target type is passed as member target type for group level policies. f.composite_target_type as member_target_type, f.composite_target_name as target_name, c.category_name as category, a.compliance_score as compliance_score, rt.target_guid as target_guid, a.policy_guid from mgmt_flat_target_memberships f, mgmt_policy_assoc_eval_summ a, mgmt_category_map c, mgmt_targets rt where f.composite_target_guid = a.target_guid and a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' ) group by group_target_guid,target_guid,rollup(member_target_type),rollup(category)'; MGMT_METRIC.CREATE_METRIC( p_target_type=>MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_metric_name=>'GROUP_COMPLIANCE', p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_metric_label=>'Group Compliance', p_is_repository=>MGMT_GLOBAL.G_TRUE, p_source_type=>MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source=>l_source_query, p_metric_column_list=>l_metricColList, p_metric_label_nlsid=>'oracle_emrep_pol_grp_comp', p_description=>'Measures group policy compliance over time.', p_description_nlsid=>'oracle_emrep_pol_grp_comp_descr'); COMMIT; -- GROUP_VIOLATIONS -- Create metric l_metricColList := MGMT_METRIC_COLUMN_ARRAY( l_mco_group_target_guid, l_mco_member_target_type, l_mco_violation_level, l_mco_category, l_mco_violation_count ); l_source_query := 'select group_target_guid,nvl(member_target_type,''ALL'') as member_target_type, violation_level,nvl(category,''ALL'') as category, sum(violation_count) as violation_count,target_guid from ( --Member targets violations count select DISTINCT f.composite_target_guid as group_target_guid, f.member_target_type as member_target_type, f.member_target_name as target_name, p.violation_level as violation_level, c.category_name as category, a.non_exempt_violations_logged as violation_count, rt.target_guid as target_guid, p.policy_guid from mgmt_flat_target_memberships f, mgmt_policy_assoc_eval_summ a, mgmt_category_map c, mgmt_policies p, mgmt_targets rt where f.member_target_guid = a.target_guid and a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and a.policy_guid = p.policy_guid and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' UNION --Group level policy violations count select DISTINCT f.composite_target_guid as group_target_guid, --To facilitate the member target type filter to include group level target also, --composite target type is passed as member target type for group level policies. f.composite_target_type as member_target_type, f.composite_target_name as target_name, p.violation_level as violation_level, c.category_name as category, a.non_exempt_violations_logged as violation_count, rt.target_guid as target_guid, p.policy_guid from mgmt_flat_target_memberships f, mgmt_policy_assoc_eval_summ a, mgmt_category_map c, mgmt_policies p, mgmt_targets rt where f.composite_target_guid = a.target_guid and a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and a.policy_guid = p.policy_guid and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' ) GROUP BY group_target_guid,violation_level,target_guid,rollup(member_target_type),rollup(category)'; MGMT_METRIC.CREATE_METRIC( p_target_type=>MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_metric_name=>'GROUP_VIOLATIONS', p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_metric_label=>'Group Violations', p_is_repository=>MGMT_GLOBAL.G_TRUE, p_source_type=>MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source=>l_source_query, p_metric_column_list=>l_metricColList, p_metric_label_nlsid=>'oracle_emrep_pol_grp_viol', p_description=>'Measures the number of group policy violations over time.', p_description_nlsid=>'oracle_emrep_pol_grp_viol_descr'); COMMIT; -- GROUP_TARGET_COMPLIANCE -- Create metric l_metricColList := MGMT_METRIC_COLUMN_ARRAY( l_mco_group_target_guid, l_mco_member_target_guid, l_mco_member_target_type, l_mco_category, l_mco_compliance_score ); l_source_query := 'select group_target_guid,member_target_guid,nvl(member_target_type,''ALL'') as member_target_type, nvl(category,''ALL'') as category,ROUND(avg(compliance_score)) as compliance_score,target_guid from ( --Member targets level compliance score select DISTINCT f.composite_target_guid as group_target_guid, a.target_guid as member_target_guid, f.member_target_type as member_target_type, f.member_target_name as target_name, c.category_name as category, a.compliance_score as compliance_score, rt.target_guid as target_guid, a.policy_guid from mgmt_flat_target_memberships f, mgmt_policy_assoc_eval_summ a, mgmt_category_map c, mgmt_targets rt where f.member_target_guid = a.target_guid and a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' UNION --Group level policy compliance score data select DISTINCT f.composite_target_guid as group_target_guid, a.target_guid as member_target_guid, --To facilitate the member target type filter to include group level target also, --composite target type is passed as member target type for group level policies. f.composite_target_type as member_target_type, f.composite_target_name as target_name, c.category_name as category, a.compliance_score as compliance_score, rt.target_guid as target_guid, a.policy_guid from mgmt_flat_target_memberships f, mgmt_policy_assoc_eval_summ a, mgmt_category_map c, mgmt_targets rt where f.composite_target_guid = a.target_guid and a.policy_guid = c.object_guid and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' ) GROUP BY group_target_guid,member_target_guid,target_guid,rollup(member_target_type),rollup(category)'; MGMT_METRIC.CREATE_METRIC( p_target_type=>MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_metric_name=>'GROUP_TARGET_COMPLIANCE', p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER, p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_metric_label=>'Group Target Compliance', p_is_repository=>MGMT_GLOBAL.G_TRUE, p_source_type=>MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_source=>l_source_query, p_metric_column_list=>l_metricColList, p_metric_label_nlsid=>'oracle_emrep_pol_grp_targ_comp', p_description=>'Measures the target policy compliance in a group over time.', p_description_nlsid=>'oracle_emrep_pol_grp_targ_comp_descr'); COMMIT; END; / DECLARE l_policy_trend_intvl NUMBER := 360; l_emrep_tgt_exists NUMBER := 1; l_emrep_tgt_name mgmt_targets.target_name%TYPE; l_emrep_typ_ver mgmt_targets.type_meta_ver%TYPE; BEGIN BEGIN SELECT target_name, type_meta_ver INTO l_emrep_tgt_name, l_emrep_typ_ver FROM MGMT_TARGETS WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN l_emrep_tgt_exists := 0; END; IF ( (l_emrep_tgt_exists = 1) AND (l_emrep_typ_ver = MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER) ) THEN mgmt_collection.start_collection( p_target_type=>MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_target_name=>l_emrep_tgt_name, p_metrics_list=>MGMT_SHORT_STRING_ARRAY('TARGET_COMPLIANCE', 'TARGET_VIOLATIONS', 'GROUP_COMPLIANCE', 'GROUP_VIOLATIONS', 'GROUP_TARGET_COMPLIANCE'), p_coll_schedule=>mgmt_coll_schedule_obj.interval_Schedule( p_interval=>l_policy_trend_intvl, p_start_time=>sysdate, p_end_time=>null) ); ELSE DBMS_OUTPUT.PUT_LINE('**** INFO : Trending collection not started emrep tgt = ' || l_emrep_tgt_exists || ' Version = ' || l_emrep_typ_ver); END IF; END; / show errors; -- Set is_transposed to 1 for target_type host and metric_name UDM -- Set is_transposed to 1 for target_type oracle_database and metric_name SQLUDM -- For all other case set it to 0 BEGIN UPDATE mgmt_metrics SET is_transposed = 1 WHERE is_transposed IS NULL AND ((target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND metric_name = 'UDM') OR (target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE AND metric_name = 'SQLUDM') ); -- set 0 for all other case UPDATE mgmt_metrics SET is_transposed = 0 WHERE is_transposed IS NULL; END; / show errors; BEGIN -- Register a target type addition callback mgmt_target.add_tgttype_addition_callback( p_callback_name => 'em_metric.handle_tgttype_addition'); COMMIT; END; /