Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\v102020\metric\metric

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\v102020\metric\metric_init.sql

Rem
Rem $Header: metric_init.sql 27-aug-2007.11:15:39 rrawat Exp $
Rem
Rem metric_init.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      metric_init.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
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    neearora    09/08/05 - call register removed 
Rem    niramach    08/18/05 - correct the target viol count and group viol 
Rem                           count violation levels. 
Rem    niramach    08/05/05 - Fix bug 4523155,4523155 and 4541386.
Rem    niramach    08/03/05 - niramach_bugfix-4527462
Rem    niramach    08/02/05 - MOved the latest one frm v102010 to v102020.
Rem                         - Delete the already created metrics before creating
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    asaraswa    05/27/05 - fixing group_target_compliance 
Rem    asaraswa    05/23/05 - fixing group_target_compliance metric for bug 
Rem                           4331955 
Rem    rpinnama    05/16/05 - rpinnama_bug-4331862_main
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_mco_bucket1 MGMT_METRIC_COLUMN_OBJ;
    l_mco_bucket2 MGMT_METRIC_COLUMN_OBJ;
    l_mco_bucket3 MGMT_METRIC_COLUMN_OBJ;
    l_mco_bucket4 MGMT_METRIC_COLUMN_OBJ;
    l_mco_bucket5 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');

l_mco_bucket1 := MGMT_METRIC_COLUMN_OBJ.NEW( 
                                            p_column_name=>'BUCKET1',
                                            p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                            p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
                                            p_column_label=>'Bucket 1',
                                            p_description=>'The number of targets with scores in the 1-20 range.',
                                            p_column_label_nlsid=>'oracle_emrep_comp_range_1',
                                            p_description_nlsid=>'oracle_emrep_comp_range_1_descr');

l_mco_bucket2 := MGMT_METRIC_COLUMN_OBJ.NEW( 
                                            p_column_name=>'BUCKET2',
                                            p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                            p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
                                            p_column_label=>'Bucket 2',
                                            p_description=>'The number of targets with scores in the 21-40 range.',
                                            p_column_label_nlsid=>'oracle_emrep_comp_range_2',
                                            p_description_nlsid=>'oracle_emrep_comp_range_2_descr');

l_mco_bucket3 := MGMT_METRIC_COLUMN_OBJ.NEW( 
                                            p_column_name=>'BUCKET3',
                                            p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                            p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
                                            p_column_label=>'Bucket 3',
                                            p_description=>'The number of targets with scores in the 41-60 range.',
                                            p_column_label_nlsid=>'oracle_emrep_comp_range_3',
                                            p_description_nlsid=>'oracle_emrep_comp_range_3_descr');

l_mco_bucket4 := MGMT_METRIC_COLUMN_OBJ.NEW( 
                                            p_column_name=>'BUCKET4',
                                            p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                            p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
                                            p_column_label=>'Bucket 4',
                                            p_description=>'The number of targets with scores in the 61-80 range.',
                                            p_column_label_nlsid=>'oracle_emrep_comp_range_4',
                                            p_description_nlsid=>'oracle_emrep_comp_range_4_descr');

