Rem drv: Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_poleval_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/2 2009/06/12 04:40:23 jsadras Exp $ Rem Rem collections_poleval_pkgbody.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem collections_poleval_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem jsadras 06/11/09 - Bug:8592056, fix noop Rem jsadras 11/07/08 - perf fixes Rem dgiaimo 05/04/07 - Fixing bug 6030187 Rem dgiaimo 05/18/07 - Backport dgiaimo_bug-6030187 from main Rem sthiruna 07/28/06 - Copying 10.2.0.3 Config Standard changes from Rem EMCORE_MAIN_LINUX Rem jsadras 02/02/06 - Enhance evaluate_policy to accept metric results Rem jsadras 07/17/06 - Backport jsadras_bug-5002887 from main Rem jsadras 07/02/06 - Bug:5046257, honour violation level of policy Rem jsadras 07/13/06 - Backport jsadras_bug-5046257 from main Rem niramach 01/25/06 - Update the get_policies for cs policy Rem evaluation. Rem aragarwa 02/02/06 - Change in config std table names. Rem niramach 01/09/06 - 10.3 Work start: Add p_config_std_guid to Rem evaluate_policy to evaluate policies only part Rem of the given config standard. Rem jsadras 08/29/05 - policy evaluation timestamp Rem jsadras 08/22/05 - Bug:4341395, get_policies Rem jsadras 08/11/05 - Bug:4541416 , collection name Rem gsbhatia 07/01/05 - New repmgr header impl Rem jsadras 05/19/05 - security fixes Rem niramach 03/15/05 - Correct typo error in get_set_composite_key Rem jsadras 02/24/05 - get_set_composite_keys Rem niramach 02/04/05 - exempt->suppress conversions. Rem jsadras 01/05/05 - remove clear_all_violations Rem jsadras 01/05/05 - increase length Rem jsadras 12/29/04 - clear message and context Rem jsadras 12/22/04 - result_key_array length Rem jsadras 12/16/04 - target list in policy Rem rpinnama 12/09/04 - Fix metric thresholds for repository collections Rem rpinnama 12/02/04 - Use object type constants Rem jsadras 11/24/04 - bug_4012088 Rem jsadras 11/10/04 - missing_keys Rem jsadras 10/19/04 - repo_timing Rem jsadras 10/13/04 - mgmt_integer_table Rem jsadras 10/07/04 - close_cursor Rem jsadras 10/06/04 - short_string_table Rem jsadras 10/01/04 - format Rem jsadras 09/30/04 - violations Rem jsadras 09/28/04 - format constants Rem jsadras 09/24/04 - message_params: Rem jsadras 09/23/04 - violations_counter Rem jsadras 09/22/04 - get_new_violation Rem jsadras 09/21/04 - format_message Rem jsadras 09/16/04 - debug Rem jsadras 09/15/04 - keyvalue Rem jsadras 08/25/04 - eval_condition_text Rem jsadras 08/09/04 - Rem jsadras 07/24/04 - jsadras_repocollect1 Rem jsadras 07/06/04 - Created Rem CREATE OR REPLACE PACKAGE BODY em_policy_eval as G_PARAM_COLUMN CONSTANT NUMBER := 1 ; G_METRIC_COLUMN CONSTANT NUMBER := 2 ; DATE_1900_01_01 CONSTANT DATE := TO_DATE('1900-01-01', 'YYYY-MM-DD'); -- Cursor to retrieve policy information TYPE g_policy_cur_type IS REF CURSOR RETURN EM_POLICY_EVAL_REC; --**************************************************** -- Formatting code copied from emd_collections package -- with minor modifications --**************************************************** -- PURPOSE -- Function to format a default message to add to a severity when -- no message is provided in the associated threshold. See -- FORMAT_SEVERITY_MESSAGE for more details. -- -- PARAMETERS -- P_METRIC_NAME - name of the metric -- P_METRIC_COLUMN - name of the metric column -- P_METRIC_LABEL - label of the metric -- P_COLUMN_LABEL - label of the metric column -- P_KEY_VALUE - name of the key column value -- P_CRIT_THRESHOLD - the critical threshold set for the policy -- P_WARN_THRESHOLD - the warning threshold set for the policy -- P_INFO_THRESHOLD - the info threshold set for the policy -- P_VALUE - the value of the metric -- P_TYPE - the type of the severity -- 0 - CLEAR -- 1 - WARNING -- 2 - CRITICAL -- 3 - INFORMATION FUNCTION format_dflt_severity_message(p_metric_label IN VARCHAR2, p_column_label IN VARCHAR2, p_key_value IN VARCHAR2, p_crit_threshold IN VARCHAR2, p_warn_threshold IN VARCHAR2, p_info_threshold IN VARCHAR2, p_value IN VARCHAR2, p_violation_level IN NUMBER) RETURN VARCHAR2 IS l_message VARCHAR2(4000); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('format_dflt_severity_message:Enter',G_MODULE_NAME) ; END IF ; l_message := nvl(p_column_label,p_metric_label) ; IF p_key_value IS NOT NULL AND p_key_value <> ' ' THEN l_message := l_message || ' for ' || p_key_value; END IF; IF p_violation_level IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,0) THEN -- not changing warning to info here since some one may depend in it l_message := l_message || ' is below the warning threshold (' || p_info_threshold ||'). Current value: ' || p_value; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL THEN l_message := l_message || ' exceeded the informational threshold (' || p_info_threshold ||'). Current value: ' || p_value; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING THEN l_message := l_message || ' exceeded the warning threshold (' || p_warn_threshold ||'). Current value: ' || p_value; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN l_message := l_message || ' exceeded the critical threshold (' || p_crit_threshold ||'). Current value: ' || p_value; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('format_dflt_severity_message:Exit Message='|| l_message,G_MODULE_NAME) ; END IF ; RETURN l_message; END format_dflt_severity_message; -- -- PURPOSE -- Returns an URL encoding string -- The only characters that are encoded are '&', '%', and ';' -- Used by FORMAT_SEVERITY_MESSAGE -- FUNCTION url_encode_string(p_string IN VARCHAR2) RETURN VARCHAR2 IS l_mod_string VARCHAR2(4000) := p_string; BEGIN IF (p_string IS NULL) THEN RETURN NULL; END IF; IF (INSTR(l_mod_string, '&') != 0) THEN l_mod_string := REPLACE(l_mod_string, '&', '%26'); END IF; IF (INSTR(l_mod_string, '%') != 0) THEN l_mod_string := REPLACE(l_mod_string, '%', '%25'); END IF; IF (INSTR(l_mod_string, ';') != 0) THEN l_mod_string := REPLACE(l_mod_string, ';', '%3b'); END IF; RETURN l_mod_string; END url_encode_string; -- -- PURPOSE -- Appends a value to nls_params -- Used by FORMAT_SEVERITY_MESSAGE -- PROCEDURE append_message_param(p_nls_params IN OUT VARCHAR2, p_param IN VARCHAR2) IS BEGIN IF (p_nls_params IS NULL) THEN p_nls_params := p_param; ELSE p_nls_params := p_nls_params || '&' || p_param; END IF; END append_message_param; -- PURPOSE -- Appends nlsid to nls_params -- Used by FORMAT_SEVERITY_MESSAGE PROCEDURE append_message_param_nlsid(p_nls_params IN OUT VARCHAR2, p_nlsid IN VARCHAR2) IS BEGIN IF (p_nlsid IS NULL) THEN RETURN; END IF; IF (p_nls_params IS NULL) THEN p_nls_params := p_nlsid; ELSE p_nls_params := p_nls_params || ';' || p_nlsid; END IF; END append_message_param_nlsid; -- -- PURPOSE -- Builds the message for a severity. This implementation is based on -- how agent formats the message based on a templated message specified in -- the metadata file. See nmecc.c for agent side implementation. -- PROCEDURE format_severity_message (p_target_name IN VARCHAR2, p_metric_info IN EM_METRIC_EVAL.METRIC_INFO_REC, p_condition_type IN NUMBER, p_condition IN VARCHAR2, p_msg_template IN VARCHAR2, p_key_value IN VARCHAR2, p_num_occurences IN NUMBER, p_value IN VARCHAR2, p_violation_level IN NUMBER, p_metric_value IN mgmt_metric_value_obj, p_param_values IN mgmt_policy_param_val_array, p_message OUT VARCHAR2, p_nls_params OUT VARCHAR2 ) IS l_idx1 NUMBER := 0; l_idx2 NUMBER := 0; l_start_pos NUMBER := 1; l_keyword VARCHAR2(64); l_value VARCHAR2(256); l_position NUMBER ; l_nlsid VARCHAR2(64); l_threshold VARCHAR2(256); l_column_label mgmt_metrics.column_label%type ; l_column_nlsid mgmt_metrics.column_label_nlsid%type ; l_col_index NUMBER := 0 ; FUNCTION param_value(p_param_name IN VARCHAR2, p_param_values IN mgmt_policy_param_val_array, p_violation_level IN NUMBER) RETURN VARCHAR2 IS l_threshold mgmt_policy_assoc_cfg_params.crit_threshold%type ; BEGIN IF p_param_values IS NULL OR p_param_values.COUNT=0 THEN RETURN NULL ; END IF; FOR i IN p_param_values.FIRST..p_param_values.LAST LOOP IF UPPER(p_param_values(i).param_name) = UPPER(p_param_name) THEN IF p_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN l_threshold := p_param_values(i).crit_threshold ; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING THEN l_threshold := p_param_values(i).warn_threshold ; ELSE l_threshold := p_param_values(i).info_threshold ; END IF ; EXIT ; END IF ; END LOOP ; RETURN(l_threshold) ; END param_value; PROCEDURE get_column_labels IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('format_severity_message:getting column labels', G_MODULE_NAME) ; END IF ; l_column_label := p_metric_info.metric_label ; l_column_nlsid := p_metric_info.metric_label_nlsid ; IF p_metric_info.all_cols IS NOT NULL AND p_metric_info.all_cols.COUNT > 0 THEN l_col_index := p_metric_info.all_cols.FIRST ; WHILE l_col_index IS NOT NULL LOOP IF UPPER(p_metric_info.all_cols(l_col_index).NAME) = UPPER(p_condition) THEN l_column_label := p_metric_info.column_labels(l_col_index).value ; l_column_nlsid := p_metric_info.column_nlsids(l_col_index).value ; EXIT ; END IF ; l_col_index := p_metric_info.all_cols.NEXT(l_col_index) ; END LOOP ; END IF ; END get_column_labels ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('format_severity_message:Enter Template='|| p_msg_template,G_MODULE_NAME) ; END IF ; IF (p_msg_template IS NULL) THEN IF p_condition_type = MGMT_GLOBAL.G_CONDITION_THRESHOLD THEN get_column_labels ; p_message:=FORMAT_DFLT_SEVERITY_MESSAGE (p_metric_label=>p_metric_info.metric_label, p_column_label=>l_column_label, p_key_value=>p_key_value, p_crit_threshold=>p_param_values(1).crit_threshold, p_warn_threshold=>p_param_values(1).warn_threshold, p_info_threshold=>p_param_values(1).info_threshold, p_value=>p_value, p_violation_level=>p_violation_level); ELSE p_message := 'Violated policy condition ('||p_condition||')' ; END IF ; RETURN; END IF; l_idx1 := INSTR(p_msg_template, '%', l_idx2 + 1, 1); p_message := ''; p_nls_params := NULL; WHILE (l_idx1 <> 0) LOOP l_nlsid := NULL; l_idx2 := INSTR(p_msg_template, '%', l_idx1+1, 1); -- End the loop if the end % is missing. EXIT WHEN l_idx2 = 0 ; -- Copy over the template data to output message IF (l_start_pos < l_idx1) THEN p_message := p_message || SUBSTR(p_msg_template, l_start_pos, (l_idx1 - l_start_pos)); END IF; l_start_pos := l_idx2 + 1; l_keyword := SUBSTR(p_msg_template, l_idx1+1, (l_idx2 - l_idx1-1)); IF (l_keyword IS NULL) THEN l_value := '%'; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_VALUE) THEN l_value := p_value; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_TARGET) THEN l_value := p_target_name ; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_METRIC) THEN l_value := p_metric_info.metric_name; l_nlsid := p_metric_info.metric_label_nlsid ; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_WARN_THRESHOLD) THEN l_value := p_param_values(1).warn_threshold; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_CRIT_THRESHOLD) THEN l_value := p_param_values(1).crit_threshold; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_INFO_THRESHOLD) THEN l_value := p_param_values(1).warn_threshold; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_NUM_OCCUR) THEN l_value := p_num_occurences; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_SEVERITY) THEN IF p_violation_level IN (MGMT_GLOBAL.G_SEVERITY_CLEAR,0) THEN l_value := 'CLEAR'; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL THEN l_value := 'INFORMATIONAL'; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING THEN l_value := 'WARNING'; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN l_value := 'CRITICAL'; ELSE l_value := 'UNDEFINED'; END IF; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_THRESHOLD) THEN IF p_violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL THEN l_value := p_param_values(1).info_threshold ; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING THEN l_value := p_param_values(1).warn_threshold ; ELSIF p_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN l_value := p_param_values(1).crit_threshold ; END IF; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_COLUMN) THEN -- call local procedure to get column label and nlsid get_column_labels ; l_value := l_column_label; l_nlsid := l_column_nlsid ; ELSIF (l_keyword = MGMT_COLLECTION.G_FORMAT_KEY_VALUE) THEN l_value := p_key_value; ELSE -- Check if the keyword is some other column name -- within the same metric l_position := p_metric_value.index_position(l_keyword) ; IF l_position > 0 THEN l_value := p_metric_value.metric_values(l_position).value ; ELSE l_value := nvl(param_value(l_keyword,p_param_values,p_violation_level), '%' || l_keyword||'%') ; END IF ; END IF; -- Substitute the keyword with the value p_message := p_message || l_value; append_message_param(p_nls_params, url_encode_string(l_value)); -- Also add the value to the NLS params IF (l_nlsid IS NOT NULL) THEN append_message_param_nlsid(p_nls_params, url_encode_string(l_nlsid)); END IF; l_idx1 := INSTR(p_msg_template, '%', l_idx2+1, 1); END LOOP; IF (l_start_pos < LENGTH(p_msg_template)) THEN p_message := p_message || SUBSTR(p_msg_template, l_start_pos); END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('format_severity_message:Exit message='|| p_message,G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('format_severity_message:Exit error='|| sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, substr(sqlerrm,1,100)||'(format severity message)') ; END format_severity_message; --***** END of formatting code copied from emd_collections -- -- get_composite_key -- -- Purpose -- Get the composite key value for the metric result record -- -- Parameters -- p_metric_value : Metric result record -- p_key_index_array : Array containing information about where -- the key column is found in the result record -- For Example if the key columns are in rows --- 2 and 4 in the metric_values record -- p_key_index_array(1)=2 and p_key_index_array(2)=4 -- p_num_keys : number of keys in the metric -- p_composite_key : the composite key -- -- Exceptions -- POLICY_EVAL_ERR -- Notes: -- 5 has been hardcoded as the number of keys since the table has 5 columns -- Assumption: there must be at least 2 keys for this procedure to be called -- PROCEDURE get_set_composite_key(p_metric_value IN mgmt_metric_value_obj, p_key_index_array IN mgmt_integer_array, p_num_keys IN NUMBER, p_composite_key OUT RAW ) IS BEGIN SELECT composite_key INTO p_composite_key FROM mgmt_metrics_composite_keys WHERE target_guid = p_metric_value.target_guid AND key_part1_value = nvl(p_metric_value.metric_values(p_key_index_array(1)).value,' ') AND key_part2_value = nvl(p_metric_value.metric_values(p_key_index_array(2)).value,' ') AND ((p_num_keys < 3 AND nvl(key_part3_value,' ') = ' ') OR (p_num_keys >=3 AND key_part3_value = nvl(p_metric_value.metric_values(p_key_index_array(3)).value,' ') ) ) AND ((p_num_keys < 4 AND nvl(key_part4_value,' ') = ' ') OR (p_num_keys >=4 AND key_part4_value = nvl(p_metric_value.metric_values(p_key_index_array(4)).value,' ') ) ) AND ((p_num_keys < 5 AND nvl(key_part5_value,' ') = ' ') OR (p_num_keys >=5 AND key_part5_value = nvl(p_metric_value.metric_values(p_key_index_array(5)).value,' ') ) ) ; EXCEPTION WHEN NO_DATA_FOUND THEN DECLARE l_key_array smp_emd_string_array := smp_emd_string_array(); BEGIN -- key array has to be at least 5 l_key_array.extend(greatest(p_num_keys,5)) ; FOR i IN 1..p_num_keys LOOP l_key_array(i) := nvl(p_metric_value.metric_values(p_key_index_array(i)).value,' ') ; END LOOP ; FOR i in p_num_keys+1..5 LOOP l_key_array(i) := ' ' ; END LOOP ; p_composite_key := MGMT_GLOBAL.GET_COMPOSITE_KEY_GUID(l_key_array) ; INSERT INTO mgmt_metrics_composite_keys (target_guid,composite_key, key_part1_value,key_part2_value,key_part3_value , key_part4_value,key_part5_value) VALUES (p_metric_value.target_guid,p_composite_key, nvl(l_key_array(1),' '), nvl(l_key_array(2),' '), nvl(l_key_array(3),' '), nvl(l_key_array(4),' '), nvl(l_key_array(5), ' ')); EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||'(get_set_composite_key)') ; END ; WHEN OTHERS THEN IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||'(get_set_composite_key)') ; END IF ; END get_set_composite_key; -- -- Procedure to get the key values -- If the key count=0 then key_value=' ' -- if key_count=1 then key_value=key_value(1) from record -- if key_count>1 then -- get the composite key for the key parts -- if no existing composite key, create -- PROCEDURE get_set_composite_key(p_metric_raw_records IN mgmt_metric_raw_table, p_key_values OUT mgmt_medium_string_table ) IS l_key_array smp_emd_string_array := smp_emd_string_array(); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_set_composite_key(raw_object):Enter',G_MODULE_NAME) ; END IF ; p_key_values := mgmt_medium_string_table() ; IF p_metric_raw_records IS NOT NULL AND p_metric_raw_records.COUNT > 0 THEN l_key_array.EXTEND(5) ; p_key_values.EXTEND(p_metric_raw_records.COUNT) ; FOR i IN 1..p_metric_raw_records.COUNT LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('get_set_composite_key(raw_object):processing record'||i, G_MODULE_NAME) ; END IF ; IF p_metric_raw_records(i).key_values IS NULL OR p_metric_raw_records(i).key_values.COUNT=0 THEN p_key_values(i) := ' '; ELSIF p_metric_raw_records(i).key_values.COUNT=1 THEN p_key_values(i) := NVL(p_metric_raw_records(i).key_values(1),' ') ; ELSE -- composite key l_key_array := smp_emd_string_array(' ',' ',' ',' ',' ') ; FOR j IN 1..p_metric_raw_records(i).key_values.COUNT LOOP l_key_array(j) := NVL(p_metric_raw_records(i).key_values(j),' ') ; END LOOP ; BEGIN SELECT composite_key INTO p_key_values(i) FROM mgmt_metrics_composite_keys WHERE target_guid = p_metric_raw_records(i).target_guid AND key_part1_value = l_key_array(1) AND key_part1_value = l_key_array(2) AND key_part1_value = l_key_array(3) AND key_part1_value = l_key_array(4) AND key_part1_value = l_key_array(5) ; EXCEPTION WHEN NO_DATA_FOUND THEN p_key_values(i) := MGMT_GLOBAL.GET_COMPOSITE_KEY_GUID(l_key_array) ; BEGIN INSERT INTO mgmt_metrics_composite_keys (target_guid,composite_key, key_part1_value,key_part2_value,key_part3_value , key_part4_value,key_part5_value) VALUES (p_metric_raw_records(i).target_guid,p_key_values(i), nvl(l_key_array(1),' '), nvl(l_key_array(2),' '), nvl(l_key_array(3),' '), nvl(l_key_array(4),' '), nvl(l_key_array(5), ' ')); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; END ; END IF ; END LOOP ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_set_composite_key(raw_object):Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, SQLERRM||'(get_set_composite_key)') ; END get_set_composite_key ; PROCEDURE get_target_info(p_target_guid IN RAW, p_target_name OUT VARCHAR2, p_target_date OUT DATE) IS BEGIN SELECT mgmt_global.sysdate_tzrgn(timezone_region), target_name INTO p_target_date, p_target_name FROM mgmt_targets WHERE target_guid = p_target_guid ; END ; -- initialize_counters -- -- Purpose -- Initialize the index array which keep tracks of where a key value is -- found in each metric result. -- For example there are 2 records and the number of keys is 2. -- In the first metric_value_obj record ,key 1 is found at position 3 in the -- metric_values array, key 2 is found at position 1 -- In the second metric_value_obj record ,key 1 is found at position 2 in the -- metric_values array, key 2 is found at position 4 -- key_index_array would contain 2 rows ( number of metric_value_obj records) -- each row with number of columns = num_keys -- key_index(1,1)=3 key_index(1,2)=1 -- key_index(2,1)=2 key_index(2,2)=4 -- -- Initialize key value array -- which stores the key_value (composite_key_value if p_num_keys>1) for each -- metric_value record -- -- Parameters -- p_metric_values : Metric values array consists of 1 or more metric_value_obj -- p_key_columns : Key column list -- p_key_index_array : key index array -- p_result_key_array : Key values for each record PROCEDURE initialize_counters(p_metric_values IN OUT mgmt_metric_value_array, p_key_columns IN mgmt_namevalue_array, p_key_index_array OUT NOCOPY g_integer_array_table, p_result_key_array OUT NOCOPY mgmt_medium_string_table ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Initialize counters:Enter',G_MODULE_NAME) ; END IF ; p_key_index_array := g_integer_array_table() ; p_result_key_array := mgmt_medium_string_table() ; -- LAST is used instead of COUNT since the records could -- be deleted in between p_result_key_array.extend(p_metric_values.LAST) ; p_key_index_array.extend(p_metric_values.LAST) ; -- for each metric_value_obj record in the results FOR rindex IN p_metric_values.FIRST..p_metric_values.LAST LOOP IF p_metric_values.EXISTS(rindex) AND nvl(p_metric_values(rindex).STATUS,EM_METRIC_EVAL.G_METRIC_EVAL_SUCCESS) = EM_METRIC_EVAL.G_METRIC_EVAL_SUCCESS THEN p_key_index_array(rindex) := mgmt_integer_array() ; p_key_index_array(rindex).extend(p_key_columns.count) ; FOR keys IN p_key_columns.first..p_key_columns.last LOOP p_key_index_array(rindex)(keys) := nvl(p_metric_values(rindex).index_position(p_key_columns(keys).name),0) ; IF p_key_index_array(rindex)(keys) = 0 THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, p_key_columns(keys).name||' not found in metric result '); END IF ; END LOOP ; -- Need to initialize up to 5 so get_set_composite_key does not error out. IF p_key_columns.COUNT < 5 THEN p_key_index_array(rindex).extend(5-p_key_columns.count) ; FOR i IN (p_key_columns.COUNT+1)..5 LOOP p_key_index_array(rindex)(i) := 1 ; END LOOP ; END IF ; IF p_key_columns.COUNT = 1 THEN p_result_key_array(rindex) := p_metric_values(rindex).metric_values( p_key_index_array(rindex)(1)).value ; ELSE get_set_composite_key(p_metric_value=>p_metric_values(rindex), p_key_index_array=>p_key_index_array(rindex), p_num_keys=>p_key_columns.COUNT, p_composite_key=>p_result_key_array(rindex) ) ; END IF ; ELSIF p_metric_values.EXISTS(rindex) AND nvl(p_metric_values(rindex).STATUS,EM_METRIC_EVAL.G_METRIC_EVAL_SUCCESS) != EM_METRIC_EVAL.G_METRIC_EVAL_SUCCESS THEN p_metric_values.DELETE(rindex) ; END IF ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Initialize counters:Exit',G_MODULE_NAME) ; END IF ; END initialize_counters; -- key_match -- -- Purpose: -- Match the key defined in policy against metric result -- Return TRUE if key matches else FALSE -- -- Parameters -- p_policy_key : Key value defined in the policy configuration -- p_metric_value : The current metric value object -- p_key_index_array :Array containing positions of key columns -- p_key_operator : key operator of the configuration -- p_num_keys : Number of keys for the metrica -- --Exceptions -- POLICY_EVAL_ERR -- Missing key value:if the key column is missing in the record -- Invalid key value:If there is no policy key configuration -- FUNCTION key_match(p_policy_key IN mgmt_medium_string_array, p_metric_value IN mgmt_metric_value_obj, p_key_index_array IN mgmt_integer_array, p_key_operator IN VARCHAR2, p_num_keys IN NUMBER ) RETURN BOOLEAN IS l_key_match BOOLEAN := TRUE ; l_key_index NUMBER(1) := 1 ; keyvalue_not_found1 exception ; keyvalue_not_found2 exception ; PRAGMA EXCEPTION_INIT(keyvalue_not_found1,-6532) ; PRAGMA EXCEPTION_INIT(keyvalue_not_found2,-6531) ; BEGIN WHILE l_key_match AND l_key_index <= p_num_keys LOOP IF bitand(p_key_operator,power(2,l_key_index-1)) = power(2,l_key_index-1) THEN l_key_match := l_key_match AND p_metric_value.metric_values(p_key_index_array(l_key_index)).value LIKE p_policy_key(l_key_index) ESCAPE '\'; ELSE l_key_match := l_key_match AND p_metric_value.metric_values(p_key_index_array(l_key_index)).value = p_policy_key(l_key_index) ; END IF ; l_key_index := l_key_index + 1 ; END LOOP ; RETURN l_key_match ; EXCEPTION WHEN keyvalue_not_found1 or keyvalue_not_found2 THEN -- key not in result record or composite keys table raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Missing/Invalid Key Value (key_match)') ; WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' when matching keys (key_match)') ; END key_match; -- get_policies -- -- Purpose -- get the list of policies associated with task or target -- ordered by target_guid, policy_guid, eval_order.If config_std_guid is -- given,further filter the list of policies which are associated with the rules -- that come under the given configuration standard. -- -- Parameters: -- p_task_id : task_id of the collection -- p_metric_guid : Metric guid of the main metric record -- p_metric_values : Result set -- p_eval_mode : TASK_EVAL_MODE: use task_id to get policies -- TARGET_EVAL_MODE: use targets in mgmt_metric_results -- to get policies -- p_num_keys : num_keys of the metric -- p_policy_cur : Cursor ref to get the policies -- p_config_std_guid: config std guid of the configuration standard being evaluated. FUNCTION get_policies(p_task_id IN NUMBER, p_metric_guid IN RAW, p_target_guids IN mgmt_target_guid_array, p_eval_mode IN NUMBER := EM_METRIC_EVAL.G_TASK_EVAL_MODE, p_num_keys IN NUMBER, p_coll_name IN VARCHAR2 := ' ', p_config_std_guid IN RAW DEFAULT NULL) RETURN g_policy_cur_type IS l_policy_cur g_policy_cur_type ; BEGIN IF p_eval_mode = EM_METRIC_EVAL.G_TASK_EVAL_MODE THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('get_policies:Enter task',G_MODULE_NAME) ; END IF ; -- Trying to get all policy information in one short -- instead of doing a master/detail query. -- trying to avoid context switching. Need to -- Check performance because of cast(multiset()). -- We are getting policies only for specified targets so we should be ok. OPEN l_policy_cur FOR SELECT task_tgt.target_guid target_guid, LEAD(task_tgt.target_guid,1) OVER (ORDER BY task_tgt.target_guid, policy.policy_guid, cfg.eval_order) next_target_guid, policy.policy_guid policy_guid, LEAD(policy.policy_guid,1) OVER (ORDER BY task_tgt.target_guid, policy.policy_guid, cfg.eval_order) next_policy_guid, policy.policy_name, policy.policy_type, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.message, policy.message), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.message, NULL) message, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.message_nlsid, policy.message_nlsid), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.message_nlsid, NULL) message_nlsid, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.clear_message, policy.clear_message), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.clear_message, NULL) clear_message, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.clear_message_nlsid, policy.clear_message_nlsid), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.clear_message_nlsid, NULL) clear_message_nlsid, policy.repo_timing_enabled, task_tgt.coll_name , POLICy.violation_level, decode(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY,0) violation_type, policy.condition_type, policy.condition, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.condition_operator, policy.condition_operator), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.condition_operator, 0) condition_operator, cfg.key_value, cfg.key_operator, cfg.is_exception, cfg.num_occurrences, NULL evaluation_date, DECODE(cfg.is_exception, MGMT_GLOBAL.G_TRUE,mgmt_policy_param_val_array(), CAST(MULTISET( SELECT mgmt_policy_param_val.new(param_name, crit_threshold, warn_threshold, info_threshold) FROM mgmt_policy_assoc_cfg_params param WHERE param.object_guid = cfg.object_guid AND param.policy_guid = cfg.policy_guid AND param.coll_name = cfg.coll_name AND param.key_value = cfg.key_value AND param.key_operator = cfg.key_operator ) AS mgmt_policy_param_val_array)) params, DECODE(policy.condition_type,MGMT_GLOBAL.G_CONDITION_SQL, CAST(MULTISET(SELECT mgmt_namevalue_obj.new(bind_column_name, bind_column_type) FROM mgmt_policy_bind_vars binds WHERE binds.policy_guid = policy.policy_guid ) AS mgmt_namevalue_array),mgmt_namevalue_array()) binds, DECODE(p_num_keys,0,mgmt_medium_string_array(), 1,mgmt_medium_string_array(cfg.key_value), CAST( (SELECT mgmt_medium_string_array( key_part1_value,key_part2_value, key_part3_value,key_part4_value, key_part5_value) FROM mgmt_metrics_composite_keys comp_keys WHERE comp_keys.composite_key = cfg.key_value AND comp_keys.target_guid = cfg.object_guid ) AS mgmt_medium_string_array) ) key_values FROM mgmt_policies policy, mgmt_policy_assoc assoc, mgmt_policy_assoc_cfg cfg, mgmt_collection_metric_tasks task_tgt WHERE task_tgt.task_id = p_task_id AND policy.metric_guid = p_metric_guid AND assoc.object_guid = task_tgt.target_guid AND policy.policy_type != MGMT_GLOBAL.G_TYPE_CS_POLICY AND ( policy.policy_type = MGMT_GLOBAL.G_TYPE_POLICY OR assoc.coll_name = task_tgt.coll_name ) AND assoc.policy_guid = policy.policy_guid AND assoc.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND assoc.is_enabled = MGMT_GLOBAL.G_TRUE AND cfg.object_guid = assoc.object_guid AND cfg.coll_name = assoc.coll_name AND cfg.policy_guid = assoc.policy_guid ORDER by task_tgt.target_guid, policy.policy_guid, cfg.eval_order , cfg.key_value desc ; ELSE IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('get_policies:Enter target COUNT='||p_target_guids.COUNT,G_MODULE_NAME) ; END IF ; --If p_config_guid is NULL IF p_config_std_guid IS NULL THEN --NOTE:WHENEVER YOU FIX ANYTHING IN THIS BLOCK , MAKE SURE TO FIX THE SAME --IN ELSE BLOCK ALSO (IF APPLICABLE). OPEN l_policy_cur FOR SELECT /*+ ORDERED USE_NL(assoc) USE_NL(cfg) NO_INDEX_FFS(assoc MGMT_POLICY_ASSOC_PK) INDEX_ASC(assoc MGMT_POLICY_ASSOC_PK) NO_INDEX_FFS(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX) INDEX_ASC(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX) CARDINALITY(targets 50)*/ assoc.object_guid target_guid, LEAD(assoc.object_guid,1) OVER (ORDER BY assoc.object_guid, policy.policy_guid, cfg.eval_order) next_target_guid, policy.policy_guid policy_guid, LEAD(policy.policy_guid,1) OVER (ORDER BY assoc.object_guid, policy.policy_guid, cfg.eval_order) next_policy_guid, policy.policy_name, policy.policy_type, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.message, policy.message), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.message, NULL) message, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.message_nlsid, policy.message_nlsid), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.message_nlsid, NULL) message_nlsid, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.clear_message, policy.clear_message), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.clear_message, NULL) clear_message, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.clear_message_nlsid, policy.clear_message_nlsid), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.clear_message_nlsid, NULL) clear_message_nlsid, policy.repo_timing_enabled, p_coll_name, policy.violation_level , decode(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY,0) violation_type, policy.condition_type, policy.condition, DECODE(policy.policy_type, MGMT_GLOBAL.G_TYPE_POLICY, NVL(cfg.condition_operator, policy.condition_operator), MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, cfg.condition_operator, 0) condition_operator, cfg.key_value, cfg.key_operator, cfg.is_exception, cfg.num_occurrences, NULL evaluation_date, CAST(MULTISET( SELECT mgmt_policy_param_val(param_name, crit_threshold, warn_threshold, info_threshold) FROM mgmt_policy_assoc_cfg_params param WHERE param.object_guid = cfg.object_guid AND param.policy_guid = cfg.policy_guid AND param.coll_name = cfg.coll_name AND param.key_value = cfg.key_value AND param.key_operator = cfg.key_operator ) AS mgmt_policy_param_val_array) params, DECODE(policy.condition_type,MGMT_GLOBAL.G_CONDITION_SQL, CAST(MULTISET(SELECT mgmt_namevalue_obj.new(bind_column_name,bind_column_type) FROM mgmt_policy_bind_vars binds WHERE binds.policy_guid = policy.policy_guid ) AS mgmt_namevalue_array),mgmt_namevalue_array()) binds, DECODE(p_num_keys,0,mgmt_medium_string_array(), 1,mgmt_medium_string_array(cfg.key_value), CAST( (SELECT mgmt_medium_string_array( key_part1_value,key_part2_value, key_part3_value,key_part4_value, key_part5_value) FROM mgmt_metrics_composite_keys comp_keys where comp_keys.composite_key = cfg.key_value AND comp_keys.target_guid = cfg.object_guid) as mgmt_medium_string_array) ) key_values FROM TABLE(CAST(p_target_guids AS mgmt_target_guid_array) ) targets, mgmt_policies policy, mgmt_policy_assoc assoc, mgmt_policy_assoc_cfg cfg WHERE policy.metric_guid = p_metric_guid AND assoc.object_guid = targets.column_value AND assoc.policy_guid = policy.policy_guid AND policy.policy_type != MGMT_GLOBAL.G_TYPE_CS_POLICY AND ( policy.policy_type = MGMT_GLOBAL.G_TYPE_POLICY OR assoc.coll_name = p_coll_name ) AND assoc.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND assoc.is_enabled = MGMT_GLOBAL.G_TRUE AND cfg.object_guid = assoc.object_guid AND cfg.policy_guid = assoc.policy_guid AND cfg.coll_name = assoc.coll_name ORDER BY assoc.object_guid, policy.policy_guid, cfg.eval_order , cfg.key_value desc ; ELSE --If config_guid is not null,add config_guid in where clause. --set violation type as MGMT_GLOBAL.G_SEVERITY_TYPE_CS_POLICY. --Since there is no target association for cs policies --object type should be G_OBJECT_TYPE_DEFAULT. OPEN l_policy_cur FOR WITH targets AS ( SELECT column_value target_guid FROM TABLE(CAST(p_target_guids AS mgmt_target_guid_array) ) ) --since the config std policy is not associated to target, fetch --the target details from "targets" instead of from "assoc" SELECT /*+ ORDERED USE_NL(assoc) USE_NL(cfg) NO_INDEX_FFS(assoc MGMT_POLICY_ASSOC_PK) INDEX_ASC(assoc MGMT_POLICY_ASSOC_PK) NO_INDEX_FFS(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX) INDEX_ASC(cfg MGMT_POLICY_ASSOC_CFG_PK_IDX) CARDINALITY(targets 50) */ targets.target_guid target_guid, LEAD(targets.target_guid,1) OVER (ORDER BY targets.target_guid, policy.policy_guid, cfg.eval_order) next_target_guid, policy.policy_guid policy_guid, LEAD(policy.policy_guid,1) OVER (ORDER BY targets.target_guid, policy.policy_guid, cfg.eval_order) next_policy_guid, policy.policy_name, policy.policy_type, NVL(cfg.message, policy.message) message, NVL(cfg.message_nlsid, policy.message_nlsid) message_nlsid, NVL(cfg.clear_message, policy.clear_message) clear_message, NVL(cfg.clear_message_nlsid, policy.clear_message_nlsid) clear_message_nlsid, policy.repo_timing_enabled, p_coll_name, policy.violation_level , MGMT_GLOBAL.G_SEVERITY_TYPE_CS_POLICY violation_type, policy.condition_type, policy.condition, NVL(cfg.condition_operator, policy.condition_operator) condition_operator, cfg.key_value, cfg.key_operator, cfg.is_exception, cfg.num_occurrences, NULL evaluation_date, CAST(MULTISET( SELECT mgmt_policy_param_val(param_name, crit_threshold, warn_threshold, info_threshold) FROM mgmt_policy_assoc_cfg_params param WHERE param.object_guid = cfg.object_guid AND param.policy_guid = cfg.policy_guid AND param.coll_name = cfg.coll_name AND param.key_value = cfg.key_value AND param.key_operator = cfg.key_operator ) AS mgmt_policy_param_val_array) params, DECODE(policy.condition_type,MGMT_GLOBAL.G_CONDITION_SQL, CAST(MULTISET(SELECT mgmt_namevalue_obj.new(bind_column_name,bind_column_type) FROM mgmt_policy_bind_vars binds WHERE binds.policy_guid = policy.policy_guid ) AS mgmt_namevalue_array),mgmt_namevalue_array()) binds, DECODE(p_num_keys,0,mgmt_medium_string_array(), 1,mgmt_medium_string_array(cfg.key_value), CAST( (SELECT mgmt_medium_string_array( key_part1_value,key_part2_value, key_part3_value,key_part4_value, key_part5_value) FROM mgmt_metrics_composite_keys comp_keys where comp_keys.composite_key = cfg.key_value AND comp_keys.target_guid = cfg.object_guid) as mgmt_medium_string_array) ) key_values FROM targets, mgmt_policies policy, mgmt_policy_assoc assoc, mgmt_policy_assoc_cfg cfg, mgmt_cs_rule csrule WHERE policy.metric_guid = p_metric_guid AND assoc.policy_guid = policy.policy_guid AND policy.policy_type = MGMT_GLOBAL.G_TYPE_CS_POLICY AND assoc.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT AND assoc.is_enabled = MGMT_GLOBAL.G_TRUE AND cfg.object_guid = assoc.object_guid AND cfg.policy_guid = assoc.policy_guid AND cfg.coll_name = assoc.coll_name AND cfg.policy_guid = csrule.policy_guid AND csrule.cs_guid = p_config_std_guid ORDER BY targets.target_guid, policy.policy_guid, cfg.eval_order , cfg.key_value desc ; END IF ; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('get_policies:Exit',G_MODULE_NAME) ; END IF ; RETURN l_policy_cur ; END get_policies; -- is_valid_condition -- Purpose -- Private procedure to verify condition text -- see package definition of verify_condition_Text for more info -- A condition text is valid if -- It has no syntax issues -- It references only policy parameters or metric columns -- -- Parameters -- p_condition_text : Condition text -- p_param_columns : Param Name and data type list -- p_metric_columns : Metric column name and data type list -- p_bind_columns : Bind columns and Bind Column Type list -- If Bind column type = 1 Then -- the bind column is a Param Column -- If Bind column type = 2 Then -- the bind column is a Metric Column -- may return duplicate bind columns if the bind column name -- is both a param column and metric column or if the column -- occurs twice in condition text -- however when storing only the first occurence is stored -- Assumptions: -- The person defining the condition text should take care of dates -- For example if the condition text is -- sysdate - :last_login_date then if the :last_login_date is not -- in correct format then the condition text would error out -- Exceptions -- None -- FUNCTION is_valid_condition(p_condition_text IN VARCHAR2, p_param_columns IN mgmt_short_string_array, p_metric_columns IN mgmt_short_string_array, p_bind_columns OUT mgmt_namevalue_array, p_error_message OUT VARCHAR2 ) RETURN BOOLEAN IS l_result NUMBER ; l_sql_code NUMBER ; l_validation_cur NUMBER ; l_proc_name CONSTANT VARCHAR2(30) := 'is_valid_condition:' ; l_sql_stmt VARCHAR2(4000) := 'SELECT count(*) FROM DUAL WHERE '|| p_condition_text ; l_value_string VARCHAR2(3):= NULL ; invalid_bind EXCEPTION ; invalid_reference EXCEPTION ; PRAGMA EXCEPTION_INIT(invalid_bind,-1006) ; PRAGMA EXCEPTION_INIT(invalid_reference,-1008) ; -- Function to check if value is in bind list FUNCTION value_in_bind_list(p_index IN NUMBER) RETURN BOOLEAN IS BEGIN IF p_bind_columns.COUNT > 0 THEN FOR i in p_bind_columns.FIRST..p_bind_columns.LAST LOOP IF p_bind_columns(i).name = p_metric_columns(p_index) THEN RETURN TRUE ; END IF ; END LOOP ; END IF ; RETURN FALSE ; END ; BEGIN p_bind_columns := mgmt_namevalue_array() ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'enter sql = ' || l_sql_stmt, G_MODULE_NAME) ; END IF ; IF INSTR(p_condition_text,';') > 0 THEN EMDW_LOG.DEBUG(l_proc_name||' contains ;',G_MODULE_NAME) ; p_error_message := 'Invalid Condition Text' ; RETURN(FALSE) ; END IF ; -- Parse the condition text to catch syntax issues l_validation_cur := DBMS_SQL.OPEN_CURSOR ; BEGIN DBMS_SQL.PARSE(l_validation_cur,l_sql_stmt,DBMS_SQL.NATIVE) ; EXCEPTION WHEN OTHERS THEN l_sql_code := SQLCODE ; IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.INFO(l_proc_name||sqlerrm,G_MODULE_NAME); END IF ; DBMS_SQL.CLOSE_CURSOR(l_validation_cur) ; p_error_message := 'Invalid Condition Text' ; RETURN(FALSE) ; END ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Successfully Parsed',G_MODULE_NAME) ; END IF ; -- Bind the param columns one by one if it succeeds -- then the param column is in the condition text IF p_param_columns IS NOT NULL and p_param_columns.COUNT >0 THEN FOR i in p_param_columns.FIRST..p_param_columns.LAST LOOP BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Binding param_column ' || p_param_columns(i), G_MODULE_NAME) ; END IF ; DBMS_SQL.BIND_VARIABLE(l_validation_cur,p_param_columns(i), l_value_string) ; p_bind_columns.extend ; p_bind_coLumns(p_bind_columns.count) := mgmt_namevalue_obj.new(p_param_columns(i),G_PARAM_COLUMN) ; EXCEPTION WHEN invalid_bind THEN NULL ; END ; END LOOP ; END IF ; -- if the metric column is not already in bind list -- ( to make sure param columns get precedence over metric columns -- in case of duplicate param and metric column) -- Bind the metric columns one by one if it succeeds -- then the metric column is in the condition text IF p_metric_columns IS NOT NULL and p_metric_columns.COUNT >0 THEN FOR i in p_metric_columns.FIRST..p_metric_columns.LAST LOOP BEGIN IF NOT value_in_bind_list(i) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Binding metric_column ' || p_metric_columns(i), G_MODULE_NAME) ; END IF ; DBMS_SQL.BIND_VARIABLE(l_validation_cur,p_metric_columns(i), l_value_string) ; p_bind_columns.extend ; p_bind_columns(p_bind_columns.count) := mgmt_namevalue_obj.new(p_metric_columns(i),G_METRIC_COLUMN) ; END IF ; EXCEPTION WHEN invalid_bind THEN NULL ; END ; END LOOP ; END IF ; l_result := dbms_sql.execute_and_fetch(l_validation_cur,TRUE) ; DBMS_SQL.CLOSE_CURSOR(l_validation_cur) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'exit normal',G_MODULE_NAME) ; END IF ; p_error_message := NULL ; RETURN(TRUE) ; EXCEPTION WHEN invalid_reference THEN l_sql_code := SQLCODE ; IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR(l_proc_name||'Invalid Column reference ',G_MODULE_NAME) ; END IF ; IF DBMS_SQL.IS_OPEN(l_validation_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_validation_cur) ; END IF ; -- no detailed messages for security reasons p_error_message := 'Invalid Condition Text' ; RETURN(FALSE) ; WHEN OTHERS THEN l_sql_code := SQLCODE ; IF DBMS_SQL.IS_OPEN(l_validation_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_validation_cur) ; END IF ; IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.INFO(l_proc_name||'exit error - '||sqlerrm,G_MODULE_NAME) ; END IF ; -- no detailed messages for security reasons p_error_message := 'Invalid Condition Text' ; RETURN(FALSE) ; END is_valid_condition; -- get_violation_status -- -- Purpose -- to get the current violation state of the policy for the key value -- -- Parameters -- p_target_guid : Target Guid -- p_policy_guid : Policy guid -- p_result_key_value : key value found in the result -- p_cfg_key_value : key value in the configuration -- p_cfg_key_operator : the key operator -- p_last_violation_level : Last severity code/Violation level of the policy -- PROCEDURE get_violation_status(p_target_guid IN RAW, p_policy_guid IN RAW, p_result_key_value IN VARCHAR2, p_coll_name IN VARCHAR2, p_num_occurrences IN NUMBER, p_crit_counter OUT NOCOPY NUMBER, p_warn_counter OUT NOCOPY NUMBER, p_info_counter OUT NOCOPY NUMBER, p_exempt_code OUT NOCOPY NUMBER, p_exempt_until OUT NOCOPY DATE, p_last_violation_guid OUT NOCOPY RAW, p_last_violation_level OUT NOCOPY NUMBER) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' get_violation_status:Enter',G_MODULE_NAME) ; END IF ; IF p_num_occurrences > 1 THEN BEGIN SELECT nvl(crit_counter,0), nvl(warn_counter,0), nvl(info_counter,0) INTO p_crit_counter, p_warn_counter, p_info_counter FROM mgmt_policy_assoc_eval_details WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND result_key_value = p_result_key_value AND coll_name = p_coll_name ; EXCEPTION WHEN NO_DATA_FOUND THEN p_crit_counter := 0 ; p_warn_counter := 0 ; p_info_counter := 0 ; END ; ELSE p_crit_counter := 0 ; p_warn_counter := 0 ; p_info_counter := 0 ; END IF ; BEGIN SELECT violation_level,violation_guid, exempt_code,exempt_until INTO p_last_violation_level,p_last_violation_guid, p_exempt_code,p_exempt_until FROM MGMT_CURRENT_VIOLATION WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND key_value = p_result_key_value ; EXCEPTION WHEN NO_DATA_FOUND THEN p_last_violation_level := MGMT_GLOBAL.G_SEVERITY_CLEAR ; END ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' get_violation_status:Exit Normal ' ,G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('get_violation_status:Exit Exception'||sqlerrm, G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' getting last state (get_current_details)') ; END get_violation_status; -- update_policy_eval_detail -- -- Purpose -- Update the policy counters and error message -- We insert a bootstrap clear record when no evaluation details -- are present. The bootstrap is skipped if the log violations package -- just inserted a violations record -- PROCEDURE update_policy_eval_detail(p_target_guid IN RAW, p_policy_guid IN RAW, p_violation_type IN NUMBER, p_coll_name IN VARCHAR2, p_collection_timestamp IN DATE, p_result_key_value IN VARCHAR2, p_cfg_key_value IN VARCHAR2, p_cfg_key_operator IN NUMBER, p_evaluation_date IN DATE, p_crit_counter IN NUMBER := NULL, p_warn_counter IN NUMBER := NULL, p_info_counter IN NUMBER := NULL, p_last_error_message IN VARCHAR := NULL, p_bootstrap IN BOOLEAN := TRUE) IS l_count NUMBER ; l_notification_status NUMBER := MGMT_GLOBAL.G_NOTIF_STATUS_NEW; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' update_policy_eval_detail:Enter',G_MODULE_NAME) ; END IF ; UPDATE mgmt_policy_assoc_eval_details SET crit_counter = nvl(p_crit_counter,crit_counter), warn_counter = nvl(p_warn_counter,warn_counter), info_counter = nvl(p_info_counter,info_counter), last_error_message = p_last_error_message, last_evaluation_date = p_evaluation_date WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name AND result_key_value = p_result_key_value ; IF SQL%NOTFOUND AND p_target_guid IS NOT NULL AND p_policy_guid IS NOT NULL AND p_coll_name IS NOT NULL AND p_result_key_value IS NOT NULL THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(' update_policy_eval_details:First time evaluation', G_MODULE_NAME) ; END IF ; INSERT INTO mgmt_policy_assoc_eval_details (target_guid,policy_guid,coll_name,result_key_value, cfg_key_Value,cfg_key_operator, crit_counter,warn_counter,info_counter, last_evaluation_date,last_error_message) VALUES (p_target_guid,p_policy_guid,p_coll_name, p_result_key_value, p_cfg_key_Value,p_cfg_key_operator, nvl(p_crit_counter,0),nvl(p_warn_counter,0),nvl(p_info_counter,0), p_evaluation_date,p_last_error_message) ; IF p_bootstrap THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(' update_policy_eval_details:'|| 'Bootstrap violation clear record inserted', G_MODULE_NAME) ; END IF ; BEGIN -- -- if the policy was evaluated before but the key was deleted -- we could get in here.So check if no violations were logged -- previously for the target/policy/key value ever -- SELECT NVL(COUNT(target_guid),0) INTO l_count FROM mgmt_violations WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND key_value = p_result_key_value AND ROWNUM < 2 ; IF l_count = 0 THEN --notification should be ignored for configuration standard policy IF (p_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_CS_POLICY) THEN l_notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE; END IF; MGMT_VIOLATION.log_violation( p_target_guid=>p_target_guid, p_policy_guid=>p_policy_guid, p_key_value=>p_result_key_value, p_collection_timestamp=>p_collection_timestamp, p_violation_level=>MGMT_GLOBAL.G_SEVERITY_CLEAR, p_violation_type=>p_violation_type, p_value=>NULL, p_string_value=>NULL, p_cfg_coll_name=>p_coll_name, p_cfg_key_value=>p_cfg_key_value, p_cfg_key_operator=>p_cfg_key_operator, p_violation_ctxt_list=>NULL, p_notification_status=>l_notification_status ) ; END IF ; EXCEPTION WHEN MGMT_GLOBAL.duplicate_record THEN NULL ; END ; END IF ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' update_policy_eval_detail:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR(' update_policy_eval_detail:Exit exception '||sqlerrm ,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||'(update_policy_eval_detail)') ; END update_policy_eval_detail; -- get_new_violation_status -- -- Purpose -- Gets the new severity_code/violation_level given current conditions -- Code taken from collections engine -- updates the counters p_crit_counter,p_warn_counter and p_info_counter -- returns new severity code PROCEDURE get_new_violation_status(p_crit_violation IN BOOLEAN, p_warn_violation IN BOOLEAN, p_info_violation IN BOOLEAN, p_num_occurrences IN NUMBER, p_last_violation_level IN NUMBER, p_crit_counter IN OUT NUMBER, p_warn_counter IN OUT NUMBER, p_info_counter IN OUT NUMBER, p_new_violation_level OUT NUMBER ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' get_new_violation_status:Enter Counters='|| p_crit_counter|| ':'||p_warn_counter||':'|| p_info_counter||' Old Violation Level='|| p_last_violation_level,G_MODULE_NAME) ; END IF ; IF p_crit_violation THEN p_crit_counter := least(p_num_occurrences,p_crit_counter+1) ; ELSE p_crit_counter := greatest(0,p_crit_counter-1) ; END IF ; IF p_warn_violation THEN p_warn_counter := least(p_num_occurrences,p_warn_counter+1) ; ELSE p_warn_counter := greatest(0,p_warn_counter-1) ; END IF ; IF p_info_violation THEN p_info_counter := least(p_num_occurrences,p_info_counter+1) ; ELSE p_info_counter := greatest(0,p_info_counter-1) ; END IF ; IF p_crit_counter >= p_num_occurrences THEN -- 1 p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_CRITICAL; ELSIF p_warn_counter >= p_num_occurrences THEN --1 p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_WARNING; ELSIF p_info_counter >= p_num_occurrences THEN --1 p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL ; ELSIF p_warn_counter = 0 AND p_crit_counter = 0 AND p_info_counter = 0 THEN --1 p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_CLEAR; ELSE --1 IF p_last_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND p_crit_counter = 0 THEN --2 IF p_warn_counter > 0 THEN --2a p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_WARNING; ELSIF p_info_counter >0 THEN --2a p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL ; ELSE --2a p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_CLEAR; END IF; --2a ELSIF p_last_violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND p_warn_counter = 0 THEN --2 IF p_info_counter >0 THEN --2b p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL ; ELSE --2b p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_CLEAR; END IF; --2b ELSIF p_last_violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL AND p_info_counter = 0 THEN --2 p_new_violation_level := MGMT_GLOBAL.G_SEVERITY_CLEAR ; ELSE --2 -- here it was clear , now setting to old severity code, to be verified p_new_violation_level := p_last_violation_level ; END IF; --2 END IF; --1 IF p_new_violation_level = p_last_violation_level THEN p_new_violation_level := 0 ; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' get_new_violation_status:Exit Counters='|| p_crit_counter|| ':'||p_warn_counter||':'|| p_info_counter||' New Violation Level='|| p_new_violation_level,G_MODULE_NAME) ; END IF ; END ; -- log_Violation -- -- Purpose -- log the violation -- if evaluation results in violation which is different from -- current violation status, it is logged. -- If evaluation results in clear status the violation is cleared -- if p_construct_context is TRUE then violation context is constructed -- This is to disable clear_missing_keys from constructing context -- PROCEDURE log_violation(p_policy_rec IN EM_POLICY_EVAL_REC, p_target_name IN VARCHAR2, p_result_key_value IN VARCHAR2, p_crit_violation IN BOOLEAN, p_warn_violation IN BOOLEAN, p_info_violation IN BOOLEAN, p_metric_info IN EM_METRIC_EVAL.METRIC_INFO_REC, p_metric_value IN mgmt_metric_value_obj, p_value IN NUMBER, p_string_value IN VARCHAR2, p_violation_counter IN OUT NUMBER, p_violation_context IN OUT NOCOPY mgmt_short_string_array, p_construct_context IN BOOLEAN := TRUE ) IS l_new_violation_level mgmt_violations.violation_level%type; l_last_violation_level mgmt_violations.violation_level%type; l_last_violation_guid mgmt_violations.violation_guid%type; l_crit_counter mgmt_policy_assoc_eval_details.crit_counter%type := 0 ; l_warn_counter mgmt_policy_assoc_eval_details.warn_counter%type := 0 ; l_info_counter mgmt_policy_assoc_eval_details.info_counter%type := 0 ; l_first_time BOOLEAN := FALSE ; l_metric_type mgmt_metrics.metric_type%type ; l_bootstrap BOOLEAN := TRUE ; l_viol_ctxt_list mgmt_viol_name_value_array := mgmt_viol_name_value_array() ; l_message mgmt_violations.message%type ; l_message_template mgmt_policies.message%type ; l_message_nlsid mgmt_policies.message_nlsid%type ; l_nls_params mgmt_violations.message%type ; l_exempt_code mgmt_current_violation.exempt_code%type ; l_exempt_until mgmt_current_violation.exempt_until%type ; l_notification_status NUMBER := MGMT_GLOBAL.G_NOTIF_STATUS_NEW; PROCEDURE construct_violation_context IS BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(' log_violations:Violation Context Start',G_MODULE_NAME) ; END IF ; -- We construct violation guid only once per policy for performance -- that is why violation context is a IN OUT parameter IF p_violation_context IS NULL THEN BEGIN SELECT column_name BULK COLLECT INTO p_violation_context FROM mgmt_policy_viol_ctxt_def WHERE policy_guid = p_policy_rec.policy_guid ORDER BY column_position ; EXCEPTION WHEN NO_DATA_FOUND THEN NULL ; END ; END IF ; IF p_violation_context IS NOT NULL AND p_violation_context.COUNT > 0 THEN FOR rec in p_violation_context.FIRST..p_violation_context.LAST LOOP l_viol_ctxt_list.extend ; -- Default metric type is String l_metric_type := MGMT_GLOBAL.G_METRIC_TYPE_STRING ; FOR i in p_metric_info.all_cols.FIRST..p_metric_info.all_cols.LAST LOOP IF UPPER(p_metric_info.all_cols(i).name) = UPPER(p_violation_context(rec)) THEN l_metric_type := p_metric_info.all_cols(i).value ; EXIT ; END IF ; END LOOP ; l_viol_ctxt_list(l_viol_ctxt_list.count) := mgmt_viol_name_value(p_violation_context(rec),l_metric_type, p_metric_value.metric_value(p_violation_context(rec)) ); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(' '|| p_violation_context(rec)||'='|| p_metric_value.metric_value(p_violation_context(rec))|| ' Type='||l_metric_type, G_MODULE_NAME) ; END IF ; END LOOP ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(' log_violations:Violation Context End',G_MODULE_NAME) ; END IF ; END construct_violation_context; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' log_violations:Enter (key='||p_result_key_value|| ') coll='||p_policy_rec.coll_name||':', G_MODULE_NAME) ; END IF ; get_violation_status(p_target_guid=>p_policy_rec.target_guid, p_policy_guid=>p_policy_rec.policy_guid, p_coll_name=>p_policy_rec.coll_name, p_result_key_value=>p_result_key_value, p_num_occurrences=>p_policy_rec.num_occurrences, p_crit_counter=>l_crit_counter, p_warn_counter=>l_warn_counter, p_info_counter=>l_info_counter, p_exempt_code=>l_exempt_code, p_exempt_until=>l_exempt_until, p_last_violation_guid=>l_last_violation_guid, p_last_violation_level=>l_last_violation_level ) ; get_new_violation_status(p_crit_violation =>p_crit_violation, p_warn_violation =>p_warn_violation, p_info_violation =>p_info_violation, p_num_occurrences => p_policy_rec.num_occurrences, p_last_violation_level => l_last_violation_level, p_crit_counter => l_crit_counter, p_warn_counter => l_warn_counter, p_info_counter => l_info_counter, p_new_violation_level => l_new_violation_level ) ; IF ( l_new_violation_level = 0 AND l_last_violation_level != MGMT_GLOBAL.G_SEVERITY_CLEAR) OR l_new_violation_level NOT IN (0,MGMT_GLOBAL.G_SEVERITY_CLEAR) THEN p_violation_counter := p_violation_counter + 1 ; END IF ; IF l_new_violation_level != 0 THEN -- construct the violation context ( local block) IF p_construct_context THEN construct_violation_context ; END IF ; IF l_new_violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR THEN l_message_template:= NVL(p_policy_rec.clear_message,p_policy_rec.message); l_message_nlsid := NVL(p_policy_rec.clear_message_nlsid, p_policy_rec.message_nlsid) ; ELSE l_message_template := p_policy_rec.message ; l_message_nlsid := p_policy_rec.message_nlsid ; END IF ; format_severity_message (p_target_name=>p_target_name, p_metric_info=>p_metric_info, p_condition_type=>p_policy_rec.condition_type, p_condition=>p_policy_rec.condition, p_msg_template=>l_message_template, p_key_value=>p_result_key_value, p_num_occurences=>p_policy_rec.num_occurrences, p_value=>nvl(p_string_value,p_value), p_violation_level=>l_new_violation_level, p_metric_value=>p_metric_value, p_param_values=>p_policy_rec.param_values, p_message=>l_message, p_nls_params=>l_nls_params ) ; -- -- Log violations -- BEGIN --notification should be ignored for configuration standard policy IF (p_policy_rec.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_CS_POLICY) THEN l_notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE; END IF; MGMT_VIOLATION.log_violation( p_target_guid=>p_policy_rec.target_guid, p_policy_guid=>p_policy_rec.policy_guid, p_key_value=>p_result_key_value, p_collection_timestamp=>p_policy_rec.evaluation_date, p_violation_level=>l_new_violation_level, p_violation_type=>p_policy_rec.violation_type, p_value=>p_value, p_string_value=>p_string_value, p_cfg_coll_name=>p_policy_rec.coll_name, p_cfg_key_value=>p_policy_rec.key_value, p_cfg_key_operator=>p_policy_rec.key_operator, p_violation_ctxt_list=>l_viol_ctxt_list, p_message=>l_message, p_message_nlsid=>l_message_nlsid, p_message_params=>l_nls_params, p_notification_status=>l_notification_status ) ; -- violation level is part of primary key so we can get -- into duplicate records rarely, we will ignore them EXCEPTION WHEN MGMT_GLOBAL.duplicate_record THEN NULL ; END ; l_bootstrap := FALSE ; ELSIF l_last_violation_level != MGMT_GLOBAL.G_SEVERITY_CLEAR THEN l_bootstrap := FALSE ; END IF ; IF l_new_violation_level != MGMT_GLOBAL.G_SEVERITY_CLEAR AND ( l_exempt_code = MGMT_GLOBAL.G_SUPPRESS_NEXTEVAL OR ( l_exempt_code = MGMT_GLOBAL.G_SUPPRESS_DATE AND l_exempt_until <= p_policy_rec.evaluation_date) ) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(' log_violations:Removing Exemption',G_MODULE_NAME) ; END IF ; MGMT_VIOLATION.remove_violation_suppression(l_last_violation_guid) ; END IF ; update_policy_eval_detail(p_target_guid=>p_policy_rec.target_guid, p_policy_guid=>p_policy_rec.policy_guid, p_coll_name=>p_policy_rec.coll_name, p_violation_type=>p_policy_rec.violation_type, p_collection_timestamp=>p_policy_rec.evaluation_date, p_result_key_value=>p_result_key_value, p_cfg_key_value=>p_policy_rec.key_value, p_cfg_key_operator=>p_policy_rec.key_operator, p_evaluation_date=>p_policy_rec.evaluation_date, p_crit_counter=>l_crit_counter, p_warn_counter=>l_warn_counter, p_info_counter=>l_info_counter, p_last_error_message=> NULL, p_bootstrap=>l_bootstrap) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' log_violations:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.INFO(' log_violations:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm|| ' when logging violations'); END IF ; END log_violation; -- -- Internal Procedure to clear the keys which are missing in current result set -- -- If there is a current violation for a key and it is missing in the current -- metric result set, it will be cleared --Example: -- If Target T1, Policy P1 had 5 keys ( K1,K2,...K5), last 3 of them -- (K3,K4,K5)in violation. -- If the current metric evaluation sends K2 to K4 , we will clear K5. -- PROCEDURE clear_missing_keys(p_policy_rec IN EM_POLICY_EVAL_REC, p_metric_info IN EM_METRIC_EVAL.METRIC_INFO_REC, p_coll_name IN VARCHAR2, p_target_name IN VARCHAR2, p_found_keys IN mgmt_medium_string_table ) IS l_missing_keys mgmt_medium_string_table := mgmt_medium_string_table(); l_viol_ctx mgmt_short_string_array := mgmt_short_string_array() ; l_viol_counter NUMBER := 0 ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('clear_missing_keys:Enter',G_MODULE_NAME) ; END IF ; IF p_metric_info.keys_from_mult_colls = MGMT_GLOBAL.G_TRUE THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('clear_missing_keys:Exit mult colls',G_MODULE_NAME) ; END IF ; RETURN ; END IF ; IF p_found_keys IS NULL OR p_found_keys.COUNT = 0 THEN SELECT result_key_value BULK collect INTO l_missing_keys FROM mgmt_policy_assoc_eval_details WHERE target_guid = p_policy_rec.target_guid AND policy_guid = p_policy_rec.policy_guid AND coll_name = p_policy_rec.coll_name ; ELSE SELECT result_key_value BULK collect INTO l_missing_keys FROM mgmt_policy_assoc_eval_details WHERE target_guid = p_policy_rec.target_guid AND policy_guid = p_policy_rec.policy_guid AND coll_name = p_policy_rec.coll_name AND result_key_value NOT IN (SELECT column_value FROM TABLE(CAST(p_found_keys AS mgmt_medium_string_table))); END IF ; IF l_missing_keys IS NOT NULL AND l_missing_keys.COUNT > 0 THEN FOR i in l_missing_keys.FIRST..l_missing_keys.LAST LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('clear_missing_keys:Target='|| p_policy_rec.target_guid||' Policy='||p_policy_rec.policy_guid|| ' Key='||l_missing_keys(i), G_MODULE_NAME); END IF ; BEGIN -- follow the normal log violation so that details table is updated -- we treat the missing record as if it resulted in a clear violation log_violation(p_policy_rec=>p_policy_rec, p_target_name=>p_target_name, p_result_key_value=>l_missing_keys(i), p_crit_violation=>FALSE, p_warn_violation=>FALSE, p_info_violation=>FALSE, p_metric_info=>p_metric_info, p_metric_value=>mgmt_metric_value_obj.new (p_policy_rec.target_guid,null,null,null), p_value=>null, p_string_value=>null, p_violation_counter=>l_viol_counter, p_violation_context=>l_viol_ctx, p_construct_context=>FALSE) ; -- violation level is part of primary key so we can get -- into duplicate records rarely, we will ignore them EXCEPTION WHEN MGMT_GLOBAL.duplicate_record THEN NULL ; END ; END LOOP ; FORALL i IN l_missing_keys.FIRST..l_missing_keys.LAST DELETE mgmt_policy_assoc_eval_details WHERE target_guid = p_policy_rec.target_guid AND policy_guid = p_policy_rec.policy_guid AND coll_name = p_coll_name AND result_key_value = l_missing_keys(i) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('clear_missing_keys:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('clear_missing_keys:Exit exception '||sqlerrm,G_MODULE_NAME); END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, substr(sqlerrm,12,100)||' (clear_missing_keys)') ; END clear_missing_keys ; -- --parse_condition_text -- --Purpose -- parse the condition text and return the cursors -- PROCEDURE parse_condition_text(p_condition_text IN VARCHAR2, p_crit_cur IN OUT NUMBER, p_warn_cur IN OUT NUMBER, p_info_cur IN OUT NUMBER ) IS l_sql_text varchar2(4000) := 'select 1 from dual where '|| p_condition_text ; BEGIN IF INSTR(p_condition_text,';') > 0 THEN EMDW_LOG.DEBUG('parse_condition_text: contains ;',G_MODULE_NAME) ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, ' Invalid condition text (parse condition text)') ; END IF ; IF p_crit_cur IS NULL OR p_warn_cur IS NULL OR p_info_cur IS NULL THEN p_crit_cur := DBMS_SQL.OPEN_CURSOR ; p_warn_cur := DBMS_SQL.OPEN_CURSOR ; p_info_cur := DBMS_SQL.OPEN_CURSOR ; END IF ; DBMS_SQL.PARSE(p_crit_cur,l_sql_text,DBMS_SQL.NATIVE) ; DBMS_SQL.PARSE(p_warn_cur,l_sql_text,DBMS_SQL.NATIVE) ; DBMS_SQL.PARSE(p_info_cur,l_sql_text,DBMS_SQL.NATIVE) ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.INFO('bind_policy_param:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, ' Invalid condition text (parse condition text)') ; END parse_condition_text; -- eval_condition_text -- -- Purpose -- evaluate the condition text, the condition text should already have been -- parsed and the p_crit_cur,p_warn_cur,p_info_cur returned -- -- Parameters -- p_param_values : Parameter value array -- p_metric_value : metric value record -- p_bind_columns : Bind Column Name And Type ( Param or Metric) -- p_crit_cur : Cursor returned for critical evaluation -- p_warn_cur : Cursor returned for warning evaluation -- p_info_cur : Cursor returned for info evaluation -- p_crit_violation : TRUE if violation else FALSE -- p_warn_violation : TRUE if violation else FALSE -- p_info_violation : TRUE if violation else FALSE -- PROCEDURE eval_condition_text(p_param_values IN mgmt_policy_param_val_array, p_metric_value IN mgmt_metric_value_obj, p_bind_columns IN mgmt_namevalue_array, p_metric_columns IN mgmt_namevalue_array, p_violation_level IN NUMBER, p_crit_cur IN NUMBER, p_warn_cur IN NUMBER, p_info_cur IN NUMBER, p_crit_violation OUT BOOLEAN, p_warn_violation OUT BOOLEAN, p_info_violation OUT BOOLEAN) IS l_string_value mgmt_metrics_raw.string_value%type ; l_num_value mgmt_metrics_raw.value%type ; l_param_rec number ; l_param_columns NUMBER := 0 ; FUNCTION is_number(p_column_name IN VARCHAR2, p_all_cols IN mgmt_namevalue_array) RETURN BOOLEAN IS l_is_number BOOLEAN := FALSE ; BEGIN FOR i IN p_metric_columns.FIRST..p_metric_columns.LAST LOOP IF UPPER(p_metric_columns(i).name) = UPPER(p_column_name) THEN IF p_metric_columns(i).value IN (MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, MGMT_GLOBAL.G_METRIC_TYPE_REPOS_NUMBER) THEN l_is_number := TRUE ; END IF ; EXIT ; END IF ; END LOOP ; RETURN(l_is_number) ; END is_number ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_condition_text:Enter', G_MODULE_NAME) ; END IF ; IF p_bind_columns.count > 0 THEN FOR i IN p_bind_columns.first..p_bind_columns.last LOOP IF p_bind_columns(i).value = G_PARAM_COLUMN THEN l_param_columns := l_param_columns + 1 ; -- Find the parameter in the param list l_param_rec := null ; IF p_param_values IS NOT NULL AND p_param_values.COUNT >0 THEN FOR j IN p_param_values.first..p_param_Values.last LOOP IF p_param_values(j).param_name = p_bind_columns(i).name THEN l_param_rec := j ; exit ; END IF ; END LOOP ; END IF ; -- Bind the policy parameters IF l_param_rec is NOT NULL THEN dbms_sql.bind_variable(p_crit_cur,p_bind_columns(i).name, p_param_values(l_param_rec).crit_threshold) ; dbms_sql.bind_variable(p_warn_cur,p_bind_columns(i).name, p_param_values(l_param_rec).warn_threshold) ; dbms_sql.bind_variable(p_info_cur,p_bind_columns(i).name, p_param_values(l_param_rec).info_threshold) ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,'Policy parameter ' ||p_bind_columns(i).name|| ' Not found (eval_condition_text)') ; END IF ; ELSE -- Bind metric values IF is_number(p_bind_columns(i).name,p_metric_columns) THEN l_num_value := to_number( p_metric_value.metric_value(p_bind_columns(i).name)); dbms_sql.bind_variable(p_crit_cur,p_bind_columns(i).name,l_num_value) ; dbms_sql.bind_variable(p_warn_cur,p_bind_columns(i).name,l_num_value) ; dbms_sql.bind_variable(p_info_cur,p_bind_columns(i).name,l_num_value) ; ELSE l_string_value := p_metric_value.metric_value(p_bind_columns(i).name); dbms_sql.bind_variable(p_crit_cur,p_bind_columns(i).name,l_string_value) ; dbms_sql.bind_variable(p_warn_cur,p_bind_columns(i).name,l_string_value) ; dbms_sql.bind_variable(p_info_cur,p_bind_columns(i).name,l_string_value) ; END IF ; END IF ; END LOOP; END IF ; IF l_param_columns > 0 THEN p_crit_violation := (dbms_sql.execute_and_fetch(p_crit_cur) !=0); p_warn_violation := (dbms_sql.execute_and_fetch(p_warn_cur) !=0); p_info_violation := (dbms_sql.execute_and_fetch(p_info_cur) !=0); ELSE p_crit_violation := (p_violation_level=MGMT_GLOBAL.G_SEVERITY_CRITICAL) AND (dbms_sql.execute_and_fetch(p_crit_cur) !=0); p_warn_violation := (p_violation_level=MGMT_GLOBAL.G_SEVERITY_WARNING) AND (dbms_sql.execute_and_fetch(p_warn_cur) !=0); p_info_violation := (p_violation_level=MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL) AND (dbms_sql.execute_and_fetch(p_info_cur) !=0); END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_condition_text:Exit Normal', G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR(' eval_condition_text:Exit Exception '||sqlerrm, G_MODULE_NAME) ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' (eval_condition_text)') ; END IF ; END eval_condition_text; -- -- eval condition text exposed for testing purpose -- PROCEDURE eval_condition_text(p_condition_text IN VARCHAR2, p_param_values IN mgmt_policy_param_val_array, p_metric_value IN mgmt_metric_value_obj, p_bind_columns IN mgmt_namevalue_array, p_metric_columns IN mgmt_namevalue_array, p_crit_violation OUT BOOLEAN, p_warn_violation OUT BOOLEAN, p_info_violation OUT BOOLEAN) IS l_crit_cur NUMBER ; l_warn_cur NUMBER ; l_info_cur NUMBER ; BEGIN parse_condition_text(p_condition_text=>p_condition_text, p_crit_cur=>l_crit_cur, p_warn_cur=>l_warn_cur, p_info_cur=>l_info_cur) ; eval_condition_text(p_param_values=>p_param_values, p_metric_value=>p_metric_value, p_bind_columns=>p_bind_columns, p_metric_columns=>p_metric_columns, p_violation_level=>MGMT_GLOBAL.G_SEVERITY_WARNING, p_crit_cur=>l_crit_cur, p_warn_cur=>l_warn_cur, p_info_cur=>l_info_cur, p_crit_violation=>p_crit_violation, p_warn_violation=>p_warn_violation, p_info_violation=>p_info_violation) ; DBMS_SQL.CLOSE_CURSOR(l_crit_cur) ; DBMS_SQL.CLOSE_CURSOR(l_warn_cur) ; DBMS_SQL.CLOSE_CURSOR(l_info_cur) ; EXCEPTION WHEN OTHERS THEN -- do not give out more detailed error messages for security reasons raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,'Invalid Condition') ; END eval_condition_text; -- eval_simple_condition -- -- Purpose -- Evaluate simple condition -- Code taken from emd_collections package -- Parameters -- p_metric_value : metric value -- p_operator : Condition operator -- p_crit_threshold : Critical threshold value -- p_warn_threshold : Warningthreshold value -- p_info_threshold : Info threshold value -- p_crit_violation : TRUE if critical violation -- p_warn_violation : TRUE if warning violation -- p_info_violation : TRUE if info violation -- PROCEDURE eval_simple_condition(p_metric_value IN NUMBER, p_operator IN NUMBER, p_crit_threshold IN NUMBER, p_warn_threshold IN NUMBER, p_info_threshold IN NUMBER, p_crit_violation OUT BOOLEAN, p_warn_violation OUT BOOLEAN, p_info_violation OUT BOOLEAN ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_simple_condition(num):Enter val='|| p_metric_value||' Op='||p_operator|| ' Thresholds='||p_crit_threshold||':'|| p_warn_threshold||':'||p_info_threshold,G_MODULE_NAME) ; END IF ; -- Existing code picked up from collections IF p_operator IS NULL OR ( p_warn_threshold IS NULL AND p_crit_threshold IS NULL AND p_info_threshold IS NULL) THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Threshold Limits are null (simple_condition)') ; END IF ; -- Check if the metric value is at a critical level IF ((p_operator = 0 AND p_metric_value > p_crit_threshold) OR (p_operator = 1 AND p_metric_value = p_crit_threshold) OR (p_operator = 2 AND p_metric_value < p_crit_threshold) OR (p_operator = 3 AND p_metric_value <= p_crit_threshold) OR (p_operator = 4 AND p_metric_value >= p_crit_threshold) OR (p_operator = 6 AND p_metric_value <> p_crit_threshold)) THEN p_crit_violation := TRUE ; ELSE p_crit_violation := FALSE ; END IF; IF ((p_operator = 0 AND p_metric_value > p_warn_threshold) OR (p_operator = 1 AND p_metric_value = p_warn_threshold) OR (p_operator = 2 AND p_metric_value < p_warn_threshold) OR (p_operator = 3 AND p_metric_value <= p_warn_threshold) OR (p_operator = 4 AND p_metric_value >= p_warn_threshold) OR (p_operator = 6 AND p_metric_value <> p_warn_threshold)) THEN p_warn_violation := TRUE ; ELSE p_warn_violation := FALSE ; END IF; IF ((p_operator = 0 AND p_metric_value > p_info_threshold) OR (p_operator = 1 AND p_metric_value = p_info_threshold) OR (p_operator = 2 AND p_metric_value < p_info_threshold) OR (p_operator = 3 AND p_metric_value <= p_info_threshold) OR (p_operator = 4 AND p_metric_value >= p_info_threshold) OR (p_operator = 6 AND p_metric_value <> p_info_threshold)) THEN p_info_violation := TRUE ; ELSE p_info_violation := FALSE ; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_simple_condition(num):Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR(' eval_simple_condition(num):Exit Exception '||sqlerrm ,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm|| ' when evaluating simple condition') ; END eval_simple_condition; -- eval_simple_condition -- -- Purpose -- Same as previous eval_simple_condition but character comparision PROCEDURE eval_simple_condition(p_metric_value IN VARCHAR2, p_operator IN NUMBER, p_crit_threshold IN VARCHAR2 , p_warn_threshold IN VARCHAR2 , p_info_threshold IN VARCHAR2, p_crit_violation OUT BOOLEAN, p_warn_violation OUT BOOLEAN, p_info_violation OUT BOOLEAN ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_simple_condition(char):Enter val='|| p_metric_value||' Op='||p_operator|| ' Thresholds='||p_crit_threshold||':'|| p_warn_threshold||':'||p_info_threshold,G_MODULE_NAME) ; END IF ; -- Existing code picked up from collections IF p_operator IS NULL OR ( (p_warn_threshold IS NULL OR p_warn_threshold = ' ') AND (p_crit_threshold IS NULL OR p_warn_threshold = ' ') AND (p_info_threshold IS NULL OR p_info_threshold = ' ') ) THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Threshold Limits are null (eval_simple_condition)') ; END IF ; -- Check if the metric value is at a critical level IF ((p_operator = 0 AND p_metric_value > p_crit_threshold) OR (p_operator = 1 AND p_metric_value = p_crit_threshold) OR (p_operator = 2 AND p_metric_value < p_crit_threshold) OR (p_operator = 3 AND p_metric_value <= p_crit_threshold) OR (p_operator = 4 AND p_metric_value >= p_crit_threshold) OR (p_operator = 5 AND INSTR(p_metric_value, p_crit_threshold) != 0 ) OR (p_operator = 6 AND p_metric_value <> p_crit_threshold) OR (p_operator = 7 AND p_metric_value LIKE p_crit_threshold)) THEN p_crit_violation := TRUE ; ELSE p_crit_violation := FALSE ; END IF; IF ((p_operator = 0 AND p_metric_value > p_warn_threshold) OR (p_operator = 1 AND p_metric_value = p_warn_threshold) OR (p_operator = 2 AND p_metric_value < p_warn_threshold) OR (p_operator = 3 AND p_metric_value <= p_warn_threshold) OR (p_operator = 4 AND p_metric_value >= p_warn_threshold) OR (p_operator = 5 AND INSTR(p_metric_value, p_warn_threshold) != 0 ) OR (p_operator = 6 AND p_metric_value <> p_warn_threshold) OR (p_operator = 7 AND p_metric_value LIKE p_warn_threshold)) THEN p_warn_violation := TRUE ; ELSE p_warn_violation := FALSE ; END IF; IF ((p_operator = 0 AND p_metric_value > p_info_threshold) OR (p_operator = 1 AND p_metric_value = p_info_threshold) OR (p_operator = 2 AND p_metric_value < p_info_threshold) OR (p_operator = 3 AND p_metric_value <= p_info_threshold) OR (p_operator = 4 AND p_metric_value >= p_info_threshold) OR (p_operator = 5 AND INSTR(p_metric_value, p_info_threshold) != 0 ) OR (p_operator = 6 AND p_metric_value <> p_info_threshold) OR (p_operator = 7 AND p_metric_value LIKE p_info_threshold)) THEN p_info_violation := TRUE ; ELSE p_info_violation := FALSE ; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_simple_condition(char):Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR(' eval_simple_condition(char):Exit Exception '||sqlerrm ,G_MODULE_NAME) ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' (eval_simple_condition)') ; END IF ; END eval_simple_condition ; -- evaluate custom_plsql -- -- Purpose -- evaluate custom pl/sql policy -- Parameters -- p_custom_plsql : Custom pl/sql to be executed -- p_metric_value : Current metric record -- p_param_values : Parameter values PROCEDURE evaluate_custom_plsql(p_custom_plsql IN VARCHAR2, p_metric_value IN mgmt_metric_value_obj, p_param_values IN mgmt_policy_param_val_array, p_crit_violation OUT BOOLEAN, p_warn_violation OUT BOOLEAN, p_info_violation OUT BOOLEAN ) IS l_crit_violation number(1):= 0 ; l_warn_violation number(1):= 0 ; l_info_violation number(1):= 0 ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_custom_plsql:Enter',G_MODULE_NAME) ; END IF ; EXECUTE IMMEDIATE 'CALL '||EM_CHECK.QUALIFIED_SQL_NAME(p_custom_plsql)|| '(:metric_value_obj,:param_values,:crit,:warn,:info)' USING IN p_metric_value, IN p_param_values, OUT l_crit_violation, OUT l_warn_violation, OUT l_info_violation ; p_crit_violation := (l_crit_violation=MGMT_GLOBAL.G_TRUE) ; p_warn_violation := (l_warn_violation=MGMT_GLOBAL.G_TRUE) ; p_info_violation := (l_info_violation=MGMT_GLOBAL.G_TRUE) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(' eval_custom_plsql:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR(' eval_custom_plsql:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||'(evaluate_custom_plsql)') ; END evaluate_custom_plsql; -- -- private procedure to debug violation results -- PROCEDURE eval_results_dump(p_crit_violation IN BOOLEAN, p_warn_violation IN BOOLEAN, p_info_violation IN BOOLEAN) IS l_violation VARCHAR2(40) ; BEGIN IF p_crit_violation THEN l_violation := ' Critical' ; END IF ; IF p_warn_violation THEN l_violation := l_violation||' Warning' ; END IF ; IF p_info_violation THEN l_violation := l_violation||' Info' ; END IF ; EMDW_LOG.DEBUG(' eval_result:'|| nvl(l_violation,'No Violation'), G_MODULE_NAME) ; END eval_results_dump; -- *************************************************************************** -- ASSUMPTION : Metric values is ordered by target_guid. If this condition is -- not met then results are not predictable. There is no checking -- to see if the targets are in order for performance reasons. -- **************************************************************************** -- Evaluate_policy -- Parameter descriptions: -- Key_cols : Contains the key column names -- met_cols : Contains metric columns and metric types -- metric_values : Metric results array -- result key array: For each record in result, this contains -- the key value ( composite key value if num_keys>1) -- key_index_array : Contains the position of key column 1, 2,3 etc -- in the name value array, see initialize_counters -- eval_mode : If it is called from collection task or directly -- with target/metric combination -- log violations : whether to log violations are not PROCEDURE evaluate_policy(p_task_id IN NUMBER, p_metric_info IN EM_METRIC_EVAL.METRIC_INFO_REC, p_target_guids IN mgmt_target_guid_array := NULL, p_metric_values IN mgmt_metric_value_array, p_result_key_array IN mgmt_medium_string_table, p_key_index_array IN g_integer_array_table, p_eval_mode IN NUMBER DEFAULT EM_METRIC_EVAL.G_TASK_EVAL_MODE, p_coll_name IN VARCHAR2 DEFAULT ' ', p_config_std_guid IN RAW DEFAULT NULL ) IS l_prev_condition_text mgmt_policies.condition%type ; l_violations_logged mgmt_policy_assoc_eval_summ.total_violations_logged%type := 0 ; l_prev_policy_guid mgmt_policies.policy_guid%type ; l_prev_target_guid mgmt_targets.target_guid%type ; l_start_pointer NUMBER; l_curr_pointer NUMBER ; l_next_pointer NUMBER ; l_policy_rules_cur g_policy_cur_type ; l_policy_rec EM_POLICY_EVAL_REC ; l_key_exception_tab mgmt_integer_table := mgmt_integer_table() ; l_temp_pointer NUMBER ; l_crit_cur NUMBER ; l_warn_cur NUMBER ; l_info_cur NUMBER ; l_evaluate_policy BOOLEAN ; l_crit_violation BOOLEAN ; l_warn_violation BOOLEAN ; l_info_violation BOOLEAN ; l_num_keys mgmt_metrics.num_keys%type ; l_dummy_text VARCHAR2(30) ; l_metric_type mgmt_metrics.metric_type%type ; l_result_key_value mgmt_policy_assoc_eval_details.result_key_value%type ; l_value mgmt_violations.value%type ; l_string_value mgmt_violations.string_value%type ; l_sysdate_utc DATE := MGMT_GLOBAL.SYSDATE_UTC ; l_evaluation_date DATE ; l_violation_context mgmt_short_string_array := mgmt_short_string_array() ; l_debug_exc NUMBER := 0; l_position NUMBER := 0 ; l_target_name mgmt_targets.target_name%type ; l_found_keys mgmt_medium_string_table := mgmt_medium_string_table() ; l_policy_start_time DATE ; l_rows_evaluated NUMBER := 0 ; PROCEDURE dump_policy_rec IS l_policy_keys VARCHAR2(4000) ; BEGIN EMDW_LOG.DEBUG('evaluate_policy:*** Policy Record Start***',G_MODULE_NAME) ; EMDW_LOG.DEBUG(' Target='||l_target_name,G_MODULE_NAME); EMDW_LOG.DEBUG(' Policy='||l_policy_rec.policy_name,G_MODULE_NAME); IF l_policy_rec.key_values IS NOT NULL AND l_policy_rec.key_values.COUNT > 0 THEN FOR i in l_policy_rec.key_values.FIRST..l_policy_rec.key_values.LAST LOOP l_policy_keys := l_policy_keys||','||l_policy_rec.key_values(i) ; END LOOP ; ELSE l_policy_keys := l_policy_rec.key_value ; END IF ; EMDW_LOG.DEBUG(' Config:Key='||l_policy_keys|| ' keyop='||l_policy_rec.key_operator|| ' exception='||l_policy_rec.is_exception, G_MODULE_NAME) ; EMDW_LOG.DEBUG(' Condition Type='||l_policy_rec.condition_type|| ' Condition='||l_policy_rec.condition|| ' Operator='||l_policy_rec.condition_operator , G_MODULE_NAME) ; IF l_policy_rec.param_values IS NOT NULL AND l_policy_rec.param_values.COUNT > 0 THEN EMDW_LOG.DEBUG(' Policy Parameters:',G_MODULE_NAME) ; FOR i IN l_policy_rec.param_values.FIRST..l_policy_rec.param_values.LAST LOOP EMDW_LOG.DEBUG(' Name='||l_policy_rec.param_values(i).param_name||' Thresholds='|| l_policy_rec.param_values(i).crit_threshold||' , '|| l_policy_rec.param_values(i).warn_threshold||' , '|| l_policy_rec.param_values(i).info_threshold||' , ', G_MODULE_NAME) ; END LOOP ; EMDW_LOG.DEBUG(' Counters:Start='||to_char(l_start_pointer)|| ' Curr='||to_char(l_curr_pointer)|| ' Next='||to_char(l_next_pointer),G_MODULE_NAME) ; END IF ; EMDW_LOG.DEBUG('evaluate_policy:*** Policy Record END ***',G_MODULE_NAME) ; EXCEPTION WHEN OTHERS THEN NULL ; END ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_policy:Enter Collection='||p_coll_name,G_MODULE_NAME) ; END IF ; IF p_metric_values IS NOT NULL AND p_metric_values.COUNT > 0 THEN l_start_pointer := p_metric_values.first ; l_curr_pointer := p_metric_values.first ; l_next_pointer := p_metric_values.first ; END IF ; -- l_metric values is NOT NULL IF p_metric_info.key_cols IS NULL OR p_metric_info.key_cols.COUNT = 0 THEN l_num_keys := 0 ; ELSE l_num_keys := p_metric_info.key_cols.COUNT ; l_key_exception_tab.extend(p_metric_values.LAST) ; END IF ; -- Get the result set of policies which are to be evaluated against result set -- Ordered by target_guid, policy_guid , key evaluation_order l_policy_rules_cur := get_policies(p_task_id=>p_task_id , p_metric_guid=>p_metric_info.metric_guid, p_target_guids=>p_target_guids, p_eval_mode=>p_eval_mode, p_num_keys=>l_num_keys, p_coll_name=>p_coll_name, p_config_std_guid=>p_config_std_guid) ; LOOP -- Loop through all configured policies for all the targets in metric result array BEGIN FETCH l_policy_rules_cur INTO l_policy_rec ; EXIT when l_policy_rules_cur%notfound ; --On Target Change, Set start_pointer to the record in metric values --Where current target from cursor is found IF l_policy_rec.target_guid != nvl(l_prev_target_guid, l_policy_rec.target_guid||'X') THEN l_curr_pointer := l_next_pointer ; get_target_info(p_target_guid=>l_policy_rec.target_guid, p_target_name=>l_target_name, p_target_date=>l_evaluation_date) ; WHILE l_curr_pointer is NOT NULL AND p_metric_values(l_curr_pointer).target_guid < l_policy_rec.target_guid LOOP l_curr_pointer := p_metric_values.next(l_curr_pointer) ; END LOOP ; l_start_pointer := l_curr_pointer ; l_policy_start_time := SYSDATE ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN dump_policy_rec ; END IF ; l_policy_rec.evaluation_date := nvl(l_evaluation_date,l_sysdate_utc) ; IF l_curr_pointer IS NOT NULL THEN -- Policy Change, reset exception counters and parse condition text IF l_policy_rec.policy_guid != nvl(l_prev_policy_guid, l_policy_rec.policy_guid||'X') THEN -- Reset violation context, force construction of violation context l_violation_context := NULL ; --Reset Exception Counters IF l_num_keys > 0 THEN l_temp_pointer := l_start_pointer; WHILE l_temp_pointer is NOT NULL AND p_metric_values(l_temp_pointer).target_guid = l_policy_rec.target_guid LOOP l_key_exception_tab(l_temp_pointer) := 0 ; l_temp_pointer := p_metric_values.next(l_temp_pointer) ; END LOOP ; l_policy_start_time := SYSDATE ; END IF ; --num_keys >0 -- parse the condition text only once IF l_policy_rec.condition_type = MGMT_GLOBAL.G_CONDITION_SQL AND l_policy_rec.condition != nvl(l_prev_condition_text, l_policy_rec.condition||'X') THEN l_prev_condition_text := l_policy_rec.condition ; parse_condition_text(l_policy_rec.condition, l_crit_cur,l_warn_cur,l_info_cur) ; END IF ; -- condition_type END IF ; --policy_guid != prev l_curr_pointer := l_start_pointer ; l_rows_evaluated := 0 ; -- Start loop evaluating Current policy record against results -- if the target guid from cursor is not in result, the config will be -- skipped WHILE p_metric_values(l_curr_pointer).target_guid=l_policy_rec.target_guid LOOP BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN IF l_num_keys >0 THEN l_debug_exc := l_key_exception_tab(l_curr_pointer) ; END IF ; EMDW_LOG.DEBUG('evaluate_policy:result record#' || to_char(l_curr_pointer)|| ' Currently excepted=' ||l_debug_exc, G_MODULE_NAME) ; END IF ; l_rows_evaluated := l_rows_evaluated + 1 ; l_policy_rec.evaluation_date := NVL(p_metric_values(l_curr_pointer).collection_timestamp, NVL(l_evaluation_date,l_sysdate_utc)) ; -- Policy to be evaluated only if -- num_keys=0 OR -- (not currently excepted and non exception key matches) l_evaluate_policy := TRUE ; IF l_num_keys >0 THEN l_result_key_value := p_result_key_array(l_curr_pointer) ; IF l_key_exception_tab(l_curr_pointer) = 1 THEN l_evaluate_policy := FALSE ; ELSE IF ( l_policy_rec.key_value = ' ' AND l_policy_rec.key_operator = 0 ) OR key_match(l_policy_rec.key_values, p_metric_values(l_curr_pointer), p_key_index_array(l_curr_pointer), l_policy_rec.key_operator, l_num_keys) THEN IF l_policy_rec.is_exception = MGMT_GLOBAL.G_TRUE THEN l_evaluate_policy := FALSE ; END IF ; -- The key is to be excepted from future evaluations -- for same policy l_key_exception_tab(l_curr_pointer) := 1 ; ELSE l_evaluate_policy := FALSE ; END IF ; END IF ; ELSE l_result_key_value :=' '; END IF; l_found_keys.extend(1) ; l_found_keys(l_found_keys.COUNT) := l_result_key_value ; IF l_evaluate_policy THEN l_crit_violation := FALSE ; l_warn_violation := FALSE ; l_info_violation := FALSE ; IF l_policy_rec.condition_type = MGMT_GLOBAL.G_CONDITION_THRESHOLD THEN FOR i IN p_metric_info.all_cols.FIRST..p_metric_info.all_cols.LAST LOOP IF UPPER(p_metric_info.all_cols(i).name) = UPPER(l_policy_rec.condition) THEN l_metric_type := p_metric_info.all_cols(i).value ; EXIT ; END IF ; END LOOP ; l_value := NULL ; l_string_value := NULL ; l_position := p_metric_values(l_curr_pointer). index_position(l_policy_rec.condition) ; IF l_metric_type IN (MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, MGMT_GLOBAL.G_METRIC_TYPE_REPOS_NUMBER) THEN -- Number Evaluation IF l_position > 0 THEN l_value := to_number(p_metric_values(l_curr_pointer). metric_values(l_position).value) ; eval_simple_condition (l_value, l_policy_rec.condition_operator, to_number(l_policy_rec.param_values(1).crit_threshold), to_number(l_policy_rec.param_values(1).warn_threshold), to_number(l_policy_rec.param_values(1).info_threshold), l_crit_violation, l_warn_violation, l_info_violation) ; END IF ; ELSE -- Character Evaluation IF l_position > 0 THEN l_string_value := p_metric_values(l_curr_pointer). metric_values(l_position).value ; eval_simple_condition (l_string_value, l_policy_rec.condition_operator, l_policy_rec.param_values(1).crit_threshold, l_policy_rec.param_values(1).warn_threshold, l_policy_rec.param_values(1).info_threshold, l_crit_violation, l_warn_violation, l_info_violation) ; END IF ; END IF ; ELSIF l_policy_rec.condition_type = MGMT_GLOBAL.G_CONDITION_SQL THEN eval_condition_text(l_policy_rec.param_values, p_metric_values(l_curr_pointer), l_policy_Rec.bind_columns, p_metric_info.all_cols, l_policy_rec.violation_level, l_crit_cur,l_warn_cur,l_info_cur, l_crit_violation, l_warn_violation, l_info_violation) ; ELSIF l_policy_rec.condition_type = MGMT_GLOBAL.G_CONDITION_PLSQL THEN evaluate_custom_plsql(l_policy_rec.condition, p_metric_values(l_curr_pointer), l_policy_rec.param_values, l_crit_violation, l_warn_violation, l_info_violation) ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Invalid Condition type '|| l_policy_rec.condition_type||' (evaluate_policy)') ; END IF ; -- For policies the violation level set at the policy level -- should override the evaluation volation levels Bug:5046257 IF l_policy_rec.policy_type = MGMT_GLOBAL.G_TYPE_POLICY THEN l_crit_violation := l_crit_violation AND (l_policy_rec.violation_level= MGMT_GLOBAL.G_SEVERITY_CRITICAL) ; l_warn_violation := l_warn_violation AND (l_policy_rec.violation_level= MGMT_GLOBAL.G_SEVERITY_WARNING) ; l_info_violation := l_info_violation AND (l_policy_rec.violation_level= MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL) ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN eval_results_dump(l_crit_violation,l_warn_violation, l_info_violation); END IF ; log_violation(p_policy_rec=>l_policy_rec, p_target_name=>l_target_name, p_result_key_value=>l_result_key_value, p_crit_violation=>l_crit_violation, p_warn_violation=>l_warn_violation, p_info_violation=>l_info_violation, p_metric_info=>p_metric_info, p_metric_value=>p_metric_values(l_curr_pointer), p_value=>l_value, p_string_value=>l_string_value, p_violation_counter=>l_violations_logged, p_violation_context=>l_violation_context, p_construct_context=>TRUE ) ; END IF ; -- if l_Evaluate_policy l_next_pointer := p_metric_values.next(l_curr_pointer) ; -- Exit evaluation for current target policy EXIT when l_next_pointer is NULL ; l_curr_pointer := l_next_pointer ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('evaluate_policy:policy evaluation error=>' ||sqlerrm,G_MODULE_NAME) ; END IF ; update_policy_eval_detail( p_target_guid=>l_policy_rec.target_guid, p_policy_guid=>l_policy_rec.policy_guid, p_violation_type=>l_policy_rec.violation_type, p_coll_name=>l_policy_rec.coll_name, p_collection_timestamp=> l_policy_rec.evaluation_date, p_result_key_value=>l_result_key_value, p_cfg_key_value=>l_policy_rec.key_value, p_cfg_key_operator=> l_policy_rec.key_operator, p_evaluation_date=>l_policy_rec.evaluation_date, p_last_error_message=> sqlerrm) ; l_next_pointer := p_metric_values.next(l_curr_pointer) ; EXIT when l_next_pointer is NULL ; l_curr_pointer := l_next_pointer ; END ; --End loop evaluating Current policy record against results END LOOP ; END IF ; -- if l_curr_pointer is NOT NULL -- Update policy evaluation summary on target or policy change IF l_policy_rec.target_guid != nvl(l_policy_rec.next_target_guid, l_policy_rec.target_guid||'1') OR l_policy_rec.policy_guid != nvl(l_policy_rec.next_policy_guid, l_policy_rec.policy_guid||'1') THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.DEBUG('evaluate_policy:Summary=>'|| 'Rows='||to_char(l_rows_evaluated)||',Violations='|| to_char(l_violations_logged), G_MODULE_NAME) ; END IF ; -- for keys which are not found in current evaluation, -- log a clear violation clear_missing_keys(p_policy_rec=>l_policy_rec, p_target_name=>l_target_name, p_metric_info=>p_metric_info, p_coll_name=>l_policy_rec.coll_name, p_found_keys=>l_found_keys ) ; EM_POLICY.UPDATE_POLICY_EVAL_SUMM( p_target_guid=>l_policy_rec.target_guid, p_policy_guid=>l_policy_rec.policy_guid, p_coll_name=>l_policy_rec.coll_name, p_evaluation_date=>l_policy_rec.evaluation_date, p_rows_evaluated=> l_rows_evaluated, p_violations_logged=>l_violations_logged ) ; IF l_policy_rec.repo_timing_enabled = MGMT_GLOBAL.G_TRUE THEN MGMT_LOG.log_performance (v_job_name_in=>G_MODULE_NAME, v_duration_in=> (SYSDATE - l_policy_start_time)*86400000, v_time_in=>SYSDATE, v_is_total_in=>'Y', v_name_in=>l_policy_rec.policy_name, v_value_in=>l_rows_evaluated) ; END IF ; l_found_keys.DELETE ; l_violations_logged := 0 ; l_rows_evaluated := 0 ; END IF ; IF l_next_pointer IS NOT NULL THEN l_next_pointer := l_curr_pointer ; END IF ; l_prev_target_guid := l_policy_rec.target_guid ; l_prev_policy_guid := l_policy_rec.policy_guid ; EXCEPTION WHEN OTHERS THEN l_prev_target_guid := l_policy_rec.target_guid ; l_prev_policy_guid := l_policy_rec.policy_guid ; IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_policy:Exception '||sqlerrm,G_MODULE_NAME); END IF ; RAISE ; END ; -- End loop for evaluating current policy record from cursor END LOOP ; IF l_policy_rules_cur%ISOPEN THEN CLOSE l_policy_rules_cur ; END IF ; IF DBMS_SQL.IS_OPEN(l_crit_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_crit_cur) ; END IF ; IF DBMS_SQL.IS_OPEN(l_warn_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_warn_cur) ; END IF ; IF DBMS_SQL.IS_OPEN(l_info_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_info_cur) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_policy:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(l_crit_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_crit_cur) ; END IF ; IF DBMS_SQL.IS_OPEN(l_warn_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_warn_cur) ; END IF ; IF DBMS_SQL.IS_OPEN(l_info_cur) THEN DBMS_SQL.CLOSE_CURSOR(l_info_cur) ; END IF ; IF l_policy_rules_cur%ISOPEN THEN CLOSE l_policy_rules_cur ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm|| ' when evaluating policy') ; END IF ; END evaluate_policy ; -- -- evaluate policy for a target/metric -- -- It is assumed that the caller is sending the metric values -- order by target_guid ( for efficiency purpose) -- if metric values is not ordered by target_guid, then the thresholds -- may not be evaluated properly -- PROCEDURE evaluate_policy(p_target_guids IN mgmt_target_guid_array, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_metric_values IN mgmt_metric_value_array ) IS l_result_key_array mgmt_medium_string_table := mgmt_medium_string_table() ; l_metric_info EM_METRIC_EVAL.METRIC_INFO_REC ; l_key_index_array g_integer_array_table := g_integer_array_table() ; l_metric_values mgmt_metric_value_array := mgmt_metric_value_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_policy(direct):Enter coll='||p_coll_name,G_MODULE_NAME) ; END IF ; IF p_target_guids IS NULL OR p_target_guids.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Target list cannot be NULL') ; END IF ; -- set repo metric to false since we do not want this to work for all metrics EM_METRIC_EVAL. get_metric_info( p_target_guid =>p_target_guids(1), p_metric_guid=>p_metric_guid, p_eval_mode => EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_metric_info=> l_metric_info, p_repo_metric=> MGMT_GLOBAL.G_FALSE ) ; IF p_metric_values IS NOT NULL AND p_metric_values.COUNT > 0 THEN l_metric_values := p_metric_values ; IF l_metric_info.num_keys >0 THEN initialize_counters(p_metric_values=>l_metric_values, p_key_columns=>l_metric_info.key_cols, p_key_index_array=>l_key_index_array, p_result_key_array=>l_result_key_array) ; END IF ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EM_METRIC_EVAL.dump_metric_results(p_metric_values) ; END IF ; EM_POLICY_EVAL.evaluate_policy ( p_target_guids=>p_target_guids, p_metric_info=>l_metric_info, p_coll_name=>p_coll_name, p_task_id=>NULL, p_metric_values=>l_metric_values, p_result_key_array=>l_result_key_array, p_key_index_array=>l_key_index_array, p_eval_mode=>EM_METRIC_EVAL.G_TARGET_EVAL_MODE ) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_policy(direct):Exit ',G_MODULE_NAME) ; END IF ; END evaluate_policy; -- -- Internal procedure to evaluate thresholds on metric raw records that -- are not inserted by agent or repository -- Example:BAM metrics, External metrics -- Note: Since we do not have access to overall record for the metric -- we cannot evaluate complex conditions or store violation context -- PROCEDURE evaluate_thresholds(p_metric_raw_records IN mgmt_metric_raw_table, p_coll_name IN VARCHAR2 ) IS l_metric_values mgmt_metric_value_array := mgmt_metric_value_array() ; l_targets mgmt_target_guid_array := mgmt_target_guid_array() ; l_target_guids mgmt_target_guid_array := mgmt_target_guid_array() ; l_prev_metric_guid mgmt_metrics.metric_guid%TYPE := MGMT_GLOBAL.G_ALL_ZERO_GUID; l_prev_target_guid mgmt_targets.target_guid%TYPE := MGMT_GLOBAL.G_ALL_ZERO_GUID; l_metric_column mgmt_metrics.metric_column%TYPE ; l_catprop mgmt_medium_string_array := mgmt_medium_string_array() ; l_prev_catprop mgmt_medium_string_array := mgmt_medium_string_array() ; l_metric_info EM_METRIC_EVAL.METRIC_INFO_REC ; l_value_array mgmt_namevalue_array := mgmt_namevalue_array() ; l_systimestamp timestamp ; l_target_sysdate DATE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_thresholds:Enter coll='||p_coll_name,G_MODULE_NAME) ; END IF ; l_catprop.extend(5) ; l_prev_catprop.extend(5) ; -- -- Process the records ordered by metric_guid,target_guid,collection_timestamp ordered -- FOR rec IN (SELECT 0 last_record,metric_guid,target_guid,collection_timestamp,key_values, value,string_value FROM TABLE(CAST(p_metric_raw_records AS MGMT_METRIC_RAW_TABLE)) UNION ALL -- A dummy record to signal the end of the records SELECT 1 last_record,null,null,null,null,null,null FROM dual ORDER BY 1,2,3 ) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('evaluate_thresholds:metric='||rec.metric_guid|| ' target='||rec.target_guid|| ' last='||rec.last_record,G_MODULE_NAME) ; END IF ; IF rec.target_guid != l_prev_target_guid AND rec.last_record = MGMT_GLOBAL.G_FALSE THEN SELECT category_prop_1,category_prop_2,category_prop_3, category_prop_4,category_prop_5, CAST(l_systimestamp AT time zone timezone_region AS DATE) INTO l_catprop(1),l_catprop(2),l_catprop(3), l_catprop(4),l_catprop(5),l_target_sysdate FROM mgmt_targets WHERE target_guid = rec.target_guid ; END IF ; -- If end of records or metric changed or target catprops changed -- then evaluate thresholds IF rec.last_record = MGMT_GLOBAL.G_TRUE OR rec.metric_guid != l_prev_metric_guid OR ( l_prev_catprop(1) IS NOT NULL AND ( l_catprop(1) != l_prev_catprop(1) OR l_catprop(2) != l_prev_catprop(2) OR l_catprop(3) != l_prev_catprop(3) OR l_catprop(4) != l_prev_catprop(4) OR l_catprop(5) != l_prev_catprop(5) ) ) THEN -- get the metric column if this is not the last record IF rec.last_record = MGMT_GLOBAL.G_FALSE THEN BEGIN SELECT metric_column INTO l_metric_column FROM mgmt_metrics WHERE metric_guid = rec.metric_guid AND ROWNUM = 1 ; EM_METRIC_EVAL. get_metric_info( p_target_guid =>rec.target_guid, p_metric_guid=>rec.metric_guid, p_eval_mode => EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_metric_info=> l_metric_info, p_repo_metric=>MGMT_GLOBAL.G_FALSE ) ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.NO_SUCH_METRIC_ERR , MGMT_GLOBAL.NO_SUCH_METRIC_ERR_M||'('||rec.metric_guid|| ') (evaluate_thresholds)') ; END ; END IF ; IF l_target_guids IS NOT NULL AND l_target_guids.COUNT > 0 THEN evaluate_policy (p_target_guids=>l_target_guids, p_metric_guid=>l_prev_metric_guid, p_coll_name => p_coll_name, p_metric_values=>l_metric_values ) ; END IF ; l_target_guids := mgmt_target_guid_array() ; l_metric_values := mgmt_metric_value_array() ; END IF ; -- convert to mgmt_metric_value_obj l_value_array := mgmt_namevalue_array() ; IF l_metric_info.num_keys > 0 AND l_metric_info.key_cols.COUNT > 0 THEN IF rec.key_values IS NULL OR rec.key_values.COUNT != l_metric_info.key_cols.COUNT THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Incorrect number of key parts passed '|| '(target='||rec.target_guid|| ')(metric='||rec.metric_guid|| ') metric has '||l_metric_info.key_cols.COUNT||' keys(evaluate_threshold)') ; END IF ; l_value_array.extend(l_metric_info.key_cols.COUNT) ; FOR i IN 1..l_metric_info.key_cols.COUNT LOOP l_value_array(i) := mgmt_namevalue_obj.NEW(l_metric_info.key_cols(i).name, rec.key_values(i)) ; END LOOP ; END IF ; l_value_array.EXTEND(1) ; l_value_array(l_value_array.COUNT) := mgmt_namevalue_obj.NEW(l_metric_column,NVL(rec.value,rec.string_value)) ; l_metric_values.EXTEND(1) ; l_metric_values(l_metric_values.COUNT) := mgmt_metric_value_obj.new (p_target_guid=>rec.target_guid, p_metric_values=>l_value_array, p_collection_timestamp=>NVL(rec.collection_timestamp,l_target_sysdate), p_status=>NULL, p_status_message=>NULL ) ; IF rec.target_guid != l_prev_target_guid OR l_target_guids.COUNT = 0 THEN l_target_guids.EXTEND(1) ; l_target_guids(l_target_guids.COUNT) := rec.target_guid ; END IF ; l_prev_target_guid := rec.target_guid ; l_prev_metric_guid := rec.metric_guid ; l_prev_catprop := l_catprop ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_thresholds:Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.INFO('evaluate_thresholds:Exit exception '||sqlerrm,G_MODULE_NAME); END IF ; IF SQLCODE IN (MGMT_GLOBAL.COLLECTION_ERR,MGMT_GLOBAL.NO_SUCH_METRIC_ERR) THEN RAISE ; ELSE RAISE_APPLICATION_ERROR(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||'(evaluate_thresholds)') ; END IF ; END evaluate_thresholds ; END em_policy_eval; / show err