Rem drv:
Rem
Rem $Header: esm_ui_metrics.sql 08-sep-2005.23:39:33 dkjain Exp $
Rem
Rem esm_ui_metrics.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem esm_ui_metrics.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem dkjain 09/08/05 - dkjain_bug-4530678
Rem dkjain 07/28/05 - Created
Rem
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
set serveroutput on
--Creates the metrics needed for the trend charts used by ESA UI
--To be created during repository creation. The metrics are created
--and the default collection is set.
DECLARE
l_metricColList MGMT_METRIC_COLUMN_ARRAY;
l_mco_object_target_guid MGMT_METRIC_COLUMN_OBJ;
l_mco_compliance_score MGMT_METRIC_COLUMN_OBJ;
l_mco_violation_level MGMT_METRIC_COLUMN_OBJ;
l_mco_new_violations MGMT_METRIC_COLUMN_OBJ;
l_mco_cleared_violations MGMT_METRIC_COLUMN_OBJ;
l_mco_group_target_guid MGMT_METRIC_COLUMN_OBJ;
l_mco_member_target_type MGMT_METRIC_COLUMN_OBJ;
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;
l_source_query VARCHAR2(4000);
l_policy_trend_intvl NUMBER := 360;
TYPE METRIC_LIST is table of VARCHAR2(50);
l_metricName VARCHAR2(50);
l_metricNameList METRIC_LIST;
BEGIN
-- Deleting the metric metadata
l_metricNameList := METRIC_LIST('TARGET_SECURITY_COMPLIANCE','GROUP_SECURITY_COMPLIANCE',
'TARGET_SECURITY_NEW_VIOLATIONS','TARGET_SECURITY_CLEARED_VIOLATIONS',
'GROUP_SECURITY_NEW_VIOLATIONS','GROUP_SECURITY_CLEARED_VIOLATIONS');
FOR i IN 1..l_metricNameList.last LOOP
l_metricName := l_metricNameList(i);
MGMT_METRIC.DELETE_METRIC_METADATA(
P_TARGET_TYPE => 'oracle_emrep',
P_METRIC_NAME => l_metricName,
P_TYPE_META_VER => NULL
);
END LOOP;
-- 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_nlsid=>'oracle_emrep_object_target_guid',
p_description_nlsid=>'oracle_emrep_object_target_guid_desc');
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_nlsid=>'oracle_emrep_compliance_score',
p_description_nlsid=>'oracle_emrep_compliance_score_desc');
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_nlsid=>'oracle_emrep_violation_level',
p_description_nlsid=>'oracle_emrep_violation_level_desc');
l_mco_new_violations := MGMT_METRIC_COLUMN_OBJ.NEW(
p_column_name=>'NEW_VIOLATIONS',
p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
p_column_label_nlsid=>'oracle_emrep_new_violations',
p_description_nlsid=>'oracle_emrep_new_violations_desc');
l_mco_cleared_violations := MGMT_METRIC_COLUMN_OBJ.NEW(
p_column_name=>'CLEARED_VIOLATIONS',
p_column_type=>MGMT_GLOBAL.G_METRIC_TYPE_NUMBER,
p_is_key=>MGMT_GLOBAL.G_METRIC_KEY_FALSE,
p_column_label_nlsid=>'oracle_emrep_cleared_violations',
p_description_nlsid=>'oracle_emrep_cleared_violations_desc');
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_nlsid=>'oracle_emrep_grp_tgt_guid',
p_description_nlsid=>'oracle_emrep_grp_tgt_guid_desc');
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_nlsid=>'oracle_emrep_mem_tgt_type',
p_description_nlsid=>'oracle_emrep_mem_tgt_type_desc');
--TARGET_SECURITY_COMPLIANCE
l_source_query :=
'select
avg(a.compliance_score) as compliance_score,
nvl(a.max_violation_level, 0) as violation_level,
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 || ''' and
a.max_violation_level in (''' || MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL
|| ''', ''' || MGMT_GLOBAL.G_SEVERITY_WARNING
|| ''', ''' || MGMT_GLOBAL.G_SEVERITY_CRITICAL || ''') and
c.category_name = '''|| MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY ||'''
group by rt.target_guid, rollup(a.max_violation_level)';
-- Create metric
l_metricColList :=
MGMT_METRIC_COLUMN_ARRAY( l_mco_violation_level,
l_mco_compliance_score );
MGMT_METRIC.CREATE_METRIC( p_target_type=>'oracle_emrep',
p_metric_name=>'TARGET_SECURITY_COMPLIANCE',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
p_metric_label=>'Target Security 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_tgt_sec_comp',
p_description=>'Measures the target security policy compliance over time.',
p_description_nlsid=>'oracle_emrep_tgt_sec_comp_descr');
COMMIT;
--GROUP_SECURITY_COMPLIANCE
l_metricColList :=
MGMT_METRIC_COLUMN_ARRAY( l_mco_group_target_guid,
l_mco_violation_level,
l_mco_compliance_score );
l_source_query :=
'select f.source_target_guid as group_target_guid,
nvl(a.max_violation_level, 0) as violation_level,
avg(a.compliance_score) as compliance_score,
rt.target_guid as target_guid
from
mgmt_flat_target_assoc f,
mgmt_policy_assoc_eval_summ a,
mgmt_category_map c,
mgmt_targets rt
where
f.assoc_target_guid = a.target_guid and
f.is_membership = 1 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 || ''' and
a.max_violation_level in (''' || MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL
|| ''', ''' || MGMT_GLOBAL.G_SEVERITY_WARNING
|| ''', ''' || MGMT_GLOBAL.G_SEVERITY_CRITICAL || ''') and
c.category_name = '''|| MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY ||'''
group by f.source_target_guid, rt.target_guid, rollup(a.max_violation_level)';
MGMT_METRIC.CREATE_METRIC( p_target_type=>'oracle_emrep',
p_metric_name=>'GROUP_SECURITY_COMPLIANCE',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
p_metric_label=>'Group Security 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_grp_sec_comp',
p_description=>'Measures the target security policy compliance of a group over time.',
p_description_nlsid=>'oracle_emrep_grp_sec_comp_descr');
COMMIT;
--TARGET_SECURITY_NEW_VIOLATIONS
l_source_query :=
'select
nvl(p.violation_level, 0) as violation_level,
SUM(DECODE(mv.violation_level,
18, 1,
20, 1,
25, 1,
0)) as new_violations,
rt.target_guid
from
mgmt_policies p,
mgmt_violations mv,
mgmt_category_map mc,
mgmt_targets rt
where
p.policy_guid = mv.policy_guid and
p.policy_type = 2 and
p.policy_guid = mc.object_guid and
p.violation_level in (18, 20, 25) and
mv.violation_level in (18, 20, 25) and
mv.collection_timestamp >= sysdate - 6/24 and
mc.category_name = '''|| MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY ||''' and
mc.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || '''
group by
rt.target_guid,
p.violation_level';
-- Create metric
l_metricColList :=
MGMT_METRIC_COLUMN_ARRAY( l_mco_violation_level,
l_mco_new_violations );
MGMT_METRIC.CREATE_METRIC( p_target_type=>'oracle_emrep',
p_metric_name=>'TARGET_SECURITY_NEW_VIOLATIONS',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
p_metric_label=>'New Target Security 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_tgt_sec_viol_new',
p_description=>'Computes the number of new target security policy violations over time.',
p_description_nlsid=>'oracle_emrep_tgt_sec_viol_new_descr');
COMMIT;
--TARGET_SECURITY_CLEARED_VIOLATIONS
l_source_query :=
'select
nvl(p.violation_level, 0) as violation_level,
SUM(DECODE(mv.violation_level,15,1,0)) as cleared_violations,
rt.target_guid
from
mgmt_policies p,
mgmt_violations mv,
mgmt_category_map mc,
mgmt_targets rt
where
p.policy_guid = mv.policy_guid and
p.policy_type = 2 and
p.policy_guid = mc.object_guid and
p.violation_level in (18, 20, 25) and
mv.violation_level = 15 and
mv.collection_timestamp >= sysdate - 6/24 and
mc.category_name = '''|| MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY ||''' and
mc.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || '''
group by
rt.target_guid,
p.violation_level';
-- Create metric
l_metricColList :=
MGMT_METRIC_COLUMN_ARRAY( l_mco_violation_level,
l_mco_cleared_violations );
MGMT_METRIC.CREATE_METRIC( p_target_type=>'oracle_emrep',
p_metric_name=>'TARGET_SECURITY_CLEARED_VIOLATIONS',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
p_metric_label=>'Cleared Target Security 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_tgt_sec_viol_clrd',
p_description=>'Computes the number of target security policy violations cleared over time.',
p_description_nlsid=>'oracle_emrep_tgt_sec_viol_clrd_descr');
COMMIT;
--GROUP_SECURITY_NEW_VIOLATIONS
l_source_query :=
'select
ft.source_target_guid as group_target_guid,
nvl(p.violation_level, 0) as violation_level,
SUM(DECODE(mv.violation_level,
18, 1,
20, 1,
25, 1,
0)) as new_violations,
rt.target_guid
from
mgmt_policies p,
mgmt_violations mv,
mgmt_category_map mc,
mgmt_targets rt,
mgmt_flat_target_assoc ft
where
p.policy_guid = mv.policy_guid and
p.policy_type = 2 and
p.policy_guid = mc.object_guid and
p.violation_level in (18, 20, 25) and
mv.violation_level in (18, 20, 25) and
mv.collection_timestamp >= sysdate - 6/24 and
mc.category_name = '''|| MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY ||''' and
mc.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' and
ft.assoc_target_guid = mv.target_guid and
ft.is_membership = 1
group by
ft.source_target_guid,
rt.target_guid,
p.violation_level';
-- Create metric
l_metricColList :=
MGMT_METRIC_COLUMN_ARRAY( l_mco_group_target_guid,
l_mco_violation_level,
l_mco_new_violations );
MGMT_METRIC.CREATE_METRIC( p_target_type=>'oracle_emrep',
p_metric_name=>'GROUP_SECURITY_NEW_VIOLATIONS',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
p_metric_label=>'New Group Security 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_grp_sec_viol_new',
p_description=>'Computes the number of new security policy violations for a group over time.',
p_description_nlsid=>'oracle_emrep_grp_sec_viol_new_descr');
COMMIT;
--GROUP_SECURITY_CLEARED_VIOLATIONS
l_source_query :=
'select
ft.source_target_guid as group_target_guid,
nvl(p.violation_level, 0) as violation_level,
SUM(DECODE(mv.violation_level,15, 1,0)) as cleared_violations,
rt.target_guid
from
mgmt_policies p,
mgmt_violations mv,
mgmt_category_map mc,
mgmt_targets rt,
mgmt_flat_target_assoc ft
where
p.policy_guid = mv.policy_guid and
p.policy_type = 2 and
p.policy_guid = mc.object_guid and
p.violation_level in (18, 20, 25) and
mv.violation_level = 15 and
mv.collection_timestamp >= sysdate - 6/24 and
mc.category_name = '''|| MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY ||''' and
mc.class_name = ''' || MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL || ''' and
rt.target_type = ''' || MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE || ''' and
ft.assoc_target_guid = mv.target_guid and
ft.is_membership = 1
group by
ft.source_target_guid,
rt.target_guid,
p.violation_level';
-- Create metric
l_metricColList :=
MGMT_METRIC_COLUMN_ARRAY( l_mco_group_target_guid,
l_mco_violation_level,
l_mco_cleared_violations );
MGMT_METRIC.CREATE_METRIC( p_target_type=>'oracle_emrep',
p_metric_name=>'GROUP_SECURITY_CLEARED_VIOLATIONS',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metric_type=>MGMT_GLOBAL.G_METRIC_TYPE_TABLE,
p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT,
p_metric_label=>'Cleared Group Security 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_grp_sec_viol_clrd',
p_description=>'Computes the number of security policy violations cleared for a group over time.',
p_description_nlsid=>'oracle_emrep_grp_sec_viol_clrd_descr');
COMMIT;
--ADD DEFAULT COLLECTION
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_SECURITY_COMPLIANCE',
'GROUP_SECURITY_COMPLIANCE',
'TARGET_SECURITY_NEW_VIOLATIONS',
'TARGET_SECURITY_CLEARED_VIOLATIONS',
'GROUP_SECURITY_NEW_VIOLATIONS',
'GROUP_SECURITY_CLEARED_VIOLATIONS'),
p_coll_schedule=>mgmt_coll_schedule_obj.interval_Schedule(p_interval=>l_policy_trend_intvl,
p_start_time=>sysdate,
p_end_time=>null)
);
DBMS_OUTPUT.PUT_LINE('**esm_ui_metrics start_collection**');
ELSE
mgmt_collection.add_default_collection( p_target_type=>'oracle_emrep',
p_coll_name=>'Compliance and Violation Flux Trend for Security Policies',
p_type_meta_ver=>MGMT_GLOBAL.G_MGMT_SYS_TYPE_META_VER,
p_metrics_list=>MGMT_SHORT_STRING_ARRAY('TARGET_SECURITY_COMPLIANCE',
'GROUP_SECURITY_COMPLIANCE',
'TARGET_SECURITY_NEW_VIOLATIONS',
'TARGET_SECURITY_CLEARED_VIOLATIONS',
'GROUP_SECURITY_NEW_VIOLATIONS',
'GROUP_SECURITY_CLEARED_VIOLATIONS'),
p_coll_schedule=>mgmt_coll_schedule_obj.interval_Schedule(p_interval=>l_policy_trend_intvl,
p_start_time=>sysdate,
p_end_time=>null)
);
DBMS_OUTPUT.PUT_LINE('**esm_ui_metrics add_default_collection**');
END IF;
-- ADD DEFAULT COLLECTION FINISH
COMMIT;
END;
/
show errors;
set serveroutput off