l_mco_bucket5 := MGMT_METRIC_COLUMN_OBJ.NEW( 
                                            p_column_name=>'BUCKET5',
                                            p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
                                            p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
                                            p_column_label=>'Bucket 5',
                                            p_description=>'The number of targets with scores in the 81-100 range.',
                                            p_column_label_nlsid=>'oracle_emrep_comp_range_5',
                                            p_description_nlsid=>'oracle_emrep_comp_range_5_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,
  MGMT_POLICIES p
 where
  a.policy_guid = c.object_guid and  
  p.policy_guid = a.policy_guid and
  c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
  p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' 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 );
    
    BEGIN                              
      --Delete the metric                                   
    MGMT_METRIC.DELETE_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);           
    EXCEPTION  
        WHEN OTHERS THEN 
        NULL;
    END;

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_usage_type=>MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
                               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_targets rt,
  MGMT_POLICIES p
 where
  a.policy_guid = c.object_guid and
  p.policy_guid = a.policy_guid and
  p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' and 
  c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and    
  rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || '''
 group by a.target_guid, p.violation_level, rt.target_guid, rollup(category_name)';

BEGIN                              
      --Delete the metric                                   
    MGMT_METRIC.DELETE_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);           
    EXCEPTION  
        WHEN OTHERS THEN 
        NULL;
    END;  
    
    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_usage_type=>MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
                               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 ct.target_guid as group_target_guid,
        mt.target_type as member_target_type,
        mt.target_name as target_name,
        c.category_name as category,
        e.compliance_score as compliance_score,
        rt.target_guid as target_guid,
        e.policy_guid
    from
        mgmt_flat_target_assoc a,
        mgmt_policy_assoc_eval_summ e,
        mgmt_category_map c,
        mgmt_targets rt,
        mgmt_targets mt,
        mgmt_targets ct,
        mgmt_policies p
    where
        a.is_membership = 1 and
        ct.target_guid = a.source_target_guid and
        mt.target_guid = a.assoc_target_guid and
        e.target_guid =  mt.target_guid and
        e.policy_guid = c.object_guid and
        p.policy_guid = e.policy_guid and
        p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' and         
        c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
        rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || '''
    UNION ALL
    --Group level compliance score    
    select t.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.
        t.target_type as member_target_type,
        t.target_name as target_name,
        c.category_name as category,
        e.compliance_score as compliance_score,
        rt.target_guid as target_guid,
        e.policy_guid
    from
        mgmt_targets t,
        mgmt_type_properties tp,
        mgmt_policy_assoc_eval_summ e,
        mgmt_category_map c,
        mgmt_targets rt,
        mgmt_policies p
    where
        t.target_guid = e.target_guid and
        e.policy_guid = c.object_guid and
        p.policy_guid = e.policy_guid and
        t.target_type = tp.target_type and        
        tp.property_name = '''|| MGMT_GLOBAL.G_IS_AGGREGATE_PROP || ''' and
        tp.property_value = ''1'' and
        p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' 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)';

BEGIN                              
      --Delete the metric                                   
    MGMT_METRIC.DELETE_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);           
    EXCEPTION  
        WHEN OTHERS THEN 
        NULL;
    END;

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_usage_type=>MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
                               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 ct.target_guid as group_target_guid,
        mt.target_type as member_target_type,
        mt.target_name as target_name,
        p.violation_level as violation_level,
        c.category_name as category,
        e.non_exempt_violations_logged as violation_count,
        rt.target_guid as target_guid,
        e.policy_guid
    from
        mgmt_flat_target_assoc a,
        mgmt_policy_assoc_eval_summ e,
        mgmt_category_map c,
        mgmt_targets rt,
        mgmt_targets mt,
        mgmt_targets ct,
        mgmt_policies p
    where
        a.is_membership = 1 and
        ct.target_guid = a.source_target_guid and
        mt.target_guid = a.assoc_target_guid and
        e.target_guid =  mt.target_guid and        
        e.policy_guid = c.object_guid and
        p.policy_guid = e.policy_guid and        
        p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' and 
        c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
        rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || '''
    UNION ALL
    --Group level policy violations count
    select t.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.
        t.target_type as member_target_type,
        t.target_name as target_name,
        p.violation_level as violation_level,
        c.category_name as category,
        e.non_exempt_violations_logged as violation_count,
        rt.target_guid as target_guid,
        e.policy_guid
    from
        mgmt_targets t,
        mgmt_type_properties tp,
        mgmt_policy_assoc_eval_summ e,
        mgmt_category_map c,
        mgmt_targets rt,
        mgmt_policies p
    where
        t.target_guid = e.target_guid and
        e.policy_guid = c.object_guid and
        p.policy_guid = e.policy_guid and        
        t.target_type = tp.target_type and
        tp.property_name = '''|| MGMT_GLOBAL.G_IS_AGGREGATE_PROP || ''' and
        tp.property_value = ''1'' and
        p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' 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,violation_level,target_guid,rollup(member_target_type),rollup(category)';

BEGIN                              
      --Delete the metric                                   
    MGMT_METRIC.DELETE_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);           
    EXCEPTION  
        WHEN OTHERS THEN 
        NULL;
    END;                   
                            
    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_usage_type=>MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
                               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_type,
                                  l_mco_category,
                                  l_mco_bucket1,
                                  l_mco_bucket2,
                                  l_mco_bucket3,
                                  l_mco_bucket4,
                                  l_mco_bucket5 );

l_source_query :=
    'with buckets as
     (
         select
           avgs.target_guid,
           avgs.target_type,
           avgs.target_name,
           NVL(avgs.category_name, ''ALL'') as category,
           case when(width_bucket(avgs.compliance_score, 0, 101, 5)=1) then 1 else 0 end as b1,
           case when(width_bucket(avgs.compliance_score, 0, 101, 5)=2) then 1 else 0 end as b2,
           case when(width_bucket(avgs.compliance_score, 0, 101, 5)=3) then 1 else 0 end as b3,
           case when(width_bucket(avgs.compliance_score, 0, 101, 5)=4) then 1 else 0 end as b4,
           case when(width_bucket(avgs.compliance_score, 0, 101, 5)=5) then 1 else 0 end as b5
         from
           (
             SELECT
               t.target_guid,
               t.target_type,
               t.target_name,
               c.category_name,
               round(AVG(a.compliance_score)) as compliance_score
              from
                sysman.mgmt_targets t,
                sysman.mgmt_category_map c,
                sysman.mgmt_policy_assoc_eval_summ a,
                sysman.mgmt_policies p
              where
                t.target_guid = a.target_guid
                and a.policy_guid = c.object_guid
                and p.policy_guid = a.policy_guid 
                and p.policy_type = '  || MGMT_GLOBAL.G_TYPE_POLICY || ' 
                and c.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || '''
              group by t.target_name, t.target_guid, t.target_type, rollup(c.category_name)
           ) avgs
      )
    SELECT
      q.group_target_guid,
      rt.target_guid,
      NVL(q.member_target_type, ''ALL'') as member_target_type,
      q.category as category,
      sum(q.bucket1) as bucket1,
      sum(q.bucket2) as bucket2,
      sum(q.bucket3) as bucket3,
      sum(q.bucket4) as bucket4,
      sum(q.bucket5) as bucket5
    FROM     
      (
        (
          SELECT
            a.source_target_guid as group_target_guid,
            x.target_type as member_target_type,
            x.category as category,
            x.b1 as bucket1,
            x.b2 as bucket2,
            x.b3 as bucket3,
            x.b4 as bucket4,
            x.b5 as bucket5
          FROM
            sysman.mgmt_flat_target_assoc a,
            buckets x
          WHERE
            a.is_membership = 1
            and (a.assoc_target_guid = x.target_guid)
        )
        UNION ALL
        (
          SELECT
            x.target_guid as group_target_guid,
            x.target_type as member_target_type,
            x.category as category,
            x.b1 as bucket1,
            x.b2 as bucket2,
            x.b3 as bucket3,
            x.b4 as bucket4,
            x.b5 as bucket5
          FROM
            buckets x,
            mgmt_type_properties p
          WHERE
            x.target_type = p.target_type
            and p.property_name = ''' || MGMT_GLOBAL.G_IS_AGGREGATE_PROP || '''
            and p.property_value = ''1''
        )
      ) q,
      sysman.mgmt_targets rt
    WHERE
      rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || '''
    GROUP BY q.group_target_guid, rt.target_guid, rollup(q.member_target_type), q.category';

BEGIN                              
      --Delete the metric                                   
    MGMT_METRIC.DELETE_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);           
    EXCEPTION  
        WHEN OTHERS THEN 
        NULL;
    END;

-- Create metric
    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_usage_type=>MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
                               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;

BEGIN
  -- Register a callback when key-values of a metric is permanently deleted
  -- Eval order is set as 5 in order to facilitate registration of other callback with higher priority
  EM_METRIC.add_metric_keyval_callback
    (
      p_callback_name         => 'EM_METRIC.handle_metric_keyval_deletion',
      p_callback_eval_order   => 5
    );
   COMMIT;
END;
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de