Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/gensvc/gensvc_view_body.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/13 12:11:27 nsalam Exp $ Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/gensvc/gensvc_view_body.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/13 12:11:27 nsalam Exp $ Rem Rem gensvc_view_body.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem gensvc_view_body.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem nsalam 03/11/09 - XbranchMerge mfidanbo_bug-6157595 from Rem st_emcore_10.2.0.1.0 Rem sbhagwat 09/19/06 - Retroactive blackout Rem jureyes 08/10/06 - Backport jureyes_bug-5397710 from main Rem jureyes 08/03/06 - Fix bug 5397710 (no correlate data for OC4J Rem clusters) Rem eporter 03/15/06 - recognize business category in get_gensvc_alerts Rem andyao 12/07/05 - Backport andyao_bug-4657949 from main Rem mfidanbo 11/16/05 - sort by target_name, type Rem mfidanbo 11/23/05 - Backport mfidanbo_bug-4731612 from main Rem andyao 11/14/05 - fix the incorrect outjoin Rem andyao 11/14/05 - fix bug 4657949 Rem andyao 11/28/05 - Rem andyao 12/07/05 - Backport andyao_bug-4739983 from main Rem yxie 09/21/05 - add license report proc Rem mfidanbo 08/16/05 - dont display avail if markers are beyond periods Rem to be shown Rem jriel 08/01/05 - fix connect by for 9206 version Rem mfidanbo 07/12/05 - optimize get_all_assoc targets Rem mfidanbo 06/26/05 - provide page level consolidated data getter Rem jpyang 06/27/05 - Rem jriel 06/07/05 - add get topo member details Rem mfidanbo 06/07/05 - fix timezone coming from toplevel tgt Rem mfidanbo 05/17/05 - add new states to gensvcCompSummary Rem mfidanbo 05/12/05 - dont use end marker time for sevs Rem mfidanbo 04/10/05 - use marker times Rem jriel 05/02/05 - remove cycle Rem jriel 03/28/05 - fix get sys Rem jriel 03/24/05 - fix member query Rem dtsao 03/21/05 - Fix tvmlz830.dif Rem mfidanbo 03/18/05 - remove global.tgt_sysdate reference Rem mfidanbo 03/16/05 - fix severities and outages cursors Rem jriel 03/10/05 - network target changes Rem jriel 02/28/05 - Rem mfidanbo 02/18/05 - return all alerts in outage_cursor Rem jpyang 02/17/05 - get test_based or not in component target Rem mfidanbo 02/03/05 - get avail_type for service target types. Rem dtsao 02/01/05 - make avail_all_assoc work for groups - modify Rem get_component_array Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem mfidanbo 01/05/05 - fix component list builder, add Rem get_component_summary Rem vagarwal 12/22/04 - change get_avail_single_target to work for Rem groups target.return null instead of unknown, if Rem there is no record in mgmt_current_availability Rem vagarwal 12/20/04 - use outer join to adapt Rem GET_AVAIL_ALL_ASSOC_TARGETS for groups Rem mfidanbo 12/01/04 - fix call to tgt_sysdate Rem mfidanbo 11/18/04 - add get testXbcn list Rem mfidanbo 11/12/04 - sys_based vs test_based avail Rem mfidanbo 11/15/04 - fix rep schema Rem mfidanbo 11/10/04 - MTA changes Rem mfidanbo 11/08/04 - fix cursor Rem mfidanbo 11/08/04 - add optional system check Rem mfidanbo 11/01/04 - add get_tests_for_svc Rem mfidanbo 10/26/04 - Key component summary Rem jpyang 10/28/04 - remove marker time Rem mfidanbo 10/25/04 - mfidanbo_sql_fix Rem mfidanbo 10/25/04 - Created Rem CREATE OR REPLACE PACKAGE BODY gensvc_view IS FUNCTION get_target_end_marker_time( target_guid_in IN mgmt_targets.target_guid%TYPE) RETURN DATE IS l_end_marker_time DATE; BEGIN -- get the end marker time SELECT marker_timestamp INTO l_end_marker_time FROM mgmt_availability_marker WHERE target_guid = target_guid_in; RETURN l_end_marker_time; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN mgmt_avail.get_target_start_marker_time(target_guid_in); END get_target_end_marker_time; PROCEDURE decide_timezone(l_target_guid IN RAW, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, l_top_lvl_tgt_guid OUT VARCHAR2, l_top_lvl_tz OUT VARCHAR2) IS BEGIN IF(top_level_target_name IS NOT NULL AND top_level_target_type IS NOT NULL) THEN --get the top_level_target_tz l_top_lvl_tgt_guid := mgmt_target.get_target_guid(top_level_target_name, top_level_target_type); SELECT timezone_region INTO l_top_lvl_tz FROM MGMT_TARGETS WHERE target_guid = l_top_lvl_tgt_guid; ELSE l_top_lvl_tgt_guid := l_target_guid; END IF; IF(top_level_tz IS NOT NULL) THEN l_top_lvl_tz := top_level_tz; END IF; IF(l_top_lvl_tz IS NULL) THEN -- if top_level tz is still null -- get it from the passed target SELECT timezone_region INTO l_top_lvl_tz FROM MGMT_TARGETS WHERE target_guid = l_target_guid; END IF; IF(l_top_lvl_tz IS NULL) THEN -- if top_level tz is still null -- change to OMS tz SELECT timezone_region INTO l_top_lvl_tz FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND rownum = 1; END IF; END decide_timezone; -- given a target, returns its timezone -- and decides which timezone to be used PROCEDURE get_target_timezone (target_guid_in IN RAW, tz_region_in IN VARCHAR2, target_tz OUT VARCHAR2, final_tz OUT VARCHAR2) IS BEGIN SELECT timezone_region INTO target_tz FROM MGMT_TARGETS WHERE target_guid = target_guid_in; -- use target tz if tz_region_in is null IF(tz_region_in IS NULL) THEN final_tz := target_tz; ELSE final_tz := tz_region_in; END IF; END get_target_timezone; PROCEDURE compute_start_end_dates( target_guid_in IN RAW, tz_region_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_date_out OUT DATE, end_date_out OUT DATE) IS l_adjusted_sysdate DATE; l_target_tz MGMT_TARGETS.TIMEZONE_REGION%TYPE; l_final_tz MGMT_TARGETS.TIMEZONE_REGION%TYPE; l_recomputed_start_date DATE; l_recomputed_end_date DATE; l_start_date DATE; l_end_date DATE; l_start_marker DATE; l_end_marker DATE; BEGIN get_target_timezone(target_guid_in, tz_region_in, l_target_tz, l_final_tz); l_adjusted_sysdate := CAST(systimestamp at time zone l_final_tz AS DATE); IF( start_date_in IS NOT NULL AND end_date_in IS NOT NULL ) THEN l_start_date := start_date_in; l_end_date := end_date_in; ELSE l_start_date := l_adjusted_sysdate - num_of_days_in; l_end_date := l_adjusted_sysdate; END IF; -- now we have start and end dates in the l_final_tz -- get target marker limits and convert them to l_final_tz l_start_marker := MGMT_GLOBAL.ADJUST_TZ(mgmt_avail.get_target_start_marker_time( target_guid_in ), l_target_tz, l_final_tz); l_end_marker := MGMT_GLOBAL.ADJUST_TZ(get_target_end_marker_time( target_guid_in ), l_target_tz, l_final_tz); l_recomputed_start_date := GREATEST( l_start_date, l_start_marker ); l_recomputed_end_date := LEAST( l_end_date, l_end_marker ); IF( l_recomputed_start_date > l_recomputed_end_date ) THEN l_recomputed_start_date := l_recomputed_end_date; END IF; start_date_out := l_recomputed_start_date; end_date_out := l_recomputed_end_date; END compute_start_end_dates; PROCEDURE compute_disp_start_end_dates( target_guid_in IN RAW, tz_region_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_date_out OUT DATE, end_date_out OUT DATE) IS l_target_tz MGMT_TARGETS.TIMEZONE_REGION%TYPE; l_final_tz MGMT_TARGETS.TIMEZONE_REGION%TYPE; l_adjusted_sysdate DATE; BEGIN get_target_timezone(target_guid_in, tz_region_in, l_target_tz, l_final_tz); l_adjusted_sysdate := CAST(systimestamp at time zone l_final_tz AS DATE); IF( start_date_in IS NOT NULL AND end_date_in IS NOT NULL ) THEN start_date_out := start_date_in; end_date_out := end_date_in; ELSE start_date_out := l_adjusted_sysdate - num_of_days_in; end_date_out := l_adjusted_sysdate; END IF; END compute_disp_start_end_dates; FUNCTION compute_tz_adj_start_date ( target_guid_in IN RAW, l_start_date IN DATE, from_tz IN VARCHAR2, to_tz IN VARCHAR2 ) RETURN DATE IS start_date_out DATE; BEGIN start_date_out := MGMT_GLOBAL.ADJUST_TZ(l_start_date, from_tz, to_tz); start_date_out := GREATEST(start_date_out, mgmt_avail.get_target_start_marker_time( target_guid_in ) ); return start_date_out; END compute_tz_adj_start_date; FUNCTION compute_tz_adj_end_date ( target_guid_in IN RAW, l_end_date IN DATE, from_tz IN VARCHAR2, to_tz IN VARCHAR2 ) RETURN DATE IS end_date_out DATE; BEGIN end_date_out := MGMT_GLOBAL.ADJUST_TZ(l_end_date, from_tz, to_tz); end_date_out := LEAST(end_date_out, get_target_end_marker_time( target_guid_in ) ); return end_date_out; END compute_tz_adj_end_date; -- only_key -- A = look for contains assoc -- E = look for supports e2e assoc FUNCTION get_component_array ( target_guid_in IN RAW, tz_region_in IN VARCHAR2, l_start_date IN DATE, l_end_date IN DATE, only_key IN VARCHAR ) RETURN COMPONENT_ARRAY IS l_targets COMPONENT_ARRAY := NULL; tz_target MGMT_TARGETS.TIMEZONE_REGION%TYPE; l_final_tz MGMT_TARGETS.TIMEZONE_REGION%TYPE; sys_guid MGMT_TARGETS.TARGET_GUID%TYPE; single_comp COMPONENT; ll_start_date DATE; ll_end_date DATE; l_count PLS_INTEGER; BEGIN get_target_timezone(target_guid_in, tz_region_in, tz_target, l_final_tz); IF ( only_key IS NULL OR only_key = 'Y' ) THEN SELECT COMPONENT(t2.target_guid, t2.target_name, t2.target_type, mgmt_target.get_avail_current_status(t2.target_guid), l_final_tz, t2.timezone_region, l_start_date, l_end_date, 0, 0 ) BULK COLLECT INTO l_targets FROM mgmt_targets t2, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t2.target_guid AND target_guid_in = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_DEPENDS_ON; ELSIF ( only_key = 'A' ) THEN SELECT COMPONENT(tgt.target_guid, tgt.target_name, tgt.target_type, mgmt_target.get_avail_current_status(tgt.target_guid), l_final_tz, tgt.timezone_region, l_start_date, l_end_date,0,0 ) BULK COLLECT INTO l_targets FROM mgmt_targets tgt, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = tgt.target_guid AND target_guid_in = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_CONTAINS; ELSIF ( only_key = 'E' ) THEN SELECT COMPONENT(tgt.target_guid, tgt.target_name, tgt.target_type, mgmt_target.get_avail_current_status(tgt.target_guid), l_final_tz, tgt.timezone_region, l_start_date, l_end_date,0,0 ) BULK COLLECT INTO l_targets FROM mgmt_targets tgt, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = tgt.target_guid AND target_guid_in = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = 'supports_e2e_on'; ELSE SELECT t2.target_guid INTO sys_guid FROM mgmt_targets t2, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t2.target_guid AND target_guid_in = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_RUNS_ON; SELECT COMPONENT(tgt.target_guid, tgt.target_name, tgt.target_type, mgmt_target.get_avail_current_status(tgt.target_guid), l_final_tz, tgt.timezone_region, l_start_date, l_end_date,0,0 ) BULK COLLECT INTO l_targets FROM (SELECT distinct tgt.target_name, tgt.target_type FROM mgmt_targets tgt, mgmt_flat_target_assoc flat_assoc, mgmt_metrics met WHERE flat_assoc.source_target_guid = sys_guid AND flat_assoc.is_membership = 1 AND flat_assoc.assoc_target_guid = tgt.target_guid AND tgt.target_type = met.target_type AND tgt.type_meta_ver = met.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' ') AND met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN MINUS SELECT tgt3.target_name, tgt3.target_type FROM mgmt_flat_target_assoc flat_assoc2, mgmt_type_properties type_props, mgmt_Targets tgt2, mgmt_target_assocs assoc, mgmt_targets tgt3 WHERE flat_assoc2.source_target_guid = sys_guid AND flat_assoc2.is_membership = 1 AND flat_assoc2.assoc_target_guid = tgt2.target_guid AND tgt2.target_type=type_props.target_type AND type_props.property_name = MGMT_GLOBAL.G_IS_CLUSTER_PROP AND type_props.property_value = '1' AND assoc.source_target_guid = tgt2.target_guid AND assoc.assoc_guid = mgmt_assoc.g_contains_guid AND assoc.assoc_target_guid = tgt3.target_guid) targets, mgmt_targets tgt WHERE tgt.target_name = targets.target_name AND tgt.target_type = targets.target_type; END IF; IF(l_targets IS NOT NULL AND l_targets.COUNT > 0) THEN FOR l_count IN l_targets.FIRST..l_targets.LAST LOOP single_comp := l_targets(l_count); compute_start_end_dates(single_comp.target_guid, l_final_tz, single_comp.start_time, single_comp.end_time, 0, ll_start_date, ll_end_date); l_targets(l_count).start_time := MGMT_GLOBAL.ADJUST_TZ(ll_start_date, l_final_tz, single_comp.target_tz); l_targets(l_count).end_time := MGMT_GLOBAL.ADJUST_TZ(ll_end_date, l_final_tz, single_comp.target_tz); -- set is_test_avail l_targets(l_count).is_test_avail := test_based_available(single_comp.target_name, single_comp.target_type); -- set has_component_array l_targets(l_count).has_comp_array := has_component_array(single_comp.target_name, single_comp.target_type); -- MGMT_LOG.LOG_ERROR('LOADER', 15, 'For target ' || l_targets(l_count).target_name || ' using: ' || to_char(ll_start_date, 'mm/dd/yyyy hh:mi:ss') || ' and ' || to_char(ll_end_date, 'mm/dd/yyyy hh:mi:ss')); END LOOP; END IF; RETURN l_targets; END get_component_array; FUNCTION IMPLEMENTS_INTERFACE(v_target_type IN VARCHAR2, v_interface IN VARCHAR2) RETURN NUMBER IS l_implements NUMBER := 0; BEGIN SELECT DECODE(property_value, '1', 1, 0) INTO l_implements FROM mgmt_type_properties WHERE target_type = v_target_type AND property_name = v_interface; return l_implements; EXCEPTION WHEN NO_DATA_FOUND THEN -- if the property is not defined, then it doesnt implement the interface return 0; END IMPLEMENTS_INTERFACE; FUNCTION has_component_array( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2 ) RETURN NUMBER IS l_avail_selector VARCHAR2(256) := NULL; BEGIN IF(MGMT_SERVICE.IMPLEMENTS_SERVICE_INTERFACE(target_type_in) = 1) THEN l_avail_selector := mgmt_service.GET_SVC_AVAIL_COMP_METHOD(target_name_in, target_type_in); IF ( l_avail_selector = MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP ) THEN RETURN 0; ELSE RETURN 1; -- system-based generic_service END IF; END IF; IF( IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_AGGREGATE_PROP) = 1 OR IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_GROUP_PROP) = 1 OR IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_COMPOSITE_PROP) = 1 OR IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_REDUNDANCY_GROUP_PROP) = 1 OR IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_CLUSTER_PROP) = 1 OR IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_SYSTEM_PROP) = 1 OR IMPLEMENTS_INTERFACE(target_type_in, MGMT_GLOBAL.G_IS_AGGREGATE_SERVICE_PROP) = 1 ) THEN RETURN 1; END IF; RETURN 0; END has_component_array; FUNCTION test_based_available( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2) RETURN NUMBER IS l_avail_selector VARCHAR2(256) := NULL; is_test_avail_out NUMBER := 0; BEGIN IF (MGMT_SERVICE.IMPLEMENTS_SERVICE_INTERFACE(target_type_in) = 1 ) THEN l_avail_selector := mgmt_service.GET_SVC_AVAIL_COMP_METHOD(target_name_in, target_type_in); IF ( l_avail_selector = MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP ) THEN is_test_avail_out := 1; ELSE is_test_avail_out := 0; END IF; END IF; RETURN is_test_avail_out; END; PROCEDURE get_sev_for_service(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, include_rbk_in IN NUMBER, outages_cur_out OUT CURSORTYPE, severities_cur_out OUT CURSORTYPE, metric_severities_cur_out OUT CURSORTYPE ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_target_sysdate DATE; l_start_date DATE; l_end_date DATE; l_recomputed_start_date DATE; l_recomputed_end_date DATE; l_num_of_days NUMBER; l_start_marker_time DATE; l_end_marker_time DATE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); compute_start_end_dates(l_target_guid, NULL, start_date_in, end_date_in, num_of_days_in, l_start_marker_time, l_end_marker_time); compute_disp_start_end_dates(l_target_guid, NULL, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); l_recomputed_start_date := l_start_marker_time; l_recomputed_end_date := l_end_date; OPEN outages_cur_out FOR SELECT avail.current_status outage_type, DECODE(avail.end_collection_timestamp, NULL, 'TRUE', 'FALSE') is_open, (LEAST(NVL(avail.end_collection_timestamp, l_recomputed_end_date),l_recomputed_end_date) - avail.start_collection_timestamp)*24*60*60 as duration, avail.start_collection_timestamp as start_timestamp, NVL(avail.end_collection_timestamp, l_recomputed_end_date) as end_timestamp FROM mgmt_availability avail WHERE avail.target_guid = l_target_guid AND NVL(avail.end_collection_timestamp, l_recomputed_end_date) > l_recomputed_start_date AND avail.start_collection_timestamp <= l_recomputed_end_date AND avail.current_status <> MGMT_GLOBAL.G_STATUS_UP ORDER BY start_timestamp; IF( include_rbk_in = 0) THEN OPEN severities_cur_out FOR SELECT violations.violation_guid, violations.collection_timestamp, violations.message, violations.message_nlsid, violations.message_params, violations.violation_level, comments.message FROM (SELECT violGuids.violation_guid, max(comments.collection_timestamp) as collection_timestamp FROM (SELECT violations.violation_guid FROM mgmt_violations violations WHERE violations.target_guid = l_target_guid AND violations.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY AND violations.collection_timestamp <= l_recomputed_end_date AND ( (violations.collection_timestamp + violations.violation_duration) > l_recomputed_start_date OR violations.violation_duration IS NULL ) ) violGuids, mgmt_severity_annotation comments WHERE comments.severity_guid (+) = violGuids.violation_guid GROUP BY violGuids.violation_guid ) data, mgmt_violations violations, mgmt_severity_annotation comments WHERE violations.violation_guid = data.violation_guid AND comments.severity_guid (+) = data.violation_guid AND comments.collection_timestamp (+) = data.collection_timestamp ORDER BY violations.collection_timestamp; ELSE OPEN severities_cur_out FOR SELECT severity_guid as violation_guid, collection_timestamp, message, message_nlsid, message_params, severity_code as violation_level, NULL as message FROM mgmt_severity_rbk WHERE target_guid = l_target_guid AND collection_timestamp > l_recomputed_start_date AND collection_timestamp <= l_recomputed_end_date UNION SELECT violations.violation_guid, violations.collection_timestamp, violations.message, violations.message_nlsid, violations.message_params, violations.violation_level, comments.message FROM (SELECT violGuids.violation_guid, max(comments.collection_timestamp) as collection_timestamp FROM (SELECT violations.violation_guid FROM mgmt_violations violations WHERE violations.target_guid = l_target_guid AND violations.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY AND violations.collection_timestamp <= l_recomputed_end_date AND ( (violations.collection_timestamp + violations.violation_duration) > l_recomputed_start_date OR violations.violation_duration IS NULL ) ) violGuids, mgmt_severity_annotation comments WHERE comments.severity_guid (+) = violGuids.violation_guid GROUP BY violGuids.violation_guid ) data, mgmt_violations violations, mgmt_severity_annotation comments WHERE violations.violation_guid = data.violation_guid AND comments.severity_guid (+) = data.violation_guid AND comments.collection_timestamp (+) = data.collection_timestamp ORDER BY collection_timestamp; END IF; OPEN metric_severities_cur_out FOR select target_name_in from dual; -- OPEN metric_severities_cur_out FOR -- SELECT violations.violation_level, -- violations.v_startdate as start_timestamp, -- violations.violation_duration*60*60 as duration, -- violations.message, -- violations.message_nlsid, -- violations.message_params, -- violations.v_guid -- FROM (SELECT m.metric_guid as metric_guid -- FROM mgmt_metrics m, -- mgmt_category_map c -- WHERE c.object_guid = m.metric_guid -- AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC -- AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE -- AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE) p_metrics, -- (SELECT v.violation_guid as v_guid, -- (v.collection_timestamp + (v.violation_duration / 24)) as v_enddate, -- v.violation_level as violation_level, -- v.collection_timestamp as v_startdate, -- v.violation_duration as violation_duration, -- v.policy_guid as policy_guid, -- v.message, v.message_nlsid, v.message_params -- FROM mgmt_violations v -- WHERE v.target_guid = l_target_guid -- AND ( (v.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING ) OR -- (v.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL) ) -- AND v.collection_timestamp <= l_recomputed_end_date) violations -- WHERE violations.policy_guid = p_metrics.metric_guid -- AND violations.v_enddate > l_recomputed_start_date -- ORDER BY start_timestamp; END get_sev_for_service; PROCEDURE get_severities_for_service( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, outages_cur_out OUT CURSORTYPE, severities_cur_out OUT CURSORTYPE, metric_severities_cur_out OUT CURSORTYPE ) IS BEGIN get_sev_for_service(target_name_in,target_type_in,start_date_in,end_date_in, num_of_days_in,0,outages_cur_out,severities_cur_out, metric_severities_cur_out); END get_severities_for_service; PROCEDURE get_sev_for_service_with_rbk( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, outages_cur_out OUT CURSORTYPE, severities_cur_out OUT CURSORTYPE, metric_severities_cur_out OUT CURSORTYPE ) IS BEGIN get_sev_for_service(target_name_in,target_type_in,start_date_in,end_date_in, num_of_days_in,1,outages_cur_out,severities_cur_out, metric_severities_cur_out); END get_sev_for_service_with_rbk; PROCEDURE get_avail_single_target( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_time_out OUT DATE, end_time_out OUT DATE, tz_out OUT VARCHAR2, current_status_out OUT NUMBER, avail_pct_out OUT NUMBER, status_since_out OUT DATE, avail_states_cur_out OUT CURSORTYPE, severity_states_cur_out OUT CURSORTYPE, is_test_avail_out OUT VARCHAR2) IS l_target_guid mgmt_targets.target_guid%TYPE; l_top_lvl_tgt_guid mgmt_targets.target_guid%TYPE; l_top_lvl_tz mgmt_targets.timezone_region%TYPE := NULL; l_target_sysdate DATE; l_start_date DATE; l_end_date DATE; l_recomputed_start_date DATE; l_recomputed_end_date DATE; l_num_of_days NUMBER; l_start_marker_time DATE; l_end_marker_time DATE; l_avail_selector VARCHAR2(128); l_is_test_avail NUMBER; l_target_tz mgmt_targets.timezone_region%TYPE; BEGIN -- Store the target guid to improve performance. l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); SELECT timezone_region INTO l_target_tz FROM MGMT_TARGETS WHERE target_guid = l_target_guid; decide_timezone(l_target_guid, top_level_target_name, top_level_target_type, top_level_tz, l_top_lvl_tgt_guid, l_top_lvl_tz); compute_disp_start_end_dates(l_target_guid, l_top_lvl_tz, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); -- the start time and the end time for the timeline window needs to be -- returned in the timezone of the target in question -- if start date and end date were passed in from the ui, then this will -- be used for the start time and end time to be returned. otherwise, the -- start date and end date computed above will be used for the start time -- and end time to be returned -- determine the start time to be returned, in top_level tz start_time_out := l_start_date; -- determine the end time to be returned, int top_level_tz end_time_out := l_end_date; -- determine the tz to be returned tz_out := l_top_lvl_tz; -- the recomputed start and end dates should be in target tz -- since the availability records is in target tz compute_start_end_dates(l_target_guid, null, start_date_in, end_date_in, num_of_days_in, l_recomputed_start_date, l_recomputed_end_date); -- get the avail pct of the target using the computed l_num_of_days above avail_pct_out := mgmt_target.get_avail_pct_value( l_target_guid, num_of_days_in); -- get the current status of the target current_status_out := mgmt_target.get_avail_current_status( l_target_guid ); -- get the current status since timestamp status_since_out := mgmt_target.get_current_status_timestamp(l_target_guid); l_is_test_avail := test_based_available(target_name_in, target_type_in); IF ( MGMT_SERVICE.IMPLEMENTS_SERVICE_INTERFACE(target_type_in) = 1 ) THEN l_avail_selector := mgmt_service.GET_SVC_AVAIL_COMP_METHOD(target_name_in, target_type_in); IF ( l_avail_selector = MGMT_SERVICE.G_TEST_BASED_AVAIL_COMP ) THEN is_test_avail_out := 'Y'; ELSE is_test_avail_out := 'N'; END IF; END IF; -- get the avail states data needed for the avail time line -- by conventions all durations are returned as seconds (s) OPEN avail_states_cur_out FOR SELECT l_target_guid as target_guid, target_name_in as target_name, target_type_in as target_type, current_status_out as current_status, avail.severity_guid as severity_guid, avail.current_status as historical_status, (LEAST(NVL(avail.end_collection_timestamp, l_recomputed_end_date),l_recomputed_end_date) - GREATEST(avail.start_collection_timestamp, l_recomputed_start_date))*24*60*60 as duration, MGMT_GLOBAL.ADJUST_TZ(GREATEST(avail.start_collection_timestamp, l_recomputed_start_date),l_target_tz, tz_out) as start_timestamp, MGMT_GLOBAL.ADJUST_TZ(NVL(avail.end_collection_timestamp, l_recomputed_end_date),l_target_tz, tz_out) as end_timestamp, l_is_test_avail as test_based, '1' as comp_targets_count FROM mgmt_availability avail WHERE avail.target_guid = l_target_guid AND NVL(avail.end_collection_timestamp, l_recomputed_end_date) > l_recomputed_start_date AND avail.start_collection_timestamp <= l_recomputed_end_date AND avail.start_collection_timestamp < NVL(avail.end_collection_timestamp, l_recomputed_end_date) ORDER BY start_timestamp; OPEN severity_states_cur_out FOR select target_name_in from dual; -- OPEN severity_states_cur_out FOR -- SELECT l_target_guid as target_guid, -- target_name_in as target_name, -- target_type_in as target_type, -- violations.violation_level as violation_level, -- violations.v_startdate as start_timestamp, -- DECODE(violations.violation_duration, 0, l_recomputed_end_date, violations.v_enddate) as end_timestamp, -- violations.violation_duration*60*60 as duration -- FROM (SELECT m.metric_guid as metric_guid -- FROM mgmt_metrics m, -- mgmt_category_map c -- WHERE c.object_guid = m.metric_guid -- AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC -- AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE -- AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE) p_metrics, -- (SELECT v.violation_guid as v_guid, -- (v.collection_timestamp + (v.violation_duration / 24)) as v_enddate, -- v.violation_level as violation_level, -- v.collection_timestamp as v_startdate, -- v.violation_duration as violation_duration, -- v.policy_guid as policy_guid -- FROM mgmt_violations v -- WHERE v.target_guid = l_target_guid -- AND ( (v.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING ) OR -- (v.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL) ) -- AND v.collection_timestamp <= l_recomputed_end_date) violations -- WHERE violations.policy_guid = p_metrics.metric_guid -- AND violations.v_enddate > l_recomputed_start_date -- ORDER BY start_timestamp; END get_avail_single_target; FUNCTION get_empty_record ( tgt IN COMPONENT, start_time IN date, end_time IN date ) RETURN COMP_AVAIL IS v_dummy COMP_AVAIL; BEGIN v_dummy := COMP_AVAIL(tgt.target_guid, tgt.target_name, tgt.target_type, tgt.current_status, NULL, -1, (end_time - start_time)*24*60*60, start_time, end_time, tgt.is_test_avail, tgt.has_comp_array); RETURN v_dummy; END get_empty_record; PROCEDURE get_avail_all_assoc_targets( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, assoc_type_in IN VARCHAR2, start_time_out OUT DATE, end_time_out OUT DATE, tz_out OUT VARCHAR2, avail_states_cur_out OUT CURSORTYPE, perf_states_cur_out OUT CURSORTYPE ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_top_lvl_tgt_guid mgmt_targets.target_guid%TYPE; l_top_lvl_tz mgmt_targets.timezone_region%TYPE := NULL; l_target_sysdate DATE; l_start_date DATE; l_end_date DATE; l_first_point_timestamp DATE; l_recomputed_start_date DATE; l_recomputed_end_date DATE; l_num_of_days NUMBER; l_targets COMPONENT_ARRAY := NULL; l_only_key VARCHAR2(5); l_target_tz mgmt_targets.timezone_region%TYPE; per_target_avail COMP_AVAIL_ARRAY := COMP_AVAIL_ARRAY(); all_target_avail COMP_AVAIL_ARRAY := COMP_AVAIL_ARRAY(); BEGIN -- Store the target guid to improve performance. l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); SELECT timezone_region INTO l_target_tz FROM MGMT_TARGETS WHERE target_guid = l_target_guid; decide_timezone(l_target_guid, top_level_target_name, top_level_target_type, top_level_tz, l_top_lvl_tgt_guid, l_top_lvl_tz); compute_disp_start_end_dates(l_target_guid, l_top_lvl_tz, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); -- determine the start time to be returned start_time_out := l_start_date; -- determine the end time to be returned end_time_out := l_end_date; -- set tz_out tz_out := l_top_lvl_tz; -- determine all of the associated targets and get current status and availability information -- for all of them. Need to do TZ conversion since the targets might be in a different timezone -- than service IF ( assoc_type_in = MGMT_ASSOC.ASSOC_TYPE_DEPENDS_ON ) THEN l_only_key := 'Y'; ELSIF (assoc_type_in = MGMT_ASSOC.ASSOC_TYPE_CONTAINS ) THEN l_only_key := 'A'; ELSE l_only_key := 'N'; END IF; l_targets := get_component_array(l_target_guid, l_top_lvl_tz, l_start_date, l_end_date, l_only_key); IF(l_targets IS NOT NULL AND l_targets.COUNT > 0) THEN FOR i IN l_targets.FIRST..l_targets.LAST LOOP per_target_avail := COMP_AVAIL_ARRAY(); SELECT COMP_AVAIL(l_targets(i).target_guid, l_targets(i).target_name, l_targets(i).target_type, l_targets(i).current_status, avail.severity_guid, avail.current_status, avail.duration, avail.start_ts, avail.end_ts, l_targets(i).is_test_avail, l_targets(i).has_comp_array) BULK COLLECT INTO per_target_avail FROM (SELECT a.severity_guid, a.current_status, (LEAST(NVL(a.end_collection_timestamp, l_targets(i).end_time), l_targets(i).end_time) - GREATEST(a.start_collection_timestamp, l_targets(i).start_time))*24*60*60 as duration, MGMT_GLOBAL.ADJUST_TZ(GREATEST( a.start_collection_timestamp, l_targets(i).start_time), l_targets(i).target_tz, l_targets(i).service_tz) as start_ts, MGMT_GLOBAL.ADJUST_TZ( LEAST(NVL(a.end_collection_timestamp, l_targets(i).end_time), l_targets(i).end_time), l_targets(i).target_tz, l_targets(i).service_tz) as end_ts FROM mgmt_availability a WHERE l_targets(i).target_guid = a.target_guid AND NVL(a.end_collection_timestamp, l_targets(i).end_time) > l_targets(i).start_time AND a.start_collection_timestamp <= l_targets(i).end_time AND a.start_collection_timestamp < NVL(a.end_collection_timestamp, l_targets(i).end_time) AND l_targets(i).start_time < l_targets(i).end_time -- the period should be valid ) avail; -- per_target_avail is already initialized so just check if count is 0. IF(per_target_avail.COUNT = 0) THEN per_target_avail := COMP_AVAIL_ARRAY(); per_target_avail.extend(1); per_target_avail(1) := get_empty_record(l_targets(i), start_time_out, end_time_out); END IF; FOR j IN per_target_avail.FIRST..per_target_avail.LAST LOOP all_target_avail.extend(1); all_target_avail(all_target_avail.count) := per_target_avail(j); END LOOP; IF(per_target_avail IS NOT NULL) THEN per_target_avail.DELETE; per_target_avail := NULL; END IF; END LOOP; END IF; OPEN avail_states_cur_out FOR SELECT superavail.target_guid as target_guid, superavail.target_name as target_name, superavail.target_type as target_type, superavail.current_status as current_status, superavail.severity_guid as severity_guid, superavail.historical_status as historical_status, superavail.duration as duration, superavail.start_timestamp as start_timestamp, superavail.end_timestamp as end_timestamp, superavail.test_based as test_based, superavail.comp_targets_count as comp_targets_count FROM (SELECT * FROM TABLE(CAST(all_target_avail AS COMP_AVAIL_ARRAY))) superavail ORDER BY superavail.target_type, superavail.target_name, superavail.start_timestamp; OPEN perf_states_cur_out FOR select target_name_in from dual; -- OPEN perf_states_cur_out FOR -- SELECT targets.target_guid as target_guid, -- targets.target_name, -- targets.target_type, -- violations.violation_level, -- violations.v_startdate as start_timestamp, -- DECODE(violations.violation_duration, 0, l_recomputed_end_date, violations.v_enddate) as end_timestamp, -- violations.violation_duration*60*60 as duration -- FROM (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, -- (SELECT m.metric_guid as metric_guid -- FROM mgmt_metrics m, -- mgmt_category_map c -- WHERE c.object_guid = m.metric_guid -- AND c.object_type = mgmt_global.G_TYPE_THRESHOLD_METRIC -- AND c.class_name = mgmt_global.G_CATEGORY_CLASS_SERVICE -- AND c.category_name = mgmt_global.G_SERVICE_CATEGORY_RESPONSE) p_metrics, -- (SELECT v.target_guid as v_target_guid, -- v.violation_guid as v_guid, -- (v.collection_timestamp + (v.violation_duration / 24)) as v_enddate, -- v.violation_level as violation_level, -- v.collection_timestamp as v_startdate, -- v.violation_duration as violation_duration, -- v.policy_guid as policy_guid -- FROM mgmt_violations v, -- (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) v_targets -- WHERE v.target_guid = v_targets.target_guid -- AND ( (v.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING ) OR -- (v.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL) ) -- AND v.collection_timestamp <= v_targets.end_time) violations -- WHERE violations.policy_guid = p_metrics.metric_guid -- AND violations.v_target_guid = targets.target_guid -- AND violations.v_enddate > targets.start_time -- ORDER BY target_guid, start_timestamp; END get_avail_all_assoc_targets; PROCEDURE get_hist_key_comp_summary( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, system_name_out OUT VARCHAR2, system_type_out OUT VARCHAR2, down_comps_out OUT NUMBER, up_comps_out OUT NUMBER, warning_alerts_out OUT NUMBER, critical_alerts_out OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_target_sysdate DATE; l_start_date DATE; l_end_date DATE; l_num_of_days NUMBER; l_targets COMPONENT_ARRAY := NULL; BEGIN -- Store the target guid to improve performance. l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); compute_start_end_dates(l_target_guid, NULL, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); SELECT t2.target_name, t2.target_type INTO system_name_out, system_type_out FROM mgmt_targets t2, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t2.target_guid AND l_target_guid = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_RUNS_ON; -- get only key components l_targets := get_component_array(l_target_guid, NULL, l_start_date, l_end_date, 'Y'); SELECT COUNT(*) INTO up_comps_out FROM TABLE(CAST(l_targets as COMPONENT_ARRAY)) WHERE current_status = MGMT_GLOBAL.G_STATUS_UP; SELECT COUNT(*) INTO down_comps_out FROM TABLE(CAST(l_targets as COMPONENT_ARRAY)) WHERE current_status = MGMT_GLOBAL.G_STATUS_DOWN; SELECT COUNT(*) INTO warning_alerts_out FROM (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, (SELECT v.target_guid as v_target_guid, v.violation_guid as v_guid, (v.collection_timestamp + (v.violation_duration / 24)) as v_enddate, v.violation_level as violation_level, v.collection_timestamp as v_startdate, v.violation_duration as violation_duration, v.policy_guid as policy_guid FROM mgmt_violations v, (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) v_targets WHERE v.target_guid = v_targets.target_guid AND v.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND v.collection_timestamp <= v_targets.end_time) violations WHERE violations.v_target_guid = targets.target_guid AND violations.v_enddate > targets.start_time; SELECT COUNT(*) INTO critical_alerts_out FROM (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, (SELECT v.target_guid as v_target_guid, v.violation_guid as v_guid, (v.collection_timestamp + (v.violation_duration / 24)) as v_enddate, v.violation_level as violation_level, v.collection_timestamp as v_startdate, v.violation_duration as violation_duration, v.policy_guid as policy_guid FROM mgmt_violations v, (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) v_targets WHERE v.target_guid = v_targets.target_guid AND v.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND v.collection_timestamp <= v_targets.end_time) violations WHERE violations.v_target_guid = targets.target_guid AND violations.v_enddate > targets.start_time; END get_hist_key_comp_summary; PROCEDURE get_hist_test_summary ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, tests_cur_out OUT CURSORTYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; l_target_sysdate DATE; l_start_date DATE; l_end_date DATE; l_num_of_days NUMBER; l_targets COMPONENT_ARRAY := NULL; BEGIN -- Store the target guid to improve performance. l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); compute_start_end_dates(l_target_guid, NULL, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); OPEN tests_cur_out FOR SELECT test.name AS test_name, test.txn_type AS test_type, --test.current_status, NVL(sev_detail.warning_count, 0) AS warning_count, NVL(sev_detail.critical_count,0) AS critical_count FROM (SELECT test.txn_guid AS txn_guid, SUM(DECODE(v.violation_level, MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(v.violation_level, MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM mgmt_metrics_composite_keys keys, (SELECT name, txn_guid FROM mgmt_bcn_txn_defn WHERE target_guid = l_target_guid AND is_representative = 'Y') test, mgmt_violations v WHERE keys.target_guid = l_target_guid AND keys.key_part1_value = test.name AND v.policy_guid = keys.composite_key AND v.collection_timestamp <= l_end_date AND (v.collection_timestamp + (v.violation_duration / 24)) > l_start_date GROUP BY test.txn_guid) sev_detail, mgmt_bcn_txn_defn test WHERE test.txn_guid = sev_detail.txn_guid (+) AND test.target_guid = l_target_guid AND test.is_representative = 'Y'; END get_hist_test_summary; PROCEDURE get_curr_key_comp_summary( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, system_name_out OUT VARCHAR2, system_type_out OUT VARCHAR2, states_out OUT CURSORTYPE, warning_alerts_out OUT NUMBER, critical_alerts_out OUT NUMBER) IS l_target_guid mgmt_targets.target_guid%TYPE; l_targets COMPONENT_ARRAY := NULL; BEGIN -- Store the target guid to improve performance. l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); warning_alerts_out := 0; critical_alerts_out := 0; BEGIN SELECT t2.target_name, t2.target_type INTO system_name_out, system_type_out FROM mgmt_targets t2, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t2.target_guid AND l_target_guid = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_RUNS_ON; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; l_targets := get_component_array(l_target_guid, null, null, null, 'Y'); OPEN states_out FOR SELECT current_status, count(*) FROM TABLE(CAST(l_targets as COMPONENT_ARRAY)) GROUP BY current_status; SELECT COUNT(*) INTO warning_alerts_out FROM (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, mgmt_current_violation v WHERE v.target_guid = targets.target_guid AND v.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND ( v.violation_type = 0 ); SELECT COUNT(*) INTO critical_alerts_out FROM (SELECT * FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, mgmt_current_violation v WHERE v.target_guid = targets.target_guid AND v.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND ( v.violation_type = 0 ); END get_curr_key_comp_summary; PROCEDURE get_curr_test_summary ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, tests_cur_out OUT CURSORTYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); OPEN tests_cur_out FOR SELECT test.name AS test_name, test.txn_type AS test_type, test.txn_guid AS test_guid, mgmt_bcn_txn_view.get_cur_test_status(l_target_guid, test.txn_guid) AS current_status, NVL(sev_detail.warning_count, 0) AS warning_count, NVL(sev_detail.critical_count,0) AS critical_count FROM (SELECT test.txn_guid AS txn_guid, SUM(DECODE(v.violation_level, MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(v.violation_level, MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM mgmt_metrics_composite_keys keys, (SELECT name, txn_guid FROM mgmt_bcn_txn_defn WHERE target_guid = l_target_guid AND is_representative = 'Y') test, mgmt_current_violation v WHERE keys.target_guid = l_target_guid AND keys.key_part1_value = test.name AND v.key_value = keys.composite_key AND v.target_guid = l_target_guid GROUP BY test.txn_guid) sev_detail, mgmt_bcn_txn_defn test WHERE test.txn_guid = sev_detail.txn_guid (+) AND test.target_guid = l_target_guid AND test.is_representative = 'Y'; END get_curr_test_summary; PROCEDURE get_test_alerts ( target_guid_in IN mgmt_targets.target_guid%TYPE, alert_type_in IN VARCHAR2, alerts_out OUT CURSORTYPE) IS BEGIN OPEN alerts_out FOR SELECT test.name AS name, test.txn_type as type, test.txn_guid as guid, alert.message as message, alert.message_nlsid as message_nlsid, alert.message_params as message_params, alert.violation_level as severity_code, alert.collection_timestamp as triggered_date, alert.violation_guid as severity_guid FROM mgmt_bcn_txn_defn test, mgmt_current_violation alert, mgmt_metrics_composite_keys keys WHERE test.target_guid = target_guid_in AND ( test.is_representative = 'Y' OR alert_type_in = 'ALL_TEST_ALERTS' ) AND keys.target_guid = target_guid_in AND keys.key_part1_value = test.name AND alert.key_value = keys.composite_key AND alert.target_guid = target_guid_in AND ( alert.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING OR alert.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL ); END get_test_alerts; PROCEDURE get_system_alerts ( target_guid_in IN mgmt_targets.target_guid%TYPE, alerts_out OUT CURSORTYPE) IS l_targets COMPONENT_ARRAY := NULL; BEGIN OPEN alerts_out FOR SELECT system.target_name AS name, system.target_type as type, system.target_guid as guid, alert.message as message, alert.message_nlsid as message_nlsid, alert.message_params as message_params, alert.violation_level as severity_code, alert.collection_timestamp as triggered_date, alert.violation_guid as severity_guid FROM (SELECT t.target_guid, t.target_name, t.target_type FROM mgmt_targets t, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t.target_guid AND target_guid_in = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_DEPENDS_ON) system, mgmt_current_violation alert WHERE alert.target_guid = system.target_guid AND alert.violation_type = 0 AND ( alert.violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING OR alert.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL ); END get_system_alerts; PROCEDURE get_gensvc_alerts ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alert_type_in IN VARCHAR2, alerts_out OUT CURSORTYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; alerts_usage_perf CURSORTYPE; alerts_system CURSORTYPE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); IF( alert_type_in IS NULL OR alert_type_in = mgmt_global.G_SERVICE_CATEGORY_USAGE OR alert_type_in = mgmt_global.G_SERVICE_CATEGORY_PERFORMANCE OR alert_type_in = mgmt_global.G_SERVICE_CATEGORY_BUSINESS ) THEN EM_AGGSVC_UI.GET_SERVICE_ALERTS_INFO(target_name_in, target_type_in, alert_type_in, alerts_out); ELSIF( alert_type_in = 'ALL_TEST_ALERTS' OR alert_type_in = 'KEY_TEST_ALERTS') THEN get_test_alerts(l_target_guid, alert_type_in, alerts_out); ELSE get_system_alerts(l_target_guid, alerts_out); END IF; END get_gensvc_alerts; PROCEDURE get_tests_for_svc ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, tests_out OUT CURSORTYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); OPEN tests_out FOR SELECT d.txn_guid AS test_guid, d.txn_type AS test_type, d.name AS test_name, d.is_representative AS is_key, d.state AS state FROM MGMT_BCN_TXN_DEFN d WHERE d.target_guid = l_target_guid AND (d.is_representative = 'Y' OR only_key = 'N'); END get_tests_for_svc; PROCEDURE get_bcns_for_svc ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, bcns_out OUT CURSORTYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); OPEN bcns_out FOR SELECT t2.target_guid AS bcn_guid, t2.target_name AS bcn_name, b.participates_avail AS avail_beacon, b.is_local AS local_beacon FROM MGMT_BCN_TARGET b, MGMT_TARGETS t2 WHERE b.target_guid = l_target_guid AND b.beacon_target_guid = t2.target_guid AND t2.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE AND (only_key = 'N' OR b.participates_avail = 'Y'); END get_bcns_for_svc; -- PROCEDURE get_tests_bcns_for_svc -- PURPOSE: Returns the test, bcn cursor for a given service -- The cursor includes the test_name, test_type, test_guid -- bcn_name, bcn_guid -- composite key value for a specific test and beacon PROCEDURE get_tests_bcns_for_svc ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key_tests IN VARCHAR2, only_key_bcns IN VARCHAR2, test_bcns_out OUT CURSORTYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); OPEN test_bcns_out FOR SELECT tests.test_guid, tests.test_type, tests.test_name, tests.is_key, bcns.bcn_guid, bcns.bcn_name, bcns.avail_beacon, bcns.local_beacon, k.composite_key as composite_key FROM (SELECT t2.target_guid AS bcn_guid, t2.target_name AS bcn_name, b.participates_avail AS avail_beacon, b.is_local AS local_beacon FROM MGMT_BCN_TARGET b, MGMT_TARGETS t2 WHERE b.target_guid = l_target_guid AND b.beacon_target_guid = t2.target_guid AND t2.target_type = MGMT_GLOBAL.G_BEACON_TARGET_TYPE AND (only_key_bcns = 'N' OR b.participates_avail = 'Y')) bcns, (SELECT d.txn_guid AS test_guid, d.txn_type AS test_type, d.name AS test_name, d.is_representative AS is_key FROM MGMT_BCN_TXN_DEFN d WHERE d.target_guid = l_target_guid AND (d.is_representative = 'Y' OR only_key_tests = 'N')) tests, MGMT_METRICS_COMPOSITE_KEYS k WHERE k.target_guid = l_target_guid AND k.key_part1_value = tests.test_name AND k.key_part2_value = bcns.bcn_name AND (k.key_part3_value IS NULL OR k.key_part3_value = ' ') AND (k.key_part4_value IS NULL OR k.key_part4_value = ' ') AND (k.key_part5_value IS NULL OR k.key_part5_value = ' ') ORDER BY tests.test_name, tests.test_type; END; -- only used for txn avail page. -- the timezone if always in tgt timezone so no need -- to parameterize PROCEDURE single_tst_bcn_avail ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, test_name_in IN VARCHAR2, test_type_in IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, start_date_out OUT DATE, end_date_out OUT DATE, avail_pct_out OUT NUMBER, current_status OUT NUMBER, since OUT DATE, test_avail OUT CURSORTYPE, test_bcn_avail OUT CURSORTYPE, test_bcn_hist_avail OUT mgmt_bcn_txn_avail_array) IS l_target_guid mgmt_targets.target_guid%TYPE; l_start_date DATE; l_end_date DATE; l_bcns CURSORTYPE; l_test_name_types SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); l_test_guids SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_bcn_guids SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_ignored VARCHAR2(256); l_test_guid VARCHAR2(256); record_count NUMBER := 1; dummy_date DATE; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); compute_disp_start_end_dates(l_target_guid, NULL, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); start_date_out := l_start_date; end_date_out := l_end_date; get_bcns_for_svc(target_name_in, target_type_in, 'Y', l_bcns); compute_start_end_dates(l_target_guid, NULL, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); MGMT_BCN_TXN_VIEW.GET_TEST_AVAIL(target_name_in, target_type_in, test_name_in, test_type_in, num_of_days_in, dummy_date, dummy_date, avail_pct_out, current_status, since, test_avail); SELECT txn_guid BULK COLLECT INTO l_test_guids FROM mgmt_bcn_txn_defn WHERE target_guid = l_target_guid AND name = test_name_in AND txn_type = test_type_in; LOOP FETCH l_bcns INTO l_test_guid, l_ignored, l_ignored, l_ignored; EXIT WHEN l_bcns%NOTFOUND; l_bcn_guids.EXTEND; l_bcn_guids(record_count) := l_test_guid; record_count := record_count + 1; END LOOP; IF (record_count > 1) THEN MGMT_BCN_TXN_VIEW.GET_BCN_TXN_STATUS(target_name_in, target_type_in, l_test_guids, l_bcn_guids, test_bcn_avail); MGMT_BCN_TXN_VIEW.GET_BCN_TXN_AVAIL(target_name_in, target_type_in, l_start_date, l_end_date, 0, l_test_guids, l_bcn_guids, null, dummy_date, dummy_date, test_bcn_hist_avail); END IF; IF l_bcns%ISOPEN THEN CLOSE l_bcns; END IF; l_test_name_types.DELETE; l_test_guids.DELETE; l_bcn_guids.DELETE; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END single_tst_bcn_avail; PROCEDURE get_test_bcn_avail( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, top_level_target_name IN VARCHAR2, top_level_target_type IN VARCHAR2, top_level_tz IN VARCHAR2, start_date_in IN DATE, end_date_in IN DATE, num_of_days_in IN NUMBER, only_key IN VARCHAR2, start_date_out OUT DATE, end_date_out OUT DATE, tz_out OUT VARCHAR2, test_avail OUT CURSORTYPE, test_bcn_avail OUT CURSORTYPE, test_bcn_hist_avail OUT mgmt_bcn_txn_avail_array) IS l_target_guid mgmt_targets.target_guid%TYPE; l_top_lvl_tgt_guid mgmt_targets.target_guid%TYPE; l_top_lvl_tz mgmt_targets.timezone_region%TYPE := NULL; l_start_date DATE; l_end_date DATE; l_tests CURSORTYPE; l_bcns CURSORTYPE; l_test_name_types SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); l_test_name VARCHAR2(256); l_test_type VARCHAR2(256); l_test_guids SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_bcn_guids SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_ignored VARCHAR2(256); l_test_guid VARCHAR2(256); record_count NUMBER := 1; dummy_date DATE; l_start_date_tgt_tz DATE; l_end_date_tgt_tz DATE; l_target_tz mgmt_targets.timezone_region%TYPE := NULL; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); SELECT timezone_region INTO l_target_tz FROM MGMT_TARGETS WHERE target_guid = l_target_guid; decide_timezone(l_target_guid, top_level_target_name, top_level_target_type, top_level_tz, l_top_lvl_tgt_guid, l_top_lvl_tz); compute_disp_start_end_dates(l_target_guid, l_top_lvl_tz, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); start_date_out := l_start_date; end_date_out := l_end_date; tz_out := l_top_lvl_tz; get_tests_for_svc(target_name_in, target_type_in, only_key, l_tests); get_bcns_for_svc(target_name_in, target_type_in, 'Y', l_bcns); LOOP FETCH l_tests INTO l_test_guid, l_test_type, l_test_name, l_ignored, l_ignored; EXIT WHEN l_tests%NOTFOUND; l_test_name_types.EXTEND; l_test_guids.EXTEND; l_test_name_types(record_count) := SMP_EMD_NVPAIR(l_test_name, l_test_type); l_test_guids(record_count) := l_test_guid; record_count := record_count + 1; END LOOP; -- the start and end dates passed to this function should be in target tz l_start_date_tgt_tz := MGMT_GLOBAL.ADJUST_TZ(start_date_out, l_top_lvl_tz, l_target_tz); l_end_date_tgt_tz := MGMT_GLOBAL.ADJUST_TZ(end_date_out, l_top_lvl_tz, l_target_tz); MGMT_BCN_TXN_VIEW.GET_TESTS_STATUS_AVAIL(target_name_in, target_type_in, l_test_name_types,l_start_date_tgt_tz, l_end_date_tgt_tz, 0, tz_out, test_avail); -- compute start and end dates for retrieving data compute_start_end_dates(l_target_guid, l_top_lvl_tz, start_date_in, end_date_in, num_of_days_in, l_start_date, l_end_date); l_start_date_tgt_tz := MGMT_GLOBAL.ADJUST_TZ(l_start_date, l_top_lvl_tz, l_target_tz); l_end_date_tgt_tz := MGMT_GLOBAL.ADJUST_TZ(L_end_date, l_top_lvl_tz, l_target_tz); record_count := 1; LOOP FETCH l_bcns INTO l_test_guid, l_test_type, l_test_name, l_ignored; EXIT WHEN l_bcns%NOTFOUND; l_bcn_guids.EXTEND; l_bcn_guids(record_count) := l_test_guid; record_count := record_count + 1; END LOOP; MGMT_BCN_TXN_VIEW.GET_BCN_TXN_STATUS(target_name_in, target_type_in, l_test_guids, l_bcn_guids, test_bcn_avail); MGMT_BCN_TXN_VIEW.GET_BCN_TXN_AVAIL(target_name_in, target_type_in, l_start_date_tgt_tz, l_end_date_tgt_tz, 0, l_test_guids, l_bcn_guids, tz_out, dummy_date, dummy_date, test_bcn_hist_avail); IF l_tests%ISOPEN THEN CLOSE l_tests; END IF; IF l_bcns%ISOPEN THEN CLOSE l_bcns; END IF; l_test_name_types.DELETE; l_test_guids.DELETE; l_bcn_guids.DELETE; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END get_test_bcn_avail; PROCEDURE get_service_home_data ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alert_type_in IN VARCHAR2, alerts_out OUT CURSORTYPE, tests_cur_out OUT CURSORTYPE, system_name_out OUT VARCHAR2, system_type_out OUT VARCHAR2, states_out OUT CURSORTYPE, warning_alerts_out OUT NUMBER, critical_alerts_out OUT NUMBER, metric_errors_out OUT NUMBER) IS BEGIN get_gensvc_alerts (target_name_in, target_type_in, alert_type_in, alerts_out); get_curr_test_summary (target_name_in, target_type_in, tests_cur_out); get_curr_key_comp_summary(target_name_in, target_type_in, system_name_out, system_type_out, states_out, warning_alerts_out, critical_alerts_out); SELECT COUNT(err.coll_name) INTO metric_errors_out FROM mgmt_targets tgt, mgmt_current_metric_errors err WHERE tgt.target_name = target_name_in AND tgt.target_type = target_type_in AND err.target_guid = tgt.target_guid AND err.metric_error_message IS NOT NULL AND err.metric_error_type = 0; END get_service_home_data; PROCEDURE get_component_summary ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, components OUT CURSORTYPE, system_name OUT VARCHAR2, system_type OUT VARCHAR2) IS l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_targets COMPONENT_ARRAY; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); l_targets := get_component_array(l_target_guid, NULL, NULL, NULL, only_key); BEGIN SELECT t2.target_name, t2.target_type INTO system_name, system_type FROM mgmt_targets t2, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t2.target_guid AND l_target_guid = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_RUNS_ON; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; OPEN components FOR SELECT tgt.target_name, tgt.target_type, tgt.target_name as display_name, tgt.type_display_name, targets.current_status AS current_status, NVL(sev_detail.warning_count, 0) AS warning_count, NVL(sev_detail.critical_count, 0) AS critical_count, tgt.target_guid, tgt.is_group AS is_group_type, tgt.host_name, NVL(policy_detail.information_count, 0) AS policy_information_count, NVL(policy_detail.warning_count, 0) AS policy_warning_count, NVL(policy_detail.critical_count, 0) AS policy_critical_count FROM (SELECT targets.target_guid as target_guid, SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM mgmt_current_severity sev, (SELECT target_guid FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets WHERE targets.target_guid = sev.target_guid GROUP BY targets.target_guid) sev_detail, (SELECT targets.target_guid as tguid, SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) information_count, SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) warning_count, SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) critical_count FROM (SELECT target_guid FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, mgmt_policy_assoc_eval_summ e, mgmt_policies r WHERE e.target_guid = targets.target_guid AND r.policy_guid = e.policy_guid AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY GROUP BY targets.target_guid) policy_detail, (SELECT target_guid, current_status FROM TABLE(CAST(l_targets as COMPONENT_ARRAY))) targets, mgmt_targets tgt WHERE tgt.target_guid = targets.target_guid AND tgt.target_guid = sev_detail.target_guid (+) AND tgt.target_guid = policy_detail.tguid (+) ORDER BY tgt.type_display_name, tgt.target_name; END get_component_summary; PROCEDURE get_system_and_components ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, only_key IN VARCHAR2, components OUT CURSORTYPE, system_name OUT VARCHAR2, system_type OUT VARCHAR2, system_guid OUT RAW) IS l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_targets COMPONENT_ARRAY; BEGIN l_target_guid := mgmt_target.get_target_guid( target_name_in, target_type_in ); l_targets := get_component_array(l_target_guid, NULL, NULL, NULL, only_key); BEGIN SELECT t2.target_guid, t2.target_name, t2.target_type INTO system_guid, system_name, system_type FROM mgmt_targets t2, mgmt_target_assocs a, mgmt_target_assoc_defs d WHERE a.assoc_target_guid = t2.target_guid AND l_target_guid = a.source_target_guid AND d.assoc_guid = a.assoc_guid AND d.association_type = MGMT_ASSOC.ASSOC_TYPE_RUNS_ON; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; IF system_guid IS NOT NULL THEN BEGIN OPEN components FOR SELECT mt.target_name, mt.target_type, mt.target_guid, mt.target_name as display_name, mt.type_display_name, mt.host_name FROM mgmt_flat_target_assoc a, mgmt_targets ct, mgmt_targets mt WHERE ct.target_guid = system_guid AND ct.target_guid = a.source_target_guid AND a.is_membership = 1 AND mt.target_guid = a.assoc_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END get_system_and_components; PROCEDURE get_service_topology ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, net_tgt_name_out OUT VARCHAR2, net_tgt_guid_out OUT RAW, topo_cursor_out OUT CURSORTYPE, cause_cursor_out OUT CURSORTYPE) IS l_violation_guid RAW(16); l_service_guid RAW(16); l_assoc_guid RAW(16); BEGIN SELECT target_guid INTO l_service_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; SELECT assoc_guid INTO l_assoc_guid FROM mgmt_target_assoc_defs WHERE assoc_def_name = MGMT_ASSOC.ASSOC_TYPE_DEPENDS_ON; OPEN topo_cursor_out FOR SELECT t1.target_guid as parent_target, t1.target_name as parent_name, t1.host_name, t1.target_type as parent_type, h1.target_guid as parent_host, t2.target_guid as child_target, t2.target_name as child_name, t2.host_name, t2.target_type as child_type, h2.target_guid as child_host, d.assoc_def_name as assoc_name, x.lev as depth, x.cyc, mgmt_service.implements_service_interface(t1.target_type) as is_parent_service, mgmt_service.implements_service_interface(t2.target_type) as is_child_service FROM mgmt_targets t, mgmt_targets t1, mgmt_targets t2, mgmt_target_assoc_defs d, mgmt_targets h1, mgmt_targets h2, ( /* 10g version SELECT DISTINCT a.source_target_guid, a.assoc_guid, a.assoc_target_guid, LEVEL as lev, connect_by_iscycle as cyc FROM mgmt_target_assocs a START WITH a.source_target_guid = (SELECT target_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in) CONNECT BY nocycle PRIOR a.assoc_target_guid = a.source_target_guid */ /* 9.2 version */ SELECT DISTINCT a.source_target_guid, a.assoc_guid, a.assoc_target_guid, LEVEL as lev, 0 as cyc FROM mgmt_target_assocs a START WITH a.source_target_guid = l_service_guid CONNECT BY PRIOR a.assoc_target_guid = a.source_target_guid AND a.assoc_guid = l_assoc_guid ) x WHERE x.source_target_guid = t.target_guid AND t1.target_guid = x.source_target_guid AND t2.target_guid = x.assoc_target_guid AND d.assoc_guid = x.assoc_guid AND d.assoc_def_name = MGMT_ASSOC.ASSOC_TYPE_DEPENDS_ON AND t1.host_name = h1.target_name (+) AND t2.host_name = h2.target_name (+) ORDER BY x.lev ASC; BEGIN SELECT target_name, target_guid INTO net_tgt_name_out, net_tgt_guid_out FROM mgmt_targets WHERE target_type = 'network'; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; BEGIN SELECT v.violation_guid INTO l_violation_guid FROM mgmt_current_violation v, mgmt_targets t, mgmt_metrics m WHERE t.target_name = target_name_in AND t.target_type = target_type_in AND m.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND m.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN 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_1 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_1 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_1 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_1 or m.category_prop_5 = ' ') AND v.target_guid = t.target_guid AND v.policy_guid = m.metric_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; IF l_violation_guid IS NOT NULL THEN BEGIN OPEN cause_cursor_out FOR SELECT x.symptom_event, x.cause_event, x.cause_target, x.symptom_target, x.is_root_cause, x.cause_target_name, x.cause_target_type, x.symptom_target_name, x.symptom_target_type, e.event_guid, 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 t.target_name, a.symptom_event_guid as symptom_event, t1.target_guid as symptom_target, t1.target_name as symptom_target_name, t1.target_type as symptom_target_type, a.cause_event_guid as cause_event, a.is_root_cause as is_root_cause, t2.target_guid as cause_target, t2.target_name as cause_target_name, t2.target_type as cause_target_type FROM mgmt_rca_summary s, mgmt_targets t, mgmt_rca_event_assoc a, mgmt_rca_event e1, mgmt_rca_event e2, mgmt_targets t1, mgmt_targets t2 WHERE t.target_guid = s.target_guid AND a.event_guid = s.event_guid AND a.update_id = s.last_update_id AND s.rca_status = 1 AND s.severity_guid = l_violation_guid AND e1.event_guid = a.symptom_event_guid AND e2.event_guid = a.cause_event_guid AND t1.target_guid = e1.target_guid AND t2.target_guid = e2.target_guid ) x WHERE (e.event_guid = x.cause_event or e.event_guid = x.symptom_event) 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 = 3 AND met.metric_guid = tes.metric_guid) OR (e.source_type = 1 and met.metric_guid = r.metric_test_guid) OR (( e.source_type = 0 or e.source_type = 2) 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 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN OPEN cause_cursor_out FOR SELECT * FROM dual WHERE ROWNUM<1; END; ELSE OPEN cause_cursor_out FOR SELECT * FROM dual WHERE ROWNUM<1; END IF; END get_service_topology; PROCEDURE get_topo_member_details(target_guid_list_in IN MGMT_TARGET_GUID_ARRAY, details_out OUT cursorType) IS BEGIN OPEN details_out FOR SELECT tgt.target_name, tgt.target_type, tgt.display_name, tgt.type_display_name, DECODE(avail_status.cur_status,NULL,MGMT_GLOBAL.G_STATUS_UNKNOWN,avail_status.cur_status) cur_status, NVL(alltgt.warning_count,0)+NVL(comptgt.warning_count,0) AS warning_count, NVL(alltgt.critical_count,0)+NVL(comptgt.critical_count,0) AS critical_count, NVL(allpolicy.information_count, 0) AS policy_information_count, NVL(allpolicy.warning_count, 0) AS policy_warning_count, NVL(allpolicy.critical_count, 0) AS policy_critical_count, tgt.target_guid, tgt.host_name FROM mgmt_targets tgt, (SELECT cur_status, target_guid FROM (SELECT avail.current_status as cur_status, avail.target_guid target_guid FROM mgmt_current_availability avail, mgmt_targets tgt WHERE tgt.target_guid=avail.target_guid UNION ALL (SELECT DECODE(SUM(DECODE(avail.current_status,1,0,1)),0,1,0) cur_status, mem.composite_target_guid target_guid FROM mgmt_current_availability avail, mgmt_flat_target_memberships mem WHERE avail.target_guid=mem.member_target_guid AND mem.composite_target_guid NOT IN (SELECT target_guid FROM mgmt_current_availability) GROUP BY mem.composite_target_guid))) avail_status, (SELECT SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count, SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count, target_guid tguid FROM mgmt_current_severity GROUP BY target_guid ) alltgt, (SELECT SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count, SUM(DECODE(cs.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count, tm.composite_target_guid tguid FROM mgmt_flat_target_memberships tm, mgmt_current_severity cs WHERE tm.member_target_guid = cs.target_guid GROUP BY tm.composite_target_guid) comptgt, /* (SELECT SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,1,0)) information_count, SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) warning_count, SUM(DECODE(violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) critical_count, target_guid tguid FROM mgmt_current_violation WHERE violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY AND (exempt_code = 0 OR (exempt_code = 2 and exempt_until <= SYSDATE)) GROUP BY target_guid ) allpolicy, */ (SELECT SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,e.non_exempt_violations_logged,0)) information_count, SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,e.non_exempt_violations_logged,0)) warning_count, SUM(DECODE(e.max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,e.non_exempt_violations_logged,0)) critical_count, e.target_guid tguid FROM mgmt_policy_assoc_eval_summ e, mgmt_policies p WHERE p.policy_guid = e.policy_guid AND p.policy_type = MGMT_GLOBAL.G_TYPE_POLICY GROUP BY e.target_guid) allpolicy, TABLE(CAST(target_guid_list_in AS MGMT_TARGET_GUID_ARRAY)) guids WHERE tgt.target_guid = guids.column_value AND tgt.target_guid = avail_status.target_guid(+) AND tgt.target_guid = comptgt.tguid(+) AND tgt.target_guid = alltgt.tguid(+) AND tgt.target_guid = allpolicy.tguid(+) ORDER BY tgt.type_display_name, tgt.target_name; END get_topo_member_details; -- Private procedure -- Given a service target guid, -- find out how many tests are performed. -- Note: because a Web Transaction is a single service test, -- but may contain multiple tests (e.g. Data Granularity is set to Step or Step Group.) PROCEDURE get_http_tests_cnts(tgt_id IN RAW, tsts_count OUT NUMBER, enabled_tsts OUT NUMBER, disabled_tsts OUT NUMBER) IS l_granularity NUMBER; l_txnbcn_granularity NUMBER; l_http_cnt NUMBER; BEGIN IF ( tgt_id IS NULL ) THEN RETURN; END IF; enabled_tsts := 0; disabled_tsts := 0; tsts_count := 0; FOR txn in (SELECT txn_guid, name, state FROM mgmt_bcn_txn_defn WHERE target_guid = tgt_id AND txn_type = 'HTTP') LOOP BEGIN SELECT MAX(DECODE(string_value, 'transaction', 1, 'uagroup', 2, 'page', 3, 0 )) INTO l_granularity FROM mgmt_bcn_txn_props WHERE target_guid = tgt_id AND txn_guid = txn.txn_guid AND name = 'granularity'; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- default to 'transaction' IF (l_granularity IS NULL) THEN l_granularity := 1; END IF; BEGIN SELECT MAX(DECODE(bp1.string_value, 'transaction', 1, 'uagroup', 2, 'page', 3, l_granularity )) INTO l_txnbcn_granularity FROM (SELECT bp.bcn_guid as beacon_target_guid, bp.string_value FROM mgmt_bcn_bcntxn_props bp WHERE bp.target_guid = tgt_id AND bp.txn_guid = txn.txn_guid AND bp.name = 'granularity') bp1, (SELECT bt.beacon_target_guid FROM mgmt_bcn_target bt WHERE bt.target_guid = tgt_id) bt1 WHERE bt1.beacon_target_guid = bp1.beacon_target_guid(+); -- outer join: because a beacon override property (in mgmt_bcn_bcntxn_props) -- may or may not exist for an associated beacon. (in mgmt_bcn_target) EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- default to transaction level if -- no beacon override found and there are no associated beacons. IF (l_txnbcn_granularity IS NULL) THEN l_txnbcn_granularity := l_granularity; END IF; IF ( l_txnbcn_granularity = 1 ) THEN l_http_cnt := 1; ELSIF ( l_txnbcn_granularity = 2 ) THEN SELECT count(*) INTO l_http_cnt FROM mgmt_bcn_stepgroup_defn WHERE target_guid = tgt_id AND txn_guid = txn.txn_guid; -- If there are no groups defined, count for just the transaction IF ( l_http_cnt = 0 ) THEN l_http_cnt := 1; END IF; ELSIF ( l_txnbcn_granularity = 3 ) THEN -- count only the user actions, where parent_step_guid is null SELECT count(*) INTO l_http_cnt FROM mgmt_bcn_step_defn WHERE target_guid = tgt_id AND txn_guid = txn.txn_guid AND parent_step_guid IS null; ELSE l_http_cnt := 0; END IF; IF ( txn.state = 'NM' ) THEN disabled_tsts := disabled_tsts + l_http_cnt; ELSIF ( txn.state = 'M' ) THEN enabled_tsts := enabled_tsts + l_http_cnt; END IF; tsts_count := tsts_count + 1; END LOOP; END get_http_tests_cnts; PROCEDURE get_slm_pack_tgt_tests ( tgt_test_list OUT SLM_PACK_SUMMARY_ARRAY) IS l_cnt NUMBER := 0; l_bcn_assoc_cnt NUMBER := 0; l_http_cnt NUMBER := 0; l_query_string VARCHAR(1000); l_tgt_tests SMP_EMD_NVPAIR_ARRAY := NULL; l_tst_count NUMBER; l_enabled_tsts NUMBER; l_disabled_tsts NUMBER; BEGIN tgt_test_list := SLM_PACK_SUMMARY_ARRAY(); FOR tgt IN (SELECT distinct t.target_name, t.target_type, t.target_guid, d.txn_type FROM mgmt_targets t, mgmt_bcn_txn_defn d, mgmt_license_definitions l WHERE l.pack_label = 'service_level_mgmt' AND t.target_guid = d.target_guid AND l.target_type = t.target_type) LOOP l_cnt := 0; l_bcn_assoc_cnt := 0; l_tst_count := 0; l_enabled_tsts := 0; l_disabled_tsts := 0; IF ( tgt.target_type = mgmt_global.G_BEACON_TARGET_TYPE) THEN -- BEACON TARGETS SELECT COUNT(*) INTO l_enabled_tsts FROM mgmt_bcn_txn_defn WHERE target_guid = tgt.target_guid AND txn_type = tgt.txn_type AND state = 'M'; SELECT COUNT(*) INTO l_disabled_tsts FROM mgmt_bcn_txn_defn WHERE target_guid = tgt.target_guid AND txn_type = tgt.txn_type AND state = 'NM'; l_tst_count := l_enabled_tsts + l_disabled_tsts; ELSE -- NON BEACON TARGETS l_bcn_assoc_cnt := 0; IF (tgt.txn_type = 'HTTP') THEN -- For Non beacon targets -- get the count of associated beacons -- Check count for http test types get_http_tests_cnts(tgt.target_guid, l_tst_count, l_enabled_tsts, l_disabled_tsts); ELSE -- non http tests SELECT COUNT(*) INTO l_enabled_tsts FROM mgmt_bcn_txn_defn WHERE target_guid = tgt.target_guid AND txn_type = tgt.txn_type AND state = 'M' AND txn_type <> 'HTTP'; SELECT COUNT(*) INTO l_disabled_tsts FROM mgmt_bcn_txn_defn WHERE target_guid = tgt.target_guid AND state = 'NM' AND txn_type = tgt.txn_type AND txn_type <> 'HTTP'; l_tst_count := l_enabled_tsts + l_disabled_tsts; END IF; SELECT COUNT(*) INTO l_bcn_assoc_cnt FROM mgmt_bcn_target WHERE target_guid = tgt.target_guid AND target_guid != beacon_target_guid; IF ( l_bcn_assoc_cnt = 0 ) THEN l_disabled_tsts := l_disabled_tsts + l_enabled_tsts; l_enabled_tsts := 0; END IF; -- END OF BEACON COUNT END IF; -- END OF NON BEACON , NON BEACON TARGET TYPE IF ( l_enabled_tsts > 0 ) THEN tgt_test_list.extend; tgt_test_list(tgt_test_list.COUNT) := SLM_PACK_SUMMARY(RAWTOHEX(tgt.target_guid), tgt.txn_type, l_tst_count, l_enabled_tsts, l_disabled_tsts + l_enabled_tsts); END IF; END LOOP; END get_slm_pack_tgt_tests; PROCEDURE get_slm_pack_tgt_test_list ( tgt_test_list OUT CURSORTYPE) IS l_tgt_test_list SLM_PACK_SUMMARY_ARRAY; l_query_string VARCHAR2(2000); BEGIN get_slm_pack_tgt_tests(l_tgt_test_list); l_query_string := 'SELECT target_name AS Target_Name, target_type AS Target_Type, tgt_with_cnt.test_type AS Type, tgt_with_cnt.value1 AS Test_Count, tgt_with_cnt.value2 AS Tests_Performed FROM mgmt_targets, (SELECT name as tgt_guid, txn_type as test_type, value1, value2, value3 FROM TABLE (CAST(:1 AS SLM_PACK_SUMMARY_ARRAY) ) ) tgt_with_cnt WHERE target_guid = HEXTORAW(tgt_with_cnt.tgt_guid)'; OPEN tgt_test_list for l_query_string using l_tgt_test_list; END get_slm_pack_tgt_test_list; PROCEDURE get_slm_pack_test_type_list (test_type_list OUT CURSORTYPE) IS l_tgt_test_list SLM_PACK_SUMMARY_ARRAY; l_query_string VARCHAR2(2000); BEGIN get_slm_pack_tgt_tests(l_tgt_test_list); -- copy the count information -- so this part only needs to be done once. l_query_string := 'SELECT tst_with_cnt.test_type As Type, SUM(tst_with_cnt.value1) As Test_Count, SUM(tst_with_cnt.value2) As TESTS_PERFORMED FROM (SELECT txn_type AS test_type, value1, value2, value3 FROM TABLE (CAST(:1 AS SLM_PACK_SUMMARY_ARRAY) ) ) tst_with_cnt GROUP BY tst_with_cnt.test_type'; OPEN test_type_list for l_query_string using l_tgt_test_list; END get_slm_pack_test_type_list; PROCEDURE get_slm_pack_report_summary ( tgt_test_list OUT CURSORTYPE) IS l_tst_enabled_cnt NUMBER := 0; l_tst_total_cnt NUMBER := 0; l_bcn_cnt NUMBER := 0; l_tst_bcn_cnt_arr SLM_PACK_SUMMARY_ARRAY := SLM_PACK_SUMMARY_ARRAY(); l_tgt_test_list SLM_PACK_SUMMARY_ARRAY; l_query_string VARCHAR2(2000); BEGIN get_slm_pack_tgt_tests(l_tgt_test_list); SELECT count(*) INTO l_bcn_cnt FROM mgmt_targets WHERE target_type = mgmt_global.G_BEACON_TARGET_TYPE; IF (l_tgt_test_list IS NOT NULL AND l_tgt_test_list.count > 0) THEN SELECT SUM(value2), SUM(value3) INTO l_tst_enabled_cnt, l_tst_total_cnt FROM TABLE (CAST(l_tgt_test_list AS SLM_PACK_SUMMARY_ARRAY) ) tst_with_cnt; END IF; l_tst_bcn_cnt_arr.extend; l_tst_bcn_cnt_arr(1) := SLM_PACK_SUMMARY(NULL, NULL, l_bcn_cnt, l_tst_enabled_cnt, l_tst_total_cnt); l_query_string := 'SELECT tst_bcn_cnt_tbl.value2 as TESTS_PERFORMED, tst_bcn_cnt_tbl.value1 as BEACONS FROM TABLE (CAST(:1 AS SLM_PACK_SUMMARY_ARRAY) ) tst_bcn_cnt_tbl'; OPEN tgt_test_list for l_query_string using l_tst_bcn_cnt_arr; END get_slm_pack_report_summary; END gensvc_view; / show errors;