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

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

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

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de