Rem drv: <create type="views" pos="severity/severity_views.sql+"/>
Rem
Rem $Header: basic_views.sql 01-jul-2005.18:43:32 gsbhatia Exp $
Rem
Rem basic_views.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      basic_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    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
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    ktlaw       01/11/05 - add repmgr header 
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

rem
rem  PURPOSE
rem
rem     The EM$CURRENT_METRICS view joins the metric definition information to
rem     the most current metric data sample that was collected for a target.
rem     This view is the primary view that would be used to find the current
rem     value of one or metrics.
rem
rem  COLUMNS
rem
rem     See MGMT_METRICS and MGMT_METRICS_RAW,
rem         MGMT_CURRENT_METRIC_ERRORS
rem
rem     Additional columns:
rem
rem     COLLECTION_TIMESTAMP_STRING  -
rem
rem        This column returns a date/time string form of
rem        the collection_timestamp that can be used
rem        directly for reporting.
rem
rem  NOTES
rem
rem
rem

CREATE OR REPLACE VIEW em$current_metrics
    (target_name, target_type, target_guid, metric_name, metric_type, metric_column,
     key_column, key_value, collection_timestamp, collection_timestamp_string,
     data_value, data_value_int, string_value,
     description, unit, warning_operator, warning_threshold,
     critical_operator, critical_threshold, metric_error_message)
  AS
    SELECT
        t.target_name, t.target_type, t.target_guid, m.metric_name,
        DECODE (m.metric_type, 0, 'NUMBER', 1, 'STRING', 2, 'TABLE',
                             3, 'RAW', 4, 'COMPOSITE_EVENT'),
        m.metric_column, m.key_column, r.key_value, collection_timestamp,
        TO_CHAR (collection_timestamp),
        value, ROUND(value), string_value, description, unit,
        DECODE (warning_operator, 0, 'GT', 1, 'EQ', 2, 'LT', 3, 'LE', 4, 'GE',
                                  5, 'CONTAINS', 6, 'NE'),
        warning_threshold,
        DECODE (critical_operator, 0, 'GT', 1, 'EQ', 2, 'LT', 3, 'LE', 4, 'GE',
                                   5, 'CONTAINS', 6, 'NE'),
        critical_threshold,
        (select metric_error_message from MGMT_CURRENT_METRIC_ERRORS e
          WHERE m.metric_guid=e.metric_guid)
      FROM
         MGMT_METRICS m, MGMT_CURRENT_METRICS r, 
         MGMT_TARGETS t, MGMT_METRIC_THRESHOLDS th
        WHERE t.target_guid=r.target_guid
          AND m.metric_guid=r.metric_guid
          AND t.target_guid = th.target_guid
          AND m.metric_guid = th.metric_guid
          AND r.key_value  = th.key_value;

rem
rem  PURPOSE
rem
rem     The EM$STRING_METRIC_HISTORY view joins the string metric definition
rem     information to the history of string metric data that was collected for
rem     a target.  This view is the primary view that would be used to find a
rem     past value(s) of a string metric.  The latest value of any metric,
rem     string or number, will be found more efficiently in the table
rem     em$current_metrics.
rem
rem  COLUMNS
rem
rem     See MGMT_METRICS and MGMT_METRICS_RAW
rem
rem     Additional columns:
rem
rem     COLLECTION_TIMESTAMP_STRING  -
rem
rem        This column returns a date/time string form of
rem        the collection_timestamp that can be used
rem        directly for reporting.
rem
rem  NOTES
rem
rem
rem

CREATE OR REPLACE VIEW em$string_metric_history
    (target_name, target_type, target_guid, metric_name, metric_column,
     key_column, key_value, collection_timestamp, collection_timestamp_string,
     string_value, description, unit, warning_operator, warning_threshold,
     critical_operator, critical_threshold)
  AS
    SELECT
        t.target_name, t.target_type, t.target_guid, m.metric_name,
        m.metric_column, m.key_column, r.key_value, collection_timestamp,
        TO_CHAR (collection_timestamp), string_value,
        description, unit,
        DECODE (warning_operator, 0, 'GT', 1, 'EQ', 2, 'LT', 3, 'LE', 4, 'GE',
                                  5, 'CONTAINS', 6, 'NE'),
        warning_threshold,
        DECODE (critical_operator, 0, 'GT', 1, 'EQ', 2, 'LT', 3, 'LE', 4, 'GE',
                                   5, 'CONTAINS', 6, 'NE'),
        critical_threshold
      FROM
         MGMT_TARGETS t, MGMT_METRICS m, 
         MGMT_STRING_METRIC_HISTORY r, MGMT_METRIC_THRESHOLDS th
        WHERE r.metric_guid=m.metric_guid
          AND r.target_guid=t.target_guid
          AND t.target_guid = th.target_guid
          AND m.metric_guid = th.metric_guid
          AND r.key_value = th.key_value;