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
Post a Comment