Rem drv: Rem Rem $Header: rca_pkgbody.sql 03-sep-2007.03:18:44 denath Exp $ Rem Rem basic_rca_pkgbody.sql Rem Rem Copyright (c) 2004, 2007, Oracle. All rights reserved. Rem Rem NAME Rem basic_rca_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem denath 07/19/07 - Fix 6219000.added error handling in peek_message Rem for RCA messages not existing in AQ but in Rem mgmt_notify_queue. Rem denath 09/01/07 - Backport denath_bug-6219000 from main Rem jriel 08/25/05 - fix NLS column label problem in comp tests Rem jriel 07/29/05 - fix query for status in alert details Rem scgrover 07/07/05 - add extended sql trace Rem gsbhatia 07/01/05 - New repmgr header impl Rem jriel 06/27/05 - add is test avail func Rem jriel 06/14/05 - fix severity lookup Rem jriel 06/13/05 - add usage type Rem jriel 06/08/05 - fix que task Rem jriel 06/02/05 - add delete task Rem jriel 05/26/05 - add outage duration Rem jriel 05/11/05 - fix metrics table ref Rem jriel 05/03/05 - no key is not null Rem jriel 03/21/05 - fix svc affect Rem chyu 03/18/05 - removing the echo off Rem jriel 03/08/05 - check for updates Rem jriel 03/02/05 - add procedures for datagen Rem jriel 01/10/05 - make columns consistent Rem jriel 01/10/05 - fix select metrics Rem jriel 01/04/05 - UI feedback Rem jriel 12/02/04 - add svc impacted Rem jriel 11/22/04 - add types for results Rem jriel 11/10/04 - add peek methods Rem jriel 11/03/04 - fix notif content Rem pmaddi 10/18/04 - Adding procedure to get the services impacted. Rem pmaddi 10/15/04 - Rem pmaddi 10/08/04 - Rem jriel 10/13/04 - add set interactive proc Rem pmaddi 09/28/04 - Adding comments for the functions. Rem jriel 10/06/04 - fill in remove callbacks Rem jriel 09/27/04 - Rem pmaddi 09/26/04 - Adding functionality to check for the target Rem availabilty. Rem pmaddi 09/25/04 - Checking if the target is a service type. Rem pmaddi 09/23/04 - Modifying queries to complete rca functionality. Rem pmaddi 08/26/04 - Rem jriel 08/30/04 - add cleanup procedures Rem pmaddi 08/19/04 - pmaddi_rca_metrix Rem pmaddi 07/29/04 - Adding more functions to rca. Rem pmaddi 07/28/04 - Created Rem SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB ON SET PAGESIZE 100 CREATE OR REPLACE PACKAGE BODY mgmt_rca AS PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_RCA_NAME, p_value); END DBMSJOB_EXTENDED_SQL_TRACE_ON; PROCEDURE purge_rca_results IS CURSOR violation_ids IS SELECT violation_guid FROM mgmt_violations v, mgmt_rca_summary s WHERE s.severity_guid = v.violation_guid (+) AND v.violation_guid IS NOT NULL; i NUMBER(1); BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_RCA_NAME); -- 1. remove RCA results for severities that no longer exist -- this happens in this job because we can not create -- a constraint between RCA data and the mgmt_violations -- table and there is no callback mechanism for purge/delete -- of severities FOR record IN violation_ids LOOP i := 0; --delete_rca_for_severity(record.violation_guid, 1); END LOOP; -- 2. remove RCA updates beyond the Nth, where N is a config -- parameter (e.g. remove oldest updates beyond the 20th). -- -- TO BE IMPLEMENTED IN A LATER RELEASE END purge_rca_results; PROCEDURE store_rca_trace(event_guid_in IN RAW, update_id_in IN NUMBER, trace_doc_in IN VARCHAR2) IS BEGIN BEGIN INSERT INTO mgmt_rca_trace (event_guid, update_id, trace_doc) VALUES (event_guid_in, update_id_in, trace_doc_in); END; END store_rca_trace; -- PURPOSE: -- This procedure returns all rca details information -- -- IN Parameters: -- severity_guid_in: RAW severity guid -- target_type_in: VARCHAR2 Target type -- OUT Parameters: -- is_service_type_out: NUMBER Flag to tell if the target is a service -- is_status_metric_out: NUMBER Flag to indicate if the severity corresponds to Response/Status metric -- rca_dep_alert_details_out: CURSOR cursor containing services impacted by the failure. -- rca_causes_alert_details_out: CURSOR cursor containing list of root causes for the failure of this service. PROCEDURE get_alert_details(severity_guid_in IN RAW, target_type_in IN VARCHAR2, root_cause_only_in IN NUMBER, is_service_type_out OUT NUMBER, is_status_metric_out OUT NUMBER, rca_dep_alert_details_out OUT cursorType, rca_causes_alert_details_out OUT cursorType) IS BEGIN is_service_type_out:=mgmt_service.implements_service_interface(target_type_in); select count(*) into is_status_metric_out from mgmt_violations v, mgmt_metrics m, mgmt_targets t where v.violation_guid = severity_guid_in and m.metric_guid = v.policy_guid and m.metric_name = mgmt_global.G_AVAIL_METRIC_NAME and m.metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN and t.target_guid = v.target_guid and m.target_type=t.target_type and t.type_meta_ver = m.type_meta_ver and (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' '); IF(is_service_type_out = SERVICE_TYPE AND is_status_metric_out>0) THEN get_failure_causes(severity_guid_in, -1, rca_causes_alert_details_out); ELSE OPEN rca_causes_alert_details_out FOR select * from dual where rownum<1; END IF; get_affected_services_bysev(severity_guid_in, root_cause_only_in, rca_dep_alert_details_out); END get_alert_details; /* * Get the RCA metric details info. This procedure returns all the relevant * information for a given target name, target type, metric name, metric column * and key value. The values returned as OUT parameters are used for display in * the Metric Details pages. */ PROCEDURE get_affected_services_bymetric(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, metric_key_value_in IN VARCHAR2, has_priv_out OUT NUMBER, page_type_in IN VARCHAR2, root_cause_only_in IN NUMBER, start_date_in IN DATE, end_date_in IN DATE, rca_metric_test_results_out OUT cursorType) IS l_days NUMBER(2); l_target_guid RAW(16); l_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_column_type VARCHAR2(16); l_end_time DATE; l_timezone_region mgmt_targets.timezone_region%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; l_count NUMBER(4); l_cause_type NUMBER(1); BEGIN dbms_output.put_line('get_rca_metrics(): IN'); -- First, determine the target_guid from the table on which the security -- policy is defined, mgmt_targets. If no data is found, we exit immediately -- since there is no need to continue (but this should never happen): BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, timezone_region 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, l_timezone_region FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN return; END; -- Then, get metric guid and metric column type BEGIN -- note: the "rownum = 1" is to ensure only one row is returned. This may -- not return the correct metric row 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 metric_guid INTO l_metric_guid FROM mgmt_metrics WHERE target_type = target_type_in AND metric_name = metric_name_in AND metric_column = metric_column_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 = ' ') AND rownum = 1; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.no_such_metric_found; END; -- check if the user has at least OPERATOR_TARGET privilege has_priv_out := mgmt_user.has_priv( mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_target_guid); -- Determine the value for the 'days' parameter depending on the page type; needed -- for the cursor returning severity data. /* IF (page_type_in = 'byDay') THEN l_days := 1; ELSIF (page_type_in = 'byWeek') THEN l_days := 7; ELSE l_days := 31; END IF; l_end_time := mgmt_global.SYSDATE_TZRGN(l_timezone_region) -l_days; */ l_cause_type := ROOT_CAUSE_TYPE; -- show root cause only IF root_cause_only_in = 0 THEN l_cause_type := 0; -- any causes END IF; -- rca_metric_test_results_out cursor contains the list of services affected -- by this metric; this should be factored into the get_svcs_aff_count_by* -- BEGIN IF((metric_name_in = mgmt_global.G_AVAIL_METRIC_NAME) AND ( metric_column_in = mgmt_global.G_AVAIL_METRIC_COLUMN)) THEN open rca_metric_test_results_out for WITH common as ( select mre1.source_guid, mre1.source_type, mt.target_name, mt.target_type, mt.timezone_region, mrs.rca_status, mrea.is_root_cause, ms.collection_timestamp, decode(ms.violation_duration, null, -1, ms.violation_duration*60) as violation_duration from mgmt_rca_event mre, mgmt_rca_event mre1,mgmt_rca_event_assoc mrea, mgmt_rca_summary mrs, mgmt_violations ms, mgmt_targets mt where mre.target_guid = l_target_guid and mre.test_guid = l_metric_guid and mrea.cause_event_guid = mre.event_guid and mrea.is_root_cause >= l_cause_type and mrea.update_id=mrs.last_update_id and mre1.event_guid=mrea.event_guid and mrs.event_guid = mre1.event_guid and ms.violation_guid = mrs.severity_guid and mt.target_guid = ms.target_guid) SELECT sev.message as message,sev.collection_timestamp as timestamp, sev.message_nlsid as msgNlsid, sev.message_params as msgParams,common.target_name as targetName, common.target_type as targetType, common.source_guid as severityGuid, common.rca_status as rcaStatus, common.is_root_cause as isRootCause, common.violation_duration as violation_duration from mgmt_severity sev, common where sev.severity_guid = common.source_guid and common.source_type = STATUS_SEVERITY -- and sev.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days) and sev.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in and sev.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region) -- end_date_in union all select mrtr.message as message,mrtr.collection_timestamp as timestamp,mrtr.message_nlsid as msgNlsid, mrtr.message_params as msgParams,common.target_name as targetname, common.target_type as targetType,common.source_guid as severityGuid, common.rca_status as rcaStatus, common.is_root_cause as isRootCause, common.violation_duration as violation_duration from mgmt_rca_test_result mrtr,common where common.source_type = STATUS_TEST and mrtr.result_guid = common.source_guid --and mrtr.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days); and mrtr.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in and mrtr.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region); -- end_date_in ELSE open rca_metric_test_results_out for WITH common as ( select mre1.source_guid, mre1.source_type, mt.target_name, mt.target_type, mt.timezone_region, mrs.rca_status, mrea.is_root_cause, ms.collection_timestamp, decode(ms.violation_duration, null, -1, ms.violation_duration*60) as violation_duration from mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_rca_event_assoc mrea, mgmt_rca_summary mrs, mgmt_violations ms, mgmt_rca_metric_test mrmt,mgmt_targets mt where mre.target_guid = l_target_guid and mrmt.target_guid = l_target_guid and mrmt.metric_guid=l_metric_guid and mrmt.key_value=metric_key_value_in and mre.test_guid = mrmt.metric_test_guid and mrea.cause_event_guid = mre.event_guid and mre1.event_guid=mrea.event_guid and mrea.is_root_cause >= l_cause_type and mrea.update_id=mrs.last_update_id and mrs.event_guid = mre1.event_guid and ms.violation_guid = mrs.severity_guid and mt.target_guid = ms.target_guid) SELECT sev.message as message, sev.collection_timestamp as timestamp,sev.message_nlsid as msgNlsid, sev.message_params as msgParams, common.target_name as targetName, common.target_type as targetType, common.source_guid as severityGuid, common.rca_status as rcaStatus, common.is_root_cause as isRootCause, common.violation_duration as violation_duration from mgmt_severity sev, common where sev.severity_guid = common.source_guid and common.source_type = STATUS_SEVERITY --and sev.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days) and sev.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in and sev.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region) -- end_date_in union all select mrtr.message as message,mrtr.collection_timestamp as timestamp,mrtr.message_nlsid as msgNlsid, mrtr.message_params as msgParams,common.target_name as targetName, common.target_type as targetType,common.source_guid as severityGuid, common.rca_status as rcaStatus, common.is_root_cause as isRootCause, common.violation_duration as violation_duration from mgmt_rca_test_result mrtr,common where common.source_type = STATUS_TEST and mrtr.result_guid = common.source_guid --and mrtr.collection_timestamp > (mgmt_global.SYSDATE_TZRGN(common.timezone_region)-l_days); and mrtr.collection_timestamp >= mgmt_global.adjust_tz(start_date_in, l_timezone_region, common.timezone_region) -- start_date_in and mrtr.collection_timestamp <= mgmt_global.adjust_tz(end_date_in, l_timezone_region, common.timezone_region); -- end_date_in END IF; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('No data found for rca.'); END; END get_affected_services_bymetric; -- -- PURPOSE: -- This procedure returns all the information to be displayed on the availabilty page -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- -- OUT Parameters: -- is_service_down: NUMBER is service down flag -- is_service_type_out: NUMBER is service type flag -- rca_status_out: NUMBER Flag indicating the rca operation status(If rca has been run fo this severity). -- rca_no_of_causes_out: NUMBER is No of root causes identified -- rca_no_of_serv_affected_out: NUMBER No of services impacted -- PROCEDURE get_availability(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, root_cause_only_in IN NUMBER, severity_guid_out OUT VARCHAR2, is_service_down OUT NUMBER, is_service_type_out OUT NUMBER, rca_status_out OUT NUMBER, rca_no_of_causes_out OUT NUMBER, rca_no_of_serv_affected_out OUT NUMBER) IS l_severity_guid RAW(16) := null; BEGIN get_service_availability(target_name_in,target_type_in,is_service_down); if(is_service_down=0)then is_service_type_out:=mgmt_service.implements_service_interface(target_type_in); select get_severity(target_name_in,target_type_in) into l_severity_guid from dual; if (l_severity_guid is not null) then select rawtohex(l_severity_guid) into severity_guid_out from dual; select count(*) into rca_status_out from mgmt_rca_summary where severity_guid=l_severity_guid; if(is_service_type_out = SERVICE_TYPE) then select cause_count into rca_no_of_causes_out from mgmt_rca_run r, mgmt_rca_summary s where s.severity_guid = l_severity_guid and r.event_guid = s.event_guid and r.update_id = s.last_update_id; else rca_no_of_causes_out:=-1; end if; get_svcs_aff_count_bysev(l_severity_guid, root_cause_only_in, rca_no_of_serv_affected_out); else select '' into severity_guid_out from dual; rca_status_out:=-1; rca_no_of_causes_out:=-1; rca_no_of_serv_affected_out:=-1; end if; end if; END get_availability; -- -- PURPOSE: --gets the list of root causes identified as the possible reasons for the failed service. -- -- IN Parameters: -- severity_guid_in: RAW severity guid -- -- OUT Parameters: -- rca_causeoffailure_cur_out: CURSOR cursor containing list of root causes. -- PROCEDURE get_failure_causes(severity_guid_in IN RAW, update_id_in IN NUMBER, rca_causeoffailure_cur_out OUT cursorType) IS BEGIN /* OPEN rca_causeoffailure_cur_out FOR with common as (select target_name, target_type, mre.source_type,mre.source_guid from mgmt_rca_summary mrs, mgmt_rca_event_assoc mrea, mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_targets mt where mrs.severity_guid = severity_guid_in and mre1.event_guid = mrs.event_guid and mre1.event_guid = mrea.event_guid and mre.event_guid = mrea.cause_event_guid and mrea.update_id = mrs.last_update_id and mrea.is_root_cause = ROOT_CAUSE_TYPE and mt.target_guid = mre.target_guid ) select res.message as message,cm.target_name as targetName,cm.target_type as targetType,res.collection_timestamp as timestamp, res.message_nlsid as msgNlsid, res.message_params as msgParams from mgmt_rca_test_result res,common cm where (cm.source_type = STATUS_TEST or cm.source_type = METRIC_TEST) and res.result_guid = cm.source_guid union all select sev.message as message,cm.target_name as targetName,cm.target_type as targetType,sev.collection_timestamp as timestamp, sev.message_nlsid as msgNlsid, sev.message_params as msgParams from mgmt_severity sev,common cm where (cm.source_type = STATUS_SEVERITY or cm.source_type = METRIC_SEVERITY) and sev.severity_guid = cm.source_guid; */ /* SELECT res.message message, cm.target_name targetname, cm.target_type targettype, res.collection_timestamp timestamp, res.message_nlsid msgnlsid, res.message_params msgparams, cm.event_action eventaction, cm.source_type sourcetype, HEXTORAW(NULL) severityguid, tes.key_value keyvalue, met.metric_name metname, met.metric_column metcolumn FROM mgmt_rca_test_result res, mgmt_metrics met, mgmt_targets t, mgmt_rca_metric_test tes, (SELECT mt.target_name, mt.target_type, mt.target_guid, mre.source_type, mre.source_guid, mre.event_action FROM mgmt_rca_summary mrs, mgmt_rca_event_assoc mrea, mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_targets mt WHERE mrs.severity_guid = severity_guid_in AND mre1.event_guid = mrs.event_guid AND mre1.event_guid = mrea.event_guid AND mre.event_guid = mrea.cause_event_guid AND ((update_id_in=-1 and mrea.update_id = mrs.last_update_id) OR (update_id_in<>-1 and mrea.update_id = update_id_in)) AND mrea.is_root_cause = ROOT_CAUSE_TYPE AND mt.target_guid = mre.target_guid) cm WHERE (cm.source_type = STATUS_TEST OR cm.source_type = METRIC_TEST) AND res.result_guid = cm.source_guid AND ((cm.source_type = METRIC_TEST and res.metric_test_guid = tes.metric_test_guid) OR (cm.source_Type = STATUS_TEST and res.metric_test_guid = met.metric_guid)) AND t.target_guid = cm.target_guid AND met.metric_guid = tes.metric_guid AND met.type_meta_ver = t.type_meta_ver AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ') UNION ALL SELECT sev.message message, cm.target_name targetname, cm.target_type targettype, sev.collection_timestamp timestamp, sev.message_nlsid msgnlsid, sev.message_params msgparams, cm.event_action eventaction, cm.source_type sourcetype, sev.severity_guid severityguid, sev.key_value keyvalue, met.metric_name metname, met.metric_column metcolumn FROM mgmt_severity sev, mgmt_metrics met, mgmt_targets t, (SELECT mt.target_name, mt.target_type, mt.target_guid, mre.source_type, mre.source_guid, mre.event_action FROM mgmt_rca_summary mrs, mgmt_rca_event_assoc mrea, mgmt_rca_event mre, mgmt_rca_event mre1, mgmt_targets mt WHERE mrs.severity_guid = severity_guid_in AND mre1.event_guid = mrs.event_guid AND mre1.event_guid = mrea.event_guid AND mre.event_guid = mrea.cause_event_guid AND ((update_id_in=-1 and mrea.update_id = mrs.last_update_id) OR (update_id_in<>-1 and mrea.update_id = update_id_in)) AND mrea.is_root_cause = ROOT_CAUSE_TYPE AND mt.target_guid = mre.target_guid) cm WHERE (cm.source_type = STATUS_SEVERITY OR cm.source_type = METRIC_SEVERITY) AND sev.severity_guid = cm.source_guid AND t.target_guid = cm.target_guid AND met.metric_guid = sev.metric_guid AND met.type_meta_ver = t.type_meta_ver AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' '); */ /* OPEN rca_causeoffailure_cur_out FOR SELECT t.target_name targetname, t.target_type targettype, e.source_type sourcetype, e.event_action eventaction, r.result_guid resultguid, tes.key_value rkeyvalue, r.collection_timestamp rtimestamp, r.message rmessage, r.message_nlsid rmsgnlsid, r.message_params rmsgparams, v.violation_guid severityguid, v.key_value vkeyvalue, v.collection_timestamp vtimestamp, v.message vmessage, v.message_nlsid vmsgnlsid, v.message_params vmsgparams, met.metric_name metname, met.metric_column metcolumn FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_summary s, mgmt_rca_test_result r, mgmt_rca_metric_test tes, mgmt_violations v, mgmt_targets t, mgmt_metrics met WHERE s.severity_guid = severity_guid_in AND a.event_guid = s.event_guid AND a.is_root_cause = ROOT_CAUSE_TYPE AND ((update_id_in=-1 and a.update_id = s.last_update_id) OR (update_id_in<>-1 and a.update_id = update_id_in)) AND e.event_guid = a.cause_event_guid AND e.source_guid = r.result_guid (+) AND e.source_guid = v.violation_guid (+) AND t.target_guid = e.target_guid AND r.metric_test_guid = tes.metric_test_guid (+) AND ( (e.source_type = METRIC_TEST AND met.metric_guid = tes.metric_guid ) OR (e.source_type = STATUS_TEST AND met.metric_guid = r.metric_test_guid) OR ((e.source_type = STATUS_SEVERITY OR e.source_type = METRIC_SEVERITY) AND met.metric_guid = v.policy_guid) ) AND met.type_meta_ver = t.type_meta_ver AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' '); */ OPEN rca_causeoffailure_cur_out FOR SELECT t.target_name targetname, t.target_type targettype, e.source_type sourcetype, e.event_action eventaction, r.result_guid resultguid, tes.key_value rkeyvalue, r.collection_timestamp rtimestamp, r.message rmessage, r.message_nlsid rmsgnlsid, r.message_params rmsgparams, v.violation_guid severityguid, v.key_value vkeyvalue, v.collection_timestamp vtimestamp, v.message vmessage, v.message_nlsid vmsgnlsid, v.message_params vmsgparams, met.metric_name metname, met.metric_column metcolumn FROM mgmt_rca_event e, mgmt_rca_test_result r, mgmt_rca_metric_test tes, mgmt_violations v, mgmt_targets t, mgmt_metrics met, ( SELECT DISTINCT e.event_guid FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_summary s WHERE s.severity_guid = severity_guid_in AND a.event_guid = s.event_guid AND a.is_root_cause = ROOT_CAUSE_TYPE AND ((update_id_in=-1 and a.update_id = s.last_update_id) OR (update_id_in<>-1 and a.update_id = update_id_in)) AND e.event_guid = a.cause_event_guid ) x WHERE e.event_guid = x.event_guid AND e.source_guid = r.result_guid (+) AND e.source_guid = v.violation_guid (+) AND t.target_guid = e.target_guid AND r.metric_test_guid = tes.metric_test_guid (+) AND ( (e.source_type = METRIC_TEST AND met.metric_guid = tes.metric_guid) OR (e.source_type = STATUS_TEST and met.metric_guid = r.metric_test_guid) OR (( e.source_type = STATUS_SEVERITY or e.source_type = METRIC_SEVERITY) and met.metric_guid = v.policy_guid)) AND met.target_type = t.target_type AND met.type_meta_ver = t.type_meta_ver AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' '); END get_failure_causes; -- -- PURPOSE: -- gets the list of services affected by a specific severity -- -- IN Parameters: -- severity_guid_in: RAW severity guid -- -- OUT Parameters: -- rca_affected_services_cur_out: CURSOR cursor containing list of services affected by this services' failure. -- PROCEDURE get_affected_services_bysev(severity_guid_in IN RAW, root_cause_only_in IN NUMBER, rca_affected_services_cur_out OUT cursorType) IS l_cause_type NUMBER(1); BEGIN l_cause_type := ROOT_CAUSE_TYPE; IF root_cause_only_in = 0 THEN l_cause_type := 0; END IF; OPEN rca_affected_services_cur_out FOR SELECT rs.severity_guid as severityGuid, rs.rca_status as rcaStatus, s.message as message, t.target_name as targetName, t.target_type as targetType, decode(s.violation_duration, null, -1, s.violation_duration*60) as violation_duration, s.collection_timestamp as timestamp, s.message_nlsid as msgNlsid, s.message_params as msgParams, x.is_root_cause as isRootCause FROM ( SELECT DISTINCT(rs.severity_guid) AS svc_sev_guid, a.is_root_cause AS is_root_cause FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs WHERE e.source_guid = severity_guid_in AND a.cause_event_guid = e.event_guid AND a.is_root_cause >= l_cause_type AND rr.event_guid = a.event_guid AND rr.update_id = a.update_id AND rs.event_guid = rr.event_guid ) x, mgmt_violations s, mgmt_targets t, mgmt_rca_summary rs WHERE s.violation_guid = x.svc_sev_guid AND t.target_guid = s.target_guid AND rs.severity_guid = x.svc_sev_guid; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN rca_affected_services_cur_out FOR select * from dual where rownum <1; END get_affected_services_bysev; -- -- PURPOSE: -- gets the list of services impacted by a specified target -- -- IN Parameters: -- target_name_in: target name -- target_type_in: target type -- num_of_days_in: NUMBER number of days for for which the data has to searched -- -- OUT Parameters: -- rca_affected_services_cur_out: CURSOR cursor containing list of services affected by this services' failure. -- PROCEDURE get_affected_services_byname(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, num_days_in IN NUMBER, root_cause_only_in IN NUMBER, rca_affected_services_cur_out OUT cursorType) IS l_cause_type NUMBER(1); BEGIN l_cause_type := ROOT_CAUSE_TYPE; IF root_cause_only_in = 0 THEN l_cause_type := 0; END IF; IF num_days_in > 0 THEN BEGIN OPEN rca_affected_services_cur_out FOR SELECT rs.severity_guid as severityGuid, rs.rca_status as rcaStatus, s.message as message, t.target_name as targetName, t.target_type as targetType,decode(s.violation_duration, null, -1, s.violation_duration*60) as violation_duration, s.collection_timestamp as timestamp, s.message_nlsid as msgNlsid, s.message_params as msgParams, x.is_root_cause AS isRootCause FROM (SELECT DISTINCT rs.severity_guid AS svc_sev_guid, a.is_root_cause AS is_root_cause FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs, mgmt_targets t, mgmt_violations s WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND e.target_guid = t.target_guid AND s.collection_timestamp > (mgmt_global.sysdate_tzrgn(t.timezone_region) - num_days_in) AND s.violation_guid = rs.severity_guid AND a.cause_event_guid = e.event_guid AND a.is_root_cause >= l_cause_type AND rr.event_guid = a.event_guid AND rr.update_id = a.update_id AND rs.event_guid = rr.event_guid) x, mgmt_violations s, mgmt_targets t, mgmt_rca_summary rs WHERE s.violation_guid = x.svc_sev_guid AND t.target_guid = s.target_guid AND rs.severity_guid = x.svc_sev_guid; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN rca_affected_services_cur_out FOR select * from dual where rownum <1; END; ELSE -- num_days = 0; only looking for services that are currently down BEGIN OPEN rca_affected_services_cur_out FOR SELECT rs.severity_guid as severityGuid, rs.rca_status as rcaStatus, s.message as message, t.target_name as targetName, t.target_type as targetType, decode(s.violation_duration, null, -1, s.violation_duration*60) as violation_duration, s.collection_timestamp as timestamp, s.message_nlsid as msgNlsid, s.message_params as msgParams, x.is_root_cause AS isRootCause FROM (SELECT DISTINCT rs.severity_guid svc_sev_guid, a.is_root_cause AS is_root_cause FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs, mgmt_targets t, mgmt_current_violation s WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND e.target_guid = t.target_guid AND a.cause_event_guid = e.event_guid AND a.is_root_cause >= l_cause_type AND s.violation_guid = rs.severity_guid AND rr.event_guid = a.event_guid AND rr.update_id = a.update_id AND rs.event_guid = rr.event_guid AND rs.rca_status = RCA_STATUS_OPEN AND rr.update_id = rs.last_update_id) x, mgmt_violations s, mgmt_targets t, mgmt_rca_summary rs WHERE s.violation_guid = x.svc_sev_guid AND t.target_guid = s.target_guid AND rs.severity_guid = x.svc_sev_guid; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN rca_affected_services_cur_out FOR select * from dual where rownum <1; END; END IF; END get_affected_services_byname; -- -- PURPOSE: --Checks if the service is running or down . -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- -- OUT Parameters: -- current_status_out: NUMBER Flag indicating the Current status indicating the availabilty of the service. -- PROCEDURE get_service_availability(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, current_status_out OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); current_status_out := mgmt_target.get_avail_current_status( l_target_guid ); END get_service_availability; -- -- PURPOSE: --Check the permissions of the user for the service and the target -- -- IN Parameters: -- service_name_in: VARCHAR2 Service name -- service_type_in: VARCHAR2 Service type -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- -- OUT Parameters: -- service_priv_out: NUMBER numeric value inidcation if the user has the privileges to operate on the service. -- target_priv_out: NUMBER numeric value inidcation if the user has the privileges to operate on the target. -- PROCEDURE check_target_privielges(service_name_in IN VARCHAR2, service_type_in IN VARCHAR2, target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, service_priv_out OUT NUMBER, target_priv_out OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_service_guid mgmt_targets.target_guid%TYPE; BEGIN BEGIN select target_guid into l_service_guid from mgmt_targets where target_name=service_name_in and target_type=service_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; end; BEGIN SELECT target_guid INTO l_target_guid 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; target_priv_out := mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_service_guid); target_priv_out := mgmt_user.has_priv( mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, l_target_guid); END; -- -- PURPOSE: --gets the list of existing fault tests defined for the service. -- -- IN Parameters: -- service_name_in: VARCHAR2 Service name -- service_type_in: VARCHAR2 Service type -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- -- OUT Parameters: -- fault_tests_out: CURSOR cursor containing list of fault tests defined for the service. -- PROCEDURE get_fault_tests(service_name_in IN VARCHAR2, service_type_in IN VARCHAR2, target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, test_scope_in IN NUMBER, fault_tests_cur_out OUT cursorType, status_metric_cur_out OUT cursorType, comp_host_test_cur_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; l_service_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; cate_prop mgmt_category_prop_array; l_show_host_tests NUMBER(1); l_host_name VARCHAR2(256); l_scope_guid RAW(16); l_target_type VARCHAR2(64); l_host_test_scope_guid RAW(16); l_component_guid mgmt_targets.target_guid%TYPE; BEGIN BEGIN SELECT target_guid into l_service_guid FROM mgmt_targets WHERE target_name=service_name_in AND target_type=service_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'99999No service found for target name '||service_name_in||' and type '||service_type_in);--MGMT_GLOBAL.target_does_not_exist; END; BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN l_target_type := target_type_in; SELECT target_guid, target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, host_name INTO l_target_guid, l_component_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, l_host_name FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'99999No target found for target name '||target_name_in||' and type '||target_type_in); END; ELSE BEGIN l_target_type := 'host'; SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name, t.target_guid 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, l_host_name, l_component_guid FROM mgmt_targets h, mgmt_targets t WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND h.target_name = t.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'99999No target found for target name '||target_name_in||' and type '||target_type_in); END; END IF; END; -- get the scope guid = GUID("svc;comp") l_scope_guid := get_scope_guid(l_service_guid, l_component_guid, test_scope_in); SELECT mgmt_category_prop_array(l_target_type,l_type_meta_ver,l_category_prop_1,l_category_prop_2,l_category_prop_3,l_category_prop_4,l_category_prop_5) INTO cate_prop FROM DUAL; BEGIN OPEN fault_tests_cur_out for SELECT metric_name "metricName",metric_test_guid "MetricTestId",mm.metric_column "Metric", decode(threshold_source,1,rca_threshold, get_threshold(l_target_guid, metric_name, metric_column, key_value,cate_prop)) "Threshold", mrmt.operator "Operator", decode(num_keys,1,mrmt.key_value,0,'', concat_keys(num_keys,key_part1_value,key_part2_value,key_part3_value,key_part4_value,key_part5_value)) "Object", mm.column_label "collabel", mm.column_label_nlsid "colnlsid",mrmt.metric_guid "metricGuid", mm.is_transposed "transposed" FROM mgmt_rca_metric_test mrmt, mgmt_metrics mm WHERE mrmt.target_guid=l_target_guid AND mrmt.metric_guid=mm.metric_guid AND scope_guid=l_scope_guid AND scope=test_scope_in AND mm.target_type = l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 OR mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 OR mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 OR mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 OR mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 OR mm.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN OPEN fault_tests_cur_out FOR select * from dual where rownum <1; END; -- get the status metric BEGIN OPEN status_metric_cur_out FOR SELECT metric_name as "metricName", 'STATUS' as "MetricTestId", mm.metric_column as "Metric", 'STATUS' as "Threshold", 'STATUS' as "Operator", 'STATUS' as "Object", mm.column_label as "Description", mm.column_label_nlsid as "Description_NlsId", 'STATUS' as "metricGuid" FROM mgmt_metrics mm WHERE mm.metric_name = mgmt_global.G_AVAIL_METRIC_NAME AND mm.metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN AND mm.target_type = l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 OR mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 OR mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 OR mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 OR mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 OR mm.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN OPEN status_metric_cur_out FOR select * from dual where rownum <1; END; l_show_host_tests := 1; IF test_scope_in <> SCOPE_SERVICE OR target_type_in = 'host' THEN l_show_host_tests := 0; END IF; IF l_show_host_tests = 0 THEN OPEN comp_host_test_cur_out FOR SELECT l_show_host_tests AS "showCompHostTest", l_host_name AS "compHostName", 0 AS "compHostTestCount" FROM DUAL; ELSE l_host_test_scope_guid := get_scope_guid(l_service_guid, l_component_guid, SCOPE_COMPONENT); -- get the component host test info BEGIN OPEN comp_host_test_cur_out FOR SELECT l_show_host_tests AS "showCompHostTest", l_host_name AS "compHostName", x.testcount AS "compHostTestCount" FROM dual, (SELECT count(*) as testcount FROM mgmt_rca_metric_test WHERE scope = SCOPE_COMPONENT AND scope_guid = l_host_test_scope_guid) x; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN comp_host_test_cur_out FOR SELECT l_show_host_tests AS "showCompHostTest", l_host_name AS "compHostName", 0 AS "compHostTestCount" FROM DUAL; END; END IF; END get_fault_tests; -- -- PURPOSE: --gets the list of metric types defined for the target. -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- -- OUT Parameters: -- metric_types_cur_out: CURSOR cursor containing list of metric types defined for the target. PROCEDURE get_metric_types(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_types_cur_out OUT cursorType) IS BEGIN OPEN metric_types_cur_out FOR select distinct metric_type "metricType" from mgmt_metrics mm, mgmt_targets mt where mt.target_name=target_name_in and mt.target_type=target_type_in and mm.target_type=mt.target_type and mm.type_meta_ver=mt.type_meta_ver and (mm.category_prop_1=mt.category_prop_1 or mm.category_prop_1 = ' ') and (mm.category_prop_2=mt.category_prop_2 or mm.category_prop_2 = ' ') and (mm.category_prop_3=mt.category_prop_3 or mm.category_prop_3 = ' ') and (mm.category_prop_4=mt.category_prop_4 or mm.category_prop_4 = ' ') and (mm.category_prop_5=mt.category_prop_5 or mm.category_prop_5 = ' ') and metric_type in (0,1);--to be modified later END get_metric_types; -- -- PURPOSE: --gets the list of metrics defined for the target. -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- metric_type_in: NUMBER Metric Type -- -- OUT Parameters: -- metric_types_cur_out: CURSOR cursor containing list of metrics defined for the target. PROCEDURE get_metrics_for_type(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_type_in IN NUMBER, test_scope_in IN NUMBER, metrics_cur_out OUT cursorType, host_name_out OUT VARCHAR2) 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; l_target_type mgmt_targets.target_type%TYPE; l_target_name mgmt_targets.target_name%TYPE; l_target_info_cur cursorType; BEGIN BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, host_name 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, host_name_out FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in); END; ELSE BEGIN SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name 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, host_name_out FROM mgmt_targets t, mgmt_targets h WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND h.target_name = t.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in); END; END IF; IF test_scope_in = SCOPE_SERVICE THEN l_target_type := target_type_in; l_target_name := target_name_in; ELSE l_target_type := 'host'; l_target_name := host_name_out; END IF; END; get_all_metrics(l_target_name, l_target_type, metrics_cur_out); END get_metrics_for_type; PROCEDURE get_all_metrics(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metrics_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) */ 'NOT_TRANSPOSED' AS trans_key, met.metric_name, met.metric_column, met.key_column, met.metric_label, met.column_label, met.metric_type, met.is_transposed, 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, met.metric_guid, met.key_order, met.usage_type 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.remote = 0 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 (+) UNION ALL SELECT thr.key_value AS trans_key, met.metric_name, met.metric_column, met.key_column, met.metric_label, met.column_label, met.metric_type, met.is_transposed, thr.warning_threshold, thr.critical_threshold, sysdate 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, met.metric_guid, met.key_order, met.usage_type FROM mgmt_metrics met, mgmt_targets t, (SELECT key_value,target_guid,metric_guid, warning_threshold, critical_threshold FROM mgmt_metric_thresholds WHERE target_guid = l_target_guid) thr, (SELECT key_value,metric_guid, MAX(collection_timestamp) AS collection_timestamp FROM mgmt_current_metrics WHERE target_guid = l_target_guid GROUP BY key_value,metric_guid) cur WHERE met.is_transposed = 1 AND t.target_guid = l_target_guid AND (met.metric_column = ' ' OR met.key_order = 0) AND met.type_meta_ver = t.type_meta_ver AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ') AND thr.target_guid = t.target_guid AND thr.metric_guid = met.metric_guid AND thr.metric_guid = cur.metric_guid (+) AND thr.key_value = cur.key_value (+) ORDER BY trans_key, metric_label, metric_sort_type DESC, column_label; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN metrics_cur_out FOR SELECT * FROM dual WHERE ROWNUM<1; END get_all_metrics; -- -- PURPOSE: --gets the metric details for the metric. -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- metric_name_in: VARCHAR2 Metric name -- metric_column_in: VARCHAR2 Metric column corresponding to the fault test -- metric_guid_in: RAW metric guid -- -- OUT Parameters: -- operator_out: operator type for the fault test -- metric_det_cur_out: CURSOR cursor containing details of the metric. The keys,column_labels and column_label_nlsids. PROCEDURE get_metric_detail(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, metric_guid_in IN RAW, test_scope_in IN NUMBER, trans_key_in IN VARCHAR2, operator_out OUT NUMBER, metric_detail_cur_out OUT cursorType, host_name_out OUT VARCHAR2, no_key_threshold_out OUT VARCHAR2, metric_info_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; l_target_type VARCHAR2(64); l_has_keys NUMBER(1); BEGIN operator_out:=0; BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN l_target_type := target_type_in; SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, host_name 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, host_name_out FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in); END; ELSE BEGIN l_target_type := 'host'; SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name 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, host_name_out FROM mgmt_targets t, mgmt_targets h WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND h.target_name = t.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in); END; END IF; END; OPEN metric_detail_cur_out for SELECT metric_column, column_label "Description", column_label_nlsid "Description_NlsId",mm.metric_guid "metricGuid",mm.is_transposed "transposed" FROM mgmt_metrics mm WHERE mm.metric_name=metric_name_in AND mm.target_type=l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ') AND key_order>0 ORDER BY key_order ASC; OPEN metric_info_cur_out for SELECT metric_label "metricLabel", metric_label_nlsid "metricNlsId", column_label "columnLabel", column_label_nlsid "columnNlsId" FROM mgmt_metrics mm WHERE mm.metric_name=metric_name_in AND mm.metric_column=metric_column_in AND mm.target_type=l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' '); BEGIN IF trans_key_in IS NULL THEN BEGIN SELECT warning_operator, critical_threshold INTO operator_out, no_key_threshold_out FROM mgmt_metric_thresholds mmt, mgmt_metrics mm WHERE mmt.target_guid = l_target_guid AND mm.metric_name=metric_name_in AND mm.metric_column=metric_column_in AND mm.target_type=l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ') AND mmt.metric_guid=mm.metric_guid AND rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN operator_out:=8; WHEN OTHERS THEN raise_application_error(-20001,'Error when getting metric detail for the target '||target_name_in||'. '|| SQLERRM); END; ELSE BEGIN SELECT warning_operator, critical_threshold INTO operator_out, no_key_threshold_out FROM mgmt_metric_thresholds mmt, mgmt_metrics mm WHERE mmt.target_guid = l_target_guid AND mm.metric_name=metric_name_in AND mm.metric_column=metric_column_in AND mm.target_type=l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ') AND mmt.metric_guid=mm.metric_guid AND mmt.key_value = trans_key_in AND rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN operator_out:=8; WHEN OTHERS THEN raise_application_error(-20001,'Error when getting metric detail for the target '||target_name_in||'. '|| SQLERRM); END; END IF; END; END get_metric_detail; -- -- PURPOSE: --Gets the metric details for a particular fault test. -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- metric_test_guid_in: VARCHAR2 Metric test guid -- metric_name_in: VARCHAR2 Metric name -- OUT parameters -- num_keys_out: NUMBER Number of keys defined for the metric column -- metric_info_out: cursorType Cursor containing the metric information like metric column,threshold ,operator etc -- metric_detail_cur_out: cursorType Cursor containing the list of keys and their correspong values -- PROCEDURE get_metric_detail_for_edit(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_test_guid_in IN VARCHAR2, metric_name_in IN VARCHAR2, test_scope_in IN NUMBER, num_keys_out OUT NUMBER, metric_info_out OUT cursorType, metric_detail_cur_out OUT cursorType, host_name_out OUT VARCHAR2) IS l_metric_column mgmt_metrics.metric_column%TYPE; l_column_label mgmt_metrics.column_label%TYPE; l_column_label_nlsid mgmt_metrics.column_label_nlsid%TYPE; l_metric_label mgmt_metrics.metric_label%TYPE; l_metric_label_nlsid mgmt_metrics.metric_label_nlsid%TYPE; l_threshold mgmt_rca_metric_test.rca_threshold%TYPE; l_keys_out varchar2(2000); l_criticalthreshold mgmt_metric_thresholds.critical_threshold%TYPE; l_operator mgmt_metric_thresholds.warning_operator%TYPE; 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; l_threshold_src mgmt_rca_metric_test.threshold_source%TYPE; l_transposed NUMBER(1); l_target_type VARCHAR2(64); BEGIN l_threshold:=''; l_threshold_src :=1; BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN l_target_type := target_type_in; SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, host_name 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, host_name_out FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in); END; ELSE BEGIN l_target_type := 'host'; SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name 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, host_name_out FROM mgmt_targets h, mgmt_targets m WHERE m.target_name = target_name_in AND m.target_type = target_type_in AND h.target_name = m.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No target found for target name '||target_name_in||' and type '||target_type_in); END; END IF; END; begin select num_keys, threshold_source, metric_column, column_label, column_label_nlsid, metric_label, metric_label_nlsid, decode(threshold_source,0,null,1,rca_threshold), decode(num_keys,0,null,1,key_value,concat_keys(num_keys,mrmt.key_part1_value,mrmt.key_part2_value,mrmt.key_part3_value,mrmt.key_part4_value,mrmt.key_part5_value)), mm.is_transposed "transposed", mrmt.operator into num_keys_out, l_threshold_src, l_metric_column, l_column_label, l_column_label_nlsid, l_metric_label, l_metric_label_nlsid, l_threshold, l_keys_out, l_transposed, l_operator from mgmt_rca_metric_test mrmt, mgmt_metrics mm where mrmt.metric_test_guid=metric_test_guid_in and mm.target_type=l_target_type and mm.type_meta_ver = l_type_meta_ver and (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') and (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') and (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') and (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') and (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ') and mrmt.target_guid=l_target_guid and mm.metric_guid=mrmt.metric_guid AND rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN num_keys_out:=0; l_threshold_src:=1; l_column_label:='Data not available'; end; begin select critical_threshold into l_criticalthreshold from mgmt_metric_thresholds mmt, mgmt_rca_metric_test mrmt where mrmt.metric_test_guid = metric_test_guid_in and mmt.target_guid = mrmt.target_guid and mmt.metric_guid = mrmt.metric_guid and (mmt.key_value = mrmt.key_value or mmt.key_value='' or mmt.key_value=' '); EXCEPTION WHEN NO_DATA_FOUND THEN l_criticalthreshold:=''; end; if(l_criticalthreshold='' or l_criticalthreshold=' ')then l_criticalthreshold:=null; end if; open metric_info_out for select l_metric_column "metricColumn",l_keys_out "keyValue",l_threshold "threshold",l_criticalthreshold "criticalThreshold",l_threshold_src "thresholdSrc", l_operator "operator", l_column_label "Description",l_column_label_nlsid "Description_NlsId", l_metric_label "metricLabel", l_metric_label_nlsid "metricNlsId", l_threshold "threshold",l_operator "operator", l_transposed "transposed" from dual; if(num_keys_out=0)then OPEN metric_detail_cur_out for select * from dual where rownum<1; else OPEN metric_detail_cur_out for select metric_column, column_label "columnLabel", column_label_nlsid "columnLabelNlsId" from mgmt_rca_metric_test mrmt, mgmt_metrics mm where mrmt.metric_test_guid=metric_test_guid_in and mrmt.target_guid=l_target_guid and mm.target_type=l_target_type and mm.metric_name=metric_name_in AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 OR mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 OR mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 OR mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 OR mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 OR mm.category_prop_5 = ' ') and key_order>0 order by key_order asc; end if; END get_metric_detail_for_edit; -- -- PURPOSE: --Creates or updates the rca fault test. -- -- IN Parameters: -- service_guid_in: RAW Service guid -- target_guid_in: RAW Target guid -- metric_test_guid_in: RAW Metric test guid -- metric_guid_in: RAW Metric guid -- metric_name_in: VARCHAR2 Metric name for which the test is defined -- metric_column_in: VARCHAR2 Metric column for which the test is defined -- threshold_in: Threshold value for the fault test -- operator_in:Operator for comparison in the rca fault test -- use_test_threshold_in: NUMBER Flag for indicating if used defined threshold intead of predefined needs to be used. -- edit_type_in: NUMBER Operation type(Create or update) -- metric_keys_array_in: mgmt_medium_string_array Array of key values to be stored. -- OUT parameters -- status_out: NUMBER The operation staus (Success or failed state) -- PROCEDURE create_fault_test(service_name_in IN VARCHAR2, service_type_in IN VARCHAR2, target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_guid_in IN RAW, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, threshold_in IN VARCHAR2, operator_in IN NUMBER, use_test_threshold_in IN NUMBER, edit_type_in IN NUMBER, metric_keys_array_in IN mgmt_medium_string_array, test_scope_in IN NUMBER, status_out OUT NUMBER) IS l_threshold varchar2(256); l_operator mgmt_rca_metric_test.operator%TYPE; l_target_type mgmt_targets.target_type%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; l_count number(5); l_service_guid RAW(16); l_target_guid RAW(16); l_scope_guid RAW(16); l_component_guid RAW(16); l_test_guid RAW(16); l_key_value VARCHAR2(256); BEGIN l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in); BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN l_target_type := target_type_in; SELECT target_guid, 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_component_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; ELSE BEGIN l_target_type := 'host'; SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.category_prop_5, t.target_guid 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, l_component_guid FROM mgmt_targets t, mgmt_targets h WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND h.target_name = t.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; END IF; END; l_scope_guid := get_scope_guid(l_service_guid, l_component_guid, test_scope_in); l_test_guid := get_test_guid(l_target_guid, metric_guid_in, metric_keys_array_in, l_scope_guid, test_scope_in); status_out :=0; if(edit_type_in=1)then select count(*) into status_out from mgmt_rca_metric_test where target_guid=l_target_guid and metric_guid=metric_guid_in and scope_guid=l_scope_guid and scope=test_scope_in and (key_value is null or key_value=metric_keys_array_in(6)); end if; if(status_out=0)then l_threshold:=null; if(use_test_threshold_in=0) then begin select x.thr into l_threshold from (select critical_threshold as thr from mgmt_metrics mm, mgmt_metric_thresholds mmt where mmt.target_guid = l_target_guid and mm.metric_name=metric_name_in and mm.metric_column=metric_column_in and mm.target_type=l_target_type and mm.type_meta_ver = l_type_meta_ver and (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') and (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') and (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') and (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') and (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ') and mmt.metric_guid=mm.metric_guid and (mmt.key_value = metric_keys_array_in(6) or mmt.key_value='' or mmt.key_value=' ') order by mmt.key_value desc) x where rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN l_threshold:=null; end; else l_threshold:=threshold_in; end if; if(edit_type_in=1)then BEGIN IF (metric_keys_array_in(6) IS NULL) THEN l_key_value := ' '; ELSE l_key_value := metric_keys_array_in(6); END IF; insert into mgmt_rca_metric_test (metric_test_guid, target_guid, metric_guid, key_value, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value, scope, scope_guid, threshold_source, rca_threshold, operator) values (l_test_guid, l_target_guid, metric_guid_in, l_key_value, metric_keys_array_in(1), metric_keys_array_in(2), metric_keys_array_in(3), metric_keys_array_in(4), metric_keys_array_in(5), test_scope_in, l_scope_guid, use_test_threshold_in, l_threshold, operator_in); END; else update mgmt_rca_metric_test set threshold_source=use_test_threshold_in, rca_threshold=l_threshold where metric_test_guid=l_test_guid; end if; end if; END create_fault_test; --Purpose --local function to concat the keys based on the number of keys -- FUNCTION concat_keys(num_keys IN NUMBER, key_part1_value IN VARCHAR2, key_part2_value IN VARCHAR2, key_part3_value IN VARCHAR2, key_part4_value IN VARCHAR2, key_part5_value IN VARCHAR2) return VARCHAR2 IS l_key_value VARCHAR(2000); BEGIN IF(num_keys>1) THEN l_key_value:=concat(key_part1_value,', '); l_key_value:=concat(l_key_value,key_part2_value); END IF; IF(num_keys>2) THEN l_key_value:=concat(l_key_value,', '); l_key_value:=concat(l_key_value,key_part3_value); END IF; IF(num_keys>3) THEN l_key_value:=concat(l_key_value,', '); l_key_value:=concat(l_key_value,key_part4_value); END IF; IF(num_keys>4) THEN l_key_value:=concat(l_key_value,', '); l_key_value:=concat(l_key_value,key_part5_value); END IF; return l_key_value; END concat_keys; -- -- PURPOSE: --Checks to see if a threshold is defined for the metric -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- metric_name_in: VARCHAR2 Metric name -- metric_column_in: VARCHAR2 Metric column -- key_value_in: VARCHAR2 the key value. if it is a composite key the composite key guid is supplied -- -- OUT Parameters: -- avail_status_out: NUMBER Flag indicating if the threshold is defined for the metric. PROCEDURE check_threshold_availabilty(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, test_scope_in IN NUMBER, avail_status_out OUT NUMBER, threshold_out OUT VARCHAR2) 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; cate_prop mgmt_category_prop_array; l_target_type VARCHAR2(64); BEGIN BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN l_target_type := target_type_in; 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; ELSE BEGIN l_target_type := 'host'; SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.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 h, mgmt_targets m WHERE m.target_name = target_name_in AND m.target_type = target_type_in AND h.target_name = m.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; END IF; END; SELECT mgmt_category_prop_array(l_target_type,l_type_meta_ver,l_category_prop_1,l_category_prop_2,l_category_prop_3,l_category_prop_4,l_category_prop_5) INTO cate_prop FROM DUAL; threshold_out := get_threshold(l_target_guid, metric_name_in, metric_column_in, key_value_in, cate_prop); if(threshold_out is not null or threshold_out <> '' or threshold_out <> ' ')then avail_status_out:=1; else avail_status_out:=0; end if; END check_threshold_availabilty; -- -- PURPOSE: --Checks to see if a threshold is defined for the metric -- -- IN Parameters: -- target_guid_in: RAW Target guid -- metric_name_in: VARCHAR2 Metric name -- metric_column_in: VARCHAR2 Metric column -- key_value_in: VARCHAR2 the key value. if it is a composite key the composite key guid is supplied --prop_array: mgmt_category_prop_array properties array to encapsulate the target properties -- Return Parameter: -- Threshold value if found otherwise null. FUNCTION get_threshold(target_guid_in IN RAW, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, key_value_in IN VARCHAR2, prop_array IN mgmt_category_prop_array) return VARCHAR2 IS l_threshold VARCHAR2(256); BEGIN l_threshold:=null; BEGIN select x.thr into l_threshold from(select critical_threshold as thr from mgmt_metric_thresholds mmt, mgmt_metrics met where mmt.target_guid=target_guid_in and met.metric_name=metric_name_in and met.target_type=prop_array(1) and met.metric_column=metric_column_in and mmt.metric_guid=met.metric_guid and met.type_meta_ver = prop_array(2) and (met.category_prop_1 = prop_array(3) or met.category_prop_1 = ' ') and (met.category_prop_2 = prop_array(4) or met.category_prop_2 = ' ') and (met.category_prop_3 = prop_array(5) or met.category_prop_3 = ' ') and (met.category_prop_4 = prop_array(6) or met.category_prop_4 = ' ') and (met.category_prop_5 = prop_array(7) or met.category_prop_5 = ' ') and (mmt.key_value=key_value_in or mmt.key_value=' ' or mmt.key_value='') ORDER BY mmt.key_value desc) x where rownum=1; EXCEPTION WHEN NO_DATA_FOUND THEN l_threshold:=null; END; if(l_threshold='' or l_threshold=' ')then l_threshold:=null; end if; return l_threshold; END get_threshold; -- -- PURPOSE: --Deletes the fault test -- -- IN Parameters: -- metric_test_id_in: VARCHAR2 Metric test guid -- metric_name_in: VARCHAR2 Metric name -- -- OUT Parameters: -- status_out: Status of the removal. -- PROCEDURE delete_rca_metric(metric_test_id_in IN VARCHAR2, status_out OUT NUMBER) IS BEGIN delete from mgmt_rca_metric_test where metric_test_guid=metric_test_id_in; status_out := 1; END delete_rca_metric; -- -- PURPOSE: --Gets all the key values identified for the metric. -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- metric_name_in: VARCHAR2 Metric name -- key_part_in: the key part value in the case of composite or the just the key in acse of single key. -- filter_value_in: VARCHAR2 The input filter value to narrow the search to a specific pattern -- OUT parameters: -- metric_all_keys_out:cursorType cursor containing the possible keys . -- PROCEDURE get_all_metric_keys(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, key_part_in IN NUMBER, filter_value_in IN VARCHAR2, test_scope_in IN NUMBER, metric_all_keys_out OUT cursorType, host_name_out OUT VARCHAR2) 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; l_target_type VARCHAR2(64); BEGIN BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN l_target_type := target_type_in; SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5, host_name 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, host_name_out 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; ELSE BEGIN l_target_type := 'host'; SELECT h.target_guid, h.type_meta_ver, h.category_prop_1, h.category_prop_2, h.category_prop_3, h.category_prop_4, h.category_prop_5, h.target_name 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, host_name_out FROM mgmt_targets t, mgmt_targets h WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND h.target_name = t.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; END IF; END; OPEN metric_all_keys_out FOR WITH common AS (SELECT DISTINCT key_value,num_keys FROM mgmt_current_metrics mmw,mgmt_metrics mm WHERE mmw.target_guid=l_target_guid AND mm.metric_name=metric_name_in AND mm.target_type=l_target_type AND mm.type_meta_ver = l_type_meta_ver AND (mm.category_prop_1 = l_category_prop_1 or mm.category_prop_1 = ' ') AND (mm.category_prop_2 = l_category_prop_2 or mm.category_prop_2 = ' ') AND (mm.category_prop_3 = l_category_prop_3 or mm.category_prop_3 = ' ') AND (mm.category_prop_4 = l_category_prop_4 or mm.category_prop_4 = ' ') AND (mm.category_prop_5 = l_category_prop_5 or mm.category_prop_5 = ' ') AND mm.metric_guid=mmw.metric_guid AND key_order=0) SELECT common.key_value as KeyValue FROM common WHERE num_keys=1 AND lower(key_value) LIKE '%'||lower(filter_value_in)||'%' UNION ALL SELECT DISTINCT decode(key_part_in,1,key_part1_value,2,key_part2_value,3,key_part3_value,4,key_part4_value,5,key_part5_value) as KeyValue FROM common, mgmt_metrics_composite_keys mmck WHERE common.num_keys>1 AND mmck.composite_key=key_value AND lower(1) like '%'||lower(filter_value_in)||'%'; END get_all_metric_keys; -- -- PURPOSE: --Return the array of key parts based on number of keys. -- -- IN Parameters: -- num_keys: NUMBER Number of keys -- key_part1_value: VARCHAR2 Key part 1 value -- key_part2_value: VARCHAR2 Key part 2 value -- key_part3_value: VARCHAR2 Key part 3 value -- key_part4_value: VARCHAR2 Key part 4 value -- key_part5_value: VARCHAR2 Key part 5 value -- Return Parameter: -- mgmt_medium_string_array of key parts. -- FUNCTION get_composite_keys(num_keys IN NUMBER, key_part1_value IN VARCHAR2, key_part2_value IN VARCHAR2, key_part3_value IN VARCHAR2, key_part4_value IN VARCHAR2, key_part5_value IN VARCHAR2) return mgmt_medium_string_array IS BEGIN if(num_keys=2) then return mgmt_medium_string_array(key_part1_value,key_part2_value); elsif (num_keys=3)then return mgmt_medium_string_array(key_part1_value,key_part2_value,key_part3_value); elsif (num_keys=4)then return mgmt_medium_string_array(key_part1_value,key_part2_value,key_part3_value,key_part4_value); elsif (num_keys=4)then return mgmt_medium_string_array(key_part1_value,key_part2_value,key_part3_value,key_part4_value,key_part5_value); end if; return mgmt_medium_string_array('key not found'); END get_composite_keys; -- -- PURPOSE: --Checks if the input key (or composite key) value is valid. -- -- IN Parameters: -- target_name_in: VARCHAR2 Target name -- target_type_in: VARCHAR2 Target type -- metric_name_in: VARCHAR2 Metric name -- num_keys_in: NUMBER number of keys -- metric_keys_array_in: mgmt_medium_string_array The key ( or composite key) value for which the existence has to verified -- OUT parameters: -- keys_status_out:VARCHAR2 Status indiacting the existence of the key( or composite key) -- PROCEDURE check_keys_availability(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, num_keys_in IN NUMBER, metric_keys_array_in IN mgmt_medium_string_array, test_scope_in IN NUMBER, keys_status_out OUT VARCHAR2) IS l_key mgmt_metric_thresholds.key_value%TYPE; l_target_guid mgmt_targets.target_guid%TYPE; BEGIN BEGIN IF test_scope_in = SCOPE_SERVICE THEN BEGIN SELECT target_guid INTO l_target_guid 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; ELSE BEGIN SELECT h.target_guid INTO l_target_guid FROM mgmt_targets h, mgmt_targets m WHERE m.target_name = target_name_in AND m.target_type = target_type_in AND h.target_name = m.host_name AND h.target_type = 'host'; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; END IF; END; keys_status_out:=''; if(num_keys_in=1)then begin select key_value INTO l_key from mgmt_current_metrics mmw where mmw.target_guid=l_target_guid and mmw.key_value=metric_keys_array_in(6) and rownum=1; EXCEPTION when NO_DATA_FOUND then l_key:=null; end; elsif(num_keys_in>1)then begin select composite_key INTO l_key from mgmt_metrics_composite_keys mmck where mmck.target_guid=l_target_guid and composite_key=hextoraw(metric_keys_array_in(6)) and rownum=1; exception when NO_DATA_FOUND then l_key:=null; end; end if; if(l_key is not null or l_key<>'')then keys_status_out:='1'; else keys_status_out:='0'; end if; END check_keys_availability; -- -- PURPOSE: --Check the user permision for the service and the dependent target -- -- IN Parameters: -- service_guid_in: RAW Serviceguid -- target_guid_in: RAW Target guid -- -- OUT Parameters: -- permissions_out: Flag to indicate the permissions for the user. -- PROCEDURE check_user_permissions(service_guid_in IN RAW, target_guid_in IN RAW, permissions_out OUT NUMBER) IS l_has_permission NUMBER(3); BEGIN permissions_out:=0;--Assuming user has all permissions l_has_permission:= mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, service_guid_in); IF(l_has_permission=0)THEN permissions_out := 1;--has no permissions for the service END IF; l_has_permission:= mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.OPERATOR_TARGET, target_guid_in); IF(l_has_permission=0)THEN IF(permissions_out=1)THEN permissions_out := 2;--has no permissions for the target ELSE permissions_out := 3;--has no permissions for both the serice and the target END IF; END IF; END check_user_permissions; -- PURPOSE: -- Delete RCA details associated with a target, typically called during -- target removal. This involves the following steps: -- -- . delete all summary, run and trace rows associated with the target (if its a service) -- . delete all event assoc rows associated with the target (if its a service) -- . delete all tests and results associated with the service scope (if its a service) -- . delete all tests and results associated with the target (service or not) -- . delete all events associated with the target (service or not) -- -- Because events are removed, it is possible that event associations may still reference -- events which have been removed. Therefore consumers of events must be able to outer-join -- from the event_assoc table to event table and in cases where events are missing show -- the appropriate message to the end-user. -- -- IN: -- target_name_in Target Name to be removed -- target_type_in Target Type -- -- OUT: -- hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV) -- PROCEDURE delete_target_rca(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS l_is_service NUMBER := mgmt_service.implements_service_interface(target_type_in); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); l_message VARCHAR2(1000); BEGIN /* BEGIN l_message := concat('delete_target_rca: ',target_name_in); l_message := concat(l_message,' '); l_message := concat(l_message,RAWTOHEX(target_guid_in)); insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message); END; */ DELETE FROM mgmt_rca_event WHERE target_guid = target_guid_in; DELETE FROM mgmt_rca_test_result WHERE target_guid = target_guid_in; DELETE FROM mgmt_rca_metric_test WHERE target_guid = target_guid_in; -- delete RCA results for the target (since its a service) IF l_is_service = 1 THEN remove_task_by_target(target_name_in, target_type_in, target_guid_in); DELETE FROM mgmt_rca_test_result WHERE metric_test_guid IN (SELECT metric_test_guid FROM mgmt_rca_metric_test WHERE scope_guid = target_guid_in); DELETE FROM mgmt_rca_metric_test WHERE scope_guid = target_guid_in; DELETE FROM mgmt_rca_trace WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_summary WHERE target_guid = target_guid_in); DELETE FROM mgmt_rca_event_assoc WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_summary WHERE target_guid = target_guid_in); DELETE FROM mgmt_rca_run WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_summary WHERE target_guid = target_guid_in); DELETE FROM mgmt_rca_summary WHERE target_guid = target_guid_in; END IF; END delete_target_rca; -- PURPOSE: -- Delete all RCA data associated with a severity; the severity may be one that -- triggers RCA (a service failure severity) or one that contributes to RCA -- results (is associated with an RCA event). This involves the following steps: -- -- . delete all summary, run, trace and event assoc associated with the severity (if service) -- . delete events with source = severity guid -- -- Because events are removed, it is possible that event associations may still reference -- events which have been removed. Therefore consumers of events must be able to outer-join -- from the event_assoc table to event table and in cases where events are missing show -- the appropriate message to the end-user. -- -- IN: -- target_name_in Target Name to be removed -- target_type_in Target Type -- severity_guid_in the severity being removed -- -- OUT: -- hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV) -- PROCEDURE delete_severity_rca(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, severity_guid_in IN RAW, hasPriv OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_is_service NUMBER := mgmt_service.implements_service_interface(target_type_in); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); l_message VARCHAR2(1000); BEGIN /* BEGIN l_message := concat('delete_severity_rca: ',target_name_in); l_message := concat(l_message,' '); l_message := concat(l_message,RAWTOHEX(severity_guid_in)); insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message); END; */ hasPriv := MGMT_USER.USER_HAS_PRIV; -- lookup the target guid l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); IF l_target_guid IS NULL THEN RETURN; END IF; -- check if user has priv IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; delete_rca_for_severity(severity_guid_in, l_is_service); END delete_severity_rca; PROCEDURE delete_rca_for_severity(severity_guid_in IN RAW, is_service IN NUMBER) IS BEGIN DELETE FROM mgmt_rca_event WHERE source_guid = severity_guid_in; -- delete RCA results for the severity (since its a service) IF is_service = 1 THEN DELETE FROM mgmt_rca_trace WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in); DELETE FROM mgmt_rca_event_assoc WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in); DELETE FROM mgmt_rca_run WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in); DELETE FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in; END IF; END delete_rca_for_severity; -- PURPOSE: -- procedure called when a target association (the relationship between -- a service and target) is removed. -- -- IN: -- target_name_in Target Name to be removed -- target_type_in Target Type -- service_name_in Service Name to be removed -- service_type_in Service Type -- -- OUT: -- hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV) -- PROCEDURE delete_target_assoc_rca(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, service_name_in IN VARCHAR2, service_type_in IN VARCHAR2) IS l_target_guid mgmt_targets.target_guid%TYPE; l_service_guid mgmt_targets.target_guid%TYPE; l_is_service NUMBER := mgmt_service.implements_service_interface(service_type_in); l_message VARCHAR2(1000); BEGIN /* BEGIN l_message := concat('delete_target_assoc_rca: ',target_name_in); l_message := concat(l_message,' '); l_message := concat(l_message,service_name_in); insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message); END; */ -- lookup the target guid l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in); IF l_target_guid IS NULL OR l_service_guid IS NULL THEN RETURN; END IF; -- NOTE: 10/7/2004 JMR - longer term we may wish to consider doing a pending -- delete where the tests and results for the association are not removed -- since if the user removes and then readds the association all of the events -- from any previous RCA are gone! DELETE FROM mgmt_rca_event WHERE source_guid IN (SELECT result_guid FROM mgmt_rca_test_result r, mgmt_rca_metric_test t WHERE t.target_guid = l_target_guid AND t.scope_guid = l_service_guid AND r.metric_test_guid = t.metric_test_guid); DELETE FROM mgmt_rca_test_result WHERE metric_test_guid IN (SELECT metric_test_guid FROM mgmt_rca_metric_test WHERE target_guid = l_target_guid AND scope_guid = l_service_guid); DELETE FROM mgmt_rca_metric_test WHERE target_guid = l_target_guid AND scope_guid = l_service_guid; END delete_target_assoc_rca; -- PURPOSE: -- procedure used to delete a fault test given the service the test applies -- to and the guid for the test. -- -- IN: -- service_name_in Service Name to be removed -- service_type_in Service Type -- metric_test_guid_in GUID (identifier) of the test -- -- OUT: -- hasPriv (either mgmt_user.USER_HAS_PRIV or USER_DOES_NOT_HAVE_PRIV) -- PROCEDURE delete_fault_test(service_name_in IN VARCHAR2, service_type_in IN VARCHAR2, metric_test_guid_in IN RAW, hasPriv OUT NUMBER) IS l_service_guid mgmt_targets.target_guid%TYPE; l_is_service NUMBER := mgmt_service.implements_service_interface(service_type_in); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); BEGIN hasPriv := MGMT_USER.USER_HAS_PRIV; -- lookup the target guid l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in); IF l_service_guid IS NULL THEN RETURN; END IF; -- check if user has priv IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_service_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; -- NOTE: 10/7/2004 JMR - longer term we may wish to consider doing a pending -- delete where the events and results for the test are not removed -- since if the user removes and then readds the test all of the events -- from any previous RCA are gone! DELETE FROM mgmt_rca_event WHERE event_guid IN (SELECT event_guid FROM mgmt_rca_test_result WHERE metric_test_guid = metric_test_guid_in); DELETE FROM mgmt_rca_test_result WHERE metric_test_guid = metric_test_guid_in; DELETE FROM mgmt_rca_metric_test WHERE metric_test_guid = metric_test_guid_in; END delete_fault_test; -- PURPOSE: -- This procedure returns all rca details information -- -- IN Parameters: -- service_guid_in: Service guid -- severity_guid_in: RAW severity guid -- update_id_in: NUMBER update inidcating the rca run or -1 to fetch the latest run detail -- OUT Parameters: -- user_privilege_out: NUMBER falg to check user privileges -- alert_summary_out: cursorType Cursor containing rca alert summary information -- run_summary_out: cursorType Cursor containing rca analysis summary information -- rca_causes_out: cursorType Cursor containing rca causes information -- rca_trace_doc_out: CLOB clob object containing rca analysis detail information -- PROCEDURE get_rca_details(service_guid_in IN RAW, severity_guid_in IN RAW, update_id_in IN NUMBER, last_id_in IN NUMBER, root_cause_only_in IN NUMBER, user_privilege_out OUT NUMBER, last_id_out OUT NUMBER, alert_summary_out OUT cursorType, run_summary_out OUT cursorType, rca_causes_out OUT cursorType, rca_impacts_out OUT cursorType, rca_trace_doc_out OUT CLOB) IS l_update_id mgmt_rca_run.update_id%TYPE; BEGIN /* if the update_id_in is -1 (requesting the latest) then we'll get the last update and compare it to the last_id_in. If they are the same then the client already has the data from the last update. If they are different then we'll continue on and load the rest of the information */ user_privilege_out:=mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.VIEW_TARGET, service_guid_in); l_update_id := update_id_in; IF(l_update_id = -1) THEN BEGIN select last_update_id into l_update_id from mgmt_rca_summary where severity_guid=severity_guid_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001,'No Root Cause Analysis found for specified severity'); END; END IF; last_id_out := l_update_id; IF (update_id_in = -1 AND l_update_id = last_id_in) THEN RETURN; END IF; rca_trace_doc_out:=NULL; IF (user_privilege_out<>0) THEN get_alert_summary(severity_guid_in, alert_summary_out); BEGIN OPEN run_summary_out FOR select exception_count "exceptions",cause_count "causesFound",start_time "analysisTime", status "rcaStatus", error_text "errorText", last_update_id "totalUpdate",last_run_time "mostRecentUpdate", confidence_possible "confidencePossible", confidence_actual "confidenceActual" from mgmt_rca_summary mrs, mgmt_rca_run mrr where mrs.severity_guid=severity_guid_in and mrr.event_guid=mrs.event_guid and mrr.update_id=l_update_id; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN run_summary_out FOR select * from dual where rownum<1; END; BEGIN select mrt.trace_doc into rca_trace_doc_out from mgmt_rca_trace mrt, mgmt_rca_summary mrs where mrs.severity_guid=severity_guid_in and mrs.event_guid=mrt.event_guid and mrt.update_id=l_update_id; EXCEPTION WHEN NO_DATA_FOUND THEN rca_trace_doc_out:=NULL; END; get_failure_causes(severity_guid_in, l_update_id, rca_causes_out); get_affected_services_bysev(severity_guid_in, root_cause_only_in, rca_impacts_out); END IF; END get_rca_details; -- PURPOSE: -- This procedure returns all rca details information -- -- IN Parameters: -- service_guid_in: Service guid -- severity_guid_in: RAW severity guid -- OUT Parameters: -- user_privilege_out: NUMBER falg to check user privileges -- alert_summary_out: cursorType Cursor containing rca alert summary information -- rca_analysis_summary_out: cursorType Cursor containing rca analysis summary information -- rca_history_out: cursorType Cursor containing history of rca run for the severity. -- PROCEDURE get_rca_history(service_guid_in IN RAW, severity_guid_in IN RAW, user_privilege_out OUT NUMBER, alert_summary_out OUT cursorType, rca_analysis_summary_out OUT cursorType, rca_history_out OUT cursorType) IS BEGIN user_privilege_out:=mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.VIEW_TARGET, service_guid_in); IF (user_privilege_out<>0) THEN get_alert_summary(severity_guid_in, alert_summary_out); BEGIN OPEN rca_analysis_summary_out FOR select last_update_id "totalUpdate",last_run_time "mostRecentUpdate",status "rcaStatus" from mgmt_rca_summary mrs, mgmt_rca_run mrr where mrs.severity_guid=severity_guid_in and mrr.event_guid=mrs.event_guid and mrr.update_id=mrs.last_update_id; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN rca_analysis_summary_out FOR select * from dual where rownum<1; END; BEGIN OPEN rca_history_out FOR select start_time "analysisTime", confidence_possible "confidencePossible", confidence_actual "confidenceActual", exception_count "exceptions", cause_count "causesFound",update_id "updateId" from mgmt_rca_summary mrs, mgmt_rca_run mrr where mrs.severity_guid=severity_guid_in and mrr.event_guid=mrs.event_guid order by mrr.update_id asc; EXCEPTION WHEN NO_DATA_FOUND THEN OPEN rca_history_out FOR select * from dual where rownum<1; END; END IF; END get_rca_history; -- PURPOSE: -- This procedure returns all alert summary information -- -- IN Parameters: -- severity_guid_in: RAW severity guid -- OUT Parameters: -- alert_summary_out: cursorType Cursor containing rca alert summary information -- PROCEDURE get_alert_summary(severity_guid_in IN RAW, alert_summary_out OUT cursorType) IS BEGIN BEGIN OPEN alert_summary_out FOR SELECT mm.metric_name "metric",mv.violation_level "severity",mv.collection_timestamp "timestamp", user_name "user",message "message",message_nlsid "message_nlsid",message_params "message_params" FROM mgmt_violations mv, mgmt_metrics mm, mgmt_targets mt WHERE mv.violation_guid = severity_guid_in AND mm.metric_guid=mv.policy_guid AND mt.target_guid=mv.target_guid AND mm.target_type=mt.target_type AND mm.type_meta_ver=mt.type_meta_ver AND (mm.category_prop_1=mt.category_prop_1 or mm.category_prop_1 = ' ') AND (mm.category_prop_2=mt.category_prop_2 or mm.category_prop_2 = ' ') AND (mm.category_prop_3=mt.category_prop_3 or mm.category_prop_3 = ' ') AND (mm.category_prop_4=mt.category_prop_4 or mm.category_prop_4 = ' ') AND (mm.category_prop_5=mt.category_prop_5 or mm.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN OPEN alert_summary_out FOR select * from dual where rownum<1; END; END get_alert_summary; -- PURPOSE: -- Procedure used to add external events (if they should ever exist). -- PROCEDURE add_rca_event (event_source_guid_in IN RAW, event_source_type_in IN NUMBER, event_target_guid_in IN RAW, collection_time_in IN DATE, event_guid_out OUT RAW) IS BEGIN -- generate guid for the event -- event_guid_out := genguid(event_source_guid_in, event_source_type_in, collection_time_in); -- insert the event INSERT INTO mgmt_rca_event (event_guid, source_guid, source_type, collection_time, target_guid) VALUES (event_guid_out, event_source_guid_in, event_source_type_in, collection_time_in, event_target_guid_in); END add_rca_event; -- PURPOSE: -- Procedure used to add external events association (if they should ever exist). -- PROCEDURE associate_event_with_severity (event_guid_in IN RAW, severity_guid_in IN RAW, is_leaf_event_in IN NUMBER) IS l_event_guid RAW(16); l_update_id NUMBER; BEGIN -- locate RCA for the severity BEGIN SELECT event_guid, last_update_id INTO l_event_guid, l_update_id FROM mgmt_rca_summary WHERE severity_guid = severity_guid_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.no_such_metric_found; END; -- insert the association BEGIN INSERT INTO mgmt_rca_event_assoc (event_guid, update_id, symptom_event_guid, cause_event_guid, is_root_cause) VALUES (l_event_guid, l_update_id, l_event_guid, event_guid_in, is_leaf_event_in); END; END associate_event_with_severity; -- PURPOSE: -- public procedure (SDK?) used to add/update a fault test -- PROCEDURE set_rca_fault_test( service_name_in IN VARCHAR2, service_type_in IN VARCHAR2, target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, metric_keys_array_in IN mgmt_medium_string_array, override_sev_threshold_in IN NUMBER, threshold_in IN VARCHAR2, new_test_in IN NUMBER, hasPriv OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_service_guid mgmt_targets.target_guid%TYPE; l_is_service NUMBER := mgmt_service.implements_service_interface(service_type_in); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); l_metric_guid RAW(16); l_test_guid RAW(16); l_operator NUMBER(2); l_key_str VARCHAR2(256); l_test_guid_str VARCHAR2(512); l_metric_status NUMBER; BEGIN hasPriv := MGMT_USER.USER_HAS_PRIV; -- must be a service IF l_is_service = 0 THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- lookup the target guid l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in); IF l_target_guid IS NULL OR l_service_guid IS NULL THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- check if user has priv IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_service_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; -- get metric guid and metric column type BEGIN -- note: the "rownum = 1" is to ensure only one row is returned. This may -- not return the correct metric row 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 metric_guid INTO l_metric_guid FROM mgmt_metrics m, mgmt_targets t WHERE t.target_guid = l_target_guid AND m.target_type = t.target_type AND m.metric_name = metric_name_in AND m.metric_column = metric_column_in 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 ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE MGMT_GLOBAL.no_such_metric_found; END; -- get operator for the metric threshold BEGIN SELECT warning_operator INTO l_operator FROM mgmt_metric_thresholds mmt, mgmt_metrics mm, mgmt_targets t WHERE mmt.target_guid = l_target_guid AND t.target_guid = mmt.target_guid AND mm.metric_name = metric_name_in AND mm.metric_column = metric_column_in AND mm.target_type = t.target_type AND mm.type_meta_ver = t.type_meta_ver AND (mm.category_prop_1 = t.category_prop_1 OR mm.category_prop_1 = ' ') AND (mm.category_prop_2 = t.category_prop_2 OR mm.category_prop_2 = ' ') AND (mm.category_prop_3 = t.category_prop_3 OR mm.category_prop_3 = ' ') AND (mm.category_prop_4 = t.category_prop_4 OR mm.category_prop_4 = ' ') AND (mm.category_prop_5 = t.category_prop_5 OR mm.category_prop_5 = ' ') AND mmt.metric_guid = mm.metric_guid AND (key_value=' ' OR key_value='') AND ROWNUM=1; EXCEPTION WHEN NO_DATA_FOUND THEN l_operator := 8; WHEN OTHERS THEN RAISE MGMT_GLOBAL.no_such_metric_found; END; -- generate the test guid create_fault_test(service_name_in, service_type_in, target_name_in, target_type_in, l_metric_guid, metric_name_in, metric_column_in, threshold_in, l_operator, override_sev_threshold_in, new_test_in, metric_keys_array_in, SCOPE_SERVICE, l_metric_status); END set_rca_fault_test; -- PURPOSE: -- public procedure (SDK?) used to remove a fault test -- PROCEDURE delete_rca_fault_test( service_name_in IN VARCHAR2, service_type_in IN VARCHAR2, target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, metric_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, metric_keys_array_in IN mgmt_medium_string_array, hasPriv OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_service_guid mgmt_targets.target_guid%TYPE; l_is_service NUMBER := mgmt_service.implements_service_interface(service_type_in); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); l_metric_guid RAW(16); l_test_guid RAW(16); l_operator mgmt_metric_thresholds.warning_operator%TYPE; l_key_str VARCHAR2(256); l_test_guid_str VARCHAR2(512); BEGIN hasPriv := MGMT_USER.USER_HAS_PRIV; -- must be a service IF l_is_service = 0 THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- lookup the target guid l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); l_service_guid := mgmt_target.get_target_guid(service_name_in, service_type_in); IF l_target_guid IS NULL OR l_service_guid IS NULL THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- check if user has priv IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_service_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; -- get metric guid and metric column type BEGIN -- note: the "rownum = 1" is to ensure only one row is returned. This may -- not return the correct metric row 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 metric_guid INTO l_metric_guid FROM mgmt_metrics m, mgmt_targets t WHERE t.target_guid = l_target_guid AND m.target_type = t.target_type AND m.metric_name = metric_name_in AND m.metric_column = metric_column_in 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 ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE MGMT_GLOBAL.no_such_metric_found; END; -- generate the test guid l_test_guid := get_test_guid(l_target_guid, l_metric_guid, metric_keys_array_in, l_service_guid, SCOPE_SERVICE); DELETE FROM mgmt_rca_metric_test WHERE metric_test_guid = l_test_guid; END delete_rca_fault_test; FUNCTION get_scope_guid(service_guid_in IN RAW, target_guid_in IN RAW, test_scope_in IN NUMBER) RETURN RAW IS l_scope_guid_str VARCHAR2(512); BEGIN IF test_scope_in = SCOPE_SERVICE THEN RETURN service_guid_in; ELSE l_scope_guid_str := RAWTOHEX(service_guid_in) || ';' || RAWTOHEX(target_guid_in); RETURN DBMS_OBFUSCATION_TOOLKIT.md5(input => UTL_RAW.cast_to_raw(l_scope_guid_str)); END IF; END get_scope_guid; FUNCTION get_test_guid(target_guid_in IN RAW, metric_guid_in IN RAW, metric_keys_array_in IN mgmt_medium_string_array, service_guid_in IN RAW, test_scope_in IN NUMBER) RETURN RAW IS l_key_str VARCHAR2(256); l_test_guid_str VARCHAR2(512); l_key_array smp_emd_string_array; l_key_data NUMBER(1); BEGIN IF metric_keys_array_in IS NULL THEN l_key_str := NULL; ELSIF metric_keys_array_in.COUNT = 1 THEN l_key_str := metric_keys_array_in(1); ELSE l_key_data := 0; BEGIN l_key_array := smp_emd_string_array(); FOR i in 1..metric_keys_array_in.COUNT LOOP l_key_array.extend(1); l_key_array(i) := metric_keys_array_in(i); IF metric_keys_array_in(i) IS NOT NULL THEN l_key_data := 1; END IF; END LOOP; IF l_key_data = 1 THEN l_key_str := RAWTOHEX(mgmt_global.get_composite_key_guid(l_key_array)); END IF; END; END IF; l_test_guid_str := RAWTOHEX(target_guid_in) || ';' || RAWTOHEX(metric_guid_in) || ';' ; IF l_key_str IS NOT NULL THEN l_test_guid_str := l_test_guid_str || l_key_str || ';' ; END IF; l_test_guid_str := l_test_guid_str || test_scope_in || ';' || RAWTOHEX(service_guid_in); RETURN DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(l_test_guid_str)); END get_test_guid; PROCEDURE get_possible_causes_detail(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, update_id_in IN NUMBER, interactive_rca_out OUT NUMBER, severity_guid_out OUT RAW, rca_causeoffailure_cur_out OUT cursorType, rca_info_cur_out OUT cursorType) IS BEGIN -- check interactive flag 1st, if supports interactive then we are done (do no more) interactive_rca_out := get_interactive_flag(target_name_in, target_type_in); IF interactive_rca_out = 1 THEN OPEN rca_causeoffailure_cur_out FOR SELECT * FROM dual WHERE ROWNUM<1; OPEN rca_info_cur_out FOR SELECT * FROM dual WHERE ROWNUM<1; SELECT get_severity(target_name_in, target_type_in) INTO severity_guid_out FROM dual; RETURN; END IF; select get_severity(target_name_in,target_type_in) into severity_guid_out from dual; get_failure_causes(severity_guid_out, update_id_in, rca_causeoffailure_cur_out); begin OPEN rca_info_cur_out FOR select last_run_time "completionTime", confidence_possible "confidencePossible", confidence_actual "confidenceActual", severity_guid_out "severity", error_text "errorText" from mgmt_rca_summary mrs, mgmt_rca_run mrr where mrs.severity_guid=severity_guid_out and mrr.event_guid=mrs.event_guid and ((update_id_in=-1 and mrr.update_id=mrs.last_update_id) or mrr.update_id=update_id_in); EXCEPTION WHEN NO_DATA_FOUND THEN OPEN rca_info_cur_out FOR select * from dual where rownum<1; end; END get_possible_causes_detail; FUNCTION get_severity(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) return raw is l_severity_guid RAW(16) := null; BEGIN begin /* select mcs.violation_guid into l_severity_guid from mgmt_current_violation mcs,mgmt_targets mt,mgmt_metrics mm where mt.target_name=target_name_in and mt.target_type=target_type_in and mcs.target_guid=mt.target_guid and mcs.policy_guid=mm.metric_guid and mcs.violation_level=mgmt_global.G_SEVERITY_CRITICAL and mm.target_type = mt.target_type and mt.type_meta_ver = mm.type_meta_ver and (mt.category_prop_1 = mm.category_prop_1 OR mm.category_prop_1 = ' ') and (mt.category_prop_2 = mm.category_prop_2 OR mm.category_prop_2 = ' ') and (mt.category_prop_3 = mm.category_prop_3 OR mm.category_prop_3 = ' ') and (mt.category_prop_4 = mm.category_prop_4 OR mm.category_prop_4 = ' ') and (mt.category_prop_5 = mm.category_prop_5 OR mm.category_prop_5 = ' ') and mm.metric_name = mgmt_global.G_AVAIL_METRIC_NAME and mm.metric_column = mgmt_global.G_AVAIL_METRIC_COLUMN; */ SELECT ca.severity_guid INTO l_severity_guid FROM mgmt_targets t, mgmt_current_availability ca WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND ca.target_guid = t.target_guid AND ca.current_status = MGMT_GLOBAL.G_STATUS_DOWN; EXCEPTION WHEN NO_DATA_FOUND THEN l_severity_guid:=null; end; return l_severity_guid; END get_severity; -- -- PURPOSE: get the number of services currently affected by the -- specified target. Affected means services that are CURRENTLY -- in a failed state. -- PROCEDURE get_svcs_aff_count_byname(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, root_cause_only_in IN NUMBER, svcs_affected_out OUT NUMBER) IS l_severity_guid RAW(16) := null; BEGIN IF root_cause_only_in = 1 THEN BEGIN SELECT COUNT(*) into svcs_affected_out FROM (SELECT DISTINCT rs.severity_guid svc_sev_guid FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs, mgmt_targets t, mgmt_current_violation s WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND e.target_guid = t.target_guid AND a.cause_event_guid = e.event_guid AND a.is_root_cause = ROOT_CAUSE_TYPE AND s.violation_guid = rs.severity_guid AND rr.event_guid = a.event_guid AND rr.update_id = a.update_id AND rs.event_guid = rr.event_guid AND rs.rca_status = RCA_STATUS_OPEN AND rr.update_id = rs.last_update_id ) x; END; ELSE BEGIN SELECT COUNT(*) into svcs_affected_out FROM (SELECT DISTINCT rs.severity_guid svc_sev_guid FROM mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs, mgmt_targets t, mgmt_current_violation s WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND e.target_guid = t.target_guid AND a.cause_event_guid = e.event_guid AND s.violation_guid = rs.severity_guid AND rr.event_guid = a.event_guid AND rr.update_id = a.update_id AND rs.event_guid = rr.event_guid AND rs.rca_status = RCA_STATUS_OPEN AND rr.update_id = rs.last_update_id ) x; END; END IF; END; -- -- PURPOSE: get the number of services affected (caused) by a particular severity -- PROCEDURE get_svcs_aff_count_bysev(severity_guid_in IN RAW, root_cause_only_in IN NUMBER, svcs_affected_out OUT NUMBER) IS BEGIN IF root_cause_only_in = 1 THEN BEGIN select count(*) into svcs_affected_out from ( select distinct(rs.event_guid) from mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs where e.source_guid = severity_guid_in and a.cause_event_guid = e.event_guid and a.is_root_cause = ROOT_CAUSE_TYPE and rr.event_guid = a.event_guid and rr.update_id = a.update_id and rs.event_guid = rr.event_guid and rs.rca_status = RCA_STATUS_OPEN ) x; END; ELSE select count(*) into svcs_affected_out from ( select distinct(rs.event_guid) from mgmt_rca_event e, mgmt_rca_event_assoc a, mgmt_rca_run rr, mgmt_rca_summary rs where e.source_guid = severity_guid_in and a.cause_event_guid = e.event_guid and rr.event_guid = a.event_guid and rr.update_id = a.update_id and rs.event_guid = rr.event_guid and rs.rca_status = RCA_STATUS_OPEN ) x; END IF; END; -- PURPOSE: -- set the interactive RCA flag for a particular target -- PROCEDURE set_interactive_test(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, interactive_rca_in IN NUMBER, hasPriv OUT NUMBER) IS l_interactive_rca NUMBER := 0; l_collect_ondemand NUMBER := 1; l_update_prop NUMBER := 1; l_target_guid RAW(16); l_resp_stat_metric_guid MGMT_METRICS.metric_guid%TYPE; l_metric_name MGMT_METRICS.metric_name%TYPE; l_metric_column MGMT_METRICS.metric_column%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; l_violation_guid mgmt_current_violation.violation_guid%TYPE; l_is_service NUMBER := mgmt_service.implements_service_interface(target_type_in); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); BEGIN hasPriv := MGMT_USER.USER_HAS_PRIV; -- must be a service IF l_is_service = 0 THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- lookup the target guid 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; END; IF l_target_guid IS NULL THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- check if user has priv IF MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV THEN hasPriv := MGMT_USER.USER_DOES_NOT_HAVE_PRIV; RETURN; END IF; -- see if there is a current flag (update or insert) BEGIN SELECT interactive_rca, collect_on_demand INTO l_interactive_rca, l_collect_ondemand FROM mgmt_rca_target_props WHERE target_guid = l_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN l_update_prop := 0; END; -- update the flag for this IF l_update_prop = 0 THEN INSERT INTO mgmt_rca_target_props (target_guid, interactive_rca, collect_on_demand) VALUES (l_target_guid, interactive_rca_in, l_collect_ondemand); ELSE UPDATE mgmt_rca_target_props SET interactive_rca = interactive_rca_in WHERE target_guid = l_target_guid; END IF; -- if changing to automatic set recovery flag for swith to automatic -- check if there is a severity and submit an RCA task if there IF interactive_rca_in = 0 THEN BEGIN -- get the metric guid for response/status l_metric_name := MGMT_GLOBAL.G_AVAIL_METRIC_NAME; l_metric_column := MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN; l_resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props( target_type_in, l_metric_name , l_metric_column, l_type_meta_ver, l_category_prop_1, l_category_prop_2, l_category_prop_3, l_category_prop_4, l_category_prop_5); -- look for a current policy violation SELECT violation_guid INTO l_violation_guid FROM mgmt_current_violation WHERE target_guid = l_target_guid AND policy_guid = l_resp_stat_metric_guid; EXCEPTION WHEN NO_DATA_FOUND THEN l_violation_guid := NULL; IF l_violation_guid IS NOT NULL THEN -- have a current status violation so queue a RCA task for it queue_rca_task(l_target_guid, l_violation_guid); END IF; END; END IF; END set_interactive_test; PROCEDURE queue_rca_task(target_guid_in IN RAW, violation_guid_in IN RAW) IS l_task_exists NUMBER(1); l_qname VARCHAR2(30); BEGIN -- 1st check to see if there are any current tasks for this combination l_qname := emd_notification.GET_QUEUE(EMD_NOTIFICATION.RCA_DEVICE_TYPE, 'RCA', target_guid_in); l_task_exists := check_message(l_qname, target_guid_in, violation_guid_in); IF l_task_exists = 0 THEN -- none exists so queue one emd_notification.queue_rca_task(target_guid_in, violation_guid_in, EMD_NOTIFICATION.RCA_SEVERITY, 'RCA_NORMAL'); END IF; END queue_rca_task; PROCEDURE queue_clear_cache(target_guid_in IN RAW) IS v_guid RAW(16); l_message VARCHAR2(1000); BEGIN /* BEGIN l_message := concat('queue_clear_cache: ',' '); l_message := concat(l_message,' '); l_message := concat(l_message,RAWTOHEX(target_guid_in)); insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message); END; */ emd_notification.queue_rca_task(target_guid_in, v_guid, EMD_NOTIFICATION.RCA_SEVERITY, 'RCA_CLEAR_CACHE'); END queue_clear_cache; -- PURPOSE: -- get the interactive RCA flag for a particular target -- PROCEDURE get_interactive_test(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, interactive_rca_out OUT NUMBER) IS l_interactive_rca NUMBER := 0; l_collect_ondemand NUMBER := 1; l_is_service NUMBER := mgmt_service.implements_service_interface(target_type_in); l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- must be a service IF l_is_service = 0 THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- lookup the target guid l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); IF l_target_guid IS NULL THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- see if there is a current flag (update or insert) BEGIN SELECT interactive_rca, collect_on_demand INTO interactive_rca_out, l_collect_ondemand FROM mgmt_rca_target_props WHERE target_guid = l_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN interactive_rca_out := 0; END; END get_interactive_test; FUNCTION get_interactive_flag(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) RETURN NUMBER IS l_flag NUMBER; BEGIN l_flag := 0; BEGIN SELECT p.interactive_rca INTO l_flag FROM mgmt_rca_target_props p, mgmt_targets t WHERE p.target_guid = t.target_guid AND t.target_name = target_name_in AND t.target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_flag := 0; END; RETURN l_flag; END get_interactive_flag; -- PURPOSE: -- get the list of events associated with a target that impact other services -- over the period of time specified. Also return the list of affected services -- over that same time. -- PROCEDURE get_events_and_impacts(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, num_days_in IN NUMBER, events_cursor_out OUT cursorType, impacts_cursor_out OUT cursorType) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- lookup the target l_target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); IF l_target_guid IS NULL THEN RAISE MGMT_GLOBAL.target_does_not_exist; END IF; -- get END get_events_and_impacts; -- PURPOSE: -- Return RCA run details and severity details -- -- IN Parameters: -- service_guid_in: Service guid -- severity_guid_in: RAW severity guid -- update_id_in: NUMBER update inidcating the rca run or -1 to fetch the latest run detail -- -- OUT Parameters: -- user_privilege_out: NUMBER falg to check user privileges -- alert_summary_out: cursorType Cursor containing rca alert summary information -- PROCEDURE get_sev_summary(service_guid_in IN RAW, severity_guid_in IN RAW, root_cause_only_in IN NUMBER, hasPriv OUT NUMBER, alert_summary_out OUT cursorType, rca_impacts_out OUT cursorType) IS BEGIN hasPriv := mgmt_user.has_priv(mgmt_user.get_current_em_user(), mgmt_user.VIEW_TARGET, service_guid_in); IF (hasPriv<>0) THEN get_alert_summary(severity_guid_in, alert_summary_out); END IF; get_affected_services_bysev(severity_guid_in, root_cause_only_in, rca_impacts_out); END get_sev_summary; PROCEDURE get_rca_notif_content(severity_guid_in IN RAW, rca_causeoffailure_cur_out OUT cursorType, target_timezone_out OUT VARCHAR2, severity_timestamp_out OUT TIMESTAMP) IS BEGIN get_failure_causes(severity_guid_in, -1, rca_causeoffailure_cur_out); BEGIN SELECT t.timezone_region, v.collection_timestamp INTO target_timezone_out, severity_timestamp_out FROM mgmt_targets t, mgmt_violations v WHERE v.violation_guid = severity_guid_in AND t.target_guid = v.target_guid; END; END get_rca_notif_content; FUNCTION check_message(qname_in IN VARCHAR2, target_guid_in IN RAW, violation_guid_in IN RAW) RETURN NUMBER IS dq_options dbms_aq.dequeue_options_t; qMsg_properties dbms_aq.message_properties_t; l_notif MGMT_NOTIFY_NOTIFICATION; l_id RAW(16); CURSOR msgs IS SELECT msg_id FROM aq$mgmt_notify_qtable where msg_state in ('READY','WAIT'); BEGIN dq_options.consumer_name := qname_in; dq_options.wait := 0; dq_options.navigation := DBMS_AQ.FIRST_MESSAGE; dq_options.dequeue_mode := DBMS_AQ.BROWSE; FOR msg IN msgs LOOP BEGIN -- peek the notification dq_options.msgid := msg.msg_id; BEGIN DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q', dequeue_options => dq_options, message_properties => qMsg_properties, payload => l_notif, msgid => l_id); EXCEPTION WHEN NO_NOTIF_AQ_MSG THEN GOTO next_msg; END; -- check the target and violation IF HEXTORAW(l_notif.rule_owner) = target_guid_in AND l_notif.source_guid = violation_guid_in THEN RETURN 1; END IF; END; << next_msg >> NULL; END LOOP; RETURN 0; END check_message; PROCEDURE remove_task(qname_in IN VARCHAR2, msgid_in IN RAW) IS dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; message MGMT_NOTIFY_NOTIFICATION; message_id RAW(16); BEGIN dequeue_options.dequeue_mode := DBMS_AQ.REMOVE; dequeue_options.msgid := msgid_in; dequeue_options.consumer_name := qname_in; DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q', dequeue_options => dequeue_options, message_properties => message_properties, payload => message, msgid => message_id); END remove_task; PROCEDURE remove_task_by_target(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS dq_options dbms_aq.dequeue_options_t; qMsg_properties dbms_aq.message_properties_t; l_notif MGMT_NOTIFY_NOTIFICATION; l_id RAW(16); l_del_id RAW(16); l_targ_guid RAW(16); l_message VARCHAR2(1000); CURSOR msgs IS SELECT msg_id FROM aq$mgmt_notify_qtable where msg_state in ('READY','WAIT'); CURSOR ques IS SELECT qname FROM mgmt_notify_queues WHERE qname LIKE 'RCA%'; BEGIN /* BEGIN l_message := concat('remove_task_by_target: ',target_name_in); l_message := concat(l_message,' '); l_message := concat(l_message,RAWTOHEX(target_guid_in)); insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message); END; */ dq_options.wait := 0; dq_options.navigation := DBMS_AQ.FIRST_MESSAGE; dq_options.dequeue_mode := DBMS_AQ.BROWSE; l_targ_guid := target_guid_in; IF l_targ_guid IS NULL THEN BEGIN SELECT target_guid INTO l_targ_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; END; END IF; -- clear any cache entries -- queue_clear_cache(l_targ_guid); FOR que IN ques LOOP BEGIN dq_options.consumer_name := que.qname; dq_options.navigation := DBMS_AQ.FIRST_MESSAGE; dq_options.msgid := NULL; FOR msg IN msgs LOOP BEGIN -- peek the notification dq_options.msgid := msg.msg_id; BEGIN DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q', dequeue_options => dq_options, message_properties => qMsg_properties, payload => l_notif, msgid => l_id); EXCEPTION WHEN NO_NOTIF_AQ_MSG THEN GOTO next_msg; END; IF HEXTORAW(l_notif.rule_owner) = l_targ_guid THEN l_del_id := l_id; END IF; END; << next_msg >> NULL; END LOOP; IF l_del_id IS NOT NULL THEN BEGIN dq_options.dequeue_mode := DBMS_AQ.REMOVE; dq_options.msgid := l_del_id; DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q', dequeue_options => dq_options, message_properties => qMsg_properties, payload => l_notif, msgid => l_id); END; END IF; END; << next_que >> NULL; END LOOP; -- COMMIT; END remove_task_by_target; PROCEDURE peek_message(qname_in IN VARCHAR2, notif_que_out OUT NOTIF_QUEUE_MSGS) IS i INTEGER := 0; l_msgs NOTIF_QUEUE_MSGS := NOTIF_QUEUE_MSGS(); dq_options dbms_aq.dequeue_options_t; qMsg_properties dbms_aq.message_properties_t; l_notif MGMT_NOTIFY_NOTIFICATION; l_id RAW(16); l_targ_name VARCHAR2(256); l_targ_type VARCHAR2(256); CURSOR msgs IS SELECT msg_id FROM aq$mgmt_notify_qtable where msg_state in ('READY','WAIT'); BEGIN dq_options.consumer_name := qname_in; dq_options.wait := 0; dq_options.navigation := DBMS_AQ.FIRST_MESSAGE; dq_options.dequeue_mode := DBMS_AQ.BROWSE; FOR msg IN msgs LOOP BEGIN -- peek the notification dq_options.msgid := msg.msg_id; BEGIN DBMS_AQ.DEQUEUE(queue_name => 'MGMT_NOTIFY_Q', dequeue_options => dq_options, message_properties => qMsg_properties, payload => l_notif, msgid => l_id); EXCEPTION WHEN NO_NOTIF_AQ_MSG THEN GOTO next_msg; END; -- get the target name BEGIN SELECT target_name, target_type INTO l_targ_name, l_targ_type FROM mgmt_targets WHERE target_guid = HEXTORAW(l_notif.rule_owner); EXCEPTION WHEN NO_DATA_FOUND THEN l_targ_name := 'No Such Target'; l_targ_type := l_notif.rule_owner; END; l_msgs.extend(1); i := i + 1; l_msgs(i) := NOTIF_QUEUE_MSG(RAWTOHEX(l_id), l_notif, l_targ_name, l_targ_type, qMsg_properties.delay, qMsg_properties.enqueue_time, qMsg_properties.state); END; << next_msg >> NULL; END LOOP; notif_que_out := l_msgs; END peek_message; PROCEDURE get_rca_result_set(severity_array_in IN SEVERITY_GUID_ARRAY, results_out OUT RCA_RESULTS_ARRAY) IS i INTEGER := 0; c INTEGER := 0; l_causes RCA_CAUSE_ARRAY := RCA_CAUSE_ARRAY(); l_cause_cursor cursorType; l_timezone VARCHAR2(64); l_timestamp TIMESTAMP; l_index NUMBER; l_targname VARCHAR2(256); l_targtype VARCHAR2(64); l_sourcetype NUMBER; l_eventaction VARCHAR2(512); l_resguid RAW(16); l_rkeyvalue VARCHAR2(256); l_rtime DATE; l_rmsg VARCHAR2(4000); l_rmsgid VARCHAR2(64); l_rmsgp VARCHAR2(4000); l_sevguid RAW(16); l_vkeyvalue VARCHAR2(256); l_vtime DATE; l_vmsg VARCHAR2(4000); l_vmsgid VARCHAR2(256); l_vmsgp VARCHAR2(4000); l_metcolumn VARCHAR2(64); l_metname VARCHAR2(64); l_guid RAW(16); BEGIN results_out := RCA_RESULTS_ARRAY(); FOR l_index IN 1..severity_array_in.LAST LOOP l_guid := HEXTORAW(severity_array_in(l_index)); get_rca_notif_content(l_guid, l_cause_cursor, l_timezone, l_timestamp); results_out.extend(1); i := i + 1; results_out(i) := RCA_RESULTS_RECORD(severity_array_in(l_index), l_timezone, l_timestamp, NULL); results_out(i).cause_array := RCA_CAUSE_ARRAY(); c := 0; LOOP FETCH l_cause_cursor INTO l_targname, l_targtype, l_sourcetype, l_eventaction, l_resguid, l_rkeyvalue, l_rtime, l_rmsg, l_rmsgid, l_rmsgp, l_sevguid, l_vkeyvalue, l_vtime, l_vmsg, l_vmsgid, l_vmsgp, l_metname, l_metcolumn; EXIT WHEN l_cause_cursor%NOTFOUND; results_out(i).cause_array.extend(1); c := c + 1; IF l_sourcetype = STATUS_SEVERITY OR l_sourcetype = METRIC_SEVERITY THEN results_out(i).cause_array(c) := RCA_CAUSE_RECORD(l_targname, l_targtype, l_vtime, l_vmsg, l_vmsgid, l_vmsgp); ELSE results_out(i).cause_array(c) := RCA_CAUSE_RECORD(l_targname, l_targtype, l_rtime, l_rmsg, l_rmsgid, l_rmsgp); END IF; END LOOP; END LOOP; END get_rca_result_set; -- Callback to delete RCA data related to a target PROCEDURE handle_target_delete(p_target_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW) IS l_message VARCHAR2(1000); BEGIN /* BEGIN l_message := concat('handle_target_delete: ',p_target_name); l_message := concat(l_message,' '); l_message := concat(l_message,RAWTOHEX(p_target_guid)); insert into mgmt_rca_log (log_timestamp, log_message) values (SYSDATE, l_message); END; */ delete_target_rca(p_target_name, p_target_type, p_target_guid); END handle_target_delete; PROCEDURE handle_assoc_delete(p_assoc_def_name IN VARCHAR2, p_source_target_name IN VARCHAR2, p_source_target_type IN VARCHAR2, p_assoc_target_name IN VARCHAR2, p_assoc_target_type IN VARCHAR2, p_scope_target_name IN VARCHAR2, p_scope_target_type IN VARCHAR2) IS BEGIN delete_target_assoc_rca(p_assoc_target_name, p_assoc_target_type, p_source_target_name, p_source_target_type); END handle_assoc_delete; -- procedure used during data generation for test harness to verify -- that target has a response-status metric PROCEDURE validate_target_avail_metric( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_category_prop_1 IN VARCHAR2, p_category_prop_2 IN VARCHAR2, p_category_prop_3 IN VARCHAR2, p_category_prop_4 IN VARCHAR2, p_category_prop_5 IN VARCHAR2) IS resp_stat_metric_guid mgmt_metrics.metric_guid%TYPE; target_guid mgmt_targets.target_guid%TYPE; p_metric_name MGMT_METRICS.metric_name%TYPE; p_metric_column MGMT_METRICS.metric_column%TYPE; BEGIN -- lookup the target target_guid := mgmt_target.get_target_guid(target_name_in, target_type_in); -- -- Check if a host/response/status metric has been defined. If not, -- create one. Otherwise, continue on. -- BEGIN p_metric_name := MGMT_GLOBAL.G_AVAIL_METRIC_NAME; p_metric_column := MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN; resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props( target_type_in, p_metric_name , p_metric_column, p_type_meta_ver, p_category_prop_1, p_category_prop_2, p_category_prop_3, p_category_prop_4, p_category_prop_5); EXCEPTION WHEN MGMT_GLOBAL.no_such_metric_found THEN -- Add Response/Status metric to the host name -- Insert 'Response' table metric. MGMT_METRIC.create_metric( target_type_in, p_metric_name, p_type_meta_ver, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_TABLE, p_metric_column_list => MGMT_METRIC_COLUMN_ARRAY( MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN, p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, p_is_key => MGMT_GLOBAL.G_FALSE)) ); resp_stat_metric_guid := MGMT_METRIC.get_metric_guid_for_props( target_type_in, p_metric_name, p_metric_column, p_type_meta_ver, p_category_prop_1, p_category_prop_2, p_category_prop_3, p_category_prop_4, p_category_prop_5); END; -- Add threshold for this metric BEGIN INSERT INTO mgmt_metric_thresholds (target_guid, metric_guid, key_value, warning_operator, warning_threshold, critical_operator, critical_threshold, num_occurences, num_warnings, num_criticals, eval_order, fixit_job) VALUES (target_guid, resp_stat_metric_guid, ' ', 0, ' ', 0, '0', 1, 0, 0, 0, ''); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Ignore duplicate errors NULL; END; -- Add data for the Response/Status metric to the host name -- Collection time stamp should be in target timezone -- loadtimestamp should be in repository timezone -- INSERT INTO mgmt_metrics_raw -- (target_guid, metric_guid, collection_timestamp, -- key_value, value, string_value) -- VALUES -- (p_target_guid, resp_stat_metric_guid, -- MGMT_GLOBAL.SYSDATE_TZRGN(:new.timezone_region), -- ' ', 1, NULL); END validate_target_avail_metric; FUNCTION IS_TEST_BASED_AVAIL ( p_service_target_name IN VARCHAR2, p_service_target_type IN VARCHAR2) RETURN NUMBER IS l_avail_method VARCHAR2(256):= NULL; l_is_service NUMBER; BEGIN l_is_service := mgmt_service.implements_service_interface(p_service_target_type); IF ( l_is_service = 1 ) THEN l_avail_method := mgmt_service.get_svc_avail_comp_method(p_service_target_name, p_service_target_type); END IF; IF ( l_avail_method = MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP ) THEN RETURN 1; ELSIF ( l_avail_method = MGMT_SERVICE.G_SYSTEM_BASED_AVAIL_COMP ) THEN RETURN 0; END IF; RETURN -1; END; end mgmt_rca; / show errors;