Rem Rem $Header: netapp_filer_pkgbody.sql 17-jan-2005.03:02:27 ramalhot Exp $ Rem Rem netapp_filer_pkgbody.sql Rem Rem Copyright (c) 2003, 2005, Oracle. All rights reserved. Rem Rem NAME Rem netapp_filer_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ajayshar 03/24/05 - Modified getFilerSiteMapInfo for ONTAP7.X Rem and getFilerMetricGuid to take cat_props and meta_ver. Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem ajere 01/10/05 - Performance fixes (mgmt_current_metrics) Rem asawant 09/27/04 - Adding generic columns to default set of user Rem pref columns. Rem asawant 09/27/04 - Cutting over mgmt_user_type_metric_prefs Rem ramalhot 08/25/04 - cutover to new assoc tables Rem ajere 08/06/04 - Add the check for target type when getting Rem EMD agent in getFilerSiteMapInfo Rem rpinnama 06/12/04 - Remove SET ECHO Rem ajere 02/17/04 - Add type_meta_ver check, for backward Rem compatibility with EM 4.0 Rem ajere 12/02/03 - Return -1 from getFilerCurrentMetricValue() when Rem value is NULL Rem ajere 11/03/03 - Customize metrics in the summary page. Rem ajere 09/05/03 - Metric cleanup (bug-3118172) Rem ajere 08/22/03 - Filter NULL IP Addresses Rem ajere 08/11/03 - Change the query for agent URL Rem hsu 06/29/03 - rm exit Rem ajere 06/26/03 - Created Rem CREATE OR REPLACE PACKAGE BODY emd_mntr_filer AS PROCEDURE getFilerSiteMapInfo ( filer_target_name_in IN VARCHAR2, filer_target_type_in IN VARCHAR2, filer_type_display_name_out OUT VARCHAR2, filer_avail_status_out OUT NUMBER, filer_avail_pct_out OUT NUMBER, filer_cpu_up_since_out OUT DATE, filer_IP_address_out OUT cursorType, filer_version_out OUT VARCHAR2, filer_vendor_out OUT NUMBER, filer_model_out OUT VARCHAR2, filer_type_out OUT VARCHAR2, filer_productid_out OUT VARCHAR2, filer_cifs_enable_flag_out OUT NUMBER, filer_vol_capacity_used_out OUT NUMBER, filer_vol_capacity_tot_out OUT NUMBER, filer_qtree_capacity_used_out OUT NUMBER, filer_qtree_capacity_tot_out OUT NUMBER, filer_qtree_enable_flag_out OUT NUMBER, filer_cluster_part_name_out OUT VARCHAR2, filer_cluster_part_flag_out OUT NUMBER, filer_monitoring_agent_out OUT VARCHAR2, filer_host_name_out OUT VARCHAR2, filer_aggr_cap_total_out OUT NUMBER, filer_aggr_cap_alloc_out OUT NUMBER, filer_flex_vol_cap_total_out OUT NUMBER, filer_flex_vol_cap_alloc_out OUT NUMBER, filer_version_bucket OUT VARCHAR2 ) IS l_filer_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_actual_metric_guid mgmt_targets.target_guid%TYPE; l_desired_metric_guid mgmt_targets.target_guid%TYPE; l_type_metric_guid mgmt_targets.target_guid%TYPE; l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_guid1 mgmt_metrics.metric_guid%TYPE; l_clust_partid mgmt_current_metrics.value%TYPE; l_part_tgtid mgmt_targets.target_guid%TYPE; l_filer_cpu_up_since NUMBER; l_count NUMBER; l_target_severities MGMT_SEVERITY_ARRAY := MGMT_SEVERITY_ARRAY(); l_cpu_uptime_coll_ts mgmt_current_metrics.collection_timestamp%TYPE; l_cat_prop1 mgmt_targets.CATEGORY_PROP_1%TYPE; l_cat_prop2 mgmt_targets.CATEGORY_PROP_2%TYPE; l_cat_prop3 mgmt_targets.CATEGORY_PROP_3%TYPE; l_cat_prop4 mgmt_targets.CATEGORY_PROP_4%TYPE; l_cat_prop5 mgmt_targets.CATEGORY_PROP_5%TYPE; BEGIN BEGIN SELECT target_guid, type_display_name, (SELECT target_name FROM mgmt_targets WHERE target_type = 'oracle_emd' AND emd_url = (SELECT emd_url FROM mgmt_targets WHERE target_name = filer_target_name_in AND target_type = filer_target_type_in ) ) INTO l_target_guid, filer_type_display_name_out, filer_monitoring_agent_out FROM mgmt_targets WHERE target_name = filer_target_name_in AND target_type = filer_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; BEGIN SELECT property_value INTO filer_host_name_out FROM mgmt_target_properties WHERE target_guid = l_target_guid AND property_name = 'AgentHost'; EXCEPTION WHEN NO_DATA_FOUND THEN filer_host_name_out := filer_target_name_in; END; BEGIN SELECT type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_filer_type_meta_ver, l_cat_prop1, l_cat_prop2, l_cat_prop3, l_cat_prop4, l_cat_prop5 FROM mgmt_targets WHERE target_name = filer_target_name_in AND target_type = filer_target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_cat_prop1 := NULL; l_cat_prop2 := NULL; l_cat_prop3 := NULL; l_cat_prop4 := NULL; l_cat_prop5 := NULL; END; filer_avail_status_out := mgmt_target.get_avail_current_status(l_target_guid); filer_avail_pct_out := ROUND(mgmt_target.get_avail_pct_value(l_target_guid, 1), 2); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'CPU', 'UpTime', l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_filer_cpu_up_since := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); -- Get the CPU Up Time l_cpu_uptime_coll_ts := getFilerCollTimeStampCurr(l_target_guid,l_metric_guid); IF (l_cpu_uptime_coll_ts IS NOT NULL) THEN SELECT l_cpu_uptime_coll_ts-((l_filer_cpu_up_since/(60*60))/24) INTO filer_cpu_up_since_out FROM dual; ELSE SELECT sysdate-((l_filer_cpu_up_since/(60*60))/24) INTO filer_cpu_up_since_out FROM dual; END IF; l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Product', 'prodVersion',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_version_out := getFilerCurrentMetricStrValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Product', 'prodVendor',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_vendor_out := getFilerCurrentMetricStrValue(l_target_guid,l_metric_guid); IF (filer_vendor_out is NULL) THEN filer_vendor_out := -1; END IF; l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Product', 'prodId',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_productid_out := getFilerCurrentMetricStrValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Product', 'prodModel',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_model_out := getFilerCurrentMetricStrValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Product', 'productCategory',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_type_out := getFilerCurrentMetricStrValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'NetworkInterfaces', 'IpAddress',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid1 := getFilerMetricGuid(filer_target_type_in,'NetworkInterfaces','Type',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); OPEN filer_IP_address_out FOR SELECT met1.string_value ipaddress FROM mgmt_current_metrics met1, mgmt_current_metrics met2 WHERE met1.target_guid = l_target_guid AND met1.target_guid = met2.target_guid AND met1.metric_guid = l_metric_guid AND met1.key_value = met2.key_value AND met2.metric_guid = l_metric_guid1 AND met2.value != 24 AND met1.string_value IS NOT NULL ORDER BY met1.string_value; l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'CifsOperations', 'CifsEnabled',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_cifs_enable_flag_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); /* Qtree ....... */ /* Changed by Abhijit - Bug Fix 2876070 */ l_metric_guid1 := getFilerMetricGuid(filer_target_type_in, 'Qtrees', 'QtreeName',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); -- Get number of Qtrees in this target SELECT count(1) INTO l_count FROM mgmt_current_metrics WHERE target_guid = l_target_guid AND metric_guid = l_metric_guid1 AND string_value is NOT NULL; IF l_count = 0 THEN filer_qtree_enable_flag_out := 0; filer_qtree_capacity_used_out := 0; filer_qtree_capacity_tot_out := 0; ELSE filer_qtree_enable_flag_out := 1; /* End of change by Abhijit - Bug Fix 2876070 */ l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'QtreeCapacityUsedGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_qtree_capacity_used_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'QtreeCapacityLimitGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_qtree_capacity_tot_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); END IF; /* Volumes ....... */ l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'VolumeCapacityUsedGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_vol_capacity_used_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'VolumeCapacityTotalGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_vol_capacity_tot_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); /* ONTAP 7.X additions */ IF (l_cat_prop3 = '7.X') THEN filer_version_bucket:='7.X'; l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'AggregateCapacityTotalGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_aggr_cap_total_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'AggregateCapacityAllocatedGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_aggr_cap_alloc_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'FlexibleVolumeCapacityTotalGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_flex_vol_cap_total_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'FilerCapacity', 'FlexibleVolumeCapacityUsedGb',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); filer_flex_vol_cap_alloc_out := getFilerCurrentMetricValue(l_target_guid,l_metric_guid); ELSE filer_version_bucket:='6.X'; END IF; /* END- ONTAP 7.X additions */ /* Cluster Partner ....... */ /* Logic to check if and only if clustered filer */ IF filer_type_out = '.1.3.6.1.4.1.789.2.3' THEN l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Cluster', 'PartnerSysId',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); BEGIN SELECT value INTO l_clust_partid FROM mgmt_current_metrics WHERE target_guid = l_target_guid AND metric_guid = l_metric_guid; EXCEPTION WHEN NO_DATA_FOUND THEN filer_cluster_part_flag_out := 0; filer_cluster_part_name_out := NULL; END; BEGIN l_metric_guid1:= getFilerMetricGuid(filer_target_type_in, 'Product', 'prodId',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); SELECT max(target_guid) INTO l_part_tgtid FROM mgmt_current_metrics WHERE target_guid IN (SELECT target_guid FROM mgmt_targets WHERE target_type=g_netapp_filer_target_type) AND metric_guid = l_metric_guid1 AND to_number(string_value) = l_clust_partid; SELECT target_name INTO filer_cluster_part_name_out FROM mgmt_targets WHERE target_guid = l_part_tgtid; filer_cluster_part_flag_out := 1; EXCEPTION WHEN NO_DATA_FOUND THEN filer_cluster_part_flag_out := 0; filer_cluster_part_name_out := NULL; /* Commented because the cluster partner name should not be given if not monitored... l_metric_guid := getFilerMetricGuid(filer_target_type_in, 'Cluster', 'PartnerName',l_filer_type_meta_ver, l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); BEGIN SELECT string_value INTO filer_cluster_part_name_out FROM mgmt_current_metrics WHERE target_guid = l_target_guid AND metric_guid = l_metric_guid; EXCEPTION WHEN NO_DATA_FOUND THEN filer_cluster_part_name_out := NULL; END; */ END; ELSE filer_cluster_part_name_out := NULL; filer_cluster_part_flag_out := 0; END IF; /* Commented because l_filer_type_meta_ver has already been fetched above. */ -- Get the target metadata version -- getTargetMetadataVersion(filer_target_name_in, filer_target_type_in, l_filer_type_meta_ver); END getFilerSiteMapInfo; PROCEDURE getFilerSummaryInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, last_load_time_out OUT DATE, emd_url_out OUT VARCHAR2, thresholds_out OUT cursorType, severities_out OUT cursorType ) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN BEGIN SELECT target_guid, type_display_name, last_load_time, emd_url INTO l_target_guid, type_display_name_out, last_load_time_out, emd_url_out FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; OPEN severities_out FOR SELECT m.metric_name, m.metric_column, s.severity_code FROM mgmt_current_severity s, mgmt_metrics m WHERE s.target_guid = l_target_guid AND m.target_type = target_type_in AND m.metric_name IN ('NfsOperations','CifsOperations','CPU') AND m.metric_guid = s.metric_guid; OPEN thresholds_out FOR SELECT m.metric_name, m.metric_column, nvl(t.warning_threshold,0), t.critical_threshold FROM mgmt_metric_thresholds t, mgmt_metrics m WHERE t.target_guid = l_target_guid AND m.target_type = target_type_in AND m.metric_name IN ('NfsOperations','CifsOperations','CPU') AND t.metric_guid = m.metric_guid; END getFilerSummaryInfo; PROCEDURE getFilerQtreeInfo ( target_name_in IN mgmt_targets.target_name%TYPE, target_type_in IN mgmt_targets.target_type%TYPE, volume_name_in IN mgmt_current_metrics.string_value%TYPE, qtrees_tab_out OUT cursorType ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid1 mgmt_metrics.metric_guid%TYPE; l_metric_guid2 mgmt_metrics.metric_guid%TYPE; l_metric_guid3 mgmt_metrics.metric_guid%TYPE; l_metric_guid4 mgmt_metrics.metric_guid%TYPE; l_metric_guid5 mgmt_metrics.metric_guid%TYPE; l_metric_guid6 mgmt_metrics.metric_guid%TYPE; l_metric_guid7 mgmt_metrics.metric_guid%TYPE; l_meta_ver mgmt_targets.type_meta_ver%TYPE; l_cat_prop1 mgmt_targets.CATEGORY_PROP_1%TYPE; l_cat_prop2 mgmt_targets.CATEGORY_PROP_2%TYPE; l_cat_prop3 mgmt_targets.CATEGORY_PROP_3%TYPE; l_cat_prop4 mgmt_targets.CATEGORY_PROP_4%TYPE; l_cat_prop5 mgmt_targets.CATEGORY_PROP_5%TYPE; BEGIN BEGIN SELECT type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_meta_ver, l_cat_prop1, l_cat_prop2, l_cat_prop3, l_cat_prop4, l_cat_prop5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_cat_prop1 := NULL; l_cat_prop2 := NULL; l_cat_prop3 := NULL; l_cat_prop4 := NULL; l_cat_prop5 := NULL; END; l_target_guid := getFilerTargetGuid(target_type_in, target_name_in); l_metric_guid1 := getFilerMetricGuid(target_type_in, 'Qtrees', 'QtreeName', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid2 := getFilerMetricGuid(target_type_in, 'Qtrees', 'QtreeVolume', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid4 := getFilerMetricGuid(target_type_in, 'Qtrees', 'QtreeTotalGb', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid5 := getFilerMetricGuid(target_type_in, 'Qtrees', 'QtreeUsedGb', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid6 := getFilerMetricGuid(target_type_in, 'Qtrees', 'QtreePercentUsed',l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid7 := getFilerMetricGuid(target_type_in, 'Qtrees', 'QtreeVolume', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); IF (volume_name_in IS NULL) THEN OPEN qtrees_tab_out FOR SELECT a.string_value qtreename, c.string_value volumename, d.value total, e.value used, f.value pctused, a.key_value pathname FROM mgmt_current_metrics a, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_current_metrics d, mgmt_current_metrics e, mgmt_current_metrics f WHERE a.target_guid = l_target_guid AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.target_guid = d.target_guid AND a.target_guid = e.target_guid AND a.target_guid = f.target_guid AND a.metric_guid = l_metric_guid1 AND b.metric_guid = l_metric_guid2 AND d.metric_guid = l_metric_guid4 AND e.metric_guid = l_metric_guid5 AND f.metric_guid = l_metric_guid6 AND a.key_value = b.key_value AND a.key_value = d.key_value AND a.key_value = e.key_value AND a.key_value = f.key_value AND b.value = c.key_value; ELSE OPEN qtrees_tab_out FOR SELECT a.string_value qtreename, c.string_value volumename, d.value total, e.value used, f.value pctused, a.key_value pathname FROM mgmt_current_metrics a, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_current_metrics d, mgmt_current_metrics e, mgmt_current_metrics f, mgmt_current_metrics g WHERE a.target_guid = l_target_guid AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.target_guid = d.target_guid AND a.target_guid = e.target_guid AND a.target_guid = f.target_guid AND a.target_guid = g.target_guid AND a.metric_guid = l_metric_guid1 AND b.metric_guid = l_metric_guid2 AND d.metric_guid = l_metric_guid4 AND e.metric_guid = l_metric_guid5 AND f.metric_guid = l_metric_guid6 AND g.metric_guid = l_metric_guid7 AND a.key_value = b.key_value AND a.key_value = d.key_value AND a.key_value = e.key_value AND a.key_value = f.key_value AND a.key_value = g.key_value AND b.value = c.key_value AND c.string_value = volume_name_in; END IF; END getFilerQtreeInfo; PROCEDURE getFilerHealthInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, health_tab_out OUT cursorType ) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := getFilerTargetGuid(target_type_in, target_name_in); OPEN health_tab_out FOR SELECT column_label, metric_column, decode(c.warning_threshold,' ',c.critical_threshold, c.warning_threshold) warning_threshold, c.critical_threshold FROM mgmt_metrics a, mgmt_metric_thresholds c WHERE a.metric_name = 'Health' AND a.metric_column IN ('TemperatureExceeded','FansFailed', 'PowerSuppliesFailed','NVRAMBatteryStatus') AND a.metric_guid = c.metric_guid AND c.target_guid = l_target_guid; /* Original Health Query removed current metrics now SELECT column_label, value, decode(c.warning_threshold,' ',c.critical_threshold, c.warning_threshold) warning_threshold, c.critical_threshold FROM mgmt_metrics a, mgmt_current_metrics b, mgmt_metric_thresholds c WHERE a.metric_name = 'Health' AND a.metric_column IN ('TemperatureExceeded','FansFailed', 'PowerSuppliesFailed','NVRAMBatteryStatus') AND b.target_guid = l_target_guid AND a.metric_guid = b.metric_guid AND c.target_guid = l_target_guid AND a.metric_guid = c.metric_guid; */ END getFilerHealthInfo; PROCEDURE getFilerPartnerListInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, target_summary_out OUT cursorType ) IS l_filer_name VARCHAR2(128); l_clust_part_name VARCHAR2(128); l_metric_guid mgmt_metrics.metric_guid%TYPE; l_meta_ver mgmt_targets.type_meta_ver%TYPE; l_cat_prop1 mgmt_targets.CATEGORY_PROP_1%TYPE; l_cat_prop2 mgmt_targets.CATEGORY_PROP_2%TYPE; l_cat_prop3 mgmt_targets.CATEGORY_PROP_3%TYPE; l_cat_prop4 mgmt_targets.CATEGORY_PROP_4%TYPE; l_cat_prop5 mgmt_targets.CATEGORY_PROP_5%TYPE; BEGIN BEGIN SELECT type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_meta_ver, l_cat_prop1, l_cat_prop2, l_cat_prop3, l_cat_prop4, l_cat_prop5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_cat_prop1 := NULL; l_cat_prop2 := NULL; l_cat_prop3 := NULL; l_cat_prop4 := NULL; l_cat_prop5 := NULL; END; l_metric_guid := getFilerMetricGuid(target_type_in, 'Cluster', 'PartnerName', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); BEGIN SELECT string_value INTO l_clust_part_name FROM mgmt_current_metrics WHERE target_guid = (SELECT target_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in ) AND metric_guid = l_metric_guid; EXCEPTION WHEN NO_DATA_FOUND THEN l_clust_part_name := NULL; -- raise MGMT_GLOBAL.target_does_not_exist; END; BEGIN SELECT target_name, type_display_name INTO l_filer_name, type_display_name_out FROM mgmt_targets WHERE target_name = l_clust_part_name AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_filer_name := NULL; type_display_name_out := NULL; -- raise MGMT_GLOBAL.target_does_not_exist; END; -- now for the given filer name find all the targets related data OPEN target_summary_out FOR SELECT tgt.target_name targetname, tgt.target_type targettype, tgt.type_display_name dispname, ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, 1), 0) AS avail_pct, mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status, NVL(sev_detail.warning_count, 0) AS warning_count, NVL(sev_detail.critical_count, 0) AS critical_count FROM mgmt_targets tgt, (SELECT sev.target_guid, SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(sev.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM mgmt_current_severity sev, mgmt_targets t WHERE t.target_name = l_clust_part_name AND sev.target_guid = t.target_guid GROUP BY sev.target_guid ) sev_detail WHERE tgt.target_guid = sev_detail.target_guid (+) AND tgt.target_name = l_clust_part_name ORDER BY tgt.type_display_name, tgt.target_name; END getFilerPartnerListInfo; FUNCTION getFilerMetricGuid ( filer_target_type_in IN mgmt_metrics.target_type%TYPE, filer_metric_name_in IN mgmt_metrics.metric_name%TYPE, filer_metric_column_in IN mgmt_metrics.metric_column%TYPE, filer_type_meta_ver_in IN mgmt_targets.type_meta_ver%TYPE DEFAULT ' ', l_category_prop1_in IN mgmt_targets.CATEGORY_PROP_1%TYPE DEFAULT ' ', l_category_prop2_in IN mgmt_targets.CATEGORY_PROP_2%TYPE DEFAULT ' ', l_category_prop3_in IN mgmt_targets.CATEGORY_PROP_3%TYPE DEFAULT ' ', l_category_prop4_in IN mgmt_targets.CATEGORY_PROP_4%TYPE DEFAULT ' ', l_category_prop5_in IN mgmt_targets.CATEGORY_PROP_5%TYPE DEFAULT ' ' ) RETURN mgmt_metrics.metric_guid%TYPE IS l_metric_guid mgmt_metrics.metric_guid%TYPE; BEGIN --dbms_output.put_line('1_PROP='||l_category_prop1_in); -- dbms_output.put_line('1_PROP='||l_category_prop2_in); -- dbms_output.put_line('1_PROP='||l_category_prop3_in); -- dbms_output.put_line('1_PROP='||l_category_prop4_in); -- dbms_output.put_line('1_PROP='||l_category_prop5_in); -- dbms_output.put_line('META_VER='||filer_type_meta_ver_in); BEGIN SELECT DISTINCT(metric_guid) INTO l_metric_guid FROM mgmt_metrics WHERE target_type = filer_target_type_in AND metric_name = filer_metric_name_in AND metric_column = filer_metric_column_in AND (mgmt_metrics.type_meta_ver = filer_type_meta_ver_in OR mgmt_metrics.type_meta_ver =' ' ) AND (mgmt_metrics.category_prop_1=l_category_prop1_in OR mgmt_metrics.category_prop_1=' ' ) AND (mgmt_metrics.category_prop_2=l_category_prop2_in OR mgmt_metrics.category_prop_2=' ' ) AND (mgmt_metrics.category_prop_3=l_category_prop3_in OR mgmt_metrics.category_prop_3=' ' ) AND (mgmt_metrics.category_prop_4=l_category_prop4_in OR mgmt_metrics.category_prop_4=' ' ) AND (mgmt_metrics.category_prop_5=l_category_prop5_in OR mgmt_metrics.category_prop_5=' ' ); -- dbms_output.put_line(l_metric_guid); return l_metric_guid; EXCEPTION WHEN NO_DATA_FOUND THEN return NULL; -- raise MGMT_GLOBAL.target_does_not_exist; END; END getFilerMetricGuid; FUNCTION getFilerTargetGuid ( filer_target_type_in IN mgmt_targets.target_type%TYPE, filer_target_name_in IN mgmt_targets.target_name%TYPE ) RETURN mgmt_targets.target_guid%TYPE IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_targets WHERE target_type = filer_target_type_in AND target_name = filer_target_name_in; return l_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; END getFilerTargetGuid; FUNCTION getFilerCurrentMetricValue ( filer_target_guid_in IN mgmt_current_metrics.target_guid%TYPE, filer_metric_guid_in IN mgmt_current_metrics.metric_guid%TYPE ) RETURN mgmt_current_metrics.value%TYPE IS l_value mgmt_current_metrics.value%TYPE; BEGIN IF (filer_metric_guid_in IS NULL) THEN return -1; END IF; BEGIN SELECT value INTO l_value FROM mgmt_current_metrics WHERE target_guid = filer_target_guid_in AND metric_guid = filer_metric_guid_in; IF l_value IS NULL THEN return -1; ELSE return l_value; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN return -1; -- raise MGMT_GLOBAL.target_does_not_exist; END; END getFilerCurrentMetricValue; FUNCTION getFilerCurrentMetricStrValue ( filer_target_guid_in IN mgmt_current_metrics.target_guid%TYPE, filer_metric_guid_in IN mgmt_current_metrics.metric_guid%TYPE ) RETURN mgmt_current_metrics.string_value%TYPE IS l_value mgmt_current_metrics.string_value%TYPE; BEGIN IF (filer_metric_guid_in is NULL) then return NULL; END IF; BEGIN SELECT string_value INTO l_value FROM mgmt_current_metrics WHERE target_guid = filer_target_guid_in AND metric_guid = filer_metric_guid_in; return l_value; EXCEPTION WHEN NO_DATA_FOUND THEN return NULL; -- raise MGMT_GLOBAL.target_does_not_exist; END; END getFilerCurrentMetricStrValue; FUNCTION getFilerTotalValue ( filer_target_guid_in IN mgmt_current_metrics.target_guid%TYPE, filer_metric_guid_in IN mgmt_current_metrics.metric_guid%TYPE ) RETURN mgmt_current_metrics.value%TYPE IS l_space mgmt_current_metrics.value%TYPE; BEGIN IF (filer_metric_guid_in IS NULL) THEN return -1; END IF; BEGIN SELECT nvl(sum(value),-1) INTO l_space FROM mgmt_current_metrics WHERE target_guid = filer_target_guid_in AND metric_guid = filer_metric_guid_in; return l_space; EXCEPTION WHEN NO_DATA_FOUND THEN return -1; -- raise MGMT_GLOBAL.target_does_not_exist; END; END getFilerTotalValue; FUNCTION getFilerVolumeTotalValue ( filer_target_guid_in IN mgmt_current_metrics.target_guid%TYPE, filer_metric_guid_in IN mgmt_metrics.metric_guid%TYPE ) RETURN mgmt_current_metrics.value%TYPE IS l_space mgmt_current_metrics.value%TYPE; BEGIN IF (filer_metric_guid_in is NULL) THEN return -1; END IF; BEGIN SELECT nvl(sum(value),-1) INTO l_space FROM mgmt_current_metrics WHERE target_guid = filer_target_guid_in AND metric_guid = filer_metric_guid_in; -- changed bcoz no snapshot volume info collected here -- AND key_value not like '%snapshot%'; return l_space; EXCEPTION WHEN NO_DATA_FOUND THEN return -1; -- raise MGMT_GLOBAL.target_does_not_exist; END; END getFilerVolumeTotalValue; PROCEDURE getFilerGroupCapacity ( target_name_in IN mgmt_targets.target_name%TYPE, target_type_in IN mgmt_targets.target_type%TYPE, mtarget_type_in IN mgmt_targets.target_type%TYPE, volume_tot_out OUT NUMBER, volume_usd_out OUT NUMBER, qtrees_tot_out OUT NUMBER, qtrees_usd_out OUT NUMBER ) IS l_metric_guid1 mgmt_metrics.metric_guid%TYPE; l_metric_guid2 mgmt_metrics.metric_guid%TYPE; l_metric_guid3 mgmt_metrics.metric_guid%TYPE; l_metric_guid4 mgmt_metrics.metric_guid%TYPE; l_metric_guid5 mgmt_metrics.metric_guid%TYPE; l_count NUMBER; l_meta_ver mgmt_targets.type_meta_ver%TYPE; l_cat_prop1 mgmt_targets.CATEGORY_PROP_1%TYPE; l_cat_prop2 mgmt_targets.CATEGORY_PROP_2%TYPE; l_cat_prop3 mgmt_targets.CATEGORY_PROP_3%TYPE; l_cat_prop4 mgmt_targets.CATEGORY_PROP_4%TYPE; l_cat_prop5 mgmt_targets.CATEGORY_PROP_5%TYPE; CURSOR c1 is SELECT assoc_target.target_guid member_target_guid FROM mgmt_target_assocs assoc, mgmt_targets source_target, mgmt_targets assoc_target WHERE assoc.assoc_guid = MGMT_ASSOC.g_contains_guid AND source_target.target_type = target_type_in AND source_target.target_name = target_name_in AND assoc.source_target_guid = source_target.target_guid AND assoc_target.target_guid = assoc.assoc_target_guid AND assoc_target.target_type = mtarget_type_in; BEGIN BEGIN SELECT type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_meta_ver, l_cat_prop1, l_cat_prop2, l_cat_prop3, l_cat_prop4, l_cat_prop5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_cat_prop1 := NULL; l_cat_prop2 := NULL; l_cat_prop3 := NULL; l_cat_prop4 := NULL; l_cat_prop5 := NULL; END ; l_metric_guid1 := getFilerMetricGuid(mtarget_type_in, 'Qtrees', 'QtreeName', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid2 := getFilerMetricGuid(mtarget_type_in, 'FilerCapacity', 'QtreeCapacityUsedGb', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid3 := getFilerMetricGuid(mtarget_type_in, 'FilerCapacity', 'QtreeCapacityLimitGb', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid4 := getFilerMetricGuid(mtarget_type_in, 'FilerCapacity', 'TraditionalVolumeCapacityUsedGb', l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); l_metric_guid5 := getFilerMetricGuid(mtarget_type_in, 'FilerCapacity', 'TraditionalVolumeCapacityTotalGb',l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); volume_tot_out := 0; volume_usd_out := 0; qtrees_tot_out := 0; qtrees_usd_out := 0; FOR c1rec IN c1 LOOP /* Qtree ....... */ SELECT count(1) INTO l_count FROM mgmt_current_metrics WHERE target_guid = c1rec.member_target_guid AND metric_guid = l_metric_guid1 AND string_value is NOT NULL; IF l_count != 0 THEN qtrees_usd_out := qtrees_usd_out + getFilerCurrentMetricValue(c1rec.member_target_guid,l_metric_guid2); qtrees_tot_out := qtrees_tot_out + getFilerCurrentMetricValue(c1rec.member_target_guid,l_metric_guid3); END IF; /* Volumes ....... */ volume_usd_out := volume_usd_out + getFilerCurrentMetricValue(c1rec.member_target_guid,l_metric_guid4); volume_tot_out := volume_tot_out + getFilerCurrentMetricValue(c1rec.member_target_guid,l_metric_guid5); END LOOP; END getFilerGroupCapacity; PROCEDURE getFilerGroupTargets ( target_name_in IN mgmt_targets.target_name%TYPE, target_type_in IN mgmt_targets.target_type%TYPE, mtarget_type_in IN mgmt_targets.target_type%TYPE, group_tgts_out OUT cursorType ) IS BEGIN OPEN group_tgts_out FOR SELECT mt.target_name target_name, b.string_value filer_version, c.string_value filer_model FROM mgmt_target_assocs a, mgmt_targets ct, mgmt_targets mt, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_metrics d, mgmt_metrics e WHERE ct.target_type = target_type_in AND ct.target_name = target_name_in AND a.assoc_guid = MGMT_ASSOC.g_contains_guid AND a.source_target_guid = ct.target_guid AND mt.target_guid = a.assoc_target_guid AND mt.target_type = mtarget_type_in AND d.metric_name = 'Product' AND d.metric_column = 'prodVersion' AND d.target_type = mt.target_type AND e.metric_name = 'Product' AND e.metric_column = 'prodModel' AND e.target_type = mt.target_type AND a.assoc_target_guid = b.target_guid AND d.metric_guid = b.metric_guid AND a.assoc_target_guid = c.target_guid AND e.metric_guid = c.metric_guid; END getFilerGroupTargets; PROCEDURE getFilerVolumeInfo ( target_name_in IN mgmt_targets.target_name%TYPE, target_type_in IN mgmt_targets.target_type%TYPE, volume_info_out OUT cursorType ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_meta_ver mgmt_targets.type_meta_ver%TYPE; l_cat_prop1 mgmt_targets.CATEGORY_PROP_1%TYPE; l_cat_prop2 mgmt_targets.CATEGORY_PROP_2%TYPE; l_cat_prop3 mgmt_targets.CATEGORY_PROP_3%TYPE; l_cat_prop4 mgmt_targets.CATEGORY_PROP_4%TYPE; l_cat_prop5 mgmt_targets.CATEGORY_PROP_5%TYPE; BEGIN BEGIN SELECT type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_meta_ver, l_cat_prop1, l_cat_prop2, l_cat_prop3, l_cat_prop4, l_cat_prop5 FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN l_cat_prop1 := NULL; l_cat_prop2 := NULL; l_cat_prop3 := NULL; l_cat_prop4 := NULL; l_cat_prop5 := NULL; END; l_target_guid := getFilerTargetGuid(target_type_in, target_name_in); l_metric_guid := getFilerMetricGuid(target_type_in, 'Volumes', 'Volume',l_meta_ver,l_cat_prop1,l_cat_prop2,l_cat_prop3,l_cat_prop4,l_cat_prop5); OPEN volume_info_out FOR SELECT key_value volumeid, string_value volumename FROM mgmt_current_metrics a WHERE a.target_guid = l_target_guid AND a.metric_guid = l_metric_guid; END getFilerVolumeInfo; PROCEDURE getFilerDiskInfo ( target_name_in IN mgmt_targets.target_name%TYPE, target_type_in IN mgmt_targets.target_type%TYPE, volume_name_in IN mgmt_current_metrics.string_value%TYPE, disk_info_out OUT cursorType ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; BEGIN l_target_guid := getFilerTargetGuid(target_type_in, target_name_in); OPEN disk_info_out FOR SELECT a.key_value diskname, a.value totalgb, b.value usedgb FROM mgmt_current_metrics a, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_current_metrics d, mgmt_metrics e, mgmt_metrics f, mgmt_metrics g WHERE a.target_guid = l_target_guid AND e.metric_guid = a.metric_guid AND e.metric_name = 'RaidConfig' AND e.metric_column = 'TotalGb' AND f.metric_guid = b.metric_guid AND f.metric_name = 'RaidConfig' AND f.metric_column = 'UsedGb' AND g.metric_guid = c.metric_guid AND g.metric_name = 'RaidConfig' AND g.metric_column = 'RaidGroupVolume' AND d.string_value = volume_name_in AND c.value = d.key_value AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.target_guid = d.target_guid AND a.key_value = b.key_value AND a.key_value = c.key_value; END getFilerDiskInfo; PROCEDURE reportFilerInfo ( filer_target_name_in VARCHAR2 ) IS filer_type_display_name_out VARCHAR2(220); filer_avail_status_out NUMBER; filer_avail_pct_out NUMBER; filer_cpu_up_since DATE; filer_ip cursorType; filer_version VARCHAR2(250); filer_prodid VARCHAR2(250); filer_agent VARCHAR2(250); filer_vendor NUMBER; filer_model VARCHAR2(50); filer_type VARCHAR2(50); filer_CIFS_enabled NUMBER; filer_vol_capacity_used NUMBER; filer_vol_capacity_tot NUMBER; filer_qtree_capacity_used NUMBER; filer_qtree_capacity_tot NUMBER; filer_qtree_enable_flag NUMBER; filer_partnername VARCHAR2(50); filer_partnerflag NUMBER; filer_host_name VARCHAR2(50); filer_aggr_cap_total NUMBER; filer_aggr_cap_alloc NUMBER; filer_flex_vol_cap_total NUMBER; filer_flex_vol_cap_alloc NUMBER; filer_version_bucket VARCHAR2(8); BEGIN getFilerSiteMapInfo ( filer_target_name_in , g_netapp_filer_target_type , filer_type_display_name_out , filer_avail_status_out , filer_avail_pct_out , filer_cpu_up_since , filer_ip, filer_version , filer_vendor , filer_model , filer_type , filer_prodid, filer_CIFS_enabled, filer_vol_capacity_used, filer_vol_capacity_tot, filer_qtree_capacity_used, filer_qtree_capacity_tot, filer_qtree_enable_flag, filer_partnername, filer_partnerflag, filer_agent, filer_host_name, filer_aggr_cap_total, filer_aggr_cap_alloc, filer_flex_vol_cap_total, filer_flex_vol_cap_alloc, filer_version_bucket ); dbms_output.put_line('----------------------- '); dbms_output.put_line('Filer Information Report'); dbms_output.put_line('----------------------- '); dbms_output.put_line('1.Type : '||filer_type_display_name_out); dbms_output.put_line('2.Status : '||filer_avail_status_out); dbms_output.put_line('3.PctOut : '||filer_avail_pct_out); dbms_output.put_line('4.UpSince : '||filer_cpu_up_since); dbms_output.put_line('5.Version : '||filer_version); dbms_output.put_line('6.Vendor : '||filer_vendor); dbms_output.put_line('7.ProductId : '||filer_prodid); dbms_output.put_line('8.Model : '||filer_model); dbms_output.put_line('9.Type : '||filer_type); dbms_output.put_line('18.CIFS Enabled : '||filer_CIFS_enabled ); dbms_output.put_line('19.Trad. Vol. Used : '||filer_vol_capacity_used ); dbms_output.put_line('20.Trad. Vol. Total : '||filer_vol_capacity_tot ); dbms_output.put_line('21.Qtree Used : '||filer_qtree_capacity_used ); dbms_output.put_line('22.Qtree Total : '||filer_qtree_capacity_tot ); dbms_output.put_line('23.Qtree Enabled : '||filer_qtree_enable_flag ); dbms_output.put_line('25.Cluster Partner : '||filer_partnername ); dbms_output.put_line('26.Partner Flag : '||filer_partnerflag ); dbms_output.put_line('27.Agent : '||filer_agent ); dbms_output.put_line('28.Host Name : '||filer_host_name ); dbms_output.put_line('30.Aggr.Cap.Total : '||filer_aggr_cap_total ); dbms_output.put_line('31.Aggr.Cap.Alloc. : '||filer_flex_vol_cap_total ); dbms_output.put_line('32.Flex.Cap.Total : '||filer_flex_vol_cap_total ); dbms_output.put_line('33.Flex.Cap.Alloc. : '||filer_flex_vol_cap_alloc ); dbms_output.put_line('34.Version Bucket. : '||filer_version_bucket ); END ; PROCEDURE reportFilerSummaryInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2 ) IS type_display_name_out VARCHAR2(50); last_load_time_out DATE; emd_url_out VARCHAR2(50); thresholds_out cursorType; severities_out cursorType; BEGIN getFilerSummaryInfo(target_name_in,target_type_in, type_display_name_out, last_load_time_out, emd_url_out, thresholds_out, severities_out); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Filer Summary Report '); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('1. type_display_name : '||type_display_name_out); -- dbms_output.put_line('2. Last Load Time : '||last_load_time_out); -- dbms_output.put_line('3. EMD URL : '||emd_url_out); END reportFilerSummaryInfo; PROCEDURE reportFilerGroupCapacity ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, mtarget_type_in IN VARCHAR2 ) IS volume_tot_out NUMBER; volume_usd_out NUMBER; qtrees_tot_out NUMBER; qtrees_usd_out NUMBER; BEGIN getFilerGroupCapacity ( target_name_in, target_type_in, mtarget_type_in, volume_tot_out, volume_usd_out, qtrees_tot_out, qtrees_usd_out ); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Group Capacity Report '); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('1. Group Name : '||target_name_in); -- dbms_output.put_line('2. Member Target : '||mtarget_type_in); -- dbms_output.put_line('3. Volume Capacity Total : '||volume_tot_out); -- dbms_output.put_line('4. Volume Capacity Used : '||volume_usd_out); -- dbms_output.put_line('5. Qtrees Capacity Total : '||qtrees_tot_out); -- dbms_output.put_line('6. Qtrees Capacity Used : '||qtrees_usd_out); END reportFilerGroupCapacity; PROCEDURE reportFilerDiskInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, volume_name_in IN VARCHAR2 ) IS TYPE disksrec IS RECORD ( diskname VARCHAR2(128), totalgb NUMBER, usedgb NUMBER ); disks_out cursorType; mydrec disksrec ; BEGIN getFilerDiskInfo(target_name_in,target_type_in,volume_name_in, disks_out); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Filer Volume Report '); -- dbms_output.put_line('----------------------- '); LOOP FETCH disks_out into mydrec; EXIT WHEN disks_out%NOTFOUND; -- dbms_output.put_line('Disk : ' || mydrec.diskname || -- ' TotalGb : ' || mydrec.totalgb || -- ' UsedGb : ' || mydrec.usedgb ); END LOOP; END reportFilerDiskInfo; PROCEDURE reportFilerHealthInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2 ) IS TYPE healthrec IS RECORD ( label VARCHAR2(128), value NUMBER, warn VARCHAR2(128), critical VARCHAR2(128) ); health_out cursorType; myrec healthrec ; BEGIN getFilerHealthInfo ( target_name_in, target_type_in, health_out ); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Appliance Health Report '); -- dbms_output.put_line('----------------------- '); LOOP FETCH health_out INTO myrec; EXIT WHEN health_out%NOTFOUND; -- dbms_output.put_line('label : ' || myrec.label || -- ' Value : ' || myrec.value || -- ' Warn : ' || myrec.warn || -- ' Crit : ' || myrec.critical); END LOOP; END reportFilerHealthInfo; PROCEDURE reportFilerVolumeInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2 ) IS TYPE volumerec IS RECORD ( volumeid VARCHAR2(128), volumename VARCHAR2(128) ); volume_out cursorType; myrec volumerec ; BEGIN getFilerVolumeInfo ( target_name_in, target_type_in, volume_out ); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Appliance Volume Info '); -- dbms_output.put_line('----------------------- '); LOOP FETCH volume_out into myrec; EXIT WHEN volume_out%NOTFOUND; -- dbms_output.put_line(' Volumeid : ' || myrec.volumeid || -- ' VolumeName : ' || myrec.volumename ); END LOOP; END reportFilerVolumeInfo; PROCEDURE reportFilerQtreesInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, volume_name_in IN VARCHAR2 ) IS TYPE qtreesrec IS RECORD ( qtreename VARCHAR2(128), volumename VARCHAR2(128), total NUMBER, used NUMBER, pctused NUMBER, pathname VARCHAR2(128) ); qtrees_out cursorType; myrec qtreesrec ; BEGIN getFilerQtreeInfo ( target_name_in, target_type_in, volume_name_in, qtrees_out ); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Appliance Qtrees Report '); -- dbms_output.put_line('----------------------- '); LOOP FETCH qtrees_out into myrec; EXIT WHEN qtrees_out%NOTFOUND; -- dbms_output.put_line(' QtreeName : ' || myrec.qtreename || -- ' VolumeName : ' || myrec.volumename || -- ' Total : ' || myrec.total || -- ' Used : ' || myrec.used || -- ' Pct.Used : ' || myrec.pctused || -- ' Pathname : ' || myrec.pathname); END LOOP; END reportFilerQtreesInfo; PROCEDURE reportFilerPartnerInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2 ) IS TYPE partrec IS RECORD ( targetname VARCHAR2(128), targettype VARCHAR2(128), dispname VARCHAR2(128), avail_pct NUMBER, current_status NUMBER, warning_count NUMBER, critical_count NUMBER ); qtrees_out cursorType; myrec partrec ; display_name_out VARCHAR2(128); BEGIN getFilerPartnerListInfo ( target_name_in, target_type_in, display_name_out, qtrees_out ); -- dbms_output.put_line('-------------------------------- '); -- dbms_output.put_line('Appliance Cluster Partner Report '); -- dbms_output.put_line('-------------------------------- '); LOOP FETCH qtrees_out INTO myrec; EXIT WHEN qtrees_out%NOTFOUND; -- dbms_output.put_line(' TargetName : ' || myrec.targetname || -- ' TargetType : ' || myrec.targettype || -- ' DisplayName : ' || myrec.dispname || -- ' AvailPct : ' || myrec.avail_pct || -- ' CurrentStatus : ' || myrec.current_status || -- ' WarningCount : ' || myrec.warning_count || -- ' CriticalCount : ' || myrec.critical_count); END LOOP; END reportFilerPartnerInfo; PROCEDURE reportFilerGroupTargets ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, mtarget_type_in IN VARCHAR2 ) IS TYPE targetrec IS RECORD ( name VARCHAR2(128), version VARCHAR2(128), model VARCHAR2(128) ); target_out cursorType; myrec targetrec ; BEGIN getFilerGroupTargets ( target_name_in, target_type_in, mtarget_type_in, target_out ); -- dbms_output.put_line('----------------------- '); -- dbms_output.put_line('Filer Targets Report '); -- dbms_output.put_line('----------------------- '); LOOP FETCH target_out into myrec; EXIT WHEN target_out%NOTFOUND; -- dbms_output.put_line(' '|| myrec.name || -- ' :: ' || myrec.version || -- ' :: ' || myrec.model ); END LOOP; END reportFilerGroupTargets; PROCEDURE customizeSummaryPageMetrics ( user_name_in IN VARCHAR2, callback_type_in IN NUMBER ) IS TYPE v_def_guid_list IS TABLE OF RAW(16); -- metrics for summary UI v_def_summary_metrics CONSTANT v_def_guid_list := v_def_guid_list (HEXTORAW('610B35D5767D28937940C0EC9986498A'), -- CPU Util % HEXTORAW('67205DA381AA830CA9C485FA40F2F94D'), -- NFS Calls per Second HEXTORAW('60128F6EB89087C12517D07EFCB5D6DF'), -- CIFS Calls per Second HEXTORAW('EBD760D17C0BAEA61E2967D9F4AEEC0D')); -- Spare Disks BEGIN IF callback_type_in = MGMT_USER.USER_CREATED_CALLBACK THEN FOR l_metric_guid_i IN 1..v_def_summary_metrics.COUNT LOOP -- By default, only metrics in v_def_guid_list are part of Summary UI -- Make a best effort to insert rows for them. BEGIN INSERT INTO mgmt_user_subtab_col_prefs (user_name, subtab_name, column_id_guid, display_order) VALUES (user_name_in, g_netapp_filer_target_type, v_def_summary_metrics(l_metric_guid_i), 2 + l_metric_guid_i); END; END LOOP; -- Filer's default generic columns INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES (user_name_in, g_netapp_filer_target_type, emd_pref.mgmt_user_pref_avail_col, emd_pref.mgmt_user_pref_generic_col_tp, 1); INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES (user_name_in, g_netapp_filer_target_type, emd_pref.mgmt_user_pref_alerts_col, emd_pref.mgmt_user_pref_generic_col_tp, 2); COMMIT; END IF; END customizeSummaryPageMetrics; PROCEDURE getTargetMetadataVersion ( target_name_in IN mgmt_targets.target_name%TYPE, target_type_in IN mgmt_targets.target_type%TYPE, metadata_ver_out OUT mgmt_targets.type_meta_ver%TYPE ) IS BEGIN SELECT type_meta_ver INTO metadata_ver_out FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN metadata_ver_out := NULL; END getTargetMetadataVersion; PROCEDURE getFilerAlertsInfo ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info_out OUT cursorType ) IS l_metadata_ver mgmt_metrics.type_meta_ver%TYPE; BEGIN -- Get the target metedata version getTargetMetadataVersion(target_name_in, target_type_in, l_metadata_ver); -- Get all alerts from the repository IF (l_metadata_ver IS NOT NULL) THEN OPEN alerts_info_out FOR SELECT mm.column_label column_label, cs.key_value key_value, cs.severity_code severity_code, cs.collection_timestamp collection_timestamp, cs.message message, cm.collection_timestamp last_collection_timestamp, mm.metric_name metric_name, mm.metric_column metric_column FROM mgmt_current_severity cs, mgmt_metrics mm, mgmt_current_metrics cm, mgmt_targets tg WHERE tg.target_name = target_name_in AND tg.target_type = target_type_in AND cs.target_guid = tg.target_guid AND cs.target_guid = cm.target_guid AND cs.metric_guid = cm.metric_guid AND cm.metric_guid = mm.metric_guid AND cm.key_value = cs.key_value AND mm.type_meta_ver = l_metadata_ver ORDER BY cs.severity_code desc, cs.collection_timestamp desc; ELSE OPEN alerts_info_out FOR SELECT mm.column_label column_label, cs.key_value key_value, cs.severity_code severity_code, cs.collection_timestamp collection_timestamp, cs.message message, cm.collection_timestamp last_collection_timestamp, mm.metric_name metric_name, mm.metric_column metric_column FROM mgmt_current_severity cs, mgmt_metrics mm, mgmt_current_metrics cm, mgmt_targets tg WHERE tg.target_name = target_name_in AND tg.target_type = target_type_in AND cs.target_guid = tg.target_guid AND cs.target_guid = cm.target_guid AND cs.metric_guid = cm.metric_guid AND cm.metric_guid = mm.metric_guid AND cm.key_value = cs.key_value ORDER BY cs.severity_code desc, cs.collection_timestamp desc; END IF; END getFilerAlertsInfo; FUNCTION getFilerCollTimeStampCurr ( filer_target_guid_in IN mgmt_current_metrics.target_guid%TYPE, filer_metric_guid_in IN mgmt_current_metrics.metric_guid%TYPE ) RETURN mgmt_current_metrics.collection_timestamp%TYPE IS l_coll_timestamp mgmt_current_metrics.collection_timestamp%TYPE; BEGIN IF (filer_metric_guid_in IS NULL) THEN RETURN NULL; END IF; SELECT MAX(collection_timestamp) INTO l_coll_timestamp FROM mgmt_current_metrics cm WHERE cm.target_guid = filer_target_guid_in AND cm.metric_guid = filer_metric_guid_in; RETURN l_coll_timestamp; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END getFilerCollTimeStampCurr; END emd_mntr_filer; /