Rem drv: Rem Rem $Header: metric_ui_pkgbody.sql 01-aug-2007.23:35:38 ssukavan Exp $ Rem Rem metric_ui_pkgbody.sql Rem Rem Copyright (c) 2005, 2007, Oracle. All rights reserved. Rem Rem NAME Rem metric_ui_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ssukavan 08/01/07 - Fix bug 6313049 Rem ssukavan 06/19/07 - Adding new APIs Rem ssukavan 05/24/07 - Adding a new API for setting the Acknowledge flag Rem from UI ER 5883319 Repeat Notifications Rem gsbhatia 07/01/05 - New repmgr header impl Rem rpatti 05/20/05 - rpatti_bugs_1 Rem rpatti 05/18/05 - Created Rem CREATE OR REPLACE PACKAGE BODY em_metric_ui AS PROCEDURE get_ack_details(p_violation_guid IN RAW, p_ack_by OUT VARCHAR2, p_ack_when OUT DATE) IS BEGIN BEGIN SELECT MAX(TO_DATE(to_char(TIMESTAMP,'DD-mon-yyyy hh24:mi:ss'),'DD-mon-yyyy hh24:mi:ss')) INTO p_ack_when FROM mgmt_annotation WHERE source_obj_guid = p_violation_guid AND annotation_type = ANNOTATION_TYPE; EXCEPTION WHEN NO_DATA_FOUND THEN p_ack_when := NULL; WHEN OTHERS THEN RAISE; END; BEGIN SELECT acknowledged_by INTO p_ack_by FROM mgmt_violations WHERE violation_guid = p_violation_guid; EXCEPTION WHEN NO_DATA_FOUND THEN p_ack_by := NULL; WHEN OTHERS THEN RAISE; END; END get_ack_details; PROCEDURE is_ack (p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_violation_guid IN RAW, p_user IN VARCHAR2, p_acknowledged OUT NUMBER, p_not_applicable OUT NUMBER, p_has_priv OUT NUMBER) IS l_severity_found NUMBER := 0; BEGIN -- Whether its possible to ack or not -- 1. check user has the OPER priv. -- if yes return p_has_priv = 1 -- if not return p_has_priv =0 -- 2. check if acknowledge is applicable -- if yes return p_not_applicable = 0 -- if no return p_not_applicable = 1 -- 3. check already ack -- if yes, return p_acknowledged 1 with other details -- if no, return p_acknowledged 0 with other details p_has_priv := 0; p_acknowledged := 0; p_not_applicable := 1; -- default ack not applicable p_has_priv := mgmt_user.has_priv(user_name_in => p_user, priv_name_in => MGMT_USER.OPERATOR_TARGET, target_name_in => p_target_name, target_type_in => p_target_type); SELECT count(*) INTO p_acknowledged FROM mgmt_violations WHERE acknowledged = 1 AND violation_guid = p_violation_guid; IF (p_has_priv = 1) THEN IF (p_acknowledged = 0) THEN -- This means that this is a unack. record. -- Check if this severity guid is the latest, i.e. it would be good to query from mgmt_current_severity -- for this severity_guid to find out if the record is a valid one. SELECT count(*) INTO l_severity_found FROM mgmt_current_severity WHERE severity_guid = p_violation_guid AND severity_code IN (MGMT_GLOBAL.G_SEVERITY_WARNING, MGMT_GLOBAL.G_SEVERITY_CRITICAL); IF (l_severity_found <> 0) THEN -- This means that this is a valid record p_not_applicable := 0; END IF; -- End of current open alert check END IF; -- End of acknowled check END IF; -- End of priv check END is_ack; -- PROCEDURE: get_all_metrics -- -- PURPOSE: -- This procedure returns all metrics information displayed in the all metrics -- page for a given target name and target type. -- PROCEDURE get_all_metrics(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metrics_cur_out OUT cursorType, target_info_out OUT cursorType, collection_cur_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_category_prop_1 mgmt_targets.category_prop_1%TYPE; l_category_prop_2 mgmt_targets.category_prop_2%TYPE; l_category_prop_3 mgmt_targets.category_prop_3%TYPE; l_category_prop_4 mgmt_targets.category_prop_4%TYPE; l_category_prop_5 mgmt_targets.category_prop_5%TYPE; BEGIN BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; OPEN metrics_cur_out for -- note: This may not return the correct metric rows if EMD allow empty and -- non-empty values of category_prop_* columns to exist at the same time. -- The problem will also occur during migration. SELECT /*+ INDEX(met) */ met.metric_guid, met.metric_name, met.metric_column, met.key_column, met.metric_label, met.column_label, met.metric_type, met.remote, thr.warning_threshold, thr.critical_threshold, err.collection_timestamp AS err_timestamp, cur.collection_timestamp AS collection_timestamp, DECODE(met.metric_type, 0, 0, 1, 0, 8, 0, met.metric_type) AS metric_sort_type, met.metric_label_nlsid, met.column_label_nlsid FROM mgmt_metrics met, (SELECT metric_guid, warning_threshold, critical_threshold FROM mgmt_metric_thresholds WHERE target_guid = l_target_guid AND key_value = ' ') thr, (SELECT metric_guid, MAX(collection_timestamp) AS collection_timestamp FROM mgmt_current_metric_errors WHERE target_guid = l_target_guid GROUP BY metric_guid) err, (SELECT metric_guid, MAX(collection_timestamp) AS collection_timestamp FROM mgmt_current_metrics WHERE target_guid = l_target_guid GROUP BY metric_guid) cur WHERE met.target_type = target_type_in AND met.usage_type = MGMT_GLOBAL.G_USAGE_VIEW_COLLECT AND met.is_transposed = 0 AND (met.metric_column = ' ' OR met.key_order = 0) AND met.metric_name NOT IN (SELECT /*+ INDEX(m) */ m.metric_name FROM mgmt_metrics m WHERE m.target_type = target_type_in AND m.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_RAW AND m.type_meta_ver = l_type_meta_ver AND (m.category_prop_1 = l_category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = l_category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = l_category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = l_category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = l_category_prop_5 OR m.category_prop_5 = ' ') ) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = thr.metric_guid (+) AND met.metric_guid = err.metric_guid (+) AND met.metric_guid = cur.metric_guid (+) ORDER BY met.metric_label, metric_sort_type DESC, met.column_label; -- Get target load and lastload timestamp with timezone emd_mntr.get_target_info(target_name_in, target_type_in, target_info_out); -- note that this query is from get_target_mntr_settings -- collection information OPEN collection_cur_out FOR SELECT met.metric_guid, met.metric_name, met.metric_label, met.metric_label_nlsid, met.metric_type, met.is_repository, met.is_transposed, met.has_push, met.has_pull, met.remote, met.non_thresholded_alerts, c.coll_name, c.is_enabled, c.schedule_ex, c.store_metric, c.upload_frequency, c.frequency_code, c.start_time, c.end_time, c.execution_hours, c.execution_minutes, c.interval, c.months, c.days, NVL(ci.is_required, 0) AS is_required, cip.property_name, cip.property_value, cc.credential_set_name, cc.credential_set_column, cc.credential_value FROM mgmt_metrics met, mgmt_collections c, mgmt_collection_metric_tasks cmt, (SELECT coll_name, is_required FROM mgmt_coll_items WHERE target_type = target_type_in AND type_meta_ver = l_type_meta_ver AND (category_prop_1 = l_category_prop_1 OR category_prop_1 = ' ') AND (category_prop_2 = l_category_prop_2 OR category_prop_2 = ' ') AND (category_prop_3 = l_category_prop_3 OR category_prop_3 = ' ') AND (category_prop_4 = l_category_prop_4 OR category_prop_4 = ' ') AND (category_prop_5 = l_category_prop_5 OR category_prop_5 = ' ') ) ci, (SELECT metric_guid, coll_name, property_name, property_value FROM mgmt_coll_item_properties WHERE object_guid = l_target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) cip, (SELECT collc.metric_guid, collc.coll_name, collc.credential_set_name, cred.credential_set_column, decode(cred.credential_value, NULL, NULL, decrypt(cred.credential_value) ) AS credential_value FROM mgmt_collection_credentials collc, mgmt_credentials2 cred WHERE collc.target_guid = l_target_guid AND collc.credential_guid = cred.credential_guid) cc WHERE met.target_type = target_type_in AND (met.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_TABLE OR met.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE OR met.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_RAW OR met.metric_type = MGMT_GLOBAL.G_METRIC_TYPE_EXTERNAL) AND met.type_meta_ver = l_type_meta_ver AND (met.category_prop_1 = l_category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = l_category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = l_category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = l_category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = l_category_prop_5 OR met.category_prop_5 = ' ') AND c.object_guid = l_target_guid AND c.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND c.coll_name = ci.coll_name (+) AND cmt.target_guid = l_target_guid AND cmt.coll_name = c.coll_name AND cmt.metric_guid = met.metric_guid AND cmt.metric_guid = cip.metric_guid (+) AND cmt.coll_name = cip.coll_name (+) AND cmt.metric_guid = cc.metric_guid (+) AND cmt.coll_name = cc.coll_name (+) ORDER BY met.metric_guid; END get_all_metrics; -- This procedure will be called from the Alerts Tab -- to acknowledge a list of violation guids. -- Internally, this API calls the mgmt_notification.acknowledge_alert API PROCEDURE acknowledge_alerts( p_violation_guid_array IN MGMT_USER_GUID_ARRAY, p_user IN VARCHAR2) IS BEGIN IF (p_violation_guid_array IS NOT NULL AND p_violation_guid_array.COUNT > 0) THEN FOR i_violation_count IN 1..p_violation_guid_array.COUNT LOOP mgmt_notification.acknowledge_alert( p_violation_guid => p_violation_guid_array(i_violation_count), p_acknowledged_by => p_user, p_annotation_type => ANNOTATION_TYPE, p_message => 'Acknowledged by '||p_user); END LOOP; END IF; END acknowledge_alerts; -- This API will be called from the Alerts Details and Metric Details UI. -- This information returned from this API will show an information / confirmation about -- 1. Insufficient user priv -- 2. Not applicable severity_guid if the user chooses -- an older severity_guid for acknowledgement. -- 3. A confirmation UI -- after processing the acknowledgement -- for already processed acknowledgment PROCEDURE acknowledge_alert( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_violation_guid IN RAW, p_user IN VARCHAR2, p_acknowledged OUT NUMBER, p_not_applicable OUT NUMBER, p_has_priv OUT NUMBER, p_ack_by OUT VARCHAR2, p_ack_when OUT DATE) IS l_violation_guid_array MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); BEGIN -- First check the priv of the user on the target (name and type). -- and the alert status open alert and ackn status is_ack (p_target_name => p_target_name, p_target_type => p_target_type, p_violation_guid => p_violation_guid, p_user => p_user, p_acknowledged => p_acknowledged, p_not_applicable => p_not_applicable, p_has_priv => p_has_priv); IF (p_has_priv =1 AND p_not_applicable =0 AND p_acknowledged = 0 ) THEN l_violation_guid_array.EXTEND; l_violation_guid_array(1) := p_violation_guid; em_metric_ui.acknowledge_alerts( p_violation_guid_array => l_violation_guid_array, p_user => p_user); END IF; get_ack_details(p_violation_guid => p_violation_guid, p_ack_by => p_ack_by, p_ack_when => p_ack_when); END acknowledge_alert; -- This API will be called from the Alerts Details. -- This information returned from this API will -- 1. Not show the ack button for insufficient priv'd user. -- 2. Not show the ack button for older severity_guid for acknowledgement. -- 3. The ack attributes for already acknowledged violation. Not show the ack button. -- 4. Will show the ack button for non-ackd current violation. PROCEDURE get_acknowledge_details( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_violation_guid IN RAW, p_user IN VARCHAR2, p_acknowledged OUT NUMBER, p_not_applicable OUT NUMBER, p_has_priv OUT NUMBER, p_ack_by OUT VARCHAR2, p_ack_when OUT DATE) IS BEGIN is_ack (p_target_name => p_target_name, p_target_type => p_target_type, p_violation_guid => p_violation_guid, p_user => p_user, p_acknowledged => p_acknowledged, p_not_applicable => p_not_applicable, p_has_priv => p_has_priv); IF (p_acknowledged <> 0) THEN get_ack_details(p_violation_guid => p_violation_guid, p_ack_by => p_ack_by, p_ack_when => p_ack_when); END IF; END get_acknowledge_details; -- This API will be called from the Availability Staus page. -- This information returned from this API will -- 1. Not show the ack button for insufficient priv'd user. -- 2. Not show the ack button for severity_guid null for acknowledgement. -- 3. The ack attributes for already acknowledged violation. Not show the ack button. -- 4. Will show the ack button for non-ackd severity in Availability Page. PROCEDURE get_acknowledge_details( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_user IN VARCHAR2, p_severity_guid OUT mgmt_violations.violation_guid%TYPE, p_acknowledged OUT NUMBER, p_not_applicable OUT NUMBER, p_has_priv OUT NUMBER, p_ack_by OUT VARCHAR2, p_ack_when OUT DATE) IS l_target_guid mgmt_targets.target_guid%TYPE := NULL; l_current_status mgmt_current_availability.current_status%TYPE := NULL; BEGIN p_acknowledged := 0; p_not_applicable := 1; p_has_priv := 0; p_ack_by := NULL; p_ack_when := NULL; p_severity_guid := NULL; -- Get the current status from mgmt_current_availability -- If the status is down (0) and severity_code = 25, -- pass on the severity_guid (violation_guid) got in the above process -- to get_acknowledge_details. BEGIN l_target_guid := mgmt_target.get_target_guid(target_name_in => p_target_name, target_type_in => p_target_type); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,'Target '||p_target_name||':'||p_target_type||' Does not exist'); WHEN OTHERS THEN RAISE; END; BEGIN SELECT current_status, severity_guid INTO l_current_status, p_severity_guid FROM mgmt_current_availability WHERE target_guid = l_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,'Availability Data for Target '|| p_target_name||':'||p_target_type||' does not exist'); WHEN OTHERS THEN RAISE; END; IF (l_current_status IN (MGMT_GLOBAL.G_STATUS_DOWN)) THEN BEGIN SELECT severity_guid INTO p_severity_guid FROM mgmt_current_severity WHERE target_guid = l_target_guid AND collection_timestamp= (SELECT MAX(collection_timestamp) FROM mgmt_current_severity WHERE target_guid= l_target_guid AND metric_guid = (SELECT m.metric_guid FROM mgmt_targets t, mgmt_metrics m WHERE t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ') AND t.target_guid = l_target_guid AND m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN)); EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20011,'Severity Data for Target '|| p_target_name||':'||p_target_type||' does not exist'); WHEN OTHERS THEN RAISE; END; END IF; IF ((l_current_status IN (MGMT_GLOBAL.G_STATUS_DOWN, MGMT_GLOBAL.G_STATUS_UNREACHABLE, MGMT_GLOBAL.G_STATUS_ERROR)) AND (p_severity_guid IS NOT NULL)) THEN get_acknowledge_details( p_target_name => p_target_name, p_target_type => p_target_type, p_violation_guid => p_severity_guid, p_user => p_user, p_acknowledged => p_acknowledged, p_not_applicable => p_not_applicable, p_has_priv => p_has_priv, p_ack_by => p_ack_by, p_ack_when => p_ack_when ); ELSE NULL; END IF; END get_acknowledge_details; -- This API will be called from the Availability History page. -- This acknowledges the alert PROCEDURE acknowledge_avail_alert( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_violation_guid IN RAW, p_user IN VARCHAR2, p_acknowledged OUT NUMBER, p_has_priv OUT NUMBER) IS l_violation_guid_array MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_not_applicable NUMBER :=0; BEGIN p_acknowledged := 0; p_has_priv := 0; -- First check the priv of the user on the target (name and type). -- and ackn status and the severity guid is not null IF p_violation_guid IS NOT NULL THEN is_ack (p_target_name => p_target_name, p_target_type => p_target_type, p_violation_guid => p_violation_guid, p_user => p_user, p_acknowledged => p_acknowledged, p_not_applicable => l_not_applicable, p_has_priv => p_has_priv); IF (p_has_priv =1 AND p_acknowledged = 0 ) THEN l_violation_guid_array.EXTEND; l_violation_guid_array(1) := p_violation_guid; em_metric_ui.acknowledge_alerts( p_violation_guid_array => l_violation_guid_array, p_user => p_user); END IF; END IF; END acknowledge_avail_alert; end em_metric_ui; / show errors;