Rem drv:
Rem
Rem $Header: ecm_views.sql 07-may-2007.07:44:22 denath Exp $
Rem
Rem ecm_views.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem ecm_views.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem denath 04/13/07 - Bug fix 5479717.addded ordered hint to
Rem mgmt$cpf_homes_info view and removed unwanted
Rem transitive joins.
Rem denath 05/07/07 - Backport denath_bug-5479717 from main
Rem rrawat 10/13/06 - Backport rrawat_bug-5546644 from main
Rem nitijain 12/05/05 - Backport nitijain_tran_bugs3 from main
Rem asaraswa 12/04/05 - Backport asaraswa_bug-4687751 from
Rem st_emcore_10.2.0.1.0
Rem rrawat 10/07/06 - fixing 5546644
Rem asaraswa 11/18/05 - fixing 4687751
Rem nitijain 11/29/05 - Correcting ecm_homes_by_aruid view to use host
Rem target name
Rem asaraswa 11/18/05 - fixing 4687751
Rem nitijain 11/29/05 - Correcting ecm_homes_by_aruid view to use host
Rem target name
Rem pdasika 09/14/05 - adding another predicate for mgmt$cpf_homes_info
Rem nitijain 08/19/05 - Changing em$ecm_homes_by_aruid so as not to use mgmt_target_type_component_map (for extensibility reasons)
Rem abhalla 08/03/05 - adding a view MGMT$CPF_PATCH_DATA
Rem asaraswa 07/13/05 - schema changes for bug 4491006
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem asaraswa 07/01/05 - fixing os summary view
Rem pdasika 06/23/05 - modifying mgmt$applied_patchsets to display external name
Rem asaraswa 05/16/05 - adding view for failed collections
Rem apbharga 04/28/05 - minor modifications to cpf homes view to improve
Rem performance
Rem asaraswa 03/28/05 - adding MHZ to processor frequency in
Rem mgmt$csa_collections
Rem mningomb 02/18/05 - Make patching MGMT$ views 10.2 security compliant
Rem gsbhatia 02/13/05 - updating repmgr header
Rem asaraswa 02/09/05 - adding new columns to mgmt$csa_collections
Rem gsbhatia 02/07/05 - updating repmgr header
Rem mningomb 02/01/05 - Correction in em$ecm_homes_by_aruid
Rem mgoodric 01/28/05 - fix UPDATE operations with null attributes
Rem ramalhot 01/17/05 - assoc def name changed from member to contains
Rem ktlaw 01/11/05 - add repmgr header
Rem pdasika 12/16/04 - adding new views for oracle home patching reports
Rem achugh 12/13/04 - to add views for host-patching
Rem mgoodric 12/13/04 - add LBL_TIMEZONE to CONFIG_HISTORY views
Rem asaraswa 11/23/04 - adding new data to mgmt$csa_collections
Rem mningomb 11/09/04 - changes to CPF_HOMES_INFO
Rem asaraswa 10/27/04 - adding views for CSA rules and custom props
Rem vkhizder 10/14/04 - improving perf of history views
Rem apbharga 09/30/04 - changes for using ARU platform id in affected homes for CPF
Rem mgoodric 09/26/04 - fix CONFIG_HISTORY views returning key columns
Rem mningomb 09/22/04 - changes to ecm_homes_by_aruid to return
Rem platform_id also
Rem mgoodric 09/20/04 - remove check for column history_ui_on (for now)
Rem mningomb 09/16/04 - changes to ecm_homes_by_aruid
Rem asaraswa 09/16/04 - removing unused view em$ecm_target_install
Rem mgoodric 09/11/04 - integrate with new history_ui_on flags
Rem apbharga 08/31/04 - New CPF Algoritm requires changes to
Rem MGMT_BUG_ADV_HOME_PATCH
Rem mningomb 08/23/04 - 10gr2: CPF Info
Rem asaraswa 08/16/04 - changing mgmt$cs_clients to work with new search
Rem ramalhot 08/24/04 - cutover to new assoc tables
Rem mgoodric 07/21/04 - add configHistory views
Rem vkhizder 07/15/04 - changing security conditions for CSA to depend on app id
Rem vkhizder 02/26/04 - various CSA schema changes
Rem asaraswa 11/14/03 - removing use of group_flat_memberships view
Rem asaraswa 11/12/03 - adding views to find installs in groups
Rem vkhizder 10/17/03 - adding csa views
Rem asaraswa 09/23/03 - fixing bug in count of missing hosts
Rem vkhizder 06/26/03 - adding csa views
Rem vkhizder 05/01/03 - changing emd_url to host_name/target_name matching for finding targets host
Rem shuberma 12/23/02 - Adding a query for the clone wizard destination lookup
Rem shuberma 11/01/02 - Fixing bug 2649501
Rem mgoodric 10/22/02 - Add support for EM_OS_BITLENGTH for platforms
Rem shuberma 09/23/02 - Removing the concatenation of columns and returning them separately
Rem shuberma 09/19/02 -
Rem shuberma 09/19/02 - Adding views for rollup tables
Rem mgoodric 07/09/02 - Generalize patch wizard targets
Rem shuberma 06/05/02 - Correcting ecm_patch_entries view..
Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Created
Rem
rem
rem PURPOSE
rem
rem The MGMT_ARU_PRODUCT_FAMILIES
rem
rem COLUMNS
rem
rem FAMILY_ID - The ARU product id (PRODUCT_ID) from the
rem ARU.ARU_PRODUCTS table for this product family.
rem
rem FAMILY_NAME - The display string from (PRODUCT_NAME) from the
rem ARU.ARU_PRODUCTS table for this product family.
rem
rem NOTES
rem
CREATE OR REPLACE VIEW MGMT_ARU_PRODUCT_FAMILIES AS
select product_id as family_id,
product_name as family_name
from mgmt_aru_products p
where exists
(
select *
from mgmt_aru_family_product_map m
where p.product_id = m.family_id
);
rem
rem PURPOSE
rem
rem The EM$ECM_DEPOT_PATCH_ENTRIES view returns the data needed by the view
rem patch page.
rem
rem COLUMNS
rem NAME - The name of the depot entry.
rem TYPE - The type of the entry (PATCH or PATCH_SET)
rem MANUALLY_ENTERED - Was this patch manually entered into the depot?
rem 'Y' or 'N';
rem PLATFORM - The platform, e.g. SunOS
rem DESCRIPTION - A text description of the depot entry.
rem BUGS_FIXED - A string which is a concatenated (space delimited)
rem list of the bug numbers.
rem APPROVED - Y or N
rem COMPONENT - The name of the base component that this patches.
rem COMPONENT_VERSION - The version of the base component that this
rem patches.
rem LANGUAGE - The language of the patch (ENGLISH)
rem ARU_ID - The internal id of the depot entry.
rem PLATFORM_ID - the ARU id of the platform.
rem PRODUCT_ID - the ARU id of the product or component.
rem PATCH_DATE - the ARU date of the patch.
rem
rem NOTES
rem
CREATE OR REPLACE VIEW EM$ECM_DEPOT_PATCH_ENTRIES
(
NAME,
TYPE,
MANUALLY_ENTERED,
PLATFORM,
DESCRIPTION,
COMPONENT,
COMPONENT_VERSION,
LANGUAGE,
ARU_ID,
PLATFORM_ID,
PRODUCT_ID,
PATCH_DATE,
AUTOMATED
)
AS
SELECT
pc.BUG_NO AS name,
pc.TYPE AS type,
pc.MANUALLY_POSTED AS manually_entered,
plat_map.em_os_name AS platform,
pc.ABSTRACT AS description,
prod_map.em_target_type AS component,
pc.RELEASE AS component_version,
pc.language_id AS language,
pc.ARU_ID AS aru_id,
pc.platform_id AS platform_id,
pc.product_id AS product_id,
pc.PATCH_DATE AS patch_date,
DECODE(pc.automated,'C','AUTOMATED','M','MANUAL','P','PARTIAL','MANUAL') AS automated
FROM
MGMT_ECM_PATCH_CACHE pc,
MGMT_ARU_PRODUCTS prod_map,
MGMT_ARU_PLATFORMS plat_map
WHERE
pc.platform_id = plat_map.platform_id(+) AND
pc.product_id = prod_map.product_id(+)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The EM$ECM_TARGETS_VIEW view returns the data needed by the view
rem targets page of the db/ias patch wizard
rem
rem COLUMNS
rem TARGET_NAME - The name of the target entry.
rem TARGET_TYPE - The type of the target entry
rem (oracle_database, oracle_emd, oracle_ias, etc)
rem TARGET_GIUD - The UNIQUE identifier FOR target
rem PLATFORM - The platform, e.g. SunOS
rem VERSION - The platform version, e.g. 5.6.
rem HOST - The host name of the target.
rem ORACLE_HOME - The Oracle Home path for this target.
rem ADDRESS_SIZE - The platform OS bit-length, e.g. 32
rem
rem NOTES
rem
CREATE OR REPLACE VIEW EM$ECM_TARGETS_VIEW AS
select db.target_name as target_name,
db.target_type as target_type,
db.target_guid as target_guid,
hos.property_value as platform,
iv.property_value as version,
htg.target_name as host,
dbp.property_value as oracle_home,
replace(os.address_length_in_bits, '-bit','') as address_size
from mgmt_targets db,
mgmt_targets htg,
mgmt_target_properties hos,
mgmt_target_properties iv,
mgmt_target_properties dbp,
mgmt_ecm_snapshot snap,
mgmt_hc_os_summary os
where
htg.target_type = 'host' and
db.host_name = htg.target_name and
hos.target_guid = htg.target_guid and
hos.property_name = 'OS' and
db.target_guid = iv.target_guid and
iv.property_name = 'Version' and
dbp.target_guid = db.target_guid and
dbp.property_name = 'OracleHome' and
snap.target_name = htg.target_name and
snap.target_type = htg.target_type and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y' and
os.snapshot_guid = snap.snapshot_guid
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The EM$ECM_HOMES_BY_ARUID view returns homes list for search in 'homes listing'
rem of patch wizard
rem
rem COLUMNS
rem HOME_ID - The home container guid
rem PLATFORM - The platform, e.g. SunOS
rem HOST - The host of the home
rem HOME_LOCATION - The directory on the host for the oracle home
rem HOME_NAME - The name of the home
rem TARGET_TYPE - The product target type of the component in the home
rem ADDRESS_SIZE - The platform OS bit-length, e.g. 32
rem ID_TYPE - 'ARU' or 'NON-ARU' signifying if aru id was found for the home or not
rem
rem NOTES
rem
CREATE OR REPLACE VIEW EM$ECM_HOMES_BY_ARUID AS
select home.container_guid as home_id,
plat.em_os_name as platform,
snap.target_name as host,
home.container_location as home_location,
home.container_name as home_name,
db.target_type as target_type,
plat.em_os_bitlength as address_size,
'ARU' as id_type,
decode(patchset.version,null,comp.version,patchset.version) as version,
plat.platform_id as platform_id
from
mgmt_inv_container home,
mgmt_inv_container_property prop,
mgmt_inv_component comp,
mgmt_ecm_snapshot snap,
mgmt_aru_platforms plat,
mgmt_inv_versioned_patch patchset,
mgmt_targets db,
mgmt_target_properties dbp
where
home.container_type = 'O' and
home.container_guid = prop.container_guid and
prop.property_name = 'ARU_PLATFORM_ID' and
plat.platform_id = to_number(prop.property_value) and
comp.container_guid = home.container_guid and
home.snapshot_guid = snap.snapshot_guid and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y' and
comp.component_guid = patchset.component_guid(+) and
dbp.property_name(+) ='OracleHome' and
db.target_guid(+) = dbp.target_guid and
dbp.property_value(+) = home.container_location
union
select /*+ INDEX(patchset MGMT_ECM_IDX_PLAT_NAME)*/
home.container_guid as home_id,
hos.property_value as platform,
snap.target_name as host,
home.container_location as home_location,
home.container_name as home_name,
db.target_type as target_type,
replace(os.address_length_in_bits, '-bit','') as address_size,
'NO_ARU' as id_type,
decode(patchset.version,null,comp.version,patchset.version) as version,
oui.aru_id as platform_id
from
mgmt_inv_container home,
mgmt_ecm_snapshot snap,
mgmt_targets htg,
mgmt_target_properties hos,
mgmt_inv_component comp,
mgmt_hc_os_summary os,
mgmt_inv_versioned_patch patchset,
mgmt_targets db,
mgmt_target_properties dbp,
mgmt_oui_aru_map oui
where
home.container_type = 'O' and
home.snapshot_guid = snap.snapshot_guid and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y' and
snap.target_name = htg.target_name and
snap.target_type = 'host' and
htg.target_guid = hos.target_guid and
hos.property_name = 'OS' and
hos.property_value is not null and
comp.container_guid = home.container_guid and
os.snapshot_guid = snap.snapshot_guid and
oui.oui_id = home.oui_platform and
comp.component_guid = patchset.component_guid(+) and
dbp.property_name(+) = 'OracleHome' and
db.target_guid(+) = dbp.target_guid and
dbp.property_value(+) = home.container_location and
not exists (
select
property_value
from
mgmt_inv_container_property p
where
container_guid = home.container_guid and
p.property_name = 'ARU_PLATFORM_ID'
)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The EM$ECM_TARGETS_BY_ARUID view returns the data needed by the view
rem targets page of the db/ias patch wizard
rem
rem COLUMNS
rem TARGET_NAME - The name of the target entry.
rem TARGET_TYPE - The type of the target entry
rem (oracle_database, oracle_emd, oracle_ias, etc)
rem TARGET_GIUD - The UNIQUE identifier FOR target
rem PLATFORM - The platform, e.g. SunOS
rem VERSION - The platform version, e.g. 5.6.
rem HOST - The host name of the target.
rem ORACLE_HOME - The Oracle Home path for this target.
rem ADDRESS_SIZE - The platform OS bit-length, e.g. 32
rem
rem NOTES
rem
CREATE OR REPLACE VIEW EM$ECM_TARGETS_BY_ARUID AS
select
db.target_name as target_name,
db.target_type as target_type,
db.target_guid as target_guid,
plat.em_os_name as platform,
iv.property_value as version,
htg.target_name as host,
dbp.property_value as oracle_home,
plat.em_os_bitlength as address_size,
'ARU' as id_type,
home.container_guid as container_guid,
prop.property_value as target_aru_id
from
mgmt_targets db,
mgmt_targets htg,
mgmt_target_properties iv,
mgmt_target_properties dbp,
mgmt_ecm_snapshot snap,
mgmt_inv_container home,
mgmt_aru_platforms plat,
mgmt_inv_container_property prop
where
htg.target_type = 'host' and
db.host_name = htg.target_name and
db.target_guid = iv.target_guid and
iv.property_name = 'Version' and
db.target_guid = dbp.target_guid and
dbp.property_name = 'OracleHome' and
snap.target_name = htg.target_name and
snap.target_type = htg.target_type and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y' and
home.snapshot_guid = snap.snapshot_guid and
home.container_location = dbp.property_value and
home.container_type = 'O' and
prop.container_guid = home.container_guid and
plat.platform_id = to_number(prop.property_value) and
prop.property_name = 'ARU_PLATFORM_ID'
union
select db.target_name as target_name,
db.target_type as target_type,
db.target_guid as target_guid,
hos.property_value as platform,
iv.property_value as version,
htg.target_name as host,
dbp.property_value as oracle_home,
replace(os.address_length_in_bits, '-bit','') as address_size,
'NOT_ARU' as id_type,
home.container_guid as container_guid,
to_char(oui.aru_id) as target_aru_id
from mgmt_targets db,
mgmt_targets htg,
mgmt_target_properties hos,
mgmt_target_properties iv,
mgmt_target_properties dbp,
mgmt_ecm_snapshot snap,
mgmt_hc_os_summary os,
mgmt_inv_container home,
mgmt_oui_aru_map oui
where
htg.target_type = 'host' and
db.host_name = htg.target_name and
hos.target_guid = htg.target_guid and
hos.property_name = 'OS' and
db.target_guid = iv.target_guid and
iv.property_name = 'Version' and
dbp.target_guid = db.target_guid and
dbp.property_name = 'OracleHome' and
snap.target_name = htg.target_name and
snap.target_type = htg.target_type and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y' and
os.snapshot_guid = snap.snapshot_guid and
home.snapshot_guid = snap.snapshot_guid and
home.container_location = dbp.property_value and
home.container_type = 'O' and
oui.oui_id = home.oui_platform and
not exists (
select
property_value
from
mgmt_inv_container_property p
where
container_guid = home.container_guid and
p.property_name = 'ARU_PLATFORM_ID'
)
WITH READ ONLY;
rem
rem PURPOSE
rem MGMT_INV_COMPONENT
rem
rem All component inventory elements will
rem have an entry in this table.
rem
rem COLUMNS
rem NAME
rem The name of the inventory element.
rem BASE_VERSION
rem The base version of the component
rem VERSION
rem The version of the inventory entry reflecting
rem the latest versioned patch.
rem CONTAINER_GUID
rem A FK pointing to the parent container.
rem COMPONENT_GUID
rem A global unique id for this entry.
rem DESCRIPTION
rem A description of the entry, as specified in the
rem installation.
rem EXTERNAL_NAME
rem A external name of this component, as specified
rem in the installation.
rem LANGUAGES
rem A list of language codes indicating the supported
rem languages.
rem INSTALLED_LOCATION
rem Location, or path of this component.
rem INSTALLER_VERSION
rem MIN_DEINSTALLER_VERSION
rem IS_TOP_LEVEL Is this a top level component in its container
rem possible values: Y, N
rem TIMESTAMP
rem When installed.
rem
CREATE OR REPLACE VIEW EM$INV_COMPONENT (
"NAME","BASE_VERSION","VERSION","CONTAINER_GUID",
"COMPONENT_GUID","DESCRIPTION","EXTERNAL_NAME",
"LANGUAGES","INSTALLED_LOCATION","INSTALLER_VERSION",
"MIN_DEINSTALLER_VERSION","IS_TOP_LEVEL","TIMESTAMP")
AS
select
c.name as name,
c.version as base_version,
decode( p.version, null, c.version, p.version ) as version,
c.container_guid,
c.component_guid,
c.description,
c.external_name,
c.languages,
c.installed_location,
c.installer_version,
c.min_deinstaller_version,
c.is_top_level,
c.timestamp
from
mgmt_inv_component c,
mgmt_inv_versioned_patch p
where
c.component_guid = p.component_guid(+)
WITH READ ONLY;
rem
rem PURPOSE
rem EM$TARGET_INSTANCE_INSTALL
rem
rem Get rollup information for targets on in composite targets on visible hosts.
rem This retrieves the number of
rem oracle-home installations,the number of instances and if there
rem are any one-off patches in the home install - for each target
rem composite_target type, composite_target name, target type, name and version.
rem
rem COLUMNS
rem COMPOSITE_TARGET_TYPE
rem The type of the composite target, e.g. 'oracle_database_group'
rem COMPOSITE_TARGET_NAME
rem The name of the composite target
rem TARGET_TYPE
rem The target type, e.g. 'oracle_database'
rem EXTERNAL_NAME
rem The external name as recorded in the inventory, or the targets installation
rem VERSION
rem The version of the target that is installed. This reflects patchset
rem version if a patchset was installed.
rem NUM_INSTALLS
rem Given the composite target, target_type, external name, and version, this is the number
rem of installations found among all the visible hosts
rem NUM_INSTANCES
rem Given the composite target, target_type, external name and version, this is the number of
rem visible instances found.
rem PATCHED
rem Are any of the installs (of the num_intalls) patched?
rem
CREATE OR REPLACE VIEW EM$ECM_COMPOSITE_TARG_INSTALL AS
select
composite_target_name,
composite_target_type,
inst.target_type,
external_name,
version,
count( distinct container_guid ) as num_installs,
count(distinct target_guid ) as num_instances,
case sum(num_patched) when 0 then 'No' else 'Yes' end as patched
from
( select
map.target_type,
component.external_name,
case when ps_patch.version is null then component.version else ps_patch.version end as version,
host.target_name as host_name,
home.container_location,
case when exists ( select * from mgmt_inv_patch p where p.container_guid = home.container_guid )then 1 else 0 end as num_patched,
home.container_guid
from
mgmt_targets host,
mgmt_ecm_snapshot snapshot,
mgmt_inv_container home,
mgmt_inv_component component,
mgmt_inv_versioned_patch ps_patch,
mgmt_target_type_component_map map
where host.target_type = 'host'
and host.target_name = snapshot.target_name
and host.target_type = snapshot.target_type
and snapshot.snapshot_type = 'host_configuration'
and snapshot.is_current = 'Y'
and home.snapshot_guid = snapshot.snapshot_guid
and component.container_guid = home.container_guid
and component.component_guid = ps_patch.component_guid(+)
and map.component_name = component.name
) inst,
( select
ct.target_name composite_target_name,
ct.target_type composite_target_type,
host.target_name as host_target_name,
target.target_name as target_name,
target.target_type as target_type,
property.property_value as home_location,
target.target_guid
from
mgmt_target_assocs composite,
mgmt_targets ct,
mgmt_target_assoc_defs def,
mgmt_targets host,
mgmt_targets target,
mgmt_target_properties property
where composite.assoc_target_guid = target.target_guid
and host.target_type = 'host'
and host.target_name = target.host_name
and property.target_guid = target.target_guid
and property.property_name = 'OracleHome'
and composite.assoc_guid = def.assoc_guid
and def.assoc_def_name = 'contains'
and def.scope_target_type = ' '
and ct.target_guid = composite.source_target_guid
) targs
where inst.host_name = targs.host_target_name(+)
and inst.container_location = targs.home_location(+)
and inst.target_type = targs.target_type(+)
group by composite_target_name, composite_target_type, inst.target_type, external_name, version
WITH READ ONLY;
rem
rem PURPOSE
rem EM$ECM_HARDWARE_COUNT
rem
rem Get hardware rollup information for visible hosts
rem This retrieves the number of each hw config
rem
rem COLUMNS
rem SYSTEM CONFIG
rem String describing hw config.
rem MACHINE_ARCHITECTURE
rem String describing hw config.
rem NUM_HOSTS
rem Number of hosts with this config.
rem
CREATE OR REPLACE VIEW EM$ECM_HARDWARE_COUNT AS
select
system_config,
machine_architecture,
count( * ) as num_hosts
from
mgmt_targets host,
mgmt_hc_hardware_master hw,
mgmt_ecm_snapshot ss,
mgmt_ecm_snap_component_info cc
where host.target_type = 'host'
and host.target_name = ss.target_name
and host.target_type = ss.target_type
and ss.snapshot_type = 'host_configuration'
and ss.is_current = 'Y'
and ss.snapshot_guid = hw.snapshot_guid
and ss.snapshot_guid = cc.snapshot_guid
and cc.component_name = 'oracle.hardware'
and cc.collection_status = 'COLLECTED'
group by system_config, machine_architecture
WITH READ ONLY;
rem
rem PURPOSE
rem EM$ECM_OS_COUNT
rem
rem Get OS rollup information for visible hosts
rem This retrieves the number of each os config
rem
rem COLUMNS
rem NAME
rem OS Name
rem BASE_VERSION
rem OS VERSION before update level
rem UPDATE_LEVEL
rem OS_UPDATE LEVEL
rem NUM_HOSTS
rem Number of hosts with this config.
rem PATCHED
rem Are any of the OS of this config patched?
rem
CREATE OR REPLACE VIEW EM$ECM_OS_COUNT AS
SELECT
o.name,
o.base_version,
o.update_level,
count(*) num_hosts,
decode(max(o.patches), 0, 'No', 'Yes') as patched
FROM
mgmt_hc_os_summary o
WHERE
o.snapshot_guid in
(
SELECT /*+ ORDERED */ c.snapshot_guid
FROM
mgmt_targets t,
mgmt_ecm_snapshot s,
mgmt_ecm_snap_component_info c
WHERE
s.snapshot_type = 'host_configuration'
AND s.target_type = 'host'
AND s.target_type = t.target_type
AND s.target_name = t.target_name
AND s.is_current = 'Y'
AND s.snapshot_guid = c.snapshot_guid
AND c.component_name = 'oracle.os_software'
AND c.collection_status = 'COLLECTED'
)
GROUP BY o.name, o.base_version, o.update_level
WITH READ ONLY;
rem
rem PURPOSE
rem EM$ECM_COMPOSITE_HW_COUNT
rem
rem Get hardware rollup information for hosts that are somehow related
rem to targets in composite targets
rem This retrieves the number of each hw config for each
rem composite_target type, composite_target name
rem
rem COLUMNS
rem COMPOSITE_TARGET_TYPE
rem The type of the composite target, e.g. 'oracle_database_group'
rem COMPOSITE_TARGET_NAME
rem The name of the composite target
rem SYSTEM CONFIG
rem String describing hw config.
rem MACHINE_ARCHITECTURE
rem String describing hw config.
rem NUM_HOSTS
rem Number of hosts with this config.
rem
CREATE OR REPLACE VIEW EM$ECM_COMPOSITE_HW_COUNT AS
select
composite_target_type,
composite_target_name,
system_config,
machine_architecture,
count( * ) as num_hosts
from
( select unique
ct.target_type composite_target_type,
ct.target_name composite_target_name,
snapshot_guid
from
mgmt_target_assocs composite,
mgmt_targets ct,
mgmt_target_assoc_defs def,
mgmt_targets target,
mgmt_targets host,
mgmt_ecm_snapshot ss
where composite.assoc_target_guid = target.target_guid
and target.host_name = host.target_name
and host.target_type = 'host'
and host.target_name = ss.target_name
and host.target_type = ss.target_type
and ss.snapshot_type = 'host_configuration'
and ss.is_current = 'Y'
and ct.target_guid = composite.source_target_guid
and def.assoc_guid = composite.assoc_guid
and def.assoc_def_name = 'contains'
and def.scope_target_type = ' '
) ss,
mgmt_hc_hardware_master hw
where ss.snapshot_guid = hw.snapshot_guid
group by composite_target_type, composite_target_name, system_config, machine_architecture
WITH READ ONLY;
rem
rem PURPOSE
rem EM$ECM_COMPOSITE_OS_COUNT
rem
rem Get OS rollup information for hosts that are somehow related
rem to targets in composite targets
rem This retrieves the number of each os config for each
rem composite_target type, composite_target name
rem
rem COLUMNS
rem COMPOSITE_TARGET_TYPE
rem The type of the composite target, e.g. 'oracle_database_group'
rem COMPOSITE_TARGET_NAME
rem The name of the composite target
rem NAME
rem OS Name
rem BASE_VERSION
rem OS VERSION before update level
rem UPDATE_LEVEL
rem OS_UPDATE LEVEL
rem NUM_HOSTS
rem Number of hosts with this config.
rem PATCHED
rem Are any of the OS of this config patched?
rem
CREATE OR REPLACE VIEW EM$ECM_COMPOSITE_OS_COUNT AS
select
composite_target_name,
composite_target_type,
name,
base_version,
update_level,
count( * ) as num_hosts,
case sum( num_patched ) when 0 then 'No' else 'Yes' end as patched
from
( select unique
ct.target_name composite_target_name,
ct.target_type composite_target_type,
name,
base_version,
update_level,
ss.snapshot_guid,
case when exists (select * from mgmt_hc_os_components c where ss.snapshot_guid = c.snapshot_guid and c.type = 'Patch') then 1 else 0 end as num_patched
from
mgmt_target_assocs composite,
mgmt_targets ct,
mgmt_target_assoc_defs def,
mgmt_targets target,
mgmt_targets host,
mgmt_hc_os_summary os,
mgmt_ecm_snapshot ss
where composite.assoc_target_guid = target.target_guid
and target.host_name = host.target_name
and host.target_type = 'host'
and host.target_name = ss.target_name
and host.target_type = ss.target_type
and ss.snapshot_type = 'host_configuration'
and ss.is_current = 'Y'
and ss.snapshot_guid = os.snapshot_guid
and ct.target_guid = composite.source_target_guid
and def.assoc_guid = composite.assoc_guid
and def.assoc_def_name = 'contains'
and def.scope_target_type = ' '
)
group by composite_target_name, composite_target_type, name, base_version, update_level
WITH READ ONLY;
CREATE OR REPLACE VIEW EM$ECM_HOST_HOME_INFO AS
SELECT
t.target_name as host_name,
ecm_util.concat_col( 'home',
'(select s.target_name as target, s.target_type as target_type,
c.container_location || ''('' || c.container_name || '')'' as home
from mgmt_ecm_snapshot s, mgmt_inv_container c
where s.is_current = ''Y'' and s.snapshot_type = ''host_configuration''
and s.snapshot_guid = c.snapshot_guid)',
'''' || t.target_type || ''' = target_type and target = ''' ||
t.target_name || '''',
', ' ) as home_info,
os.name as os_name,
os.base_version os_base_version,
os.update_level os_update_level,
os.address_length_in_bits os_address_length_in_bits
FROM
mgmt_targets t,
mgmt_ecm_snapshot s,
mgmt_hc_os_summary os
WHERE t.target_type = 'host' and
t.target_name = s.target_name and
s.snapshot_type = 'host_configuration' and
s.target_type = 'host' and
s.is_current = 'Y' and
s.snapshot_guid = os.snapshot_guid
WITH READ ONLY;
rem ********************** CSA VIEWS (BEGIN) **************************
-- The following views had been added for history / generic UI purposes
-- Given the next set of views, these should be reviewed / removed if
-- not needed, or modified to use the other views.. Or they can just stay
-- the same.
CREATE OR REPLACE VIEW EM$ECM_CSA_CUSTOM AS
SELECT
ECM_SNAPSHOT_ID, TYPE, NAME, TYPE_UI, NAME_UI, VALUE, DISPLAY_UI
FROM
MGMT_ECM_CSA_CUSTOM
WHERE HISTORY_TRACKING = 'Y';
CREATE OR REPLACE VIEW EM$ECM_HW_CPU AS
SELECT
ECM_SNAPSHOT_ID, VENDOR_NAME, FREQ_IN_MHZ, ECACHE_IN_MB,
IMPL, REVISION, MASK, COUNT(*) AS COUNT
FROM
MGMT_ECM_HW_CPU
group by ECM_SNAPSHOT_ID, VENDOR_NAME, FREQ_IN_MHZ, ECACHE_IN_MB,
IMPL, REVISION, MASK;
CREATE OR REPLACE VIEW EM$ECM_HW_IOCARD AS
SELECT
ECM_SNAPSHOT_ID, VENDOR_NAME, NAME, FREQ_IN_MHZ,
BUS, REVISION, COUNT(*) AS COUNT
FROM
MGMT_ECM_HW_IOCARD
group by ECM_SNAPSHOT_ID, VENDOR_NAME, NAME, FREQ_IN_MHZ, BUS, REVISION;
CREATE OR REPLACE VIEW EM$ECM_HW_NIC AS
SELECT
NAME, DESCRIPTION, FLAGS, MAX_TRANSFER_UNIT, INET_ADDRESS,
MASK, BROADCAST_ADDRESS, MAC_ADDRESS, HOSTNAME_ALIASES,
DEFAULT_GATEWAY, DHCP_ENABLED, COUNT(*) AS COUNT
FROM
MGMT_ECM_HW_NIC
group by NAME, DESCRIPTION, FLAGS, MAX_TRANSFER_UNIT, INET_ADDRESS,
MASK, BROADCAST_ADDRESS, MAC_ADDRESS, HOSTNAME_ALIASES,
DEFAULT_GATEWAY, DHCP_ENABLED;
-- The following views are hidden for now but ARE accessible to
-- knowledgable (internal CSA) users through "Search using SQL" GUI.
-- View Name : MGMT$CSA_CLIENTS
-- Description : This view presents all top-level information about CSA hosts.
CREATE OR REPLACE VIEW MGMT$CSA_CLIENTS AS
SELECT
s.display_target_name,
s.start_timestamp as collection_timestamp,
csa.NET_IP,
csa.NET_EFFECTIVE_IP,
s.message as collection_message,
info.os_user_name,
info.hostname,
info.domain,
info.boot_disk_volume_serial_num,
info.worst_rule_status as compliance,
info.appid as appid,
csa.NET_SUBNET,
csa.NET_LATENCY_IN_MS,
csa.NET_BANDWIDTH_IN_KBITPS,
csa.BROWSER_TYPE,
csa.BROWSER_VERSION,
csa.BROWSER_JVM_VENDOR,
csa.BROWSER_JVM_VERSION,
csa.BROWSER_PROXY_SERVER,
csa.BROWSER_PROXY_EXCEPTIONS,
csa.BROWSER_CACHE_SIZE_IN_MB,
csa.BROWSER_CACHE_UPDATE_FRQ,
csa.BROWSER_HTTP1_1_SUPPORT,
csa.REFERRING_URL_HEADER,
csa.REFERRING_URL_PARAMS,
csa.CSA_URL_HEADER,
csa.CSA_URL_PARAMS,
csa.DESTINATION_URL_HEADER,
csa.DESTINATION_URL_PARAMS,
csa.CONNECTION_TYPE,
( select count(*)
from mgmt_ecm_csa_cookies c
where s.snapshot_guid = c.ecm_snapshot_id ) as number_of_cookies,
( select count(*)
from mgmt_ecm_csa_custom c
where s.snapshot_guid = c.ecm_snapshot_id ) as number_of_custom_values,
hw.vendor_name as hardware_vendor_name,
hw.system_config,
hw.machine_architecture,
hw.clock_freq_in_mhz as bus_freq_in_mhz,
hw.MEMORY_SIZE_IN_MB,
hw.AVAIL_MEMORY_SIZE_IN_MB,
hw.LOCAL_DISK_SPACE_IN_GB,
hw.AVAIL_LOCAL_DISK_SPACE_IN_GB,
hw.cpu_count,
( select min(freq_in_mhz)
from mgmt_ecm_hw_cpu cpu
where s.snapshot_guid = cpu.ecm_snapshot_id ) as min_cpu_speed_in_mhz,
( select max(freq_in_mhz)
from mgmt_ecm_hw_cpu cpu
where s.snapshot_guid = cpu.ecm_snapshot_id ) as max_cpu_speed_in_mhz,
( select max(vendor_name || ' ' || impl || ' ' || freq_in_mhz || 'MHz')
from mgmt_ecm_hw_cpu cpu
where s.snapshot_guid = cpu.ecm_snapshot_id) as cpu,
hw.cpu_board_count,
hw.iocard_count,
( select count(*)
from mgmt_ecm_hw_nic nic
where s.snapshot_guid = nic.ecm_snapshot_id ) as nic_count,
hw.fan_count,
hw.power_supply_count,
hw.system_bios,
os.name as os_name,
os.vendor_name as os_vendor_name,
os.base_version as os_base_version,
os.update_level as os_update_level,
os.distributor_version as os_distributor_version,
os.max_swap_space_in_mb,
os.address_length_in_bits as os_address_length_in_bits,
os.max_process_virtual_memory,
os.TIMEZONE,
os.TIMEZONE_REGION,
os.TIMEZONE_DELTA,
( select count(*)
from mgmt_ecm_os_property p
where s.snapshot_guid = p.ecm_snapshot_id ) as number_of_os_properties,
( select count(*)
from mgmt_ecm_os_component c
where s.snapshot_guid = c.ecm_snapshot_id and c.type = 'Patch' ) as number_of_os_patches,
( select count(*)
from mgmt_ecm_os_filesystem f
where s.snapshot_guid = f.ecm_snapshot_id ) as number_of_os_filesystems,
( select count(*)
from mgmt_ecm_os_registered_sw s
where s.snapshot_guid = s.ecm_snapshot_id ) as number_of_os_registered_sw,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.target_name as internal_target_name,
s.target_type as internal_target_type,
s.elapsed_time as collection_duration,
s.saved_timestamp as loaded_timestamp,
info.applet_version,
info.target_id_method,
info.custom_class,
info.custom_class_version,
info.target_key1,
info.target_key2,
info.target_key3,
info.proxy_target_name,
info.proxy_target_display_name,
info.proxy_target_id
FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa csa,
mgmt_ecm_csa_general_info info, mgmt_ecm_hw hw,
mgmt_ecm_os os
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = csa.ecm_snapshot_id
AND s.snapshot_guid = info.ecm_snapshot_id
AND s.snapshot_guid = hw.ecm_snapshot_id
AND s.snapshot_guid = os.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_appid_target_map m,
mgmt_targets t
WHERE info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
COMMENT ON TABLE MGMT$CSA_CLIENTS IS
'This view has been deprecated. It has been replaced by MGMT$CSA_COLLECTIONS';
-- View Name : MGMT$CSA_COLLECTIONS
-- Description : View of all collections, used for search feature
CREATE OR REPLACE VIEW MGMT$CSA_COLLECTIONS AS
SELECT
s.display_target_name,
(s.display_target_name || NVL2(info.target_key1, '; ' || target_key1, '')
|| NVL2(info.target_key2, '; ' || target_key2, '')
|| NVL2(info.target_key3, '; ' || target_key3, '')) as csaclient,
s.start_timestamp as collection_timestamp,
FROM_TZ(CAST(s.start_timestamp as TIMESTAMP), os.timezone_region) AT LOCAL as adj_timestamp,
csa.NET_IP,
csa.NET_EFFECTIVE_IP,
s.message as collection_message,
info.os_user_name,
info.hostname,
info.domain,
NVL2(info.domain,
(CASE WHEN info.hostname LIKE '%' || info.domain THEN info.hostname ELSE info.hostname
|| '.' || info.domain END),
info.hostname) as host,
info.boot_disk_volume_serial_num,
info.worst_rule_status as compliance,
info.appid as appid,
csa.NET_SUBNET,
csa.NET_LATENCY_IN_MS,
csa.NET_BANDWIDTH_IN_KBITPS,
csa.BROWSER_TYPE,
csa.BROWSER_VERSION,
csa.browser_type || ' ' || csa.browser_version as browser,
csa.BROWSER_JVM_VENDOR,
csa.BROWSER_JVM_VERSION,
csa.BROWSER_PROXY_SERVER,
csa.BROWSER_PROXY_EXCEPTIONS,
csa.BROWSER_CACHE_SIZE_IN_MB,
csa.BROWSER_CACHE_UPDATE_FRQ,
csa.BROWSER_HTTP1_1_SUPPORT,
csa.REFERRING_URL_HEADER,
csa.REFERRING_URL_PARAMS,
csa.referring_url_header || NVL2(referring_url_params, '?' || referring_url_params, '') as refurl,
csa.CSA_URL_HEADER,
csa.CSA_URL_PARAMS,
csa.csa_url_header || NVL2(csa_url_params, '?' || csa_url_params, '') as csaurl,
csa.DESTINATION_URL_HEADER,
csa.DESTINATION_URL_PARAMS,
csa.destination_url_header || NVL2(destination_url_params, '?' || destination_url_params, '') as desturl,
csa.CONNECTION_TYPE,
csa.IS_WINDOWS_ADMIN,
csa.WINDOWS_DOMAIN,
csa.BROWSER_PROXY_ENABLED,
csa.AUTO_CONFIG_URL,
( select count(*)
from mgmt_ecm_csa_cookies c
where s.snapshot_guid = c.ecm_snapshot_id ) as number_of_cookies,
( select count(*)
from mgmt_ecm_csa_custom c
where s.snapshot_guid = c.ecm_snapshot_id ) as number_of_custom_values,
hw.system_config || ' ' || hw.machine_architecture || ', ' || hw.memory_size_in_mb || 'MB RAM ('
|| hw.avail_memory_size_in_mb || ' free), ' || hw.local_disk_space_in_gb || 'GB HD ('
|| hw.avail_local_disk_space_in_gb || ' free)'
|| NVL2(hw.clock_freq_in_mhz, ', ' || hw.clock_freq_in_mhz || 'MHz FSB', '')
|| NVL2(hw.cpu_count, ', ' || hw.cpu_count || ' CPUs', '')
|| NVL2(hw.vendor_name, ', ' || hw.vendor_name, '') as hardware,
hw.vendor_name as hardware_vendor_name,
hw.system_config,
hw.machine_architecture,
hw.clock_freq_in_mhz as bus_freq_in_mhz,
hw.MEMORY_SIZE_IN_MB,
hw.AVAIL_MEMORY_SIZE_IN_MB,
hw.LOCAL_DISK_SPACE_IN_GB,
hw.AVAIL_LOCAL_DISK_SPACE_IN_GB,
hw.cpu_count,
hw.SYSTEM_SERIAL_NUMBER,
( select min(freq_in_mhz)
from mgmt_ecm_hw_cpu cpu
where s.snapshot_guid = cpu.ecm_snapshot_id ) as min_cpu_speed_in_mhz,
( select max(freq_in_mhz)
from mgmt_ecm_hw_cpu cpu
where s.snapshot_guid = cpu.ecm_snapshot_id ) as max_cpu_speed_in_mhz,
( select max(vendor_name || ' ' || impl || ' ' || freq_in_mhz || 'MHz')
from mgmt_ecm_hw_cpu cpu
where s.snapshot_guid = cpu.ecm_snapshot_id) as cpu,
hw.cpu_board_count,
hw.iocard_count,
( select count(*)
from mgmt_ecm_hw_nic nic
where s.snapshot_guid = nic.ecm_snapshot_id ) as nic_count,
hw.fan_count,
hw.power_supply_count,
hw.system_bios,
os.name || ' ' || os.base_version || ' ' || os.update_level || '(' || os.address_length_in_bits || ')'
|| decode (os.distributor_version, 'N/A', '', ' ' || os.distributor_version) as operatingsystem,
os.name as os_name,
os.vendor_name as os_vendor_name,
os.base_version as os_base_version,
os.update_level as os_update_level,
os.distributor_version as os_distributor_version,
os.max_swap_space_in_mb,
os.address_length_in_bits as os_address_length_in_bits,
os.max_process_virtual_memory,
os.TIMEZONE,
os.TIMEZONE_REGION,
os.TIMEZONE_DELTA,
( select count(*)
from mgmt_ecm_os_property p
where s.snapshot_guid = p.ecm_snapshot_id ) as number_of_os_properties,
( select count(*)
from mgmt_ecm_os_component c
where s.snapshot_guid = c.ecm_snapshot_id and c.type = 'Patch' ) as number_of_os_patches,
( select count(*)
from mgmt_ecm_os_filesystem f
where s.snapshot_guid = f.ecm_snapshot_id ) as number_of_os_filesystems,
( select count(*)
from mgmt_ecm_os_registered_sw s
where s.snapshot_guid = s.ecm_snapshot_id ) as number_of_os_registered_sw,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.target_name as internal_target_name,
s.target_type as internal_target_type,
s.elapsed_time as collection_duration,
s.saved_timestamp as loaded_timestamp,
info.applet_version,
info.target_id_method,
info.custom_class,
info.custom_class_version,
NVL(info.target_key1, ' ') as key1,
NVL(info.target_key2, ' ') as key2,
NVL(info.target_key3, ' ') as key3,
info.proxy_target_name,
info.proxy_target_display_name,
info.proxy_target_id,
( select count(*)
from mgmt_ecm_csa_rules r
where s.snapshot_guid = r.ecm_snapshot_id) as rules_count,
( select count(*)
from mgmt_ecm_csa_rules r
where
s.snapshot_guid = r.ecm_snapshot_id
and r.status = -2 ) as rules_na_count,
( select count(*)
from mgmt_ecm_csa_rules r
where
s.snapshot_guid = r.ecm_snapshot_id
and r.status = 15 ) as rules_passed_count,
( select count(*)
from mgmt_ecm_csa_rules r
where
s.snapshot_guid = r.ecm_snapshot_id
and r.status = 18 ) as rules_info_count,
( select count(*)
from mgmt_ecm_csa_rules r
where
s.snapshot_guid = r.ecm_snapshot_id
and r.status = 20 ) as rules_warning_count,
( select count(*)
from mgmt_ecm_csa_rules r
where
s.snapshot_guid = r.ecm_snapshot_id
and r.status = 25 ) as rules_critical_count
FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa csa,
mgmt_ecm_csa_general_info info, mgmt_ecm_hw hw,
mgmt_ecm_os os
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = csa.ecm_snapshot_id
AND s.snapshot_guid = info.ecm_snapshot_id
AND s.snapshot_guid = hw.ecm_snapshot_id
AND s.snapshot_guid = os.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_appid_target_map m,
mgmt_targets t
WHERE info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_FAILED
-- Description : View for all failed all collections, used for search feature
CREATE OR REPLACE VIEW MGMT$CSA_FAILED AS
SELECT * FROM mgmt_ecm_csa_failed
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_OS_COMPONENTS
-- Description : This view presents all os components (such as patches
-- and packages) found on CSA hosts.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_OS_COMPONENTS AS
SELECT
s.display_target_name,
c.type,
c.name,
c.version,
c.description,
c.installation_date,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_os_component c
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = c.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_SW
-- Description : This view presents all os-registered software found on CSA hosts.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_SW AS
SELECT
s.display_target_name,
sw.name,
sw.vendor_name,
sw.version,
sw.installation_date,
sw.installed_location,
sw.description,
sw.vendor_sw_specific_info,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_os_registered_sw sw
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = sw.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_COOKIES
-- Description : This view presents cookies collection by CSA host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_COOKIES AS
SELECT
s.display_target_name,
c.name,
c.value,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_csa_cookies c
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = c.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_CUSTOM
-- Description : This view presents custom values collected by CSA host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_CUSTOM AS
SELECT
s.display_target_name,
c.type,
c.name,
c.type_ui,
c.name_ui,
c.value,
c.display_ui,
c.history_tracking,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_csa_custom c
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = c.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View name : MGMT$CSA_HOST_RULES
-- Descrption : This view represents all rules evaluated by a CSA collection
CREATE OR REPLACE VIEW MGMT$CSA_HOST_RULES AS
SELECT
s.snapshot_guid as snapshot_id,
r.name as name,
r.description as description,
r.status as status,
r.moreinfo as moreinfo
FROM
mgmt_ecm_gen_snapshot s,
mgmt_ecm_csa_rules r
WHERE
s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = r.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_CPUS
-- Description : This view presents CPU information collected by CSA host collections.
-- host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_CPUS AS
SELECT
s.display_target_name,
c.vendor_name,
c.freq_in_mhz,
c.ecache_in_mb,
c.impl,
c.revision,
c.mask,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp,
count(*) as number_of_cpus
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_hw_cpu c
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = c.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
GROUP BY
s.display_target_name,
c.vendor_name,
c.freq_in_mhz,
c.ecache_in_mb,
c.impl,
c.revision,
c.mask,
s.snapshot_guid,
s.target_guid,
s.start_timestamp
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_IOCARDS
-- Description : This view presents iocards collected by CSA host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_IOCARDS AS
SELECT
s.display_target_name,
c.vendor_name,
c.name,
c.freq_in_mhz,
c.bus,
c.revision,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp,
count(*) as number_of_iocards
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_hw_iocard c
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = c.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
GROUP BY
s.display_target_name,
c.vendor_name,
c.name,
c.freq_in_mhz,
c.bus,
c.revision,
s.snapshot_guid,
s.target_guid,
s.start_timestamp
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_NICS
-- Description : This view presents NICs collected by CSA host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_NICS AS
SELECT
s.display_target_name,
n.name,
n.description,
n.flags,
n.max_transfer_unit,
n.inet_address,
n.mask,
n.broadcast_address,
n.mac_address,
n.hostname_aliases,
n.default_gateway,
n.dhcp_enabled,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_hw_nic n
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = n.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_OS_PROPERTIES
-- Description : This view presents OS properties collected by CSA host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_OS_PROPERTIES AS
SELECT
s.display_target_name,
p.source,
p.name,
p.value,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_os_property p
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = p.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
-- View Name : MGMT$CSA_HOST_OS_FILESYSTEMS
-- Description : This view presents OS filesystems collected by CSA host collections.
CREATE OR REPLACE VIEW MGMT$CSA_HOST_OS_FILESYSTEMS AS
SELECT
s.display_target_name,
f.resource_name,
f.mount_location,
f.type,
f.disk_space_in_gb,
f.avail_disk_space_in_gb,
f.local_drive,
f.mount_options,
s.snapshot_guid as snapshot_id,
s.target_guid as target_id,
s.start_timestamp as collection_timestamp
FROM mgmt_ecm_gen_snapshot s,
mgmt_ecm_os_filesystem f
WHERE s.snapshot_type = 'oracle_csa_host'
AND s.is_current = 'Y'
AND s.snapshot_guid = f.ecm_snapshot_id
AND (EXISTS (SELECT * FROM mgmt_ecm_csa_general_info info,
mgmt_ecm_csa_appid_target_map m, mgmt_targets t
WHERE s.snapshot_guid = info.ecm_snapshot_id
AND info.appid = m.appid
AND t.target_guid = m.target_guid)
OR
mgmt_user.has_priv(mgmt_user.get_current_em_user(), 'SUPER_USER') = 1)
WITH READ ONLY;
rem ********************** CSA VIEWS (END) **************************
rem ********************* VIEWS for ECM rollup tables
--View Name: EM$ECM_INSTALLS_GROUPS
--Description: returns all groups, the software targets in those groups, and the installations for those targets
CREATE OR REPLACE VIEW EM$ECM_INSTALLS_GROUPS AS
SELECT UNIQUE
source_target.target_name as composite_target_name,
source_target.target_type as composite_target_type,
t.target_name as software_target_name,
t.target_type as software_target_type,
t.target_guid as software_target_guid,
s.display_target_name as display_host_name,
s.target_name as host_name,
h.container_name as container_name,
h.container_location as container_location,
h.container_guid as container_guid,
c.external_name as external_name,
decode(ivp.version, NULL, c.version, ivp.version) as version,
c.name as component_name,
map.property_name,
map.property_value
FROM
mgmt_ecm_snapshot s,
mgmt_inv_container h,
mgmt_flat_target_assoc tm,
mgmt_targets source_target,
mgmt_targets assoc_target,
mgmt_targets t,
mgmt_targets host,
mgmt_inv_component c,
mgmt_inv_versioned_patch ivp,
mgmt_target_properties home_prop,
mgmt_target_type_component_map map
WHERE tm.is_membership = 1
and tm.assoc_target_guid = t.target_guid
and source_target.target_guid = tm.source_target_guid
and assoc_target.target_guid = tm.assoc_target_guid
and t.host_name = s.target_name
and s.is_current = 'Y'
and s.target_type = 'host'
and s.snapshot_type = 'host_configuration'
and s.target_name = host.target_name
and s.target_type = host.target_type
and s.snapshot_guid = h.snapshot_guid
and c.container_guid = h.container_guid
and assoc_target.target_type = map.target_type
and c.name = map.component_name
and t.target_guid = home_prop.target_guid
and home_prop.property_name = 'OracleHome'
and home_prop.property_value = h.container_location
and c.component_guid = ivp.component_guid(+)
WITH READ ONLY;
--View Name: EM$ECM_HOST_INSTALLS_GROUPS
--Description: finds representative components (along
-- with their mapped types) for all homes on hosts included in groups
CREATE OR REPLACE VIEW EM$ECM_HOST_INSTALLS_GROUPS AS
SELECT UNIQUE
source_target.target_name as composite_target_name,
source_target.target_type as composite_target_type,
s.display_target_name as display_host_name,
s.target_name as host_name,
h.container_name as container_name,
h.container_location as container_location,
h.container_guid as container_guid,
c.external_name as external_name,
decode(ivp.version, NULL, c.version, ivp.version) as version,
c.name as component_name,
map.property_name,
map.property_value,
map.target_type
FROM
mgmt_flat_target_assoc tm,
mgmt_targets source_target,
mgmt_targets t,
mgmt_ecm_snapshot s,
mgmt_inv_container h,
mgmt_inv_component c,
mgmt_inv_versioned_patch ivp,
mgmt_target_type_component_map map
WHERE tm.is_membership = 1
and tm.assoc_target_guid = t.target_guid
and source_target.target_guid = tm.source_target_guid
and t.target_type = 'host' --this gets us all the hosts explicitly included with this group
and s.is_current = 'Y'
and s.snapshot_type = 'host_configuration'
and t.target_name = s.target_name
and t.target_type = s.target_type
and s.snapshot_guid = h.snapshot_guid
and h.container_guid = c.container_guid
and map.component_name = c.name
and c.component_guid = ivp.component_guid(+)
WITH READ ONLY;
--View Name: MGMT$CPF_ADVISORY_INFO
--Description: Gets The CPF Specific Advisory Information
CREATE OR REPLACE VIEW MGMT$CPF_ADVISORY_INFO
AS
select
a.advisory_name as advisory_name,
a.url as advisory_url,
a.impact as advisory_impact,
a.abstract as advisory_abstract,
ab.bug_number as advisory_bug
from
mgmt_bug_advisory a,
mgmt_bug_advisory_bug ab
where
a.advisory_name = ab.advisory_name;
--View Name: MGMT$CPF_HOMES_INFO
--Description: Gets the CPF Specific Oracle Home Information
CREATE OR REPLACE VIEW MGMT$CPF_HOMES_INFO
AS
select /*+ ORDERED */
cont.container_guid as home_id,
tgt.target_guid as host_target_guid,
tgt.target_name as home_host,
cont.container_location as home_directory,
cont.container_name as home_name,
decode(prop.property_value,
null, decode(cont.oui_platform,
610, 212,
87, 87,
197, 197,
2, 59,
211, 209,
110, 214,
46, 46,
912, 912,
208, 208,
453, 453,
23, 23,
173, 173
),
prop.property_value) as home_aru_platform,
decode(prop.property_value,
null, 'OUI',
'ARU') as home_platform_type,
summ.map_target_type as home_target_type,
summ.comp_version as home_version,
replace(host.address_length_in_bits,
'-bit', '') as home_host_bit_length
from
mgmt_targets tgt,
mgmt_ecm_snapshot snap,
mgmt_inv_container cont,
mgmt_inv_summary summ,
mgmt_inv_container_property prop,
mgmt_hc_os_summary host
where
tgt.target_type = 'host' and
tgt.target_type = snap.target_type and
tgt.target_name = snap.target_name and
snap.is_current = 'Y' and
snap.snapshot_type = 'host_configuration' and
snap.snapshot_guid = cont.snapshot_guid and
host.snapshot_guid = snap.snapshot_guid and
cont.container_type = 'O' and
cont.container_guid = summ.container_guid and
cont.container_guid = prop.container_guid (+) and
'ARU_PLATFORM_ID' = prop.property_name (+) and
snap.snapshot_guid = summ.snapshot_guid;
--View Name: MGMT$CPF_PATCH_INFO
--Description: Gets the CPF Specific Patch Information
CREATE OR REPLACE VIEW MGMT$CPF_PATCH_INFO
AS
select
data.ap_guid as patch_ap_guid,
data.patch_guid as patch_guid,
data.patch_id as patch_id,
data.release_id as patch_release_id,
data.platform_id as patch_platform_id,
data.product_id as patch_product_id,
data.patch_type as patch_type,
data.bug_number as patch_fixes_bug,
decode(pc.patch_id,
null, 'N',
'Y') as patch_valid_status
from
(select
ap.ap_guid,
pp.patch_guid,
ap.patch_id,
ap.release_id,
pp.platform_id,
ap.product_id,
ap.patch_type,
pfb.bug_number
from mgmt_bug_available_patch ap,
mgmt_bug_patch_platform pp,
mgmt_bug_patch_fixes_bug pfb
where ap.ap_guid = pp.ap_guid and
ap.ap_guid = pfb.ap_guid) data,
mgmt_bug_patch_certificate pc
where
data.patch_id = pc.patch_id (+) and
data.release_id = pc.release_id (+) and
data.platform_id = pc.platform_id (+)
order by
data.patch_id,
data.release_id,
data.platform_id;
--View Name: MGMT$CPF_PATCH_DATA
--Description: Gets the CPF Specific Patch Information without Patch Validation Status
CREATE OR REPLACE VIEW MGMT$CPF_PATCH_DATA
(PATCH_AP_GUID,
PATCH_GUID,
PATCH_ID,
PATCH_RELEASE_ID,
PATCH_PLATFORM_ID,
PATCH_PRODUCT_ID,
PATCH_TYPE,
PATCH_FIXES_BUG
) as
select
data.ap_guid as patch_ap_guid,
data.patch_guid as patch_guid,
data.patch_id as patch_id,
data.release_id as patch_release_id,
data.platform_id as patch_platform_id,
data.product_id as patch_product_id,
data.patch_type as patch_type,
data.bug_number as patch_fixes_bug
from
(select
ap.ap_guid,
pp.patch_guid,
ap.patch_id,
ap.release_id,
pp.platform_id,
ap.product_id,
ap.patch_type,
pfb.bug_number
from mgmt_bug_available_patch ap,
mgmt_bug_patch_platform pp,
mgmt_bug_patch_fixes_bug pfb
where ap.ap_guid = pp.ap_guid and
ap.ap_guid = pfb.ap_guid) data
order by data.patch_id,
data.release_id,
data.platform_id;
rem ********************** CONFIG HISTORY VIEWS (BEGIN) **************************
-- The following views had been added for Configuration History delta purposes
rem
rem PURPOSE
rem
rem The ECM$HIST_HARDWARE view returns the data needed by the snapshot
rem delta functions to record general hardware history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem HOSTNAME Name of the host (w/o domain)
rem DOMAIN IP domain of the host
rem VENDOR_NAME Name of the hardware vendor
rem SYSTEM_CONFIG Hardware platform, e.g. SunOS
rem MACHINE_ARCHITECTURE Machine architecture, e.g. i686
rem CLOCK_FREQ_IN_MHZ System clock frequency in megahertz
rem MEMORY_SIZE_IN_MB Total size of memory in megabytes
rem LOCAL_DISK_SPACE_IN_GB Total size of local disk space in gigabytes
rem CPU_COUNT Number of CPUs
rem CPU_BOARD_COUNT Number of CPU boards
rem FAN_COUNT Number of fans
rem POWER_SUPPLY_COUNT Number of power supplies
rem
rem NOTES
rem
CREATE OR REPLACE VIEW ECM$HIST_HARDWARE AS
SELECT
s.snapshot_guid AS ecm_snapshot_id,
s.hostname,
s.domain,
m.vendor_name,
m.system_config,
m.machine_architecture,
m.clock_freq_in_mhz,
m.memory_size_in_mb,
m.local_disk_space_in_gb,
m.cpu_count,
m.cpu_board_count,
m.iocard_count,
m.fan_count,
m.power_supply_count
FROM
mgmt_hc_system_summary s,
mgmt_hc_hardware_master m
WHERE s.snapshot_guid = m.snapshot_guid
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_CPU_DETAILS view returns the data needed by the snapshot
rem delta functions to record CPU history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem VENDOR_NAME Name of the vendor of the CPU
rem FREQ_IN_MHZ Frequency of CPU in megahertz
rem ECACHE_IN_MB Size of CPU cache in megabytes
rem IMPL CPU implementation
rem REVISION CPU revision
rem MASK
rem COUNT Number of CPU instances
rem
CREATE OR REPLACE VIEW ECM$HIST_CPU_DETAILS AS
SELECT
snapshot_guid AS ecm_snapshot_id,
vendor_name,
freq_in_mhz,
ecache_in_mb,
impl,
revision,
mask,
count(*) AS count
FROM
mgmt_hc_cpu_details
GROUP BY snapshot_guid,vendor_name,freq_in_mhz,ecache_in_mb,impl,revision,mask
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_IOCARD_DETAILS view returns the data needed by the snapshot
rem delta functions to record I/O card history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem VENDOR_NAME Name of the vendor of the I/O card
rem NAME Name of the I/O card
rem FREQ_IN_MHZ Frequency of I/O card in megahertz
rem BUS Type of bus (e.g. ISA, PCI, API)
rem REVISION I/O card revision
rem COUNT Number of I/O card instances
rem
CREATE OR REPLACE VIEW ECM$HIST_IOCARD_DETAILS AS
SELECT
snapshot_guid AS ecm_snapshot_id,
vendor_name,
name,
freq_in_mhz,
bus,
revision,
count(*) AS count
FROM
mgmt_hc_iocard_details
GROUP BY snapshot_guid,vendor_name,name,freq_in_mhz,bus,revision
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_NIC_DETAILS view returns the data needed by the snapshot
rem delta functions to record Network card history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem NAME Name of the network card
rem FLAGS Network interface flags
rem MAX_TRANSFER_UNIT Maximum transfer packet size in bytes
rem INET_ADDRESS Internet Protocol address
rem MASK IP address mask
rem BROADCAST_ADDRESS IP address of broadcast router
rem MAC_ADDRESS Internal identifier of network card
rem HOST_ALIASES List of DNS aliases for IP address
rem
CREATE OR REPLACE VIEW ECM$HIST_NIC_DETAILS AS
SELECT
snapshot_guid AS ecm_snapshot_id,
name,
flags,
max_transfer_unit,
inet_address,
mask,
broadcast_address,
mac_address,
hostname_aliases
FROM
mgmt_hc_nic_details
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_OS_SUMMARY view returns the data needed by the snapshot
rem delta functions to record general OS history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem NAME Name of the Operation System
rem VENDOR_NAME Name of the vendor of the OS
rem BASE_VERSION Version of the OS
rem UPDATE_LEVEL Patch level of the OS
rem DISTRIBUTOR_VERSION Version of the OS distibutor (Linux only)
rem MAX_SWAP_SPACE_IN_MB Size of OS swap space in megabytes
rem ADDRESS_LENGTH_IN_BITS OS word length in bits (e.g. 32 or 64)
rem
CREATE OR REPLACE VIEW ECM$HIST_OS_SUMMARY AS
SELECT
snapshot_guid AS ecm_snapshot_id,
name,
vendor_name,
base_version,
update_level,
distributor_version,
max_swap_space_in_mb,
address_length_in_bits
FROM mgmt_hc_os_summary
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_OS_COMPONENTS view returns the data needed by the snapshot
rem delta functions to record OS component history.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem NAME Name of the component
rem TYPE Type of the component (e.g. Package or Patch)
rem VERSION Version of the component
rem DESCRIPTION Description of the component
rem INSTALLATION_DATE Install date for the component
rem
CREATE OR REPLACE VIEW ECM$HIST_OS_COMPONENTS AS
SELECT
snapshot_guid AS ecm_snapshot_id,
name,
type,
version,
description,
installation_date
FROM mgmt_hc_os_components
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_OS_PROPERTIES view returns the data needed by the snapshot
rem delta functions to record OS property history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem TYPE Source of the property (e.g. /usr/sbin/sysdev)
rem NAME Name of the property
rem VALUE Property value
rem
CREATE OR REPLACE VIEW ECM$HIST_OS_PROPERTIES AS
SELECT
snapshot_guid AS ecm_snapshot_id,
type,
name,
value
FROM mgmt_hc_os_properties
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_FS_MOUNT_DETAILS view returns the data needed by the snapshot
rem delta functions to record OS file system history changes.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem RESOURCE_NAME Name of the mount point
rem MOUNT_LOCATION Path for the mount point
rem TYPE Type of file system (e.g. nfs, vfs)
rem MOUNT_OPTIONS Flags for the mount location (e.g. rw, timeout=600)
rem
CREATE OR REPLACE VIEW ECM$HIST_FS_MOUNT_DETAILS AS
SELECT
snapshot_guid AS ecm_snapshot_id,
resource_name,
mount_location,
type,
mount_options
FROM mgmt_hc_fs_mount_details
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_OS_REGISTERED_SW view returns the data needed by the snapshot
rem delta functions to record installed OS software history.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem NAME Name of the software
rem VENDOR_NAME Name of the software vendor
rem VERSION Version of the software
rem INSTALLATION_DATE Install date for the software
rem INSTALLED_LOCATION Path to the installed location
rem DESCRIPTION Description of the software
rem VENDOR_SOFTWARE_SPECIFIC_INFO Optional software info
rem
CREATE OR REPLACE VIEW ECM$HIST_OS_REGISTERED_SW AS
SELECT
snapshot_guid AS ecm_snapshot_id,
name,
vendor_name,
version,
installation_date,
installed_location,
description,
vendor_software_specific_info
FROM mgmt_hc_vendor_sw_summary
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_ORACLE_SOFTWARE view returns the data needed by the snapshot
rem delta functions to record history changes for a snapshot.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem CONTAINER_LOCATION Path to the location of the container
rem CONTAINER_NAME Name of the inventory container
rem
CREATE OR REPLACE VIEW ECM$HIST_ORACLE_SOFTWARE AS
SELECT
snapshot_guid AS ecm_snapshot_id,
container_location,
container_name
FROM mgmt_inv_container
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_INV_COMPONENTS view returns the data needed by the snapshot
rem delta functions to record history changes for a snapshot.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem NAME Name of the inventory element
rem VERSION Base version of the component
rem CONTAINER_LOCATION Path to the location of the home
rem DESCRIPTION Cescription of the entry, as specified in the
rem installation
rem EXTERNAL_NAME External name of this component, as specified
rem in the installation
rem LANGUAGES List of language codes indicating the supported
rem languages
rem INSTALLED_LOCATION Location, or path of this component
rem INSTALLER_VERSION
rem MIN_DEINSTALLER_VERSION
rem IS_TOP_LEVEL Is this a top level component in its container
rem possible values: Y, N
rem TIMESTAMP When installed
rem PATCH_VERSION Version of the inventory entry reflecting
rem the latest versioned patch
rem
CREATE OR REPLACE VIEW ECM$HIST_INV_COMPONENTS AS
SELECT
h.snapshot_guid AS ecm_snapshot_id,
c.name,
c.version,
h.container_location,
c.description,
NVL(c.external_name,c.name) AS external_name,
c.languages,
c.installed_location,
c.installer_version,
c.min_deinstaller_version,
c.is_top_level,
c.timestamp,
NVL(p.version,c.version) AS patch_version
FROM
mgmt_inv_component c,
mgmt_inv_container h,
mgmt_inv_versioned_patch p
WHERE c.container_guid = h.container_guid
AND c.component_guid = p.component_guid(+)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_INV_PATCHSETS view returns the data needed by the snapshot
rem delta functions to record history changes for a snapshot.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem NAME Name of the inventory patchset
rem VERSION Base version of the patchset
rem CONTAINER_LOCATION Path to the location of the home
rem DESCRIPTION Description of the entry, as specified in the
rem installation
rem EXTERNAL_NAME External name of this component, as specified
rem in the installation
rem INSTALLER_VERSION
rem MIN_DEINSTALLER_VERSION
rem IS_TOP_LEVEL Is this a top level component in its container
rem possible values: Y, N
rem TIMESTAMP When installed
rem
CREATE OR REPLACE VIEW ECM$HIST_INV_PATCHSETS AS
SELECT
h.snapshot_guid AS ecm_snapshot_id,
p.name,
p.version,
h.container_location,
p.description,
NVL(p.external_name,p.name) AS external_name,
p.installer_version,
p.min_deinstaller_version,
p.timestamp
FROM
mgmt_inv_patchset p,
mgmt_inv_container h
WHERE p.container_guid = h.container_guid
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The ECM$HIST_INV_PATCHES view returns the data needed by the snapshot
rem delta functions to record history changes for a snapshot.
rem
rem COLUMNS
rem ECM_SNAPSHOT_ID snapshot_guid of the snapshot
rem ID Id of the inventory patch
rem CONTAINER_LOCATION Path to the location of the home
rem DESCRIPTION Description of the entry, as specified in the
rem installation
rem TIMESTAMP When installed
rem IS_ROLLBACKABLE Is this patch rollbackable
rem possible values: Y, N
rem
CREATE OR REPLACE VIEW ECM$HIST_INV_PATCHES AS
SELECT
h.snapshot_guid AS ecm_snapshot_id,
p.id,
h.container_location,
p.description,
p.timestamp,
p.is_rollbackable
FROM
mgmt_inv_patch p,
mgmt_inv_container h
WHERE p.container_guid = h.container_guid
WITH READ ONLY;
-- The following views had been added for Configuration History UI purposes
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY view returns the data needed for generic categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem ROWGUID Entry ID GUID
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY AS
SELECT -- UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
mdt.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || mdt.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
i.row_guid AS ROWGUID,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_snap p,
mgmt_targets g,
mgmt_ecm_snapshot_metadata md,
mgmt_ecm_snapshot_md_tables mdt
WHERE e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND p.delta_type = 'HISTORY'
AND p.target_type = md.target_type
AND p.snapshot_type = md.snapshot_type
AND md.kind = 'P'
AND md.history_ui_on = 'Y'
AND md.metadata_id = mdt.metadata_id
AND mdt.name = i.collection_type
AND mdt.history_ui_on = 'Y'
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = md.metadata_id
AND mdc.table_name = mdt.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY_KEY1 view returns the data needed for specific categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem KEY1 Value of Key 1
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY_KEY1 AS
SELECT -- UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
t.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || t.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
iv1.value AS KEY1,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_ecm_md_hist_tbls t,
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_id_values iv1,
mgmt_delta_snap p,
mgmt_targets g
WHERE t.num_hist_ui_keys = 1
AND t.target_type = p.target_type
AND t.snapshot_type = p.snapshot_type
AND t.name = i.collection_type
AND p.delta_type = 'HISTORY'
AND e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND iv1.delta_ids_guid = i.row_guid
AND iv1.name = t.hist_ui_key1
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = t.metadata_id
AND mdc.table_name = t.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY_KEY2 view returns the data needed for specific categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem KEY1 Value of Key 1
rem KEY2 Value of Key 2
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY_KEY2 AS
SELECT -- UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
t.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || t.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
iv1.value AS KEY1,
iv2.value AS KEY2,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_ecm_md_hist_tbls t,
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_id_values iv1,
mgmt_delta_id_values iv2,
mgmt_delta_snap p,
mgmt_targets g
WHERE t.num_hist_ui_keys = 2
AND t.target_type = p.target_type
AND t.snapshot_type = p.snapshot_type
AND t.name = i.collection_type
AND p.delta_type = 'HISTORY'
AND e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND iv1.delta_ids_guid = i.row_guid
AND iv1.name = t.hist_ui_key1
AND iv2.delta_ids_guid = i.row_guid
AND iv2.name = t.hist_ui_key2
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = t.metadata_id
AND mdc.table_name = t.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY_KEY3 view returns the data needed for specific categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem KEY1 Value of Key 1
rem KEY2 Value of Key 2
rem KEY3 Value of Key 3
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY_KEY3 AS
SELECT --UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
t.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || t.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
iv1.value AS KEY1,
iv2.value AS KEY2,
iv3.value AS KEY3,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_ecm_md_hist_tbls t,
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_id_values iv1,
mgmt_delta_id_values iv2,
mgmt_delta_id_values iv3,
mgmt_delta_snap p,
mgmt_targets g
WHERE t.num_hist_ui_keys = 3
AND t.target_type = p.target_type
AND t.snapshot_type = p.snapshot_type
AND t.name = i.collection_type
AND p.delta_type = 'HISTORY'
AND e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND iv1.delta_ids_guid = i.row_guid
AND iv1.name = t.hist_ui_key1
AND iv2.delta_ids_guid = i.row_guid
AND iv2.name = t.hist_ui_key2
AND iv3.delta_ids_guid = i.row_guid
AND iv3.name = t.hist_ui_key3
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = t.metadata_id
AND mdc.table_name = t.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY_KEY4 view returns the data needed for specific categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem KEY1 Value of Key 1
rem KEY2 Value of Key 2
rem KEY3 Value of Key 3
rem KEY4 Value of Key 4
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY_KEY4 AS
SELECT --UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
t.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || t.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
iv1.value AS KEY1,
iv2.value AS KEY2,
iv3.value AS KEY3,
iv4.value AS KEY4,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_ecm_md_hist_tbls t,
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_id_values iv1,
mgmt_delta_id_values iv2,
mgmt_delta_id_values iv3,
mgmt_delta_id_values iv4,
mgmt_delta_snap p,
mgmt_targets g
WHERE t.num_hist_ui_keys = 4
AND t.target_type = p.target_type
AND t.snapshot_type = p.snapshot_type
AND t.name = i.collection_type
AND p.delta_type = 'HISTORY'
AND e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND iv1.delta_ids_guid = i.row_guid
AND iv1.name = t.hist_ui_key1
AND iv2.delta_ids_guid = i.row_guid
AND iv2.name = t.hist_ui_key2
AND iv3.delta_ids_guid = i.row_guid
AND iv3.name = t.hist_ui_key3
AND iv4.delta_ids_guid = i.row_guid
AND iv4.name = t.hist_ui_key4
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = t.metadata_id
AND mdc.table_name = t.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY_KEY5 view returns the data needed for specific categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem KEY1 Value of Key 1
rem KEY2 Value of Key 2
rem KEY3 Value of Key 3
rem KEY4 Value of Key 4
rem KEY5 Value of Key 5
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY_KEY5 AS
SELECT --UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
t.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || t.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
iv1.value AS KEY1,
iv2.value AS KEY2,
iv3.value AS KEY3,
iv4.value AS KEY4,
iv5.value AS KEY5,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_ecm_md_hist_tbls t,
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_id_values iv1,
mgmt_delta_id_values iv2,
mgmt_delta_id_values iv3,
mgmt_delta_id_values iv4,
mgmt_delta_id_values iv5,
mgmt_delta_snap p,
mgmt_targets g
WHERE t.num_hist_ui_keys = 5
AND t.target_type = p.target_type
AND t.snapshot_type = p.snapshot_type
AND t.name = i.collection_type
AND p.delta_type = 'HISTORY'
AND e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND iv1.delta_ids_guid = i.row_guid
AND iv1.name = t.hist_ui_key1
AND iv2.delta_ids_guid = i.row_guid
AND iv2.name = t.hist_ui_key2
AND iv3.delta_ids_guid = i.row_guid
AND iv3.name = t.hist_ui_key3
AND iv4.delta_ids_guid = i.row_guid
AND iv4.name = t.hist_ui_key4
AND iv5.delta_ids_guid = i.row_guid
AND iv5.name = t.hist_ui_key5
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = t.metadata_id
AND mdc.table_name = t.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem
rem PURPOSE
rem
rem MGMT$ECM_CONFIG_HISTORY_KEY6 view returns the data needed for specific categories
rem
rem COLUMNS
rem
rem DELTATIME Entry collection timestamp
rem DELTAGUID Entry GUID
rem TIMEZONE Target timezone region
rem TARGET_NAME Target name of entry
rem HOSTNAME Host name for target
rem TARGET_TYPE Target type of target
rem SNAPSHOTTYPE Snapshot type name
rem COLLECTIONTYPE Table name of entry
rem TABLE_PATH Full path of table, including parents
rem CATEGORY Fully qualified leaf category
rem OPERATION Type of change (UPDATE,DELETE,INSERT,SAME)
rem KEY1 Value of Key 1
rem KEY2 Value of Key 2
rem KEY3 Value of Key 3
rem KEY4 Value of Key 4
rem KEY5 Value of Key 5
rem KEY6 Value of Key 6
rem ATTRIBUTE Attribute column name
rem NEWVALUE New value of attribute
rem OLDVALUE Old value of attribute
rem
CREATE OR REPLACE VIEW MGMT$ECM_CONFIG_HISTORY_KEY6 AS
SELECT --UNIQUE
e.delta_time AS DELTATIME,
e.delta_entry_guid AS DELTAGUID,
g.timezone_region AS TIMEZONE,
g.target_name AS TARGET_NAME,
g.host_name AS HOSTNAME,
g.target_type AS TARGET_TYPE,
p.snapshot_type AS SNAPSHOTTYPE,
i.collection_type AS COLLECTIONTYPE,
t.full_table_path AS TABLE_PATH,
p.target_type || '|' || p.snapshot_type || '|' || t.full_table_path || '|L' AS CATEGORY,
e.operation AS OPERATION,
iv1.value AS KEY1,
iv2.value AS KEY2,
iv3.value AS KEY3,
iv4.value AS KEY4,
iv5.value AS KEY5,
iv6.value AS KEY6,
d.name AS ATTRIBUTE,
d.value AS NEWVALUE,
d.old_value AS OLDVALUE
FROM
mgmt_ecm_md_hist_tbls t,
mgmt_delta_entry e,
mgmt_delta_entry_values d,
mgmt_delta_ids i,
mgmt_delta_id_values iv1,
mgmt_delta_id_values iv2,
mgmt_delta_id_values iv3,
mgmt_delta_id_values iv4,
mgmt_delta_id_values iv5,
mgmt_delta_id_values iv6,
mgmt_delta_snap p,
mgmt_targets g
WHERE t.num_hist_ui_keys = 6
AND t.target_type = p.target_type
AND t.snapshot_type = p.snapshot_type
AND t.name = i.collection_type
AND p.delta_type = 'HISTORY'
AND e.row_guid = i.row_guid
AND e.delta_entry_guid = d.delta_entry_guid(+)
AND e.operation != 'SAME'
AND ((e.operation != 'UPDATE') OR NOT(NVL(d.value,' ') = NVL(d.old_value,' ')))
AND p.delta_guid = e.delta_guid
AND p.new_left_target_name = g.target_name
AND p.target_type = g.target_type
AND iv1.delta_ids_guid = i.row_guid
AND iv1.name = t.hist_ui_key1
AND iv2.delta_ids_guid = i.row_guid
AND iv2.name = t.hist_ui_key2
AND iv3.delta_ids_guid = i.row_guid
AND iv3.name = t.hist_ui_key3
AND iv4.delta_ids_guid = i.row_guid
AND iv4.name = t.hist_ui_key4
AND iv5.delta_ids_guid = i.row_guid
AND iv5.name = t.hist_ui_key5
AND iv6.delta_ids_guid = i.row_guid
AND iv6.name = t.hist_ui_key6
AND (exists (select * from mgmt_ecm_snapshot_md_columns mdc
where mdc.metadata_id = t.metadata_id
AND mdc.table_name = t.name
AND mdc.name = d.name
AND mdc.history_ui_on = 'Y')
OR d.name is null)
WITH READ ONLY;
rem ********************** CONFIG HISTORY VIEWS (END) **************************
rem
rem PURPOSE
rem
rem The MGMT$EM_HOMES_PLATFORM view returns the data needed for the patches applied report
rem
rem COLUMNS
rem
rem PLATFORM_ID - The aru platform id of the home
rem PLATFORM_NAME - The aru platform name of the home
rem The hosts platform is returned in case
rem the aru platform id cannot be determined
rem PLATFORM_ID - The aru platform id
CREATE OR REPLACE VIEW MGMT$EM_HOMES_PLATFORM
(HOME_ID, PLATFORM_ID, PLATFORM)
AS
SELECT
home.container_guid as home_id,
plat.platform_id as platform_id,
plat.platform_name as platform
FROM
mgmt_inv_container home,
mgmt_inv_container_property prop,
mgmt_aru_platforms plat,
mgmt_ecm_snapshot snap,
mgmt_targets target
WHERE
home.container_type = 'O' and
home.container_guid = prop.container_guid and
prop.property_name = 'ARU_PLATFORM_ID' and
plat.platform_id = to_number(prop.property_value) and
home.snapshot_guid = snap.snapshot_guid and
snap.target_name = target.target_name and
snap.target_type = target.target_type and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y'
UNION
SELECT
distinct home.container_guid as home_id,
plat.platform_id as platform_id,
hos.property_value as platform
FROM
mgmt_inv_container home,
mgmt_ecm_snapshot snap,
mgmt_targets htg,
mgmt_target_properties hos,
mgmt_aru_platforms plat
WHERE
home.container_type = 'O' and
home.snapshot_guid = snap.snapshot_guid and
snap.snapshot_type = 'host_configuration' and
snap.is_current = 'Y' and
snap.target_name = htg.target_name and
snap.target_type = htg.target_type and
htg.target_type = 'host' and
htg.target_guid = hos.target_guid and
hos.property_name = 'OS' and
hos.property_value is not null and
plat.em_os_name = hos.property_value and
not exists (
select
property_value
from
mgmt_inv_container_property p
where
container_guid = home.container_guid and
p.property_name = 'ARU_PLATFORM_ID'
)
WITH READ ONLY;
rem PURPOSE
rem
rem The MGMT$HOMES_AFFECTED view returns the results for the Affected Homes report.
rem
rem COLUMNS
rem
rem HOST - The host name
rem HOME_DIRECTORY - The home location
rem TARGET_GUID - The target_guid
rem ALERTS - Number of alerts affecting the home
CREATE OR REPLACE VIEW MGMT$HOMES_AFFECTED
(HOST, HOME_DIRECTORY, TARGET_GUID, ALERTS)
AS
SELECT
a.host_name as host,
a.home_location as home_directory,
a.target_guid,
count (distinct advisory_name) as alerts
FROM
mgmt_bug_adv_home_patch a, mgmt_targets t
WHERE
a.target_guid = t.target_guid
group by a.host_name,a.home_location,a.target_guid
WITH READ ONLY;
rem
rem PURPOSE
rem
rem The MGMT$PATCHES_AFFECTED view returns the results for the Advisories report.
rem
rem COLUMNS
rem
rem ADVISORY_NAME - The Alert that is being described
rem IMPACT - The impact of the alert
rem ABSTRACT - The abtract of what the Alert applies to
rem HOST_NAME - The host on which the affected home lies
rem HOME_LOCATION - The Directory of the Home
rem TARGET_GUID - The target_guid of the home
CREATE OR REPLACE VIEW MGMT$PATCH_ADVISORIES
(ADVISORY_NAME, IMPACT, ABSTRACT, HOST_NAME, HOME_LOCATION,
TARGET_GUID)
AS
SELECT
hom.advisory_name,
adv.impact,
adv.abstract,
hom.host_name,
hom.home_location,
tgt.TARGET_GUID
FROM
mgmt_bug_advisory adv,
mgmt_bug_adv_home_patch hom,
mgmt_targets tgt
WHERE
adv.advisory_name = hom.advisory_name and
tgt.target_type = 'host' and
tgt.target_name = hom.host_name
group by hom.advisory_name,impact,abstract,hom.host_name,home_location,tgt.target_guid
WITH READ ONLY;
COMMENT ON COLUMN SYSMAN.MGMT$PATCH_ADVISORIES.ADVISORY_NAME IS 'the advisory which is applicable to the home';
COMMENT ON COLUMN SYSMAN.MGMT$PATCH_ADVISORIES.TARGET_GUID IS 'target_guid of the home';
rem
rem PURPOSE
rem
rem The MGMT$APPL_PATCH_AND_PATCHSET view returns the results for the Applicable Patches/Patchsets report
rem
rem COLUMNS
rem
rem PATCH_ID - The patch_id of the Patch/Patchset
rem TYPE - The type of entry(Patch or Patchset)
rem PRODUCT - product name on which the patch is applicable
rem PATCH_RELEASE - The release_name of the patch.
rem For a patch it is the version that it is applicable on.
rem For a patchset it is the version that the home becomes on applying the patchset.
rem PLATFORM - The platform on which the patch is applicable
rem ADVISORY - The advisory which is applicable for the patch
rem HOST_NAME - The host name of the home on which the patch is applicable
rem HOME_LOCATION - The directory of the Home on which the patch is applicable
rem PATCH_GUID - The patch_guid of the patch/patchset
rem TARGET_GUID - The target_guid of the home
CREATE OR REPLACE VIEW MGMT$APPL_PATCH_AND_PATCHSET
(PATCH_ID, TYPE, PRODUCT, PATCH_RELEASE, PLATFORM,
ADVISORY, HOST_NAME, HOME_LOCATION, PATCH_GUID, TARGET_GUID)
AS
SELECT
ap.patch_id,
ap.patch_type as type,
prod.product_name as product,
rel.release_name as patch_release,
plat.platform_name as platform,
ecm_util.concat_col('distinct ADVISORY_NAME',
'MGMT_BUG_ADV_HOME_PATCH',
'PATCH_GUID = ''' || pp.patch_guid || '''',
',
') as advisory,
patch.host_name,
patch.home_location,
patch.patch_guid,
patch.target_guid
FROM
mgmt_bug_available_patch ap,
mgmt_bug_patch_platform pp,
mgmt_aru_products prod,
mgmt_aru_releases rel,
mgmt_aru_platforms plat,
mgmt_bug_adv_home_patch patch,
mgmt_targets targets
WHERE
ap.ap_guid = pp.ap_guid and
ap.product_id = prod.product_id and
ap.release_id = rel.release_id and
pp.PLATFORM_ID = plat.PLATFORM_ID and
pp.patch_guid = patch.patch_guid and
patch.target_guid = targets.target_guid
WITH READ ONLY;
rem PURPOSE
rem
rem The view returns the results for the Applied Patches report
rem
rem COLUMNS
rem
rem PATCH - The patch id of the patch applied
rem BUGS - The list of bugs that the patch fixes
rem INSTALLATION_TIME - The installation time of the patch
rem HOST - The Host on which the patch was applied
rem HOME_LOCATION - The Home Directory on which the patch was applied.
rem HOME_NAME - The Home Name where the patch was applied
rem CONTAINER_GUID - The container_guid of the home
rem TARGET_GUID - The target_guid of the target
CREATE OR REPLACE VIEW MGMT$APPLIED_PATCHES
(PATCH, BUGS, INSTALLATION_TIME, HOST, HOME_LOCATION,
HOME_NAME, CONTAINER_GUID, TARGET_GUID)
AS
SELECT
to_char(patch.id) as patch,
ecm_util.concat_col('distinct BUG_NUMBER',
'mgmt_inv_patch_fixed_bug',
'PATCH_GUID = ''' || patch.patch_guid || '''',',') as bugs,
patch.timestamp as installation_time,
tgt.target_name as host,
con.container_location as home_location,
con.container_name as home_name,
con.container_guid,
tgt.target_guid
FROM
mgmt_inv_container con,
mgmt_ecm_snapshot snap,
mgmt_inv_patch patch,
mgmt_targets tgt
WHERE
con.snapshot_guid = snap.snapshot_guid AND
snap.is_current = 'Y' AND
snap.snapshot_type = 'host_configuration' AND
con.container_guid = patch.container_guid AND
tgt.target_name = snap.target_name
WITH READ ONLY;
rem PURPOSE
rem
rem The view returns the results for the Applied Patchsets report
rem
rem COLUMNS
rem
rem VERSION - The platform version
rem NAME - The Patchset Name
rem TIMESTAMP - The installation time of the patch
rem HOST - The Host on which the patch was applied
rem HOME_NAME - The Home Name where the patch was applied
rem HOME_LOCATION - The Home Directory on which the patch was applied.
rem CONTAINER_GUID - The container_guid of the home
rem TARGET_GUID - The target_guid of the target
CREATE OR REPLACE VIEW MGMT$APPLIED_PATCHSETS
(VERSION, NAME, TIMESTAMP, HOST, HOME_NAME,
HOME_LOCATION, CONTAINER_GUID, TARGET_GUID)
AS
SELECT
patchset.version,
NVL(patchset.external_name,patchset.name) as name,
patchset.timestamp,
snap.target_name as host,
con.container_name as home_name,
con.container_location as home_location,
con.container_guid,
tgt.target_guid
FROM
mgmt_inv_container con,
mgmt_ecm_snapshot snap,
mgmt_inv_patchset patchset,
mgmt_targets tgt
WHERE
con.snapshot_guid = snap.snapshot_guid AND
snap.is_current = 'Y' AND
snap.snapshot_type = 'host_configuration' AND
con.container_guid = patchset.container_guid AND
tgt.target_name = snap.target_name
WITH READ ONLY;
rem Create Host Patching views.
@&EM_SQL_ROOT/core/latest/ecm/ecm_hostpatch_views.sql
show errors;