FabricPool Data in the NetApp OnCommand Insight 7.3.x DWH - Storage Pool Calculation


In this post - 
http://www.cosonok.com/2021/02/fabricpool-data-in-netapp-oncommand.html 
- we showed how to get rough fabricpool data out of the OCI DWH dwh_inventory.extended_data by using the objectStoreUsedSpace on INTERNAL_VOLUME. 
 
A patch a few months ago brought in a whole load of new objectStore attributes at the STORAGE_POOL level, which is actually a better way to get this data. 
 
If you run - 
 
SELECT distinct `fieldName` FROM dwh_inventory.extended_data WHERE objectType = 'STORAGE_POOL' AND fieldName LIKE 'object%'; 
 
- you see all the aggregate-level FabricPool (object) related statistics: 
 
objectStoreMetadataSpace 
objectStoreName 
objectStorePhysicalUsedSpace 
objectStoreProviderType 
objectStoreReferencedSpace 
objectStoreS3Name 
objectStoreServer 
objectStoreSisSavedSpace 
objectStoreTieringFullnessThreshold 
objectStoreUnreclaimedSpace 
objectStoreUsedSpace 
 
In order to get the FabricPool data from the OnCommand Insight DataWarehouse, you can use these MySQL queries: 
 
FabricPool Savings Per Aggregate 
 
-- TO GET FABRIC POOL PER AGGREGATE INFORMATION -- 
SELECT s.name AS 'Storage',t.aggregate, 
  MAX(CASE WHEN t.fieldname = 'objectStoreServer' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreServer', 
  MAX(CASE WHEN t.fieldname = 'objectStoreName' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreName', 
  MAX(CASE WHEN t.fieldname = 'objectStoreUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUsedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStorePhysicalUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStorePhysicalUsedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStoreReferencedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreReferencedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStoreSisSavedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreSisSavedSpace TB', 
  MAX(CASE WHEN t.fieldname = 'objectStoreUnreclaimedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUnreclaimedSpace TB' 
FROM ( 
 
  -- TO GET EXTENDED DATA INFORMATION -- 
  SELECT sp.name AS 'aggregate',sp.id,sp.storageId,ed.* 
  FROM dwh_inventory.storage_pool AS sp 
  JOIN dwh_inventory.extended_data AS ed ON sp.id = ed.objectId 
  WHERE objectType = 'STORAGE_POOL' AND fieldName LIKE 'object%' 
 
) AS t 
JOIN dwh_inventory.storage AS s ON s.id = t.storageId 
GROUP BY t.id 
 
FabricPool Savings Per ONTAP Cluster 
 
SELECT t2.Storage AS 'Cluster', 
  FLOOR(SUM(t2.`objectStoreUsedSpace TB`)) AS 'objectStoreUsedSpace TB', 
  FLOOR(SUM(t2.`objectStorePhysicalUsedSpace TB`)) AS 'objectStorePhysicalUsedSpace TB', 
  FLOOR(SUM(t2.`objectStoreReferencedSpace TB`)) AS 'objectStoreReferencedSpace TB', 
  FLOOR(SUM(t2.`objectStoreSisSavedSpace TB`)) AS 'objectStoreSisSavedSpace TB', 
  FLOOR(SUM(t2.`objectStoreUnreclaimedSpace TB`)) AS 'objectStoreUnreclaimedSpace TB' 
FROM( 
   
  -- TO GET FABRIC POOL PER AGGREGATE INFORMATION -- 
  SELECT s.name AS 'Storage',t.aggregate, 
    MAX(CASE WHEN t.fieldname = 'objectStoreServer' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreServer', 
    MAX(CASE WHEN t.fieldname = 'objectStoreName' THEN t.fieldvalue ELSE NULL END) AS 'objectStoreName', 
    MAX(CASE WHEN t.fieldname = 'objectStoreUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUsedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStorePhysicalUsedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStorePhysicalUsedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStoreReferencedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreReferencedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStoreSisSavedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreSisSavedSpace TB', 
    MAX(CASE WHEN t.fieldname = 'objectStoreUnreclaimedSpace' THEN t.fieldvalue ELSE NULL END)/1024 AS 'objectStoreUnreclaimedSpace TB' 
  FROM ( 
   
    -- TO GET EXTENDED DATA INFORMATION -- 
    SELECT sp.name AS 'aggregate',sp.id,sp.storageId,ed.* 
    FROM dwh_inventory.storage_pool AS sp 
    JOIN dwh_inventory.extended_data AS ed ON sp.id = ed.objectId 
    WHERE objectType = 'STORAGE_POOL' AND fieldName LIKE 'object%' 
   
  ) AS t 
  JOIN dwh_inventory.storage AS s ON s.id = t.storageId 
  GROUP BY t.id 
 
) AS t2 
GROUP BY t2.Storage 

Comments