Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\chronos\chronos

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\chronos\chronos_views.sql

Rem drv: <create type="views" pos="ip/webapps/webapp_views.sql+"/>
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 - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
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_<time_span>
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_<time_span>
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_<time_span>
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_<time_span>
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_<time_span>
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_<time_span>
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_<time span>
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_<time span>
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<time span>
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_<time span>
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_<time span>
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_<time span>
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_<time span>
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_<time span>
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_<time span>
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_<time span>
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<time span>
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_<time span>
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_<time span>
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;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de