Rem Rem $Header: client_config_pkgbody.sql 19-jun-2002.12:02:46 lgloyd Exp $ Rem Rem client_config_pkgsbody.sql Rem Rem Copyright (c) 2002, Oracle Corporation. All rights reserved. Rem Rem NAME Rem client_config_pkgsbody.sql - client config queries Rem Rem DESCRIPTION Rem Returns information about client configuration data Rem for a given application or all applications. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem lgloyd 06/19/02 - lgloyd_cliinv_0604 Rem CREATE OR REPLACE PACKAGE body em_client_config AS -- -- getApplications - get a list of applications in this repository -- -- Inputs: -- -- Outputs: -- results_out - results cursor -- Items returned include: -- application -- PROCEDURE getApplications(results_out OUT cursorType) IS sqlquery VARCHAR(3000); BEGIN sqlquery := 'select distinct app_id from mgmt_client_config_summary'; OPEN results_out FOR sqlquery; END; -- -- getGroupByStats - get stats for client configuration by group -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_by_in (subnet, domain, ipaddress) -- is_composite_in - set to "true" if given target is a composite, will show data for member targets -- -- Outputs: -- -- -- results_out - results cursor -- Items returned include: -- groupName -- avgLatencyVal -- stddevLatencyVal -- minLatencyVal -- maxLatencyVal -- throughputVal -- hitsVal -- PROCEDURE getGroupByStats(apps_id_in IN VARCHAR2, group_by_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); groupColumn VARCHAR(200); defaultGroupColumn VARCHAR(200); BEGIN defaultGroupColumn := 'NET_SUBNET'; IF ((length(trim(group_by_in))) > 0) THEN IF group_by_in = 'subnet' THEN groupColumn := 'NET_SUBNET'; ELSIF group_by_in = 'domain' THEN groupColumn := 'CLIENT_DOMAIN'; ELSIF group_by_in = 'ipAddress' THEN groupColumn := 'NET_IPADDRESS'; ELSE groupColumn := defaultGroupColumn; END IF; ELSE groupColumn := defaultGroupColumn; END IF; sqlquery := 'select '|| groupColumn||', '|| ' to_char(trunc(avg(net_latency))), '|| ' to_char(trunc(stddev(net_latency))), '|| ' to_char(min(net_latency)), '|| ' to_char(max(net_latency)), '|| ' to_char(trunc(avg(net_measuredrate))), '|| ' count(*) '|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :1'; END IF; sqlquery := sqlquery||' group by '||groupColumn; sqlquery := sqlquery||' order by avg(net_latency) desc'; IF ((length(trim(apps_id_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in; ELSE OPEN results_out FOR sqlquery; END IF; END; -- -- getIpStats - get stats for clients for the given group -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- -- results_out - results cursor -- Items returned include: -- client node -- ip address -- os userid -- subnet -- avgLatencyVal -- throughput -- cpu clock speed -- total memory -- free disk space -- % used disk space -- timestamp -- domain -- -- PROCEDURE getIpStats(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); search_ipaddress_lower VARCHAR(80); group_column VARCHAR(50); BEGIN sqlquery := 'select '|| ' nvl(client_node, ''n/a''), '|| ' net_ipaddress, '|| ' os_userid, '|| ' net_subnet, '|| ' to_char(net_latency), '|| ' to_char(net_measuredrate), '|| ' HW_CLOCKSPEED_MHZ, '|| ' HW_TOTAL_MEMORY, '|| ' HW_AVAIL_DISKSPACE, '|| ' trunc(((HW_TOTAL_DISKSPACE-HW_AVAIL_DISKSPACE)/HW_TOTAL_DISKSPACE)*100), '|| ' to_char(CC_COLL_TIMESTAMP, ''YYYY-MM-DD HH24:MI''), '|| ' client_domain '|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :1'; END IF; group_column := 'net_subnet'; IF ((length(trim(group_in))) > 0) THEN IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = '''||group_in||''''; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; IF ((length(trim(search_ipaddress_in))) > 0) THEN search_ipaddress_lower := lower(search_ipaddress_in); sqlquery := sqlquery||' and lower(net_ipaddress) like ''%'||search_ipaddress_lower||'%'''; END IF; sqlquery := sqlquery||' order by CC_COLL_TIMESTAMP desc'; IF ((length(trim(apps_id_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in; ELSE OPEN results_out FOR sqlquery; END IF; END; -- -- getHostProperties - get stats for client configuration -- -- Inputs: -- apps_id_in - application ID or "" -- client_node_in - host name -- os_userid_in -- group_in -- group_type_in -- -- Outputs: -- -- -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getHostProperties(apps_id_in IN VARCHAR2, client_node_in IN VARCHAR2, os_userid_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); group_column VARCHAR(50); iparam INTEGER; BEGIN sqlquery := 'select '|| ' * '|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; iparam := 1; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; END IF; iparam := iparam + 1; sqlquery := sqlquery||' and client_node = :'||iparam; iparam := iparam + 1; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; END IF; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, client_node_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in, client_node_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING client_node_in, group_in; ELSE OPEN results_out FOR sqlquery USING client_node_in; END IF; END IF; END; -- -- getCPUDistribution - get cpu clock speed distribution for clients by subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- -- -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getCPUDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc((hw_clockspeed_mhz+50)/100 +1),'|| ' null, ''Unmeasured'', '|| ' 0,''0'', '|| ' 1,''0-149'',2,''0-149'','|| ' 3,''150-249'',4,''250-349'',5,''350-449'','|| ' 6,''450-549'',7,''550-649'', 8,''650-749'',9,''750-849'',10,''850-949'','|| ' 11,''950-1049'', 12,''1050-1149'',13,''1150-1249'', '|| '''1250+'') "DIST_VALUE", '|| ' decode(trunc((hw_clockspeed_mhz+50)/100 +1),'|| ' null, 0, '|| ' 0,1,'|| ' 1,2,2,2,'|| ' 3,4,4,5,5,6,'|| ' 6,7,7,8, 8,9,9,10,10,11,'|| ' 11,12,12,13,13,14, '|| ' 15) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getTotalDiskDistribution - get total disk space distribution for clients -- by application, subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getTotalDiskDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc((hw_total_diskspace)/1000 +1),'|| ' null, ''Unmeasured'', '|| ' 0,''0'','|| ' 1,''<1'',2,''1-2'',3,''2-3'',4,''3-4'',5,''4-5'','|| ' 6, ''5-10'', 7, ''5-10'', 8, ''5-10'', 9, ''5-10'', 10, ''5-10'','|| ' 11, ''10-15'', 12, ''10-15'', 13, ''10-15'', 14, ''10-15'', 15, ''10-15'','|| ' 16, ''15-20'', 17, ''15-20'', 18, ''15-20'', 19, ''15-20'', 20, ''15-20'','|| ' ''20+'') "DIST_VALUE", '|| ' decode(trunc((hw_total_diskspace)/1000 +1),'|| ' null, 0, '|| ' 0,1,'|| ' 1,2,2,3,3,4,4,5,5,6,'|| ' 6,7,7,7,8,7,9,7,10,7,'|| ' 11,8,12,8,13,8,14,8,15,8,'|| ' 16,9,17,9,18,9,19,9,20,9,'|| ' 10) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getFreeDiskDistribution - get total disk space distribution for clients -- by application, subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getFreeDiskDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc((hw_avail_diskspace)/1000 +1),'|| ' null, ''Unmeasured'', '|| ' 0,''0'','|| ' 1,''<1'',2,''1-2'',3,''2-3'',4,''3-4'',5,''4-5'','|| ' 6, ''5-10'', 7, ''5-10'', 8, ''5-10'', 9, ''5-10'', 10, ''5-10'','|| ' 11, ''10-15'', 12, ''10-15'', 13, ''10-15'', 14, ''10-15'', 15, ''10-15'','|| ' 16, ''15-20'', 17, ''15-20'', 18, ''15-20'', 19, ''15-20'', 20, ''15-20'','|| ' ''20+'') "DIST_VALUE", '|| ' decode(trunc((hw_avail_diskspace)/1000 +1),'|| ' null, 0, '|| ' 0,1,'|| ' 1,2,2,3,3,4,4,5,5,6,'|| ' 6,7,7,7,8,7,9,7,10,7,'|| ' 11,8,12,8,13,8,14,8,15,8,'|| ' 16,9,17,9,18,9,19,9,20,9,'|| ' 10) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getPctFreeDiskDistribution - get free disk space % distribution for clients -- by application, subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getPctFreeDiskDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc(((HW_AVAIL_DISKSPACE)/HW_TOTAL_DISKSPACE)*10),'|| ' null, ''Unmeasured'', '|| ' 0,''0-10%'',1,''10-20%'',2,''20-30%'',3,''30-40%'',4,''40-50%'',5,''50-60%'','|| ' 6,''60-70%'',7,''70-80%'', 8,''80-90%'',9,''90-100%'',10,''100%'') "DIST_VALUE", '|| ' decode(trunc(((HW_AVAIL_DISKSPACE)/HW_TOTAL_DISKSPACE)*10),'|| ' null, 0, '|| ' 0,1,1,2,2,3,3,4,4,5,5,6,'|| ' 6,7,7,8,8,9,9,10,10,11) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''|| ' and hw_total_diskspace is not null '|| ' and hw_total_diskspace > 0 '|| ' and hw_avail_diskspace is not null'; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getPctUsedDiskDistribution - get free disk space % distribution for clients -- by application, subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ip_addres_in - ip address pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getPctUsedDiskDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc(((HW_TOTAL_DISKSPACE-HW_AVAIL_DISKSPACE)/HW_TOTAL_DISKSPACE)*10),'|| ' 0,''0-10%'',1,''10-20%'',2,''20-30%'',3,''30-40%'',4,''40-50%'',5,''50-60%'','|| ' 6,''60-70%'',7,''70-80%'', 8,''80-90%'',9,''90-100%'',10,''100%'') "DIST_VALUE", '|| ' decode(trunc(((HW_TOTAL_DISKSPACE-HW_AVAIL_DISKSPACE)/HW_TOTAL_DISKSPACE)*10),'|| ' 0,0,1,1,2,2,3,3,4,4,5,5,'|| ' 6,6,7,7, 8,8,9,9,10,10) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''|| ' and hw_total_diskspace is not null '|| ' and hw_total_diskspace > 0 '|| ' and hw_avail_diskspace is not null'; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getMemoryDistribution - get memory distribution for clients by subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- -- -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getMemoryDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc((HW_TOTAL_MEMORY)/256 +1),'|| ' null, ''Unmeasured'', '|| ' 0,''0'',1,''1-255'',2,''256-511'',3,''512-768'',4,''768-1023'',5,''1024-1279'','|| ' 6,''1280-1535'',7,''1536-1791'', 8,''1792-1817'',9,''1818-2073'',10,''2074-2329'','|| ' 11,''2001-2200'', 12,''2201-2400'',13,''2401-2600'',14,''2601-2800'',15,''2801-3000'', '|| ' 16, ''2330-2585'',17,''2586-2841'',18,''2842-3097'',19, '|| '''3098+'') "DIST_VALUE", '|| ' decode(trunc((HW_TOTAL_MEMORY)/256 +1),'|| ' null, 0, '|| ' 0,1,1,2,2,3,3,4,4,5,5,6,'|| ' 6,7,7,8, 8,9,9,10,10,11,'|| ' 11,12, 12,13,13,14,14,15,15,16, '|| ' 16, 17,17,18,18,19,19, '|| ' 20) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getNetLatencyDistribution - get network latency distribution for clients -- by application, subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- results_out - results cursor -- Items returned include: -- distribution results -- PROCEDURE getNetLatencyDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc((net_latency)/100 +1),'|| ' null, ''Unmeasured'', '|| ' 0,''Unmeasured'',1,''1-100'',2,''100-200'',3,''200-300'',4,''300-400'',5,''400-500'','|| ' 6,''500-600'',7,''600-700'', 8,''700-800'',9,''800-900'',10,''900-1000'','|| ' ''1000+'') "DIST_VALUE", '|| ' decode(trunc((net_latency)/100 +1),'|| ' null, 0, '|| ' 0,0,1,2,2,3,3,4,4,5,5,6,'|| ' 6,7,7,8, 8,9,9,10,10,11,'|| ' 12) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getNetThroughputDistribution - get network throughput distribution for clients -- by application, subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- -- Outputs: -- results_out - results cursor -- Items returned include: -- distribution results -- PROCEDURE getNetThroughputDistribution(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; sqlquery := ' select dist_value, sum(clients) from (select '|| ' decode(trunc((net_measuredrate)/100 +1),'|| ' null, ''Unmeasured'', '|| ' 0,''Unmeasured'',1,''1-99'',2,''100-199'',3,''200-299'',4,''300-399'',5,''400-499'','|| ' 6,''500-599'',7,''600-799'', 8,''700-799'',9,''800-899'',10,''900-999'','|| ' ''1000+'') "DIST_VALUE", '|| ' decode(trunc((net_measuredrate)/100 +1),'|| ' null, 0, '|| ' 0,0,1,2,2,3,3,4,4,5,5,6,'|| ' 6,7,7,8, 8,9,9,10,10,11,'|| ' 12) "DISPLAY_ORDER", '|| ' 1 "CLIENTS"'|| ' from mgmt_client_config_summary '|| ' where rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' ) group by dist_value, display_order order by display_order '; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; -- -- getUseBy - get browser use for clients by subnet -- -- Inputs: -- apps_id_in - application ID or "" for all applications -- group_in - subnet or "" for all groups -- group_type_in (subnet, domain, ipaddress) -- search_client_node_in - client node pattern (optional), "" for all nodes -- search_os_user_id_in - user id pattern (optional), "" for all users -- search_ipaddress_id_in - ip address pattern (optional), "" for all users -- use_type_in (byBrowser (default), byOS...) -- -- Outputs: -- -- -- results_out - results cursor -- Items returned include: -- all properties in table -- PROCEDURE getUseBy(apps_id_in IN VARCHAR2, group_in IN VARCHAR2, group_type_in IN VARCHAR2, search_client_node_in IN VARCHAR2, search_os_userid_in IN VARCHAR2, search_ipaddress_in IN VARCHAR2, use_type_in IN VARCHAR2, results_out OUT cursorType) IS sqlquery VARCHAR(3000); iparam INTEGER; columnSelString VARCHAR2(100); columnWhereString VARCHAR2(100); columnGroupString VARCHAR2(100); search_client_node_lower VARCHAR(80); search_os_userid_lower VARCHAR(80); group_column VARCHAR(50); BEGIN iparam := 0; IF (use_type_in = 'byOS') THEN columnSelString := 'sw_os_name'; columnWhereString := 'sw_os_name is not null'; columnGroupString := ' sw_os_name '; ELSE columnSelString := 'sw_browser_type ||'' ''||sw_browser_version'; columnWhereString := 'sw_browser_type is not null'; columnGroupString := ' sw_browser_type, sw_browser_version '; END IF; sqlquery := 'select '|| columnSelString||', '|| ' count(*) '|| ' from mgmt_client_config_summary '|| ' where '|| columnWhereString|| ' and rec_type = ''C'''; IF ((length(trim(apps_id_in))) > 0) THEN sqlquery := sqlquery||' and app_id = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(group_in))) > 0) THEN group_column := 'net_subnet'; IF ((length(trim(group_type_in))) > 0) THEN IF (group_type_in = 'domain') THEN group_column := 'client_domain'; END IF; IF (group_type_in = 'ipaddress') THEN group_column := 'net_ipaddress'; END IF; END IF; sqlquery := sqlquery||' and '||group_column||' = :'||iparam; iparam := iparam + 1; END IF; IF ((length(trim(search_client_node_in))) > 0) THEN search_client_node_lower := lower(search_client_node_in); sqlquery := sqlquery||' and lower(client_node) like ''%'||search_client_node_lower||'%'''; END IF; IF ((length(trim(search_os_userid_in))) > 0) THEN search_os_userid_lower := lower(search_os_userid_in); sqlquery := sqlquery||' and lower(os_userid) like ''%'||search_os_userid_lower||'%'''; END IF; sqlquery := sqlquery||' group by '|| columnGroupString|| ' order by '|| columnGroupString; IF ((length(trim(apps_id_in))) > 0) THEN IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING apps_id_in, group_in; ELSE OPEN results_out FOR sqlquery USING apps_id_in; END IF; ELSE IF ((length(trim(group_in))) > 0) THEN OPEN results_out FOR sqlquery USING group_in; ELSE OPEN results_out FOR sqlquery; END IF; END IF; END; END em_client_config; / show errors;