Rem
Rem $Header: ecm_hostpatch_views.sql 13-jul-2005.23:27:38 gsbhatia Exp $
Rem
Rem ecm_hostpatch_views.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_hostpatch_views.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Views for Host-Patching.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    07/13/05 - Remove repmgr header 
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    achugh      12/20/04 - achugh_bug-4046097
Rem    achugh      12/13/04 - Created
Rem


rem  PURPOSE
rem
rem    Stores Host Patching specific information for Hosts.
rem    
rem
rem  COLUMNS
rem    
rem    HOST_NAME - the name of the Host.
rem    GROUP_NAME - the name of the Group to which the host belongs.
rem    OUT_OF_DATE_PACKAGES - the number of out-of-date packages 
rem    on the host.
rem    ROGUE_PACKAGES - the number of rogue packages.
rem
rem 
rem
rem    
rem

CREATE OR REPLACE VIEW MGMT$HOSTPATCH_HOSTS AS 
SELECT t1.target_name as host_name,
       t2.target_name as group_name,
       h.out_of_date_pkgs as out_of_date_packages,
       h.rogue_pkgs as rogue_packages
FROM   mgmt_ecm_hostpatch_hosts h, 
       mgmt_targets t1,
       mgmt_targets t2
WHERE  t1.target_guid = h.host_guid
  AND  t2.target_guid = h.group_guid;

rem
rem  PURPOSE
rem
rem    Store additional Host Patching specific information for Groups
rem    created for Host Patching.
rem
rem  COLUMNS
rem
rem    GROUP_NAME - the (unique) name of the Group.
rem    MATURITY_LEVEL - the maturity level of the Group.
rem    NEED_REBOOT_PKGS - comma-separated list of specific package names
rem      or package name regular expressions denoting packages that should
rem      only be updated on a reboot.
rem

CREATE OR REPLACE VIEW MGMT$HOSTPATCH_GROUPS AS
SELECT t.target_name as group_name,
       g.maturity_level,
       g.need_reboot_pkgs
  FROM mgmt_ecm_hostpatch_groups g,
       mgmt_targets t
 WHERE t.target_guid = g.group_guid;

rem
rem  PURPOSE
rem    
rem    Stores the compliance summary history for Host Patching Groups.
rem
rem  COLUMNS
rem
rem    GROUP_NAME - the name of the Group.
rem    TOTAL_HOSTS - the number of Host Patching Hosts in the Group.
rem    COMPLIANT_HOSTS - the number of compliant Hosts in the Group.
rem    LAST_CHECKED_ON - the date on which this record was collected.
rem 
rem 

CREATE OR REPLACE VIEW MGMT$HOSTPATCH_GRP_COMPL_HIST AS
SELECT t.target_name as group_name,
       h.total_hosts,
       h.compl_hosts as compliant_hosts,
       h.checked_on as last_checked_on
FROM   mgmt_ecm_hostpatch_compl_hist h, 
       mgmt_targets t
WHERE  t.target_guid = h.group_guid;

rem  
rem  PURPOSE
rem 
rem    Stores the compliance information for a Host with respect to
rem    installed packages.
rem
rem  COLUMNS
rem 
rem    HOST_NAME - the name of the Host.
rem    PKG_NAME - the name of the installed package on the Host.
rem    VERSION - the version specification of the package.
rem    IS_ROGUE - "1" if the package is rogue, "0" otherwise.
rem    IS_OUT_OF_DATE - "1" if the package is out-of-date, "0" otherwise.
rem 

CREATE OR REPLACE VIEW MGMT$HOSTPATCH_HOST_COMPL AS
SELECT t.target_name as host_name,
       h.pkg_name,
       h.pkg_verspec as version,
       h.is_out_of_date,
       h.is_rogue
FROM   mgmt_ecm_hostpatch_host_compl h,
       mgmt_targets t
WHERE  t.target_guid = h.host_guid;