Rem drv: Rem Rem $Header: chronos_views.sql 12-dec-2005.11:34:59 yxie Exp $ Rem Rem chronos_views.sql Rem Rem Copyright (c) 2002, 2005, Oracle. All rights reserved. Rem Rem NAME Rem chronos_views.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem yxie 12/12/05 - Backport yxie_bug-4725344 from main Rem yxie 11/22/05 - remove rollup time filtering Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem yxie 06/06/05 - changes depends_on to supports_eum_on Rem yxie 04/15/05 - changing contains to depends_on Rem eporter 03/14/05 - Add views for bootstrapping new tables Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem ramalhot 01/17/05 - assoc def name changed from member to contains Rem ktlaw 01/11/05 - add repmgr header Rem eporter 12/13/04 - Remove references to mgmt_rt_region_members Rem eporter 12/02/04 - Bug 3849061 - Change region implementation Rem ramalhot 08/25/04 - cutover to new assoc tables Rem jhsy 08/13/04 - add database time Rem snakai 09/26/03 - fix region queries Rem sgrover 03/19/03 - remove load_timstampand variance Rem rmarripa 06/14/02 - delete views which involves rep url properties Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/14/02 - Created Rem rem rem Chronos View definitions are provided below. The views are the rem public interface to the above tables. rem rem This section provides a high level overview of what is available rem rem View Name Description rem ----------------------- ------------------------------------------------------------------------- rem rem EM$RT_COOKIE_DATA View on raw cookie data table rem EM$RT_COOKIES Raw response time data tied together with its associated cookie data rem EM$RT_CURRENT_URL_AVG_1HOUR Most recent value for the URL in question taken from hourly rollup table rem EM$RT_CURRENT_URL_AVG_1DAY Most recent value for the URL in question taken from daily rollup table rem EM$RT_DOMAIN_DETAILS_1DAY Domain details for 1 day time span rem EM$RT_DOMAIN_DETAILS_31DAY Domain details for 31 day time span rem EM$RT_DOMAIN_DETAILS_7DAY Domain details for 7 days time span rem EM$RT_DOMAIN_DIST_1DAY Domain rt distribution for 1 day rem EM$RT_DOMAIN_DIST_31DAY Domain rt distribution for 31 days rem EM$RT_DOMAIN_DIST_7DAY Domain rt distribution for 7 days rem EM$RT_DOMAIN_MOST_HITS_1DAY Domain rt stats for 1 day, ordered by most hits rem EM$RT_DOMAIN_MOST_HITS_31DAY Domain rt stats for 31 days, ordered by most hits rem EM$RT_DOMAIN_MOST_HITS_7DAY Domain rt stats for 7 days, ordered by most hits rem EM$RT_DOMAIN_MOST_UNF_1DAY Domain rt status, ordered by most hits, unfiltered rem EM$RT_DOMAIN_MOST_UNF_31DAY Domain rt status, ordered by most hits, unfiltered rem EM$RT_DOMAIN_MOST_UNF_7DAY Domain rt status, ordered by most hits, unfiltered rem EM$RT_DOMAIN_SLOWEST_1DAY Domain rt stats for 1 day, ordered by response time rem EM$RT_DOMAIN_SLOWEST_31DAY Domain rt stats for 31 days, ordered by response time rem EM$RT_DOMAIN_SLOWEST_7DAY Domain rt stats for 7 days, ordered by response time rem EM$RT_DOMAIN_SLOWEST_UNF_1DAY Domain rt stats for 1 day, ordered by response time, unfiltered rem EM$RT_DOMAIN_SLOWEST_UNF_31DAY Domain rt stats for 31 days, ordered by response time, unfiltered rem EM$RT_DOMAIN_SLOWEST_UNF_7DAY Domain rt stats for 7 days, ordered by response time, unfiltered rem EM$RT_IP_DETAILS_1DAY IP details for 1 day time span rem EM$RT_IP_DETAILS_31DAY IP details for 31 day time span rem EM$RT_IP_DETAILS_7DAY IP details for 7 day time span rem EM$RT_IP_DIST_1DAY IP rt distribution for 1 day rem EM$RT_IP_DIST_31DAY IP rt distribution for 31 days rem EM$RT_IP_DIST_7DAY IP rt distribution for 7 days rem EM$RT_IP_MOST_HITS_1DAY IP rt stats for 1 day, ordered by most hits rem EM$RT_IP_MOST_HITS_31DAY IP rt stats for 31 days, ordered by most hits rem EM$RT_IP_MOST_HITS_7DAY IP rt stats for 7 days, ordered by most hits rem EM$RT_IP_MOST_HITS_UNF_1DAY IP rt stats for 1 day, ordered by most hits, unfiltered rem EM$RT_IP_MOST_HITS_UNF_31DAY IP rt stats for 31 days, ordered by most hits, unfiltered rem EM$RT_IP_MOST_HITS_UNF_7DAY IP rt stats for 7 days, ordered by most hits, unfiltered rem EM$RT_IP_SLOWEST_1DAY IP rt stats for 1 day, ordered by response time rem EM$RT_IP_SLOWEST_31DAY IP rt stats for 31 days, ordered by response time rem EM$RT_IP_SLOWEST_7DAY IP rt stats for 7 days, ordered by response time rem EM$RT_IP_SLOWEST_UNF_1DAY IP rt stats for 1 day, ordered by response time, unfiltered rem EM$RT_IP_SLOWEST_UNF_31DAY IP rt stats for 31 days, ordered by response time, unfiltered rem EM$RT_IP_SLOWEST_UNF_7DAY IP rt stats for 7 days, ordered by response time, unfiltered rem EM$RT_LOOPBACK_DETAILS_1DAY URL Timing metric stats for 1 day rem EM$RT_LOOPBACK_DETAILS_31DAY URL Timing metric stats for 31 days rem EM$RT_LOOPBACK_DETAILS_7DAY URL Timing metric stats for 7 days rem EM$RT_METRICS_DETAILS_1DAY Response Metrics stats for 1 day rem EM$RT_METRICS_DETAILS_31DAY Response Metrics stats for 31 days rem EM$RT_METRICS_DETAILS_7DAY Response Metrics stats for 7 days rem EM$RT_RAW View on raw data table with individual metric samples rem EM$RT_REGIONS Region definitions (meta data) rem EM$RT_REGION_DETAILS_1DAY Region details for 1 day time span rem EM$RT_REGION_DETAILS_31DAY Region details for 31 day time span rem EM$RT_REGION_DETAILS_7DAY Region details for 7 day time span rem EM$RT_REGION_DIST_1DAY 1 day region rollup distribution rem EM$RT_REGION_DIST_1HOUR 1 hour region rollup distribution rem EM$RT_REGION_MOST_HITS_1DAY Region rt stats for 1 day, ordered by most hits rem EM$RT_REGION_MOST_HITS_31DAY Region rt stats for 31 days, ordered by most hits rem EM$RT_REGION_MOST_HITS_7DAY Region rt stats for 7 days, ordered by most hits rem EM$RT_REGION_MOST_UNF_1DAY Region rt stats for 1 day, ordered by most hits, unfiltered rem EM$RT_REGION_MOST_UNF_31DAY Region rt stats for 31 days, ordered by most hits, unfiltered rem EM$RT_REGION_MOST_UNF_7DAY Region rt stats for 7 days, ordered by most hits, unfiltered rem EM$RT_REGION_RAW_RECENT_HITS Most recent URL hits by region rem EM$RT_REGION_RAW_SLOWEST_1DAY Region slowest from raw table rem EM$RT_REGION_RAW_SLOWEST_31DAY Region slowest from raw table rem EM$RT_REGION_RAW_SLOWEST_7DAY Region slowest from raw table rem EM$RT_REGION_SLOWEST_1DAY Region rt stats for 1 day, ordered by response time rem EM$RT_REGION_SLOWEST_31DAY Region rt stats for 31 days, ordered by response time rem EM$RT_REGION_SLOWEST_7DAY Region rt stats for 7 days, ordered by response time rem EM$RT_REGION_SLOWEST_UNF_1DAY Region rt stats for 1 day, ordered by response time, unfiltered rem EM$RT_REGION_SLOWEST_UNF_31DAY Region rt stats for 31 days, ordered by response time, unfiltered rem EM$RT_REGION_SLOWEST_UNF_7DAY Region rt stats for 7 days, ordered by response time, unfiltered rem EM$RT_URL_DETAILS_1DAY URL timeseries details for 1 day time span rem EM$RT_URL_DETAILS_31DAY URL timeseries details for 31 day time span rem EM$RT_URL_DETAILS_7DAY URL timeseries details for 7 day time span rem EM$RT_URL_DIST_1DAY URL rt distribution for 1 day rem EM$RT_URL_DIST_31DAY URL rt distribution for 31 days rem EM$RT_URL_DIST_7DAY URL rt distribution for 7 days rem EM$RT_URL_MOST_FAVORITE_1DAY Most favorite URL response time stats for 1 day rem EM$RT_URL_MOST_FAVORITE_31DAY Most favorite URL response time stats for 31 days rem EM$RT_URL_MOST_FAVORITE_7DAY Most favorite URL response time stats for 7 days rem EM$RT_URL_MOST_HITS_1DAY URL rt stats for 1 day, ordered by most hits rem EM$RT_URL_MOST_HITS_31DAY URL rt stats for 31 days, ordered by most hits rem EM$RT_URL_MOST_HITS_7DAY URL rt stats for 7 days, ordered by most hits rem EM$RT_URL_MOST_HITS_UNF_1DAY URL rt stats for 1 day, ordered by most hits rem EM$RT_URL_MOST_HITS_UNF_31DAY URL rt stats for 31 days, ordered by most hits rem EM$RT_URL_MOST_HITS_UNF_7DAY URL rt stats for 7 days, ordered by most hits rem EM$RT_URL_RAW_RECENT_HITS Most recent URL hits rem EM$RT_URL_RAW_SLOWEST_1DAY URL hits for 1 day, ordered by slowest rem EM$RT_URL_RAW_SLOWEST_31DAY URL hits for 31 days, ordered by slowest rem EM$RT_URL_RAW_SLOWEST_7DAY URL hits for 7 days, ordered by slowest rem EM$RT_URL_SLOWEST_1DAY URL rt slowest for 1 day, ordered by response time rem EM$RT_URL_SLOWEST_31DAY URL rt slowest for 31 days, ordered by response time rem EM$RT_URL_SLOWEST_7DAY URL rt slowest for 7 days, ordered by response time rem EM$RT_URL_SLOWEST_UNF_1DAY URL rt slowest for 1 day, ordered by response time, unfiltered rem EM$RT_URL_SLOWEST_UNF_31DAY URL rt slowest for 31 days, ordered by response time, unfiltered rem EM$RT_URL_SLOWEST_UNF_7DAY URL rt slowest for 7 days, ordered by response time, unfiltered rem EM$RT_WATCH_LIST_1DAY Watch list rt stats for 1 day, ordered by preference, response time rem EM$RT_WATCH_LIST_31DAY Watch list rt stats for 31 days, ordered by preference, response time rem EM$RT_WATCH_LIST_7DAY Watch list rt stats for 7 days, ordered by preference, response time rem EM$RT_INCOMPLETE_LOADS Incomplete Loads hourly rollup data with bootstrap data. rem EM$RT_PR_MAPPING Page Request Mapping hourly rollup data with bootstrap data. rem rem PURPOSE rem rem The EM$RT_RAW view provides a public interface to the url rem access response time metrics collected for given targets. rem rem COLUMNS rem rem The same as MGMT_RT_METRICS_RAW, except for the following addition: rem - url_link, which is the concatanation of url_base and url_filename rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_raw ( target_guid, metric_name, collection_timestamp, status, status_description, submit_action_timestamp, load_action_timestamp, elapsed_time, url_base, url_link, url_filename, visitor_node, visitor_domain, visitor_ip, visitor_ip_num, server_in_timestamp, server_out_timestamp, server_latency_time, database_time, browser_name, browser_version, os_name, os_version, username, cookie_index, cache_rendered ) AS SELECT target_guid, metric_name, collection_timestamp, status, status_description, submit_action_timestamp, load_action_timestamp, elapsed_time, url_base, url_base || url_filename, url_filename, visitor_node, visitor_domain, visitor_ip, visitor_ip_num, server_in_timestamp, server_out_timestamp, server_latency_time, database_time, browser_name, browser_version, os_name, os_version, username, cookie_index, cache_rendered FROM MGMT_RT_METRICS_RAW; rem rem PURPOSE rem rem This view selects a set of regions. rem Each region consits of a set of domain name substring patterns. rem Regions are not hierarchical. rem rem COLUMNS rem The columns are the same as in MGMT_RT_REGIONS rem and MGMT_RT_REGION_MEMBERS. rem The MGMT_RT_REGION_MEMBERS table has been removed, but the data is rem still in the same format. If the region is defined by domain, the rem member_type will be 'D' and if by subnet the member_type will be 'S'. rem rem NOTES rem rem CREATE OR REPLACE VIEW EM$RT_REGIONS ( target_guid, region_name, description, region_member, member_type ) AS SELECT r.target_guid, r.region_name, r.description, e.domain, DECODE(e.min_ip, -1, 'D', 'S') FROM MGMT_RT_REGIONS r, MGMT_RT_REGION_MAPPING m, MGMT_RT_REGION_ENTRIES e WHERE m.region_guid = r.region_guid AND m.id = e.id; rem rem VIEW em$rt_cookie_data rem rem PURPOSE rem View on raw cookie data tables. rem rem COLUMNS rem The same as MGMT_RT_COOKIE_DATA. rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_cookie_data (raw_index,name,value) AS SELECT raw_index,name,value FROM MGMT_RT_COOKIE_DATA; rem rem VIEW EM$RT_CURRENT_URL_AVG_1DAY rem rem PURPOSE rem Most recently daily average value by URL rem rem COLUMNS rem see MGMT_RT_URL_1DAY rem rem NOTES rem rem CREATE OR REPLACE VIEW EM$RT_CURRENT_URL_AVG_1DAY (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, URL_FILENAME, RESPONSE_TIME_CURRENT_AVG) AS SELECT COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, URL_FILENAME, sum(RESPONSE_TIME_AVERAGE*HITS)/sum(HITS) as RESPONSE_TIME_CURRENT_AVG FROM (SELECT m.source_target_guid composite_target_guid, ct.target_name composite_target_name, ct.target_type composite_target_type, d.metric_name, d.url_filename, d.response_time_average, d.rollup_timestamp, max(d.rollup_timestamp) OVER (PARTITION by source_target_guid, metric_name, url_filename) as ts_max, d.hits FROM MGMT_RT_URL_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid ) WHERE rollup_timestamp = ts_max group by COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, URL_FILENAME ; rem rem VIEW EM$RT_CURRENT_URL_AVG_1HOUR rem rem PURPOSE rem Most recently hourly average value by URL rem rem COLUMNS rem see MGMT_RT_URL_1HOUR rem rem NOTES rem rem CREATE OR REPLACE VIEW EM$RT_CURRENT_URL_AVG_1HOUR (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, URL_FILENAME, RESPONSE_TIME_CURRENT_AVG) AS SELECT COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, URL_FILENAME, sum(RESPONSE_TIME_AVERAGE*HITS)/sum(HITS) as RESPONSE_TIME_CURRENT_AVG FROM (SELECT m.source_target_guid composite_target_guid, ct.target_name composite_target_name, ct.target_type composite_target_type, d.metric_name, d.url_filename, d.response_time_average, d.rollup_timestamp, max(d.rollup_timestamp) OVER (PARTITION by m.source_target_guid, metric_name, url_filename) as ts_max, d.hits FROM MGMT_RT_URL_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid ) WHERE rollup_timestamp = ts_max group by COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, URL_FILENAME ; rem rem VIEW em$url_rt_details_ rem rem PURPOSE rem Selects response time details for the time span specified. rem This view is used to populate timeseries displays. rem The 1 and 7 day time span selects from the hourly rollup table. rem The 31 day time spans select from the daily rollup table. rem rem COLUMNS rem see MGMT_RT_URL_1HOUR rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_url_details_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, nvl((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, d.URL_LINK, d.ROLLUP_TIMESTAMP, d.response_time_average, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_URL_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_url_details_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, nvl((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, d.URL_LINK, d.ROLLUP_TIMESTAMP, d.response_time_average, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_URL_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_url_details_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.assoc_TARGET_GUID, d.METRIC_NAME, nvl((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, d.URL_LINK, d.ROLLUP_TIMESTAMP, d.response_time_average, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_URL_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; rem rem VIEW em$rt_domain_details_ rem rem PURPOSE rem Selects response time details for the time span specified. rem This view is used to populate timeseries displays. rem The 1 and 7 day time span selects from the hourly rollup table. rem The 31 day time spans select from the daily rollup table. rem rem COLUMNS rem see MGMT_RT_DOMAIN_1HOUR rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_domain_details_1day ( COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_DOMAIN, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, d.VISITOR_DOMAIN, d.ROLLUP_TIMESTAMP, d.RESPONSE_TIME_AVERAGE, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_DOMAIN_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_domain_details_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_DOMAIN, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, d.VISITOR_DOMAIN, d.ROLLUP_TIMESTAMP, d.RESPONSE_TIME_AVERAGE, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_DOMAIN_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_domain_details_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_DOMAIN, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, d.VISITOR_DOMAIN, d.ROLLUP_TIMESTAMP, d.RESPONSE_TIME_AVERAGE, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_DOMAIN_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; rem rem VIEW em$rt_region_details_ rem rem PURPOSE rem Selects response time details for the time span specified. rem This view is used to populate timeseries displays. rem The 1 and 7 day time span selects from the hourly rollup table. rem The 31 day time spans select from the daily rollup table. rem rem COLUMNS rem see MGMT_RT_DOMAIN_1HOUR rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_region_details_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_REGION, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, R.REGION_NAME, d.ROLLUP_TIMESTAMP, sum(response_time_average*hits)/sum(hits), min(RESPONSE_TIME_MINIMUM), max(RESPONSE_TIME_MAXIMUM), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(HITS) FROM MGMT_RT_DOMAIN_1HOUR d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) GROUP BY m.SOURCE_TARGET_GUID, m.ASSOC_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, r.REGION_NAME, d.ROLLUP_TIMESTAMP; CREATE OR REPLACE VIEW em$rt_region_details_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_REGION, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, R.REGION_NAME, d.ROLLUP_TIMESTAMP, sum(response_time_average*hits)/sum(hits), min(RESPONSE_TIME_MINIMUM), max(RESPONSE_TIME_MAXIMUM), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(HITS) FROM MGMT_RT_DOMAIN_1HOUR d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) GROUP BY m.SOURCE_TARGET_GUID, m.ASSOC_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, r.REGION_NAME, d.ROLLUP_TIMESTAMP; CREATE OR REPLACE VIEW em$rt_region_details_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_REGION, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, R.REGION_NAME, d.ROLLUP_TIMESTAMP, sum(response_time_average*hits)/sum(hits), min(RESPONSE_TIME_MINIMUM), max(RESPONSE_TIME_MAXIMUM), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(HITS) FROM MGMT_RT_DOMAIN_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid and r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, m.assoc_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, r.REGION_NAME, d.ROLLUP_TIMESTAMP; rem rem VIEW em$rt_ip_details_ rem rem PURPOSE rem Selects response time details for the time span specified. rem The 1 day time span selects from the hourly rollup table. rem 7 and 31 day time spans select from the daily rollup table. rem rem COLUMNS rem see MGMT_RT_IP_1HOUR rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_ip_details_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_NODE, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, d.VISITOR_NODE, d.ROLLUP_TIMESTAMP, d.RESPONSE_TIME_AVERAGE, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_IP_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_ip_details_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_NODE, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, d.VISITOR_NODE, d.ROLLUP_TIMESTAMP, d.RESPONSE_TIME_AVERAGE, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_IP_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_ip_details_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, VISITOR_NODE, ROLLUP_TIMESTAMP, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, m.ASSOC_TARGET_GUID, d.METRIC_NAME, d.VISITOR_NODE, d.ROLLUP_TIMESTAMP, d.RESPONSE_TIME_AVERAGE, d.RESPONSE_TIME_MINIMUM, d.RESPONSE_TIME_MAXIMUM, d.RESPONSE_TIME_SDEV, d.HITS FROM MGMT_RT_IP_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; rem rem Duplicate views, do not use rem VIEW em$rt_loopback_details_ rem rem PURPOSE rem Selects url timing loopback metric response time details rem for the time span specified. rem The 1 and 7 day time span selects from the hourly rollup table. rem 31 day time spans select from the daily rollup table. rem rem COLUMNS rem see MGMT_RT_IP_1HOUR rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_loopback_details_1day (TARGET_GUID, METRIC_NAME, METRIC_COLUMN, METRIC_GUID, COLUMN_LABEL, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV) AS SELECT t.target_guid, METRIC_NAME, METRIC_COLUMN, MT.METRIC_GUID, column_label, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV FROM MGMT_METRICS_1HOUR d, MGMT_TARGETS t, MGMT_METRICS mt WHERE mt.metric_name = 'Response' AND mt.metric_column = 'Timing' AND mt.metric_guid = d.metric_guid AND d.target_guid = t.target_guid AND t.target_type = mt.target_type AND t.type_meta_ver = mt.type_meta_ver AND (t.category_prop_1 = mt.category_prop_1 OR mt.category_prop_1 = ' ') AND (t.category_prop_2 = mt.category_prop_2 OR mt.category_prop_2 = ' ') AND (t.category_prop_3 = mt.category_prop_3 OR mt.category_prop_3 = ' ') AND (t.category_prop_4 = mt.category_prop_4 OR mt.category_prop_4 = ' ') AND (t.category_prop_5 = mt.category_prop_5 OR mt.category_prop_5 = ' ') AND d.ROLLUP_TIMESTAMP >= (SYSDATE - 1); CREATE OR REPLACE VIEW em$rt_loopback_details_7day (TARGET_GUID, METRIC_NAME, METRIC_COLUMN, METRIC_GUID, COLUMN_LABEL, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV) AS SELECT t.target_guid, METRIC_NAME, METRIC_COLUMN, MT.METRIC_GUID, column_label, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV FROM MGMT_METRICS_1DAY d, MGMT_TARGETS t, MGMT_METRICS mt WHERE mt.metric_name = 'Response' AND mt.metric_column = 'Timing' AND mt.metric_guid = d.metric_guid AND d.target_guid = t.target_guid AND t.target_type = mt.target_type AND t.type_meta_ver = mt.type_meta_ver AND (t.category_prop_1 = mt.category_prop_1 OR mt.category_prop_1 = ' ') AND (t.category_prop_2 = mt.category_prop_2 OR mt.category_prop_2 = ' ') AND (t.category_prop_3 = mt.category_prop_3 OR mt.category_prop_3 = ' ') AND (t.category_prop_4 = mt.category_prop_4 OR mt.category_prop_4 = ' ') AND (t.category_prop_5 = mt.category_prop_5 OR mt.category_prop_5 = ' ') AND d.ROLLUP_TIMESTAMP >= (SYSDATE - 7); CREATE OR REPLACE VIEW em$rt_loopback_details_31day (TARGET_GUID, METRIC_NAME, METRIC_COLUMN, METRIC_GUID, COLUMN_LABEL, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV) AS SELECT t.target_guid, METRIC_NAME, METRIC_COLUMN, MT.METRIC_GUID, column_label, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV FROM MGMT_METRICS_1DAY d, MGMT_TARGETS t, MGMT_METRICS mt WHERE mt.metric_name = 'Response' AND mt.metric_column = 'Timing' AND mt.metric_guid = d.metric_guid AND d.target_guid = t.target_guid AND t.target_type = mt.target_type AND t.type_meta_ver = mt.type_meta_ver AND (t.category_prop_1 = mt.category_prop_1 OR mt.category_prop_1 = ' ') AND (t.category_prop_2 = mt.category_prop_2 OR mt.category_prop_2 = ' ') AND (t.category_prop_3 = mt.category_prop_3 OR mt.category_prop_3 = ' ') AND (t.category_prop_4 = mt.category_prop_4 OR mt.category_prop_4 = ' ') AND (t.category_prop_5 = mt.category_prop_5 OR mt.category_prop_5 = ' ') AND d.ROLLUP_TIMESTAMP >= (SYSDATE - 31); rem rem Duplicate views, do not use rem VIEW em$rt_metrics_details_ rem rem PURPOSE rem Selects 'Response' metric time details rem for the time span specified. rem The 1 and 7 day time span selects from the hourly rollup table. rem 31 day time spans select from the daily rollup table. rem rem COLUMNS rem see MGMT_RT_IP_1HOUR rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_metrics_details_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, METRIC_COLUMN, COLUMN_LABEL, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV) AS SELECT d.target_guid, t.target_name, t.target_type, METRIC_NAME, METRIC_COLUMN, column_label, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV FROM MGMT_METRICS_1HOUR d, MGMT_METRICS mt, MGMT_TARGETS t WHERE ROLLUP_TIMESTAMP >= (SYSDATE - 1) AND d.metric_guid = mt.metric_guid AND d.target_guid = t.target_guid AND mt.metric_name = 'Response' AND mt.metric_column != 'Status' AND mt.metric_column is not null AND mt.metric_type = 0 AND t.target_type = mt.target_type AND t.type_meta_ver = mt.type_meta_ver AND (t.category_prop_1 = mt.category_prop_1 OR mt.category_prop_1 = ' ') AND (t.category_prop_2 = mt.category_prop_2 OR mt.category_prop_2 = ' ') AND (t.category_prop_3 = mt.category_prop_3 OR mt.category_prop_3 = ' ') AND (t.category_prop_4 = mt.category_prop_4 OR mt.category_prop_4 = ' ') AND (t.category_prop_5 = mt.category_prop_5 OR mt.category_prop_5 = ' '); CREATE OR REPLACE VIEW em$rt_metrics_details_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, METRIC_COLUMN, COLUMN_LABEL, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV) AS SELECT d.target_guid, t.target_name, t.target_type, METRIC_NAME, METRIC_COLUMN, column_label, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV FROM MGMT_METRICS_1HOUR d, MGMT_METRICS mt, MGMT_TARGETS t WHERE ROLLUP_TIMESTAMP >= (SYSDATE - 7) AND d.metric_guid = mt.metric_guid AND d.target_guid = t.target_guid AND mt.metric_name = 'Response' AND mt.metric_column != 'Status' AND mt.metric_column is not null AND mt.metric_type = 0 AND t.target_type = mt.target_type AND t.type_meta_ver = mt.type_meta_ver AND (t.category_prop_1 = mt.category_prop_1 OR mt.category_prop_1 = ' ') AND (t.category_prop_2 = mt.category_prop_2 OR mt.category_prop_2 = ' ') AND (t.category_prop_3 = mt.category_prop_3 OR mt.category_prop_3 = ' ') AND (t.category_prop_4 = mt.category_prop_4 OR mt.category_prop_4 = ' ') AND (t.category_prop_5 = mt.category_prop_5 OR mt.category_prop_5 = ' '); CREATE OR REPLACE VIEW em$rt_metrics_details_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, METRIC_COLUMN, COLUMN_LABEL, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV) AS SELECT d.target_guid, t.target_name, t.target_type, METRIC_NAME, METRIC_COLUMN, column_label, ROLLUP_TIMESTAMP, VALUE_AVERAGE, VALUE_MINIMUM, VALUE_MAXIMUM, VALUE_SDEV FROM MGMT_METRICS_1HOUR d, MGMT_METRICS mt, MGMT_TARGETS t WHERE ROLLUP_TIMESTAMP >= (SYSDATE - 31) AND d.metric_guid = mt.metric_guid AND d.target_guid = t.target_guid AND mt.metric_name = 'Response' AND mt.metric_column != 'Status' AND mt.metric_column is not null AND mt.metric_type = 0 AND t.target_type = mt.target_type AND t.type_meta_ver = mt.type_meta_ver AND (t.category_prop_1 = mt.category_prop_1 OR mt.category_prop_1 = ' ') AND (t.category_prop_2 = mt.category_prop_2 OR mt.category_prop_2 = ' ') AND (t.category_prop_3 = mt.category_prop_3 OR mt.category_prop_3 = ' ') AND (t.category_prop_4 = mt.category_prop_4 OR mt.category_prop_4 = ' ') AND (t.category_prop_5 = mt.category_prop_5 OR mt.category_prop_5 = ' '); rem rem VIEW em$rt_watch_list_ rem rem PURPOSE rem Selects response time statistics for watch list URLs for rem the time span specified, ordered alphabetically rem rem COLUMNS rem See MGMT_RT_URL_1HOUR rem See MGMT_RT_URL_1DAY rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_member_target_urls (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, MEMBER_TARGET_GUID, DISPLAY_NAME, URL_FILENAME, DESCRIPTION) AS SELECT m.source_target_guid,ct.TARGET_NAME, ct.TARGET_TYPE, m.assoc_target_guid, u.display_name, u.url_filename, u.description FROM MGMT_RT_URLS u, mgmt_target_assocs m, mgmt_target_assoc_defs d, mgmt_targets ct WHERE u.target_guid = m.source_target_guid AND d.assoc_guid = m.assoc_guid AND d.assoc_def_name = 'supports_eum_on' AND scope_target_type = ' ' AND ct.target_guid = m.source_target_guid; CREATE OR REPLACE VIEW em$rt_watch_list_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS, CRITICAL_THRESHOLD, WARNING_THRESHOLD, RESPONSE_TIME_CURRENT_AVG) AS SELECT f.composite_target_guid, f.composite_target_name, f.composite_target_type, nvl(d.metric_name, 'latency'), f.display_name "DISPLAY_NAME", f.url_filename, max(d.url_link), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits), to_number(0), to_number(0), max((select response_time_current_avg from EM$RT_CURRENT_URL_AVG_1HOUR curr where curr.COMPOSITE_TARGET_GUID = f.composite_target_guid and curr.METRIC_NAME = d.metric_name and curr.URL_FILENAME = f.url_filename)) FROM MGMT_RT_URL_1HOUR d, em$rt_member_target_urls f WHERE f.member_target_guid = d.target_guid(+) AND f.url_filename = d.url_filename(+) GROUP BY f.composite_target_guid, f.composite_target_name, f.composite_target_type, f.display_name, f.url_filename, nvl(d.metric_name, 'latency'); CREATE OR REPLACE VIEW em$rt_watch_list_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS, CRITICAL_THRESHOLD, WARNING_THRESHOLD, RESPONSE_TIME_CURRENT_AVG) AS SELECT f.composite_target_guid, f.composite_target_name, f.composite_target_type, nvl(d.metric_name, 'latency'), f.display_name "DISPLAY_NAME", f.url_filename, max(d.url_link), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits), to_number(0), to_number(0), max((select response_time_current_avg from EM$RT_CURRENT_URL_AVG_1HOUR curr where curr.COMPOSITE_TARGET_GUID = f.composite_target_guid and curr.METRIC_NAME = d.metric_name and curr.URL_FILENAME = f.url_filename)) FROM MGMT_RT_URL_1DAY d, em$rt_member_target_urls f WHERE f.member_target_guid = d.target_guid(+) AND f.url_filename = d.url_filename(+) GROUP BY f.composite_target_guid, f.composite_target_name, f.composite_target_type, f.display_name, f.url_filename, nvl(d.metric_name, 'latency'); CREATE OR REPLACE VIEW em$rt_watch_list_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS, CRITICAL_THRESHOLD, WARNING_THRESHOLD, RESPONSE_TIME_CURRENT_AVG) AS SELECT f.composite_target_guid, f.composite_target_name, f.composite_target_type, nvl(d.metric_name, 'latency'), f.display_name "DISPLAY_NAME", f.url_filename, max(d.url_link), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits), to_number(0), to_number(0), max((select response_time_current_avg from EM$RT_CURRENT_URL_AVG_1DAY curr where curr.COMPOSITE_TARGET_GUID = f.composite_target_guid and curr.METRIC_NAME = d.metric_name and curr.URL_FILENAME = f.url_filename)) FROM MGMT_RT_URL_1DAY d, em$rt_member_target_urls f WHERE f.member_target_guid = d.target_guid(+) AND f.url_filename = d.url_filename(+) GROUP BY f.composite_target_guid, f.composite_target_name, f.composite_target_type, f.display_name, f.url_filename, nvl(d.metric_name, 'latency'); rem rem rem VIEW em$rt_url_slowest_ rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by slowest response time. rem rem COLUMNS rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_url_slowest_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, max(URL_LINK), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_URL_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct,MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid and def.assoc_guid = m.assoc_guid and def.assoc_def_name = 'supports_eum_on' and def.scope_target_type = ' ' and ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, d.URL_FILENAME ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_url_slowest_1day AS SELECT * from em$rt_url_slowest_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_url_slowest_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, max(URL_LINK), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_URL_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.source_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, d.URL_FILENAME ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_url_slowest_7day AS SELECT * from em$rt_url_slowest_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_url_slowest_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, max(URL_LINK), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_URL_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, d.URL_FILENAME ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_url_slowest_31day AS SELECT * from em$rt_url_slowest_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_url_most_hits rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by most hits. rem rem NOTES rem rem CREATE OR REPLACE VIEW em$rt_url_most_hits_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, max(URL_LINK), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_URL_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid and def.assoc_guid = m.assoc_guid and def.assoc_def_name = 'supports_eum_on' and def.scope_target_type = ' ' and ct.target_guid = m.source_target_guid GROUP BY m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, d.URL_FILENAME ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_url_most_hits_1day AS SELECT * from em$rt_url_most_hits_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_url_most_hits_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, max(URL_LINK), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_URL_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, d.URL_FILENAME ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_url_most_hits_7day AS SELECT * from em$rt_url_most_hits_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_url_most_hits_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", d.URL_FILENAME, max(URL_LINK), sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_URL_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, d.METRIC_NAME, d.URL_FILENAME ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_url_most_hits_31day AS SELECT * from em$rt_url_most_hits_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_url_dist_ rem rem PURPOSE rem View on the MGMT_RT_URL_DIST_1DAY/hour table contains the rollup metrics from the rem response distribution data samples into 1 day/hour intervals that were collected on the target rem system. For more information on valid metrics, see the description of the rem MGMT_RT_URL_DIST_1DAY/hour table. rem rem Selects composite target response time distribution for the rem given time span. rem The distribution shows the number of times an access took a rem certain number of seconds, ordered by number of seconds ascending. rem rem This view uses the decode function to group results together into rem larget buckets. rem rem COLUMNS rem See MGMT_RT_URL_DIST_1DAY rem See MGMT_RT_URL_DIST_1HOUR rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_url_dist_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, NUM_SECONDS, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME,ct.TARGET_TYPE, METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", URL_FILENAME, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_URL_DIST_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, URL_FILENAME, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_url_dist_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, NUM_SECONDS, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", URL_FILENAME, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_URL_DIST_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, URL_FILENAME, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_url_dist_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, NUM_SECONDS, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, nvl(max((SELECT display_name FROM MGMT_RT_URLS u WHERE u.target_guid = m.source_target_guid AND u.url_filename = d.url_filename)), d.url_filename) "DISPLAY_NAME", URL_FILENAME, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_URL_DIST_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, URL_FILENAME, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); rem rem VIEW em$rt_domain_slowest_ rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by slowest response time. rem rem COLUMNS rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_domain_slowest_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_domain_slowest_1day AS SELECT * from em$rt_domain_slowest_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_domain_slowest_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_domain_slowest_7day AS SELECT * from em$rt_domain_slowest_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_domain_slowest_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_domain_slowest_31day AS SELECT * from em$rt_domain_slowest_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_domain_most_hits_ rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by most hits. rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_domain_most_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_domain_most_hits_1day AS SELECT * from em$rt_domain_most_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_domain_most_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_domain_most_hits_7day AS SELECT * from em$rt_domain_most_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_domain_most_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME,ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_domain_most_hits_31day AS SELECT * from em$rt_domain_most_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_domain_dist_ rem rem PURPOSE rem Selects composite target response time distribution for the rem given time span. rem The distribution shows the number of times an access took a rem certain number of seconds, ordered by number of seconds ascending. rem rem COLUMNS rem See MGMT_RT_DOMAIN_DIST_1DAY rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_domain_dist_1day ( COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, NUM_SECONDS, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_DOMAIN_DIST_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_domain_dist_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, NUM_SECONDS, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_DOMAIN_DIST_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_domain_dist_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, NUM_SECONDS, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_DOMAIN_DIST_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_DOMAIN, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); rem rem VIEW em$rt_region_slowest_ rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by slowest response time. rem rem COLUMNS rem NOTES rem CREATE OR REPLACE VIEW em$rt_region_slowest_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1HOUR d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_region_slowest_1day AS SELECT * from em$rt_region_slowest_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_region_slowest_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_region_slowest_7day AS SELECT * from em$rt_region_slowest_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_region_slowest_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_region_slowest_31day AS SELECT * from em$rt_region_slowest_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_region_most_hits_ rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by most hits. rem rem COLUMNS rem NOTES rem CREATE OR REPLACE VIEW em$rt_region_hits_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1HOUR d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_region_most_hits_1day AS SELECT * from em$rt_region_hits_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_region_hits_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_region_most_hits_7day AS SELECT * from em$rt_region_hits_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_region_hits_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_DOMAIN_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_region_most_hits_31day AS SELECT * from em$rt_region_hits_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_region_dist_1day rem rem PURPOSE rem View on the MGMT_RT_URL_DIST_1DAY table contains the rollup metrics from the rem response distribution data samples into 1 day intervals that were collected on the target rem system. For more information on valid metrics, see the description of the rem MGMT_RT_DOMAIN_DIST_1DAY table. rem rem COLUMNS rem See MGMT_RT_DOMAIN_DIST_1DAY rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_region_dist_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, NUM_SECONDS, HITS) AS SELECT m.source_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_DOMAIN_DIST_1HOUR d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_region_dist_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, NUM_SECONDS, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_DOMAIN_DIST_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_region_dist_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_REGION, NUM_SECONDS, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_DOMAIN_DIST_1DAY d, mgmt_rt_regions r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.source_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND d.visitor_subnet_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||d.visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) and d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, r.region_name, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); rem rem VIEW em$rt_ip_slowest_ rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by slowest response time. rem rem COLUMNS rem NOTES rem CREATE OR REPLACE VIEW em$rt_ip_slowest_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_IP_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_ip_slowest_1day AS SELECT * from em$rt_ip_slowest_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_ip_slowest_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_IP_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_ip_slowest_7day AS SELECT * from em$rt_ip_slowest_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_ip_slowest_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_IP_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE ORDER BY sum(response_time_average*hits)/sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_ip_slowest_31day AS SELECT * from em$rt_ip_slowest_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_ip_most_hits rem rem PURPOSE rem Selects composite target response time stats for the time rem span specified, ordered by most hits. rem rem COLUMNS rem NOTES rem CREATE OR REPLACE VIEW em$rt_ip_most_hits_unf_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_IP_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_ip_most_hits_1day AS SELECT * from em$rt_ip_most_hits_unf_1day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_ip_most_hits_unf_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_IP_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_ip_most_hits_7day AS SELECT * from em$rt_ip_most_hits_unf_7day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); CREATE OR REPLACE VIEW em$rt_ip_most_hits_unf_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, RESPONSE_TIME_AVERAGE, RESPONSE_TIME_MINIMUM, RESPONSE_TIME_MAXIMUM, RESPONSE_TIME_STDDEV, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, sum(response_time_average*hits)/sum(hits), min(response_time_minimum), max(response_time_maximum), sum(RESPONSE_TIME_SDEV*hits)/sum(hits), sum(hits) FROM MGMT_RT_IP_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE ORDER BY sum(hits) DESC; CREATE OR REPLACE VIEW em$rt_ip_most_hits_31day AS SELECT * from em$rt_ip_most_hits_unf_31day d WHERE d.hits >= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = d.composite_target_guid AND pro.property_name = 'mgmt_rt_min_hits'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_min_hits')); rem rem VIEW em$rt_ip_dist_ rem rem PURPOSE rem Selects composite target response time distribution for the rem given time span. rem The distribution shows the number of times an access took a rem certain number of seconds, ordered by number of seconds ascending. rem rem COLUMNS rem See MGMT_RT_IP_DIST_1DAY rem rem NOTES rem CREATE OR REPLACE VIEW em$rt_ip_dist_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, NUM_SECONDS, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_IP_DIST_1HOUR d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_ip_dist_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, NUM_SECONDS, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_IP_DIST_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); CREATE OR REPLACE VIEW em$rt_ip_dist_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, METRIC_NAME, VISITOR_NODE, NUM_SECONDS, HITS) AS SELECT m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), SUM(HITS) FROM MGMT_RT_IP_DIST_1DAY d, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE d.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND d.dist_value_type = 0 GROUP BY m.SOURCE_TARGET_GUID, ct.TARGET_NAME, ct.TARGET_TYPE, METRIC_NAME, VISITOR_NODE, decode(num_seconds,0,'0',1,'1',2,'2',3,'3',4,'4',5,'5',6,'6',7,'7', 8,'8',9,'9',10,'10',11,'11', 12,'12',13,'13',14,'14',15,'15', 16, '16-20',17,'16-20',18,'16-20',19,'16-20',20,'16-20', 21, '21-25',22,'21-25',23,'21-25',24,'21-25',25,'21-25', 26, '26-30',27,'26-30',28,'26-30',29,'26-30',30,'26-30', 31, '31-35',32,'31-35',33,'31-35',34,'31-35',35,'31-35', 36, '36-40',37,'36-40',38,'36-40',39,'36-40',40,'36-40', 41, '41-50',42,'41-50',43,'41-50',44,'41-50',45,'41-50', 46, '41-50',47,'41-50',48,'41-50',49,'41-50',50,'41-50', 51, '51-60',52,'51-60',53,'51-60',54,'51-60',55,'51-60', 56, '51-60',57,'51-60',58,'51-60',59,'51-60',60,'51-60', '61+'), decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61) ORDER BY decode(NUM_SECONDS,0,0,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10, 11,11,12,12,13,13,14,14,15,15, 16,16,17,16,18,16,19,16,20,16,21,21,22,21,23,21,24,21,25,21, 26,26,27,26,28,26,29,26,30,26,31,31,32,31,33,31,34,31,35,31, 36,36,37,36,38,36,39,36,40,36, 41,41,42,41,43,41,44,41,45,41,46,41,47,41,48,41,49,41,50,41, 51,51,52,51,53,51,54,51,55,51,56,51,57,51,58,51,59,51,60,51, 61); rem rem VIEW em$rt_url_raw_slowest_ rem rem PURPOSE rem Slowest individual URL hits for the time rem span specified, ordered by slowest response time. rem rem COLUMNS rem NOTES rem CREATE OR REPLACE VIEW em$rt_url_raw_slowest_1day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_SUBNET, VISITOR_IP_NUM, VISITOR_IP, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, m.assoc_target_guid, r.metric_name, nvl( (SELECT u.display_name FROM MGMT_RT_URLS u WHERE m.source_target_guid = u.target_guid AND u.url_filename = r.url_filename), r.url_filename), r.url_filename, r.url_link, r.visitor_node, r.visitor_domain, SUBSTR(r.visitor_ip, 1, INSTR(r.visitor_ip, '.', 1, 3)-1) "VISITOR_SUBNET", r.visitor_ip_num, r.visitor_ip, r.collection_timestamp, round(r.elapsed_time/1000,2), r.submit_action_timestamp, r.os_name, r.os_version, r.browser_name, r.browser_version FROM em$rt_raw r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND r.elapsed_time <= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = r.target_guid AND pro.property_name = 'mgmt_rt_max_elapsed_time'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_max_elapsed_time')) ORDER BY r.elapsed_time DESC; CREATE OR REPLACE VIEW em$rt_url_raw_slowest_7day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_SUBNET, VISITOR_IP_NUM, VISITOR_IP, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, m.assoc_target_guid, r.metric_name, nvl( (SELECT u.display_name FROM MGMT_RT_URLS u WHERE m.source_target_guid = u.target_guid AND u.url_filename = r.url_filename), r.url_filename), r.url_filename, r.url_link, r.visitor_node, r.visitor_domain, SUBSTR(r.visitor_ip, 1, INSTR(r.visitor_ip, '.', 1, 3)-1) "VISITOR_SUBNET", r.visitor_ip_num, r.visitor_ip, r.collection_timestamp, round(r.elapsed_time/1000,2), r.submit_action_timestamp, r.os_name, r.os_version, r.browser_name, r.browser_version FROM em$rt_raw r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND r.elapsed_time <= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = r.target_guid AND pro.property_name = 'mgmt_rt_max_elapsed_time'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_max_elapsed_time')) ORDER BY r.elapsed_time DESC; CREATE OR REPLACE VIEW em$rt_url_raw_slowest_31day (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_SUBNET, VISITOR_IP_NUM, VISITOR_IP, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, m.assoc_target_guid, r.metric_name, nvl( (SELECT u.display_name FROM MGMT_RT_URLS u WHERE m.source_target_guid = u.target_guid AND u.url_filename = r.url_filename), r.url_filename), r.url_filename, r.url_link, r.visitor_node, r.visitor_domain, SUBSTR(r.visitor_ip, 1, INSTR(r.visitor_ip, '.', 1, 3)-1) "VISITOR_SUBNET", r.visitor_ip_num, r.visitor_ip, r.collection_timestamp, round(r.elapsed_time/1000,2), r.submit_action_timestamp, r.os_name, r.os_version, r.browser_name, r.browser_version FROM em$rt_raw r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.assoc_target_guid AND r.elapsed_time <= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = r.target_guid AND pro.property_name = 'mgmt_rt_max_elapsed_time'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_max_elapsed_time')) AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid ORDER BY r.elapsed_time DESC; rem rem VIEW em$rt_url_raw_recent_hits rem rem PURPOSE rem Most recent hits for a given URL rem rem COLUMNS rem NOTES rem CREATE OR REPLACE VIEW em$rt_url_raw_recent_hits (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_SUBNET, VISITOR_IP_NUM, VISITOR_IP, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT m.source_target_guid, ct.TARGET_NAME, ct.TARGET_TYPE, m.assoc_target_guid, r.metric_name, nvl( (SELECT u.display_name FROM MGMT_RT_URLS u WHERE m.source_target_guid = u.target_guid AND u.url_filename = r.url_filename), r.url_filename), r.url_filename, r.url_link, r.visitor_node, r.visitor_domain, SUBSTR(r.visitor_ip, 1, INSTR(r.visitor_ip, '.', 1, 3)-1) "VISITOR_SUBNET", r.visitor_ip_num, r.visitor_ip, r.collection_timestamp, round(r.elapsed_time/1000,2), r.submit_action_timestamp, r.os_name, r.os_version, r.browser_name, r.browser_version FROM em$rt_raw r, MGMT_TARGET_ASSOCS m, MGMT_TARGETS ct, MGMT_TARGET_ASSOC_DEFS def WHERE r.target_guid = m.assoc_target_guid AND def.assoc_guid = m.assoc_guid AND def.assoc_def_name = 'supports_eum_on' AND def.scope_target_type = ' ' AND ct.target_guid = m.source_target_guid AND r.elapsed_time <= NVL((SELECT property_value FROM mgmt_rt_target_properties pro WHERE pro.target_guid = r.target_guid AND pro.property_name = 'mgmt_rt_max_elapsed_time'), ( SELECT parameter_value FROM mgmt_parameters WHERE parameter_name = 'mgmt_rt_max_elapsed_time')) ORDER BY collection_timestamp DESC; CREATE OR REPLACE VIEW EM$RT_REGION_RAW_SLOWEST_1DAY (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, (select REGION_NAME from mgmt_rt_regions r where r.target_guid = d.composite_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) ) VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION FROM EM$RT_URL_RAW_SLOWEST_1DAY D ORDER BY d.elapsed_time DESC; CREATE OR REPLACE VIEW EM$RT_REGION_RAW_SLOWEST_7DAY (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, (select REGION_NAME from mgmt_rt_regions r where r.target_guid = d.composite_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) ) VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION FROM EM$RT_URL_RAW_SLOWEST_7DAY D ORDER BY d.elapsed_time DESC; CREATE OR REPLACE VIEW EM$RT_REGION_RAW_SLOWEST_31DAY (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, (select REGION_NAME from mgmt_rt_regions r where r.target_guid = d.composite_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) ) VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION FROM EM$RT_URL_RAW_SLOWEST_31DAY D ORDER BY d.elapsed_time DESC; CREATE OR REPLACE VIEW em$rt_region_raw_recent_hits (COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION) AS SELECT COMPOSITE_TARGET_GUID, COMPOSITE_TARGET_NAME, COMPOSITE_TARGET_TYPE, TARGET_GUID, METRIC_NAME, DISPLAY_NAME, URL_FILENAME, URL_LINK, VISITOR_NODE, VISITOR_DOMAIN, (select REGION_NAME from mgmt_rt_regions r where r.target_guid = d.composite_target_guid and r.region_guid IN (select mp.region_guid /*+ INDEX(mgmt_rt_region_entries IDX_REGION_MIN_IP) */ FROM mgmt_rt_region_entries e, mgmt_rt_region_mapping mp WHERE e.id = mp.id AND ((e.min_ip >= 0 AND visitor_ip_num between e.min_ip AND e.max_ip) OR (e.min_ip < 0 AND UPPER(substr('.'||visitor_domain, -LENGTH(e.domain)-1)) = UPPER('.'||e.domain)))) ) VISITOR_REGION, COLLECTION_TIMESTAMP, ELAPSED_TIME, SUBMIT_ACTION_TIMESTAMP, OS_NAME, OS_VERSION, BROWSER_NAME, BROWSER_VERSION FROM em$rt_url_raw_recent_hits D ORDER BY collection_timestamp DESC; rem rem PURPOSE rem rem The EM$RT_COOKIES view returns the response time data with rem its associated cookie data rem rem COLUMNS rem rem See MGMT_RT_METRICS_RAW rem MGMT_RT_COOKIE_DATA rem rem rem NOTES rem rem If all response time data doesn't have cookies, we'll need to tweak this. CREATE OR REPLACE VIEW em$rt_cookies ( target_guid, metric_name, collection_timestamp, status, status_description, submit_action_timestamp, load_action_timestamp, elapsed_time, url_filename, url_base, visitor_node, visitor_domain, visitor_ip, server_in_timestamp, server_out_timestamp, server_latency_time, database_time, browser_name, browser_version, os_name, os_version, username, cookie_name, cookie_value) AS SELECT target_guid, metric_name, collection_timestamp, status, status_description, submit_action_timestamp, load_action_timestamp, elapsed_time, url_filename, url_base, visitor_node, visitor_domain, visitor_ip, server_in_timestamp, server_out_timestamp, server_latency_time, database_time, browser_name, browser_version, os_name, os_version, username, t.name, t.value FROM MGMT_RT_METRICS_RAW s, MGMT_RT_COOKIE_DATA t WHERE s.cookie_index=t.raw_index; rem rem VIEW em$rt_incomplete_loads rem rem PURPOSE rem Incomplete Loads hourly rollup data with bootstrap data. rem COLUMNS rem See mgmt_rt_incomplete_loads_1hour rem NOTES rem This table contains all of the incomplete loads data for the past day. CREATE OR REPLACE VIEW em$rt_incomplete_loads ( target_guid, page_url, num_incomplete_loads, avg_server_time, rollup_timestamp) AS SELECT i.target_guid, page_url, num_incomplete_loads, avg_server_time, i.rollup_timestamp FROM MGMT_RT_INCOMPLETE_LOADS_1HOUR i, MGMT_TARGET_ROLLUP_TIMES r WHERE i.target_guid = r.target_guid AND r.rollup_table_name = 'MGMT_RT_INCOMPLETE_LOADS_1HOUR' AND i.rollup_timestamp <= r.rollup_timestamp UNION SELECT i.target_guid, page_url, num_incomplete_loads, avg_server_time, aggregate_hour_timestamp FROM MGMT_RT_INCOMPLETE_LOADS i, MGMT_TARGET_ROLLUP_TIMES r WHERE i.target_guid = r.target_guid AND r.rollup_table_name = 'MGMT_RT_INCOMPLETE_LOADS_1HOUR' AND TRUNC(i.aggregate_hour_timestamp, 'HH24') > r.rollup_timestamp; rem rem VIEW em$rt_pr_mapping rem rem PURPOSE rem Page Request Mapping hourly rollup data with bootstrap data. rem COLUMNS rem See mgmt_rt_pr_mapping_1hour rem NOTES rem This table contains all of the page request mapping data for the past day. CREATE OR REPLACE VIEW em$rt_pr_mapping ( target_guid, page_url, request_url, num_cache_hits, cache_hits_avg_svr_time, num_non_cache_hits, non_cache_hits_avg_svr_time, rollup_timestamp) AS SELECT i.target_guid, page_url, request_url, num_cache_hits, cache_hits_avg_svr_time, num_non_cache_hits, non_cache_hits_avg_svr_time, i.rollup_timestamp FROM MGMT_RT_PR_MAPPING_1HOUR i, MGMT_TARGET_ROLLUP_TIMES r WHERE i.target_guid = r.target_guid AND r.rollup_table_name = 'MGMT_RT_PR_MAPPING_1HOUR' AND i.rollup_timestamp <= r.rollup_timestamp UNION SELECT i.target_guid, page_url, request_url, num_cache_hits, cache_hits_avg_svr_time, num_non_cache_hits, non_cache_hits_avg_svr_time, i.aggregate_hour_timestamp FROM MGMT_RT_PR_MAPPING i, MGMT_TARGET_ROLLUP_TIMES r WHERE i.target_guid = r.target_guid AND r.rollup_table_name = 'MGMT_RT_PR_MAPPING_1HOUR' AND TRUNC(i.aggregate_hour_timestamp, 'HH24') > r.rollup_timestamp;