[Sybase][NetBackup] Opscenter Query to Get Sum of Valid Client Images Grouped By Master Server

The format of this query will depend slightly on how your customer wants to present their backup data. This following Sybase NetBackup OpsCenter query can be run to get the size in MB of all the valid client images grouped by Master Server.

SELECT
  ms.networkName AS 'masterServer',
  SUM(i.sizeOfImageInKBytes * i.unexpiredCopyCount) / 1024 AS 'sizeMB'
FROM
  domain_Image AS i
JOIN
  domain_MasterServer AS ms ON ms.id = i.masterServerId
WHERE
  i.isValid = 1
  AND i.deletionTime = 122192928000000000
  AND UTCBigIntToNomTime( i.expirationTime ) > GETDATE()
GROUP BY
  ms.networkName

Note: To understand the NetBackup OpsCenter schema, you should google  something like "Symantec OpsCenter Database Schema Document."

And just because this is a useful place to put this information (only useful with one customer in the world):

SELECT

  m.name AS 'Master Server',

  SUM(f.sizeMB) AS 'Total MB'

FROM nbu_backup_fact AS f

JOIN nbu_master_server_dimension AS m ON m.tk = f.masterServerTk

JOIN nbu_ops_center_server_dimension AS o ON o.tk = f.opsCenterServerTk

JOIN nbu_acquisition_dimension AS a ON a.tk = f.acquisitionTk

WHERE a.latest = 1

  AND o.name = 'OPSCENTERSERVERNAME' -- CHANGE THIS!!!

GROUP BY f.masterServerTk

Note: OpsCenter Server you can get from:

SELECT name FROM nbu_ops_center_server_dimension WHERE enabled = 1;