Rem Rem $Header: website_mntr_pkgdef.sql 21-nov-2006.17:42:39 yxie Exp $ Rem Rem website_mntr_pkgdef.sql Rem Rem Copyright (c) 2002, 2006, Oracle. All rights reserved. Rem Rem NAME Rem website_mntr_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem yxie 11/21/06 - XbranchMerge yxie_bug5527422 from main Rem yxie 09/14/06 - XbranchMerge yxie_bug-5500487 from main Rem yxie 07/12/06 - XbranchMerge yxie_forms-monitoring from main Rem yxie 09/11/06 - Remove duplicate method Rem yxie 01/03/06 - Get Forms Application config data Rem eporter 10/19/04 - getWebappConfigData: change to oracle_webserver Rem rpatti 09/25/03 - removing obsolete code Rem asawant 07/08/03 - Adding code for config webapp OC4J and Webcache Rem asawant 05/20/03 - Adding OC4J aggregate data retrieval Rem jriel 04/24/03 - move general metrics Rem jriel 04/18/03 - split webapp homepage procedures Rem asawant 07/11/02 - changing GETWEBSITEHOMEPAGEDATA interface Rem rmarripa 07/03/02 - add procedure to get website homepage data Rem rpinnama 05/16/02 - Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem rem ------------------------------------------------------- rem rem Website Sitemap Package rem rem ------------------------------------------------------- CREATE OR REPLACE PACKAGE emd_mntr_website AS -- getWebsiteHomepageData -- -- Get the website homepage data. This includes the data for all the -- components. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- page_type: VARCHAR2 page type -- metric_name: VARCHAR2 Metric name for chronos data -- obj_type: VARCHAR2 Object type for chronos data -- -- OUT Parameters: -- tgt_mntr_sum_data CURSORTYPE A cursor which returns rows containing: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- avail_pct: NUMBER The availability percent over the timeperiod -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- memtgt_mntr_sum_data CURSORTYPE A cursor containing same above mentione -- data. -- avail_txn_perf_data p_cursor_type Cursor containing the current metric -- values for availability txn from availbility beacons and -- local beacon. -- beacon_name: VARCHAR2 beacon Name -- Composite_key: VARCHAR2 Composite key for the beacon and availability txn -- Collection_timestamp: VARCHAR2 Collection timestamp -- is_bcn : Char(1) 0 - Target; 1 - beacon -- met_col : VARCHAR2 Metric column name -- val : VARCHAR2 Metric Value -- str_val : VARCHAR2 Metric Value description. This contains the error -- description. -- is_old : CHAR(1) - 'Y' - if the data is old; otherwise 'N' - This column -- is not being used. This can be removed. -- avail_txn_sev_data p_cursor_type - CURSOR containing the alerts for availability txn -- beacon_name: VARCHAR2 beacon Name -- is_bcn : Char(1) 0 - Target; 1 - beacon -- sev_code: INTEGER -- -- avail_txn_chart_data: p_cursor_type - Cursor containing the chart data for -- avg_response_time metric for availability txn from all the -- beacons including LOCAL for last 24 hours. -- collection_timestamp: VARCHAR2 Collection timestamp -- bcn_name: VARCHAR2 beacon name -- val: VARCHAR2 metric value -- watchlist_data: MGMT_MNTR_USER_STATS_ARRAY Array of MGMT_MNTR_USER_STATS_OBJ -- url_perf_rows_data: cursorType -- Top 5 slowest URLs -- query_name: VARCHAR2 Url name -- display_name: VARCHAR2 display name of the Url -- visit_link: VARCHAR2 - Visit the page Url -- hits: NUMBER number of hits -- avg_resp_time: NUMBER Average response time -- min_resp_time: NUMBER Minimum response time -- max_resp_time: NUMBER Maximum response time -- filter_min_hits : the min hits filter -- bcn_list: MGMT_BCN_SYNC_ARRAY Array of MGMT_BCN_SYNC objects -- bcn_sev_data p_cursor_type - CURSOR containing the alerts for associate beacons -- beacon_name: VARCHAR2 beacon Name -- is_bcn : Char(1) 0 - Target; 1 - beacon -- sev_code: INTEGER -- avail_txn_guid VARCHAR2 Availability transaction guid -- txn_name VARCHAR2 Availability Transaction Name -- tgt_tz NUMBER Target Timezone -- homepage_url_prop VARCHAR2 Homepage URL property -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebsiteHomepageData( targetName IN VARCHAR2, targetType IN VARCHAR2, page_type IN VARCHAR2, metric_name IN VARCHAR2, obj_type IN VARCHAR2, tgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, memtgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, avail_txn_perf_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_sev_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_chart_data OUT EMD_BCNTXN.P_CURSOR_TYPE, watchlist_data OUT MGMT_MNTR_USER_STATS_ARRAY, url_perf_rows_data OUT EMD_MNTR_USER.CURSORTYPE, filter_min_hits OUT NUMBER, bcn_list OUT MGMT_BCN_SYNC_ARRAY, bcn_sev_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_guid OUT VARCHAR2, avail_txn_name OUT VARCHAR2, tgt_tz OUT NUMBER, homepage_url_prop OUT VARCHAR2, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- getWebappGeneralMetricData -- -- Get the general metrics used on the webapp home page -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- -- OUT Parameters: -- current_status_time : DATE - the timestamp of the last target status change -- homepage_url : VARCHAR2 - URL of the homepage for this webapp -- components_down : NUMBER - count of components down -- beacons_down : NUMBER - count of beacons down -- beacons_out_of_sync : NUMBER - count of beacons out of sync -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description -- PROCEDURE getWebappGeneralMetricData( -- targetName IN VARCHAR2, -- targetType IN VARCHAR2, -- current_status_time OUT DATE, -- homepage_url_prop OUT VARCHAR2, -- components_down OUT NUMBER, -- beacons_down OUT NUMBER, -- beacons_out_of_sync OUT NUMBER, -- result OUT NUMBER, -- err_desc OUT VARCHAR2 ); PROCEDURE getWebappGeneralMetricData( targetName IN VARCHAR2, targetType IN VARCHAR2, current_status_time OUT DATE, homepage_url_prop OUT VARCHAR2, components_down OUT NUMBER, beacons_down OUT NUMBER, beacons_out_of_sync OUT NUMBER, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- getWebappComponentsData -- -- Get the webapp components data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- -- OUT Parameters: -- tgt_mntr_sum_data CURSORTYPE A cursor which returns rows containing: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- avail_pct: NUMBER The availability percent over the timeperiod -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- memtgt_mntr_sum_data CURSORTYPE A cursor containing same above mentione -- data. -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappComponentsData( targetName IN VARCHAR2, targetType IN VARCHAR2, tgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, memtgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- getWebappAvailTransactionData -- -- Get the webapp availability transaction data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- -- OUT Parameters: -- avail_txn_perf_data p_cursor_type Cursor containing the current metric -- values for availability txn from availbility beacons and -- local beacon. -- beacon_name: VARCHAR2 beacon Name -- Composite_key: VARCHAR2 Composite key for the beacon and availability txn -- Collection_timestamp: VARCHAR2 Collection timestamp -- is_bcn : Char(1) 0 - Target; 1 - beacon -- met_col : VARCHAR2 Metric column name -- val : VARCHAR2 Metric Value -- str_val : VARCHAR2 Metric Value description. This contains the error -- description. -- is_old : CHAR(1) - 'Y' - if the data is old; otherwise 'N' - This column -- is not being used. This can be removed. -- avail_txn_sev_data p_cursor_type - CURSOR containing the alerts for availability txn -- beacon_name: VARCHAR2 beacon Name -- is_bcn : Char(1) 0 - Target; 1 - beacon -- sev_code: INTEGER -- -- avail_txn_chart_data: p_cursor_type - Cursor containing the chart data for -- avg_response_time metric for availability txn from all the -- beacons including LOCAL for last 24 hours. -- collection_timestamp: VARCHAR2 Collection timestamp -- bcn_name: VARCHAR2 beacon name -- val: VARCHAR2 metric value -- avail_txn_guid VARCHAR2 Availability transaction guid -- txn_name VARCHAR2 Availability Transaction Name -- tgt_tz NUMBER Target Timezone -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappAvailTransactionData( targetName IN VARCHAR2, targetType IN VARCHAR2, avail_txn_perf_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_sev_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_chart_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_guid OUT VARCHAR2, avail_txn_name OUT VARCHAR2, tgt_tz OUT NUMBER, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- getWebappWatchListData -- -- Get the webapp watchlist data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- page_type: VARCHAR2 page type -- metric_name: VARCHAR2 Metric name for chronos data -- -- OUT Parameters: -- watchlist_data: MGMT_MNTR_USER_STATS_ARRAY Array of MGMT_MNTR_USER_STATS_OBJ -- homepage_url_prop: URL for web app homepage -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappWatchListData( targetName IN VARCHAR2, targetType IN VARCHAR2, page_type IN VARCHAR2, metric_name IN VARCHAR2, watchlist_data OUT MGMT_MNTR_USER_STATS_ARRAY, homepage_url_prop OUT VARCHAR2, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- getWebappEndUserPerfData -- -- Get the website homepage data. This includes the data for all the -- components. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- page_type: VARCHAR2 page type -- metric_name: VARCHAR2 Metric name for chronos data -- obj_type: VARCHAR2 Object type for chronos data -- -- OUT Parameters: -- url_perf_rows_data: cursorType -- Top 5 slowest URLs -- query_name: VARCHAR2 Url name -- display_name: VARCHAR2 display name of the Url -- visit_link: VARCHAR2 - Visit the page Url -- hits: NUMBER number of hits -- avg_resp_time: NUMBER Average response time -- min_resp_time: NUMBER Minimum response time -- max_resp_time: NUMBER Maximum response time -- filter_min_hits : the min hits filter -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappEndUserPerfData( targetName IN VARCHAR2, targetType IN VARCHAR2, page_type IN VARCHAR2, metric_name IN VARCHAR2, obj_type IN VARCHAR2, url_perf_rows_data OUT EMD_MNTR_USER.CURSORTYPE, filter_min_hits OUT NUMBER, result OUT NUMBER, err_desc OUT VARCHAR2 ); -- getWebappOc4jTopData -- -- Get the OC4J Top data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- axis: VARCHAR2 The data axis (total tm, comp tm, hits) -- component: VARCHAR2 The component type (EJB, JSP, SERVLET) -- time: VARCHAR2 The period for the data (24h, 7d, 31d) -- instance: VARCHAR2 The OC4J instance name or '' for all -- -- OUT Parameters: -- data : cursorType -- Top 5 slowest of the type "component" by "axis" over the "time" period -- for "instance" (if provided else all) -- oc4j_list : cursorType -- All oc4j target names that belong to this webapp target (internal and -- display names). -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getWebappOc4jTopData( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_axis IN VARCHAR2, l_component IN VARCHAR2, l_time IN VARCHAR2, l_instance IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_oc4j_list OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2); -- getWebappConfigData -- -- The procedure returns a cursor with the following format: -- Target Real Name, Configure Link -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'website') -- component: VARCHAR2 The component type (oc4j, oracle_webserver) -- -- OUT Parameters: -- data : cursorType -- The list of targets of type component that belong to the composite target -- and their respective properties (necessary to get their configure links). -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getWebappConfigData( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_component IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2); -- getFormsConfigData -- -- The procedure returns a cursor with the following format: -- Target Real Name, Configure Link -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'formsapp') -- component: VARCHAR2 The component type (oc4j, oracle_webserver) -- -- OUT Parameters: -- data : cursorType -- The list of targets of type component that belong to the composite target -- and their respective properties (necessary to get their configure links). -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getFormsConfigData( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_component IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2); -- getFormsTargetsForEUM -- -- The procedure returns a list of Forms target that user -- needs to configure for enabling End-User Performance Monitoring -- for this particular Forms Application target -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'formsapp') -- -- OUT Parameters: -- data : cursorType -- The list of Forms targets that need to be configured -- for enabling End-User Performance Monitoring for this -- particular Forms Application target -- The Forms target should be either a key component of the -- Forms Application target, or member of key group component of the -- Forms Application target. If the Forms target has property -- ebsConfigURL, we need to get the property out as well. -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getFormsTargetsForEUM( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2); -- getFormsTargetsForTxn -- -- The procedure returns a list of Forms target that user -- needs to configure for enabling Forms Transaction Monitoring -- for this particular Forms Application target -- -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'formsapp') -- -- OUT Parameters: -- data : cursorType -- The list of Forms targets that need to be configured -- for enabling Forms Transaction Monitoring for this -- particular Forms Application target -- The Forms target should be either a key component of the -- Forms Application target, or member of key group component of the -- Forms Application target. If the Forms target has property -- formsJarLocation, we need to get the property out as well. -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getFormsTargetsForTxn( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2); -------------------------------------------------------------------------- -- Globals / Constants -------------------------------------------------------------------------- p_axis_req_processed CONSTANT VARCHAR2(10) := 'byRp'; p_axis_proc_time CONSTANT VARCHAR2(10) := 'byPt'; p_component_svlt CONSTANT VARCHAR2(10) := 'servlet'; p_component_jsp CONSTANT VARCHAR2(10) := 'jsp'; p_time_24h CONSTANT VARCHAR2(10) := '24h'; p_time_7d CONSTANT VARCHAR2(10) := '7da'; p_time_31d CONSTANT VARCHAR2(10) := '31d'; p_oc4j_tgt_type CONSTANT VARCHAR2(10) := 'oc4j'; p_ias_tgt_type CONSTANT VARCHAR2(15) := 'oracle_ias'; p_webcache_tgt_type CONSTANT VARCHAR2(20) := 'oracle_webcache'; p_apache_tgt_type CONSTANT VARCHAR2(20) := 'oracle_apache'; p_webserver_tgt_type CONSTANT VARCHAR2(20) := 'oracle_webserver'; p_metcol_delta_comp CONSTANT VARCHAR2(25) := 'service.deltaCompleted'; p_metcol_delta_time CONSTANT VARCHAR2(25) := 'service.deltaTime'; p_met_servlet_delta_rollup CONSTANT VARCHAR2(30) := 'oc4j_servlet_delta_rollup'; p_met_servlet_rollup CONSTANT VARCHAR2(30) := 'oc4j_servlet_rollup'; p_met_jsp_delta_rollup CONSTANT VARCHAR2(30) := 'oc4j_jsp_delta_rollup'; p_met_jsp_rollup CONSTANT VARCHAR2(30) := 'oc4j_jsp_rollup'; end emd_mntr_website; / show errors;