Rem drv: <create type="views" pos="chronos/chronos_views.sql+"/>
Rem
Rem $Header: sdk_chronos_views.sql 28-nov-2005.12:03:32 eporter Exp $
Rem
Rem sdk_chronos_views.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_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    eporter     11/28/05 - XbranchMerge eporter_bug-4682256 from main 
Rem    eporter     11/08/05 - Fix views wrt new region implementation. 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    yxie        06/06/05 - changes contains to supports_eum_on 
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/15/04 - Replace MGMT_RT_REGION_MEMBERS table 
Rem    ramalhot    08/24/04 - cutover to new assoc tables 
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    snakai      01/07/02 - Merged snakai_chronos_views
Rem    snakai      01/07/02 - Fixing views on emd_rt_metrics_raw, target name and type columns removed.
Rem    snakai      01/03/02 - Created
Rem

--------------------------------------------------------------------------
-- REGION 
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_REGION
  (target_name, target_type, region_name, description, 
   region_member, region_member_type)
AS 
SELECT
  tg.target_name, tg.target_type,
  rg.region_name, rg.description, re.domain, 
  DECODE(re.min_ip,
    -1, 'DOMAIN',
    'SUBNET')
FROM
  MGMT_TARGETS tg,
  MGMT_RT_REGIONS rg,
  MGMT_RT_REGION_MAPPING rm,
  MGMT_RT_REGION_ENTRIES re
WHERE
  tg.target_guid = rg.target_guid
  AND rg.region_guid = rm.region_guid 
  AND rm.id = re.id
WITH READ ONLY;
--------------------------------------------------------------------------
-- WATCHLIST
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_WATCHLIST
  (target_name, target_type, display_name, url, description)
AS 
SELECT
  tg.target_name, tg.target_type,
  wl.display_name, wl.url_filename, wl.description
FROM
  MGMT_TARGETS tg,
  MGMT_RT_URLS wl
WHERE
  tg.target_guid = wl.target_guid
WITH READ ONLY;
--------------------------------------------------------------------------
-- RAW
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_METRIC_DETAILS 
  (target_name, target_type,
   collection_timestamp, metric_name, metric_value,
   url, visitor_ip, visitor_node, 
   visitor_domain, visitor_subnet)
AS 
SELECT 
  st.target_name,st.target_type,
  rw.collection_timestamp, rw.metric_name, rw.elapsed_time,
  rw.url_filename, rw.visitor_ip, rw.visitor_node,
  rw.visitor_domain, 
  SUBSTR(rw.visitor_ip, 1, INSTR(rw.visitor_ip, '.', 1, 3)-1)
FROM 
  MGMT_RT_METRICS_RAW rw,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND rw.target_guid = tm.assoc_target_guid 
  AND st.target_guid = tm.source_target_guid
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_METRIC_DETAILS 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   collection_timestamp, metric_name, metric_value,
   url, visitor_ip, visitor_node, 
   visitor_domain, visitor_subnet)
AS 
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  rw.collection_timestamp, rw.metric_name, rw.elapsed_time,
  rw.url_filename, rw.visitor_ip, rw.visitor_node,
  rw.visitor_domain, 
  SUBSTR(rw.visitor_ip, 1, INSTR(rw.visitor_ip, '.', 1, 3)-1)
FROM 
  MGMT_RT_METRICS_RAW rw,
  MGMT_TARGETS tg,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = rw.target_guid
  AND rw.target_guid = tm.assoc_target_guid (+)
  AND st.target_guid = tm.source_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
WITH READ ONLY;
--------------------------------------------------------------------------
-- URL
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_URL_HOURLY 
  (target_name, target_type,
   url, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_URL_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_URL_DAILY 
  (target_name, target_type,
   url, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_URL_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_URL_DIST_HOURLY 
  (target_name, target_type,
   url, rollup_timestamp, metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_URL_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_URL_DIST_DAILY 
  (target_name, target_type,
   url, rollup_timestamp, metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_URL_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
-- URL BY MEMBER TARGET
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_URL_HOURLY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   url, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name, 
  ru.hits, ru.response_time_average,
  ru.response_time_minimum, ru.response_time_maximum,
  ru.response_time_sdev, ru.response_time_variance
FROM 
  MGMT_RT_URL_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_URL_DAILY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   url, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name, 
  ru.hits, ru.response_time_average,
  ru.response_time_minimum, ru.response_time_maximum,
  ru.response_time_sdev, ru.response_time_variance
FROM 
  MGMT_RT_URL_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_URL_DIST_HOURLY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   url, rollup_timestamp, metric_name, metric_value, sample_count)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds, ru.hits
FROM 
  MGMT_RT_URL_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_URL_DIST_DAILY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   url, rollup_timestamp, metric_name, metric_value, sample_count)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.url_filename, ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds, ru.hits
FROM 
  MGMT_RT_URL_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
WITH READ ONLY;
--------------------------------------------------------------------------
-- IP
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_IP_HOURLY 
  (target_name, target_type,
   visitor, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_IP_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_IP_DAILY 
  (target_name, target_type,
   visitor, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_IP_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_IP_DIST_HOURLY 
  (target_name, target_type,
   visitor, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_IP_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_IP_DIST_DAILY 
  (target_name, target_type,
   visitor, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_IP_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
-- IP BY MEMBER TARGET
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_IP_HOURLY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor, rollup_timestamp, metric_name, 
   sample_count, average, minimum, maximum, 
   standard_deviation, variance)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, ru.metric_name, 
  ru.hits, ru.response_time_average,
  ru.response_time_minimum, ru.response_time_maximum,
  ru.response_time_sdev, ru.response_time_variance
FROM 
  MGMT_RT_IP_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE tg.target_guid = ru.target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_IP_DAILY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor, rollup_timestamp, metric_name, 
   sample_count, average, minimum, maximum, 
   standard_deviation, variance)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, ru.metric_name, 
  ru.hits, ru.response_time_average,
  ru.response_time_minimum, ru.response_time_maximum,
  ru.response_time_sdev, ru.response_time_variance
FROM 
  MGMT_RT_IP_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_IP_DIST_HOURLY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, ru.hits
FROM 
  MGMT_RT_IP_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_IP_DIST_DAILY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_node, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, ru.hits
FROM 
  MGMT_RT_IP_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
WITH READ ONLY;
--------------------------------------------------------------------------
-- DOMAIN
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_DOMAIN_HOURLY 
  (target_name, target_type,
   visitor_domain, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_DOMAIN_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_DOMAIN_DAILY 
  (target_name, target_type,
   visitor_domain, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_DOMAIN_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_DOMAIN_DIST_HOURLY 
  (target_name, target_type,
   visitor_domain, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_DOMAIN_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_DOMAIN_DIST_DAILY 
  (target_name, target_type,
   visitor_domain, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_DOMAIN_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_domain, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
-- SUBNET
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_SUBNET_HOURLY 
  (target_name, target_type,
   visitor_subnet, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_DOMAIN_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_SUBNET_DAILY 
  (target_name, target_type,
   visitor_subnet, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_DOMAIN_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_SUBNET_DIST_HOURLY 
  (target_name, target_type,
   visitor_subnet, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_DOMAIN_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_SUBNET_DIST_DAILY 
  (target_name, target_type,
   visitor_subnet, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_DOMAIN_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  ru.visitor_subnet, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
-- REGION
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_REGION_HOURLY 
  (target_name, target_type,
   visitor_region, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_DOMAIN_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE ru.target_guid = tm.assoc_target_guid
  AND tm.source_target_guid = rg.target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
GROUP BY
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_REGION_DAILY 
  (target_name, target_type,
   visitor_region, rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS 
SELECT 
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, ru.metric_name,
  SUM(ru.hits), 
  DECODE(SUM(ru.hits),
    0, 0,
    SUM(ru.response_time_average * ru.hits)/SUM(ru.hits)),
  MIN(ru.response_time_minimum), 
  MAX(ru.response_time_maximum),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    SQRT(
      (SUM(ru.hits) * 
        (SUM(DECODE(ru.hits,
          0, 0,
          (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
            POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
        )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
      ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
    )
  ),
  DECODE( 
    SUM(ru.hits),
    0, 0,
    1, 0,
    (SUM(ru.hits) * 
      (SUM(DECODE(ru.hits,
        0, 0,
        (((ru.response_time_variance * ru.hits * (ru.hits - 1)) + 
          POWER((ru.hits * ru.response_time_average), 2)) / ru.hits))
      )) - POWER(SUM(ru.response_time_average * ru.hits), 2)
    ) / (SUM(ru.hits) * (SUM(ru.hits) - 1))
  )
FROM 
  MGMT_RT_DOMAIN_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  ru.target_guid = tm.assoc_target_guid
  AND tm.source_target_guid = rg.target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
GROUP BY
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, ru.metric_name
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_REGION_DIST_HOURLY 
  (target_name, target_type,
   visitor_region, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_DOMAIN_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  ru.target_guid = tm.assoc_target_guid
  AND tm.source_target_guid = rg.target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_REGION_DIST_DAILY 
  (target_name, target_type,
   visitor_region, rollup_timestamp, 
   metric_name, metric_value, sample_count)
AS 
SELECT 
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds, SUM(ru.hits)
FROM 
  MGMT_RT_DOMAIN_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  ru.target_guid = tm.assoc_target_guid
  AND tm.source_target_guid = rg.target_guid
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
  AND ru.dist_value_type = 0
GROUP BY
  st.target_name, st.target_type,
  rg.region_name, ru.rollup_timestamp, 
  ru.metric_name, ru.num_seconds
WITH READ ONLY;
--------------------------------------------------------------------------
-- DOMAIN/SUBNET/REGION BY MEMBER TARGET
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_DSR_HOURLY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor_domain, visitor_subnet, visitor_region, 
   rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_domain, ru.visitor_subnet, rg.region_name,
  ru.rollup_timestamp, ru.metric_name, 
  ru.hits, ru.response_time_average,
  ru.response_time_minimum, ru.response_time_maximum,
  ru.response_time_sdev, ru.response_time_variance
FROM 
  MGMT_RT_DOMAIN_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.source_target_guid = rg.target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_DSR_DAILY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor_domain, visitor_subnet, visitor_region, 
   rollup_timestamp, metric_name, sample_count,
   average, minimum, maximum, standard_deviation, variance)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_domain, ru.visitor_subnet, rg.region_name,
  ru.rollup_timestamp, ru.metric_name, 
  ru.hits, ru.response_time_average,
  ru.response_time_minimum, ru.response_time_maximum,
  ru.response_time_sdev, ru.response_time_variance
FROM 
  MGMT_RT_DOMAIN_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_DSR_DIST_HOURLY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor_domain, visitor_subnet, visitor_region, 
   rollup_timestamp, metric_name, metric_value, sample_count)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_domain, ru.visitor_subnet, rg.region_name,
  ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds, ru.hits
FROM 
  MGMT_RT_DOMAIN_DIST_1HOUR ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.source_target_guid = rg.target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
  AND ru.dist_value_type = 0
WITH READ ONLY;
--------------------------------------------------------------------------
CREATE OR REPLACE VIEW MGMT$CSM_MT_DSR_DIST_DAILY 
  (member_target_name, member_target_type,
   composite_target_name, composite_target_type,
   visitor_domain, visitor_subnet, visitor_region, 
   rollup_timestamp, metric_name, metric_value, sample_count)
AS
SELECT 
  tg.target_name, tg.target_type,
  st.target_name, st.target_type,
  ru.visitor_domain, ru.visitor_subnet, rg.region_name,
  ru.rollup_timestamp, ru.metric_name,
  ru.num_seconds, ru.hits
FROM 
  MGMT_RT_DOMAIN_DIST_1DAY ru,
  MGMT_TARGET_ASSOCS tm,
  MGMT_TARGETS tg,
  MGMT_RT_REGIONS rg,
  MGMT_TARGETS st,
  MGMT_TARGET_ASSOC_DEFS d
WHERE
  tg.target_guid = ru.target_guid
  AND ru.target_guid = tm.assoc_target_guid (+)
  AND tm.source_target_guid = rg.target_guid (+)
  AND tm.assoc_guid = d.assoc_guid
  AND d.assoc_def_name = 'supports_eum_on'
  AND d.scope_target_type = ' '
  AND st.target_guid = tm.source_target_guid
  AND rg.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 
             ru.visitor_subnet_num BETWEEN e.min_ip AND e.max_ip) 
         OR (e.min_ip < 0 AND UPPER(substr('.'||ru.visitor_domain, 
             -LENGTH(e.domain)-1)) = UPPER('.'||e.domain))))
  AND ru.dist_value_type = 0
WITH READ ONLY;
--------------------------------------------------------------------------