Rem Rem $Header: csa_client_pkgbody.sql 27-jun-2005.01:43:04 pratagar Exp $ Rem Rem csa_client_pkgbody.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem csa_client_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem pratagar 06/27/05 - Type Display Name Rem asaraswa 06/27/05 - enabling display_ui column in custom properties Rem display Rem asaraswa 04/20/05 - adding cpu to list of columns for hw details Rem page Rem asaraswa 03/22/05 - returning timezone delta Rem asaraswa 02/09/05 - adding browser_proxy_enabled and auto_config_url Rem to network details page Rem asaraswa 12/07/04 - changing collector query to return number of Rem cients Rem asaraswa 11/23/04 - adding new columns to UI Rem asaraswa 09/30/04 - grabtrans 'asaraswa_csa_target_associations' Rem asaraswa 09/29/04 - fixing display_name col Rem asaraswa 09/20/04 - adding procedures for target associations Rem asaraswa 08/31/04 - adding rel2 data to client display Rem asaraswa 08/24/04 - adding rules cursor to client homepage query Rem asaraswa 08/12/04 - changing client homepage queries to use internal Rem target name Rem rpinnama 06/12/04 - Rem asaraswa 03/30/04 - adding DHCP enabled column to NIC card table Rem asaraswa 03/19/04 - asaraswa_csa_patch Rem asaraswa 03/01/04 - Created Rem CREATE OR REPLACE PACKAGE BODY ecm_csa AS PROCEDURE client_homepage_data( details_cursor_out OUT cursorType, sw_cursor_out OUT cursorType, cookie_cursor_out OUT cursorType, custom_cursor_out OUT cursorType, rules_cursor_out OUT cursorType, target_name_in IN VARCHAR2) IS BEGIN OPEN details_cursor_out FOR SELECT v.display_target_name as display_name, v.key1 as target_key1, v.key2 as target_key2, v.key3 as target_key3, v.collection_timestamp as timestamp, v.hostname as hostname, v.domain as domain, v.os_user_name as username, v.is_windows_admin, v.windows_domain, (v.referring_url_header || decode(v.referring_url_params, NULL, '', '?') || v.referring_url_params) as ref_url, (v.csa_url_header || decode(v.csa_url_params, NULL, '', '?') || v.csa_url_params) as csa_url, (v.destination_url_header || decode(v.destination_url_params, NULL, '', '?') || v.destination_url_params) as dest_url, v.NET_IP as IP_addr, v.NET_EFFECTIVE_IP as effective_IP_addr, (v.browser_type || ' ' || v.browser_version) as browser, v.browser_proxy_server as proxy_server, v.net_latency_in_ms as latency, v.net_bandwidth_in_kbitps as bandwidth, v.NET_SUBNET as subnet, (v.BROWSER_JVM_VENDOR || ' ' || v.BROWSER_JVM_VERSION) as JVM, v.BROWSER_PROXY_EXCEPTIONS as browser_proxy_exceptions, v.BROWSER_CACHE_SIZE_IN_MB as browser_cache_size, v.BROWSER_CACHE_UPDATE_FRQ as cache_update_freq, v.BROWSER_HTTP1_1_SUPPORT as http1_1_support, (v.os_name || ' ' || v.os_base_version || ' ' || v.os_update_level) as os, v.os_address_length_in_bits as os_address_length, v.os_vendor_name as os_vendor, v.os_distributor_version as os_distributor_version, v.max_swap_space_in_mb as max_swap_space, v.timezone as os_timezone, v.timezone_region as os_timezone_region, v.timezone_delta as os_timezone_delta, v.max_process_virtual_memory as os_max_process_virtual_memory, v.number_of_os_patches as number_of_os_patches, v.system_config as sc, v.machine_architecture as ma, v.hardware_vendor_name as provider, v.cpu_count as cpu_count, v.memory_size_in_mb as memory_size, v.local_disk_space_in_gb as disk_size, v.avail_memory_size_in_mb as avail_memory, v.avail_local_disk_space_in_gb as avail_disk, v.bus_freq_in_mhz as fsb_freq, v.cpu_board_count as cpu_board_count, v.iocard_count as iocard_count, v.system_bios as system_bios, v.number_of_custom_values as number_of_custom_values, v.proxy_target_name as collector, v.connection_type as connection_type, v.compliance as overall_compliance, v.appid as application_id FROM mgmt$csa_collections v WHERE v.internal_target_name = target_name_in; OPEN sw_cursor_out FOR SELECT sw.name as sw_name, sw.version as sw_version, sw.vendor_name as vendor_name, sw.installed_location as installed_location, sw.installation_date as install_date FROM mgmt_ecm_os_registered_sw sw, mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id AND s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and sw.ecm_snapshot_id = s.snapshot_guid; OPEN cookie_cursor_out FOR SELECT c.name as name, c.value as value FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_csa_cookies c WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = c.ecm_snapshot_id; OPEN custom_cursor_out FOR SELECT decode(c.type_ui, NULL, c.type, c.type_ui) as category, decode(c.name_ui, NULL, c.name, c.name_ui) as name, c.value as value FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_csa_custom c WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = c.ecm_snapshot_id and (c.display_ui IS NULL or c.display_ui != 'N'); OPEN rules_cursor_out FOR SELECT r.name as name, r.status as status, r.description as description, r.moreinfo as moreinfo FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_csa_rules r WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = r.ecm_snapshot_id; end client_homepage_data; PROCEDURE client_network_browser_data(data_cursor_out OUT cursorType, target_name_in IN VARCHAR2) IS begin OPEN data_cursor_out FOR SELECT v.display_target_name as display_name, v.key1 as target_key1, v.key2 as target_key2, v.key3 as target_key3, v.timezone_delta as os_timezone_delta, v.timezone_region as os_timezone_region, v.collection_timestamp as timestamp, v.NET_IP as IP_addr, v.NET_EFFECTIVE_IP as effective_IP_addr, v.NET_LATENCY_IN_MS as latency, v.NET_BANDWIDTH_IN_KBITPS as bandwidth, v.NET_SUBNET as subnet, (v.BROWSER_TYPE || ' ' || v.BROWSER_VERSION) as browser, (v.BROWSER_JVM_VENDOR || ' ' || v.BROWSER_JVM_VERSION) as JVM, v.BROWSER_PROXY_SERVER as proxy_server, v.BROWSER_PROXY_EXCEPTIONS as browser_proxy_exceptions, v.BROWSER_CACHE_SIZE_IN_MB as browser_cache_size, v.BROWSER_CACHE_UPDATE_FRQ as cache_update_freq, v.BROWSER_HTTP1_1_SUPPORT as http1_1_support, v.connection_type as connection_type, v.appid as application_id, v.auto_config_url as auto_config_url, v.browser_proxy_enabled as browser_proxy_enabled FROM mgmt$csa_collections v WHERE v.internal_target_name = target_name_in; end client_network_browser_data; PROCEDURE client_os_general_data(data_cursor_out OUT cursorType, props_cursor_out OUT cursorType, target_name_in IN VARCHAR2) IS begin OPEN data_cursor_out FOR SELECT v.display_target_name as display_name, v.key1 as target_key1, v.key2 as target_key2, v.key3 as target_key3, v.collection_timestamp as timestamp, (v.os_name || ' ' || v.os_base_version || ' ' || v.os_update_level) as os, v.os_address_length_in_bits as os_address_length, v.os_vendor_name as os_vendor, v.os_distributor_version as os_distributor_version, v.max_swap_space_in_mb as max_swap_space, v.timezone as os_timezone, v.timezone_region as os_timezone_region, v.timezone_delta as os_timezone_delta, v.max_process_virtual_memory as os_max_process_virtual_memory, v.appid as application_id FROM mgmt$csa_collections v WHERE v.internal_target_name = target_name_in; OPEN props_cursor_out FOR SELECT p.source as os_prop_source, p.name as os_prop_name, p.value as os_prop_value FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_os_property p WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = p.ecm_snapshot_id; end client_os_general_data; PROCEDURE client_os_filesystem_data(data_cursor_out OUT cursorType, fs_cursor_out OUT cursorType, target_name_in IN VARCHAR2) IS begin OPEN data_cursor_out FOR SELECT v.display_target_name as display_name, v.key1 as target_key1, v.key2 as target_key2, v.key3 as target_key3, v.collection_timestamp as timestamp, v.timezone_delta as os_timezone_delta, v.timezone_region as os_timezone_region, (v.os_name || ' ' || v.os_base_version || ' ' || v.os_update_level) as os, v.os_address_length_in_bits as os_address_length, v.os_vendor_name as os_vendor, v.appid as application_id FROM mgmt$csa_collections v WHERE v.internal_target_name = target_name_in; OPEN fs_cursor_out FOR SELECT f.resource_name as resource_name, f.type as type, f.mount_location as mount_location, f.mount_options as mount_options FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_os_filesystem f WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = f.ecm_snapshot_id; end client_os_filesystem_data; PROCEDURE client_os_patches_data(data_cursor_out OUT cursorType, patches_cursor_out OUT cursorType, target_name_in IN VARCHAR2) IS begin OPEN data_cursor_out FOR SELECT v.display_target_name as display_name, v.key1 as target_key1, v.key2 as target_key2, v.key3 as target_key3, v.collection_timestamp as timestamp, v.timezone_delta as os_timezone_delta, v.timezone_region as os_timezone_region, (v.os_name || ' ' || v.os_base_version || ' ' || v.os_update_level) as os, v.os_address_length_in_bits as os_address_length, v.os_vendor_name as os_vendor, v.appid as application_id FROM mgmt$csa_collections v WHERE v.internal_target_name = target_name_in; OPEN patches_cursor_out FOR SELECT c.name as os_patch_name, c.version as os_patch_version FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_os_component c WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = c.ecm_snapshot_id and c.type = 'Patch' ORDER BY os_patch_name; end client_os_patches_data; PROCEDURE client_hw_details(data_cursor_out OUT cursorType, cpu_cursor_out OUT cursorType, io_devices_cursor_out OUT cursorType, network_interfaces_cursor_out OUT cursorType, target_name_in IN VARCHAR2) IS begin OPEN data_cursor_out FOR SELECT v.display_target_name as display_name, v.key1 as target_key1, v.key2 as target_key2, v.key3 as target_key3, v.collection_timestamp as timestamp, v.timezone_delta as os_timezone_delta, v.timezone_region as os_timezone_region, v.system_config as sc, v.machine_architecture as ma, v.hardware_vendor_name as provider, v.MEMORY_SIZE_IN_MB as memory_size, v.LOCAL_DISK_SPACE_IN_GB as disk_size, v.AVAIL_MEMORY_SIZE_IN_MB as avail_memory, v.AVAIL_LOCAL_DISK_SPACE_IN_GB as avail_disk, v.bus_freq_in_mhz as fsb_freq, v.cpu_count as cpu_count, v.cpu_board_count as cpu_board_count, v.iocard_count as iocard_count, v.system_bios as system_bios, v.os_name as os, v.appid as application_id, v.system_serial_number, v.cpu as processor_arch FROM mgmt$csa_collections v WHERE v.internal_target_name = target_name_in; OPEN cpu_cursor_out FOR SELECT c. FREQ_IN_MHZ as cpu_speed, c.VENDOR_NAME as cpu_vendor, c.revision as cpu_prom_rev, c.ecache_in_mb as cpu_ecache, c.impl as cpu_implementation, c.mask as cpu_mask FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_hw_cpu c WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = c.ecm_snapshot_id; OPEN io_devices_cursor_out FOR SELECT i.name as iocard_name, i.vendor_name as iocard_vendor, i.bus as iocard_bus_type, i.freq_in_mhz as iocard_freq, i.revision as iocard_prom_rev FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_hw_iocard i WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = i.ecm_snapshot_id; OPEN network_interfaces_cursor_out FOR SELECT n.name as nic_name, n.inet_address as nic_inet_address, n.max_transfer_unit as nic_max_transfer_unit, n.broadcast_address as nic_broadcast_address, n.mask as nic_mask, n.flags as nic_flags, n.mac_address as nic_mac_address, n.hostname_aliases as nic_aliases, n.dhcp_enabled as dhcp_enabled, n.default_gateway as default_gateway, n.description as description FROM mgmt_ecm_gen_snapshot s, mgmt_ecm_csa_general_info info, mgmt_ecm_hw_nic n WHERE s.snapshot_type = 'oracle_csa_host' and s.is_current = 'Y' and s.snapshot_guid = info.ecm_snapshot_id and s.target_name = target_name_in and (exists (select * from mgmt_targets t where t.target_guid = info.proxy_target_id)) and s.snapshot_guid = n.ecm_snapshot_id; end client_hw_details; PROCEDURE collector_details(host_out OUT VARCHAR2, dir_out OUT VARCHAR2, num_clients_out OUT NUMBER, target_name_in IN VARCHAR2) IS begin SELECT t.host_name into host_out FROM mgmt_targets t WHERE t.target_name = target_name_in; SELECT p.property_value into dir_out FROM mgmt_targets t, mgmt_target_properties p WHERE t.target_name = target_name_in and t.target_guid = p.target_guid and p.property_name = 'recvFileDir' and p.property_type = 'INSTANCE'; SELECT count(csa.snapshot_id) into num_clients_out FROM mgmt$csa_collections csa WHERE csa.proxy_target_name = target_name_in; end collector_details; PROCEDURE target_assoc_details(targets_out OUT cursorType, appIDs_out OUT cursorType, target_types_out OUT cursorType, target_type_in IN VARCHAR2) IS begin OPEN appIDs_out FOR SELECT unique appid as application_id FROM mgmt_ecm_csa_general_info; OPEN target_types_out FOR SELECT unique target_type, target_type as target_type_display FROM mgmt_targets; IF target_type_in IS NULL THEN OPEN targets_out FOR SELECT unique target_name, display_name as target_display_name FROM mgmt_targets; ELSE OPEN targets_out FOR SELECT unique t.target_name, t.display_name as target_display_name FROM mgmt_targets t WHERE t.target_type = target_type_in; END IF; end target_assoc_details; end ecm_csa; / show errors;