[OCI DWH MySQL] Percent of Volumes / Internal Volumes which are DR Volumes

A couple of interesting queries if you want to find out what percent of your storage are DR volumes. Two different MySQL scripts, one for Volumes (SAN), on for internal volumes (NAS). In my case all the SAN volumes are on EMC arrays, and all the NAS volumes are AFF/FAS NetApp, hence I use GROUP_CONCAT, but you could group by family if you have different types of storage for SAN/NAS.

Volumes

SELECT
    GROUP_CONCAT(targets.family) AS 'EMC Arrays'
  , SUM(capacityTBr)/SUM(capacityTB) * 100 AS 'capacity for target %'
  , SUM(consumedCapacityTBr)/SUM(consumedCapacityTB) * 100 AS 'consumedCapacity for target %'
  , SUM(capacityTB) AS 'capacityTB'
  , SUM(capacityTB) - SUM(capacityTBr) AS 'capacityTB wo DR'
  , SUM(consumedCapacityTB) AS 'consumedCapacityTB'
  , SUM(consumedCapacityTB) - SUM(consumedCapacityTBr) AS 'consumedCapacityTB wo DR'
FROM(
  select
      s.family
    , sum(v.capacityMB)/(1024*1024) AS capacityTB
    , sum(v.consumedCapacityMB)/(1024*1024) AS consumedCapacityTB
  from dwh_inventory.volume AS v
  join dwh_inventory.storage AS s ON s.id = v.storageId
  group by s.family
) AS volumes
JOIN(
  select
      s.family
    , sum(v.capacityMB)/(1024*1024) AS capacityTBr
    , sum(v.consumedCapacityMB)/(1024*1024) AS consumedCapacityTBr
  from dwh_inventory.volume AS v
  join dwh_inventory.dr_volume_replica AS dr ON dr.targetVolumeId = v.id
  join dwh_inventory.storage AS s ON s.id = v.storageId
  group by s.family
) AS targets ON targets.family = volumes.family

Internal Volumes

SELECT
    GROUP_CONCAT(iVolumes.family) AS 'AFF/FAS Devices'
  , SUM(totalAllocatedCapacityTBr)/SUM(totalAllocatedCapacityTB) * 100 AS 'allocated for target %'
  , SUM(totalUsedCapacityTBr)/SUM(totalUsedCapacityTB) * 100 AS 'used for target %' 
  , SUM(totalAllocatedCapacityTB) AS 'totalAllocatedTB'
  , SUM(totalAllocatedCapacityTB) - SUM(totalAllocatedCapacityTBr) AS 'totalAllocatedTB wo DR'
  , SUM(totalUsedCapacityTB) AS 'totalUsedTB'
  , SUM(totalUsedCapacityTB) - SUM(totalUsedCapacityTBr) AS 'totalUsedTB wo DR'
FROM(
  SELECT
      s.family AS 'family'
    , sum(totalAllocatedCapacityMB)/(1024*1024) AS totalAllocatedCapacityTB
    , sum(totalUsedCapacityMB)/(1024*1024) AS totalUsedCapacityTB
  FROM dwh_inventory.internal_volume AS i
  JOIN dwh_inventory.storage AS s ON s.id = i.storageId
  GROUP by s.family
) AS iVolumes
JOIN(
  SELECT
      s.family AS 'family'
    , sum(totalAllocatedCapacityMB)/(1024*1024) AS totalAllocatedCapacityTBr
    , sum(totalUsedCapacityMB)/(1024*1024) AS totalUsedCapacityTBr
  FROM dwh_inventory.internal_volume AS i
  JOIN dwh_inventory.storage AS s ON s.id = i.storageId
  JOIN dwh_inventory.dr_internal_volume_replica AS dr ON dr.targetInternalVolumeId = i.id
  GROUP by s.family
) AS targets ON targets.family = iVolumes.family;