[MySQL][OCI][CI][ONTAP] DWH Object Store Extended Data Queries

Some generic OCI/CI DWH ONTAP Object Store Queries.

Note: OCI might be TB and CI might do TiB - please check before producing reports from this data.

# FINDING ALL THE 'OBJECT' EXTENDED_DATA FIELDS
select distinct objectType,fieldName from dwh_inventory.extended_data where fieldName like '%object%';

# OBJECT STORE USED SPACE FOR INTERNAL_VOLUME AND ONTAP #
SELECT
  s.name
,iv.name
,e.fieldValue AS 'objectStoreUsedSpace'
FROM dwh_inventory.internal_volume AS iv
JOIN dwh_inventory.storage AS s ON s.id = iv.storageId
JOIN dwh_inventory.extended_data AS e ON e.objectId = iv.id
WHERE e.objectType = 'INTERNAL_VOLUME'
AND e.fieldName = 'objectStoreUsedSpace'
AND s.microCodeVersion LIKE '%clustered%'
ORDER BY 1,2;

# OBJECT STORE USED SPACE FOR STORAGE_POOL AND ONTAP #
SELECT
  s.name
,sp.name
,e.fieldValue AS 'objectStoreUsedSpace'
FROM dwh_inventory.storage_pool AS sp
JOIN dwh_inventory.storage AS s ON s.id = sp.storageId
JOIN dwh_inventory.extended_data AS e ON e.objectId = sp.id
WHERE e.objectType = 'STORAGE_POOL'
AND e.fieldName = 'objectStoreUsedSpace'
AND s.microCodeVersion LIKE '%clustered%'
ORDER BY 1,2;

# OBJECT STORE METRICS FOR STORAGE_POOL #
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
ORDER BY 1,2