--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 117
DECLARE @surveyXML  NVARCHAR(MAX)

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 
DECLARE @diff INT = DATEDIFF(SECOND, GETUTCDATE(), GETDATE())
DECLARE @tempCostCategory XML 
DECLARE @tempCostCategory1 NVARCHAR(MAX) 
DECLARE @csReleaseId INT = (SELECT releaseId FROM APP_Client WITH (NOLOCK) WHERE id = 2)

IF OBJECT_ID('tempdb..#DisksPerMA') IS NOT NULL DROP TABLE #DisksPerMA
IF OBJECT_ID('tempdb..#LibraryMA') IS NOT NULL DROP TABLE #LibraryMA
IF OBJECT_ID('tempdb..#StoragePoolMA') IS NOT NULL DROP TABLE #StoragePoolMA
IF OBJECT_ID('tempdb..#HyperScaleMediaSide') IS NOT NULL DROP TABLE #HyperScaleMediaSide
IF OBJECT_ID('tempdb..#HyperScaleUsage') IS NOT NULL DROP TABLE #HyperScaleUsage
IF OBJECT_ID('tempdb..#HyperScaleJobs') IS NOT NULL DROP TABLE #HyperScaleJobs
IF OBJECT_ID('tempdb..#MountPathProp') IS NOT NULL DROP TABLE #MountPathProp
IF OBJECT_ID('tempdb..#MountPathSize') IS NOT NULL DROP TABLE #MountPathSize
IF OBJECT_ID('tempdb..#LibraryHardDrive') IS NOT NULL DROP TABLE #LibraryHardDrive
IF OBJECT_ID('tempdb..#CloudVendors') IS NOT NULL DROP TABLE #CloudVendors
IF OBJECT_ID('tempdb..#librariesperMA') IS NOT NULL DROP TABLE #librariesperMA
IF OBJECT_ID('tempdb..#MountPathsStatus') IS NOT NULL  DROP TABLE #MountPathsStatus
CREATE TABLE #librariesperMA (Mediaagentid INT, Libraries NVARCHAR(128) )
CREATE TABLE #DisksPerMA (MediaAgentId INT, NumOfDisks INT)
CREATE TABLE #LibraryMA (LibraryId INT, MediaAgent NVARCHAR(256))
CREATE TABLE #StoragePoolMA (StoragePoolId INT, LibraryId INT, MediaAgentId INT, MediaAgent NVARCHAR(256))
CREATE TABLE #HyperScaleMediaSide (ApplianceType INT, StoragePoolId INT, LibraryId INT, MediaSideId INT, HyperScaleVersion INT)
CREATE TABLE #HyperScaleUsage (ApplianceType INT, NumStoragePools INT, NumMediaAgents INT, 
	TotalSpaceMB BIGINT, FreeSpaceMB BIGINT, DataWrittenMB BIGINT, NumClients INT, NumVMs INT, NumMAs1To4Disks INT, NumMAs5To12Disks INT,NumMAs13To24Disks INT, HyperScaleVersion INT)
CREATE TABLE #HyperScaleJobs (ApplianceType INT, jobId INT, commCellId INT, HyperScaleVersion INT)

CREATE TABLE #MountPathProp (LibraryId INT, MediaSideId INT, MountPathId INT, MountPathName NVARCHAR(1024), BaseFolder NVARCHAR(1024), DeviceName NVARCHAR(128),
	IsEnabled INT, IsOffline INT, OfflineReason INT, OfflineTimeStamp INT,
	MaxConcurrentWriters INT, TotalSpaceMB BIGINT, FreeBytesMB BIGINT, MagneticSpaceRsrvInMB BIGINT, DeviceTypeId INT, StorageClass TINYINT)

CREATE TABLE #MountPathSize (MediaSideId INT, DataWrittenMB BIGINT)

CREATE TABLE #LibraryHardDrive (LibraryId INT, MediaSideId INT, TotalSpaceMB BIGINT, FreeBytesMB BIGINT, FreeBytesMBRange BIGINT, HardDrive NVARCHAR(1024), RowId INT, Shared INT)


CREATE TABLE #MountPathsStatus (
    MAId INT , 
    MAName nvarchar(255) ,
    MAStatus INT DEFAULT 0)



DECLARE @sqlQuery NVARCHAR(MAX) = ''
CREATE TABLE #CloudVendors(LibraryId INT, CloudVendorTypeId INT)
 
IF EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE NAME = N'DisplayDeviceTypeId' AND OBJECT_ID = OBJECT_ID(N'MMDevice'))
BEGIN
        SET @sqlQuery = 
        'INSERT INTO #CloudVendors(LibraryId, CloudVendorTypeId)
		SELECT  MP.LibraryId, MAX(D.DisplayDeviceTypeId)
		FROM    MMMountPath MP WITH (NOLOCK)
				INNER JOIN MMMountPathToStorageDevice MPD WITH (NOLOCK) ON MP.MountPathId = MPD.MountPathId
				INNER JOIN MMDevice D WITH (NOLOCK) ON MPD.DeviceId = D.DeviceId
		WHERE   MountPathTypeId = 7 AND MountPathName <> ''CVDiskFolder'' AND D.DeviceTypeId <> 59
		GROUP BY MP.LibraryId'

	EXEC sp_executesql @sqlQuery
END
ELSE
BEGIN
	INSERT	INTO #CloudVendors (LibraryId, CloudVendorTypeId)
	SELECT	MP.LibraryId, MAX(D.DeviceTypeId)
	FROM	MMMountPath MP WITH (NOLOCK)
			INNER JOIN MMMountPathToStorageDevice MPD WITH (NOLOCK) ON MP.MountPathId = MPD.MountPathId
			INNER JOIN MMDevice D WITH (NOLOCK) ON MPD.DeviceId = D.DeviceId AND D.DeviceTypeId <> 59
	WHERE	MP.MountPathTypeId = 7 AND MountPathName <> 'CVDiskFolder' 
	GROUP BY MP.LibraryId

	UPDATE	V SET CloudVendorTypeId = 
				CASE WHEN T.DeviceTypeId = 2 AND T.UserName NOT LIKE '%amazonaws.com%' THEN 40
					 WHEN T.DeviceTypeId = 3 AND T.UserName NOT LIKE '%blob.core.%' THEN 39
					 ELSE T.DeviceTypeId
				END
	FROM	#CloudVendors V
			INNER JOIN (
				SELECT	D.DeviceTypeId, DC.UserName, 
						ROW_NUMBER() OVER (PARTITION BY D.DeviceId ORDER BY DC.DeviceControllerId) AS RowId
				FROM	MMDevice D WITH (NOLOCK) 
						INNER JOIN MMDeviceController DC WITH (NOLOCK) ON DC.DeviceId = D.DeviceId AND D.DeviceTypeId IN (2, 3)
				) T ON V.CloudVendorTypeId = T.DeviceTypeId AND T.RowId = 1
END

DECLARE @RangeMB INT = 250
DECLARE @UpdateCloudMountPathSize INT = 0
DECLARE @MountPathsNotShareDiskSpace INT = 0

SELECT	@UpdateCloudMountPathSize = value FROM MMConfigs WITH(NOLOCK) WHERE name = 'MMCONFIG_INCLUDE_CLOUD_MOUNTPATHS_FOR_SIZE_UPDATES'
SELECT  @MountPathsNotShareDiskSpace = value FROM MMConfigs WITH(NOLOCK) WHERE name = 'MMCONFIG_MOUNTPATHS_DONOT_SHARE_DISK_SPACE'

IF @csReleaseId >= 15
    INSERT  INTO #MountPathProp
    SELECT  L.LibraryId,
            MS.MediaSideId,
            MP.MountPathId,
            MP.MountPathName,
            MPT.MountPathName,
            (SELECT AliasName FROM MMDevice WHERE DeviceId = MP.DeviceId),
            MP.IsEnabled,
            ((1 - MP.IsEnabled) | MP.IsOffline),
            CASE WHEN MP.IsEnabled = 0 THEN 2 ELSE MP.OfflineReason END,
            CASE WHEN MP.OfflineTimeStamp > 0 THEN MP.OfflineTimeStamp + @diff ELSE 0 END,
            MP.MaxConcurrentWriters,
            CASE WHEN MP.MountPathTypeId = 7 AND MP.MaxDataToWriteMB > 0 AND @UpdateCloudMountPathSize = 1 THEN CAST(MP.MaxDataToWriteMB AS BIGINT) 
                 WHEN MP.MountPathTypeId = 7 AND MS.TotalSpaceMB = 1048576 THEN -1 ELSE MS.TotalSpaceMB END,
            CASE WHEN MP.MountPathTypeId = 7 AND MS.TotalSpaceMB = 1048576 THEN -1 ELSE MS.FreeBytesMB END,
            MP.MagneticSpaceRsrvInMB, 0, 0
    FROM    MMS2getMountPathNameGUIView MP WITH (NOLOCK)
			INNER JOIN MMMountPath MPT WITH (NOLOCK) ON MP.MountPathId = MPT.MountPathId
            INNER JOIN MMLibrary L WITH (NOLOCK) ON MP.LibraryId = L.LibraryId
            INNER JOIN MMMediaSide MS WITH (NOLOCK) ON MP.MediaSideId = MS.MediaSideId
    WHERE   MP.MountPathId > 0 AND MP.MountPathName <> 'CVDiskFolder' AND L.LibraryId > 0 AND L.LibraryTypeId != 8
ELSE
    INSERT  INTO #MountPathProp
    SELECT  L.LibraryId,
            MS.MediaSideId,
            MP.MountPathId,
            CASE 
                WHEN MP.MountPathTypeId = 1
                THEN ISNULL((SELECT TOP 1 A.FriendlyName 
                            FROM MMCenteraInfo A WITH (NOLOCK)
                            INNER JOIN MMMountPathToStorageDevice B WITH (NOLOCK) ON A.CenteraId = B.CenteraId 
                            WHERE B.MountPathId = MP.MountPathId), 'Unavailable')
                WHEN MP.MountPathTypeId IN (4, 5, 7) AND S.MountPathId IS NOT NULL
                THEN S.Folder
                ELSE MP.MountPathName
            END,
            MP.MountPathName,
            (SELECT AliasName FROM MMDevice WHERE DeviceId = S.DeviceId),
            MP.IsEnabled,
            ((1 - MP.IsEnabled) | MP.IsOffline),
            CASE WHEN MP.IsEnabled = 0 THEN 2 ELSE MP.OfflineReason END,
            CASE WHEN MP.OfflineTimeStamp > 0 THEN MP.OfflineTimeStamp + @diff ELSE 0 END,
            MP.MaxConcurrentWriters,
            CASE WHEN MP.MountPathTypeId = 7 AND MS.TotalSpaceMB = 1048576 THEN -1 ELSE MS.TotalSpaceMB END,
            CASE WHEN MP.MountPathTypeId = 7 AND MS.TotalSpaceMB = 1048576 THEN -1 ELSE MS.FreeBytesMB END,
            MP.MagneticSpaceRsrvInMB, 0, 0
    FROM    MMMountPath MP WITH (NOLOCK)
            INNER JOIN MMLibrary L WITH (NOLOCK) ON MP.LibraryId = L.LibraryId
            INNER JOIN MMMediaSide MS WITH (NOLOCK) ON MP.MediaSideId = MS.MediaSideId
            LEFT OUTER JOIN (
                SELECT  c.MountPathId, DC.DeviceId, DC.Folder
                FROM    (SELECT a.MountPathId, a.DeviceId
                        FROM    MMMountPathToStorageDevice a WITH (NOLOCK),
                                MMDeviceController b WITH (NOLOCK)
                        WHERE   a.DeviceId = b.DeviceId
                        GROUP BY a.MountPathId, a.DeviceId
                        HAVING COUNT(b.DeviceControllerId) = 1) c
                        INNER JOIN MMDeviceController DC WITH (NOLOCK) ON DC.DeviceId = c.DeviceId
                ) S ON S.MountPathId = MP.MountPathId
    WHERE   MP.MountPathId > 0 AND MP.MountPathName <> 'CVDiskFolder' AND L.LibraryId > 0 AND L.LibraryTypeId != 8

UPDATE	MP 
SET		IsOffline = CASE WHEN T.Offline > 0 THEN MP.IsOffline | 1 ELSE MP.IsOffline END, 
		OfflineReason = CASE WHEN MP.OfflineReason NOT IN (2,4) THEN 4 ELSE MP.OfflineReason END,
		DeviceTypeId = T.DeviceTypeId,
		StorageClass = CASE WHEN T.UserName LIKE '%@%//%' 
							THEN CAST(SUBSTRING(T.UserName, CHARINDEX('@', T.UserName, 0)+1, PATINDEX(N'%[^0-9]%',RIGHT(T.UserName, LEN(T.UserName) - CHARINDEX('@', T.UserName, 0)))-1) AS TINYINT)
							ELSE 0 
						END,
		MountPathName = CASE WHEN T.DeviceTypeId > 1 /*For cloud take bucket/container name*/
							THEN T.Folder
							ELSE MP.MountPathName 
						END
FROM	#MountPathProp MP
        INNER JOIN (
            SELECT  a.MountPathId, 
                    MAX((1-b.DeviceControllerEnabled) | (1-b.DeviceAccessible) | (1-c.DeviceEnabled) | c.DeviceBroken) AS Offline,
					MAX(c.DisplayDeviceTypeId) DeviceTypeId,
					MAX(b.UserName) UserName,
					MAX(b.Folder) Folder
            FROM    MMMountPathToStorageDevice a WITH (NOLOCK)
                    INNER JOIN (
                        SELECT DeviceId, MAX(DeviceControllerEnabled) AS DeviceControllerEnabled, MAX(DeviceAccessible) AS DeviceAccessible,
								MAX(Username) UserName, MAX(Folder) Folder
                        FROM   MMDeviceController WITH (NOLOCK)
                        GROUP BY DeviceId
                    ) b  ON a.DeviceId = b.DeviceId
                    INNER JOIN MMDevice c WITH (NOLOCK) ON b.DeviceId = c.DeviceId
            WHERE   a.DeviceId = b.DeviceId
            GROUP BY a.MountPathId
            ) T ON MP.MountPathId = T.MountPathId
		INNER JOIN MMLibrary L WITH(READUNCOMMITTED) ON MP.LibraryId = L.LibraryId

INSERT  INTO #MountPathSize
SELECT	V.MediaSideId, SUM(CAST(V.PhysicalBytesMB AS BIGINT)) AS DataWrittenMB
FROM	MMVolume V WITH (READUNCOMMITTED)
		INNER JOIN MMMountPath MP WITH(READUNCOMMITTED) ON V.MediaSideId = MP.MediaSideId
		LEFT OUTER JOIN MMMetallicStorage MS WITH(READUNCOMMITTED) ON MP.MountPathId = MS.MountPathId
WHERE	(Attributes & 512) = 0 AND SiloStatus <> 3
		AND MS.MountPathId IS NULL
GROUP BY V.MediaSideId

UNION ALL

SELECT	MP.MediaSideId, MS.UsedSpaceInBytes / (1024 * 1024) AS DataWrittenMB
FROM	MMMountPath MP WITH(READUNCOMMITTED)
		INNER JOIN MMMetallicStorage MS WITH(READUNCOMMITTED) ON MP.MountPathId = MS.MountPathId

------------------------------------libraries per MediaAgent----------------------------------
insert into #librariesperMA
select distinct cl.id ,  ML.AliasName from MMHost MH inner join app_client cl on cl.id= MH.ClientId
left outer join MMDrivePool DPool on DPool.ClientId = cl.id
left outer join MMMasterPool Mpool on Mpool.MasterPoolId = DPool.MasterPoolId
left outer join MMLibrary ML on ML.LibraryId = Mpool.LibraryId

-------------------------------------Ransomware Protection-------------------------------------
INSERT INTO #MountPathsStatus (MAId,MAName)
    SELECT AC.id,AC.name from APP_Client AC with (nolock)
        INNER JOIN MMHOST with (nolock) ON AC.id = MMHost.ClientId 
        INNER JOIN simOperatingSystem SOS with (nolock) on AC.simOperatingSystemId = SOS.id
        LEFT JOIN simInstalledPackages PKG with (nolock) on AC.id = PKG.clientid AND PKG.simPackageID IN(51,1301)
        WHERE AC.id>1 
        AND MMHOST.mmhostsoftstate= 1 AND MMHOST.mmhostenabled = 1 --Consider enabled and online media agents only
        AND SOS.Type = 'Windows' --Consider Windows media agents only
        AND AC.releaseid >= 16 AND ISNULL(PKG.HighestSP,0) > 8 --Consider V11 SP9 and older only
        AND EXISTS
       (
              SELECT  1
              FROM    MMDrivepool md WITH(READUNCOMMITTED)
                           INNER JOIN MMMasterPool mp WITH(READUNCOMMITTED) ON md.MasterPoolId = mp.MasterPoolId
                           INNER JOIN MMMountPath mpath WITH(READUNCOMMITTED) ON mpath.MasterPoolId = mp.MasterPoolId
              WHERE   md.ClientId = AC.id AND mpath.MountPathTypeId <> 7 /*Cloud*/
       )

UPDATE #MountPathsStatus SET MAStatus = 1
    WHERE MAId in (SELECT componentNameId from app_clientprop with (nolock) where attrname in ('dlpEnableAutomaticDecryption', 'enableDLP') 
                        GROUP BY componentNameId HAVING SUM(CONVERT(int,attrVal)) = 2)
--------------------------------------------------------------------------------------------------

INSERT  INTO #LibraryHardDrive
SELECT	T.LibraryId, T.MediaSideId, T.TotalSpaceMB, T.FreeBytesMB, T.FreeBytesMBRange, T.HardDrive,
		ROW_NUMBER() OVER (PARTITION BY T.TotalSpaceMB, T.FreeBytesMBRange, T.HardDrive ORDER BY T.MediaSideId) AS RowId, 0
FROM            
        (SELECT  MP.LibraryId, MP.MediaSideId, MS.TotalSpaceMB, MS.FreeBytesMB, @RangeMB*(MS.FreeBytesMB/@RangeMB) AS FreeBytesMBRange, 
                CASE WHEN Folder LIKE '\\%\%' THEN SUBSTRING(Folder, 1, CHARINDEX('\', Folder, 3)-1) 
                     WHEN Folder LIKE '/%/%'  THEN SUBSTRING(Folder, 1, CHARINDEX('/', Folder, 2)-1) 
                     WHEN Folder LIKE '%:\%'  THEN SUBSTRING(Folder, 1, CHARINDEX(':\', Folder, 0)) 
                     ELSE Folder 
                END AS HardDrive
        FROM    MMMediaSide MS WITH (NOLOCK)
                INNER JOIN MMMountPath MP WITH (NOLOCK) ON MS.MediaSideId = MP.MediaSideId
                INNER JOIN (
					SELECT MPSD.MountPathId, MAX(DC.Folder) AS Folder
                    FROM   MMMountPathToStorageDevice MPSD WITH (NOLOCK)
                            INNER JOIN MMDeviceController DC WITH (NOLOCK) ON MPSD.DeviceId = DC.DeviceId AND DC.Folder <> ''
                    GROUP BY MPSD.MountPathId) F ON MP.MountPathId = F.MountPathId
 		WHERE   MP.MountPathTypeId <> 7 AND MS.TotalSpaceMB > 0
 		) T

INSERT  INTO #LibraryHardDrive
SELECT	T.LibraryId, T.MediaSideId, T.TotalSpaceMB, T.FreeBytesMB, T.FreeBytesMBRange, T.HardDrive,
		ROW_NUMBER() OVER (PARTITION BY T.TotalSpaceMB, T.FreeBytesMBRange, T.HardDrive ORDER BY T.MediaSideId) AS RowId, 0
FROM            
        (SELECT MP.LibraryId, MP.MediaSideId, MS.TotalSpaceMB, MS.FreeBytesMB, @RangeMB*(MS.FreeBytesMB/@RangeMB) AS FreeBytesMBRange, 
                F.IP AS HardDrive
        FROM    MMMediaSide MS WITH (NOLOCK)
                INNER JOIN MMMountPath MP WITH (NOLOCK) ON MS.MediaSideId = MP.MediaSideId AND MP.IsOffline = 0
                INNER JOIN (
					SELECT MPSD.MountPathId, MAX(SUBSTRING(DC.UserName, 0, CHARINDEX('//', DC.UserName))) AS IP
                    FROM   MMMountPathToStorageDevice MPSD WITH (NOLOCK)
                            INNER JOIN MMDeviceController DC WITH (NOLOCK) ON MPSD.DeviceId = DC.DeviceId AND DC.Folder <> ''
							INNER JOIN MMDevice D WITH (NOLOCK) ON DC.DeviceId = D.DeviceId AND D.DeviceTypeId = 59
                    GROUP BY MPSD.MountPathId) F ON MP.MountPathId = F.MountPathId
 		WHERE   MP.MountPathTypeId = 7 AND MP.IsOffline = 0 AND MS.TotalSpaceMB > 0
 		) T

IF @MountPathsNotShareDiskSpace = 0
	UPDATE  T SET Shared = 1
	FROM    #LibraryHardDrive T
	        INNER JOIN (
	            SELECT  TotalSpaceMB, FreeBytesMBRange, HardDrive
	            FROM    #LibraryHardDrive
	            WHERE   RowId = 2
	        ) S ON T.TotalSpaceMB = S.TotalSpaceMB AND T.FreeBytesMBRange = S.FreeBytesMBRange AND T.HardDrive = S.HardDrive

INSERT  INTO #LibraryHardDrive
SELECT  MP.LibraryId, MP.MediaSideId, MS.TotalSpaceMB, MS.FreeBytesMB, MS.FreeBytesMB, MAX(CNTR.FriendlyName), 1, 0
FROM    MMMediaSide MS WITH (NOLOCK)
        INNER JOIN MMMountPath MP WITH (NOLOCK) ON MS.MediaSideId = MP.MediaSideId
        INNER JOIN MMMountPathToStorageDevice MPSD WITH (NOLOCK) ON MP.MountPathId = MPSD.MountPathId AND MP.MountPathName <> 'CVDiskFolder' 
        INNER JOIN MMCenteraInfo CNTR WITH (NOLOCK) ON MPSD.CenteraId = CNTR.CenteraId AND CNTR.CenteraId > 0
GROUP BY MP.LibraryId, MP.MediaSideId, MS.TotalSpaceMB, MS.FreeBytesMB, MS.FreeBytesMB


IF OBJECT_ID('tempdb..#StoragePool') IS NOT NULL DROP TABLE #StoragePool
CREATE TABLE #StoragePool (
	StoragePoolId     integer,
	StoragePoolName   nvarchar(255),
	StoragePoolType   integer,
	SIDBStoreId       integer,
	ClientGroupName   nvarchar(512),
	Libraries         varchar(1024),
	LibraryType       integer,
	NumMediaAgents    integer,
	TotalControllers  integer,
	ActiveControllers integer,
	LibOfflineReason  integer,
	MPOfflineReason   integer,
	MAOfflineReason   integer,
	MountPathType     integer,
	StatusCode        integer,
	DeviceTypeId      integer,
	LibrarySubType	  integer,
	StorageOfferingType 	integer,
	RegionId		integer,
	IsWORMStorage		integer
)

IF @csReleaseId >= 16 AND OBJECT_ID('MMGetStoragePools', 'P') IS NOT NULL
BEGIN
	DECLARE @procRevision NVARCHAR(20) = ''
	SELECT	@procRevision = revision FROM GxDBVersions WITH(NOLOCK) WHERE name = 'MMGetStoragePools'

	DECLARE	@n INT = 1, @p INT = 1, @q INT = 1
	DECLARE @r VARCHAR(20) = '', @s VARCHAR(20) = ''

	IF @procRevision LIKE 'v%'
	BEGIN
		SET @procRevision = @procRevision+'.'
		SET @q = CHARINDEX('.', @procRevision, @p+1)
		WHILE @q > 0
		BEGIN
			SET @s = SUBSTRING(@procRevision, @p+1, @q-@p-1)
			IF  @n <= 4
				SET @r += (CASE @q-@p-1 WHEN 1 THEN '000' WHEN 2 THEN '00' WHEN 3 THEN '0' ELSE '' END + @s)
			ELSE
				SET @r += (CASE @q-@p-1 WHEN 1 THEN '0' ELSE '' END + @s)
			
			SET @n += 1
			SET @p = @q
			SET @q = CHARINDEX('.', @procRevision, @p+1)
		END
		SET @procRevision = @r
	END	

	IF	ISNUMERIC(@procRevision) = 0
		SET @procRevision = '9223372036854775807'
		
	DECLARE @procRevNum BIGINT = CAST(@procRevision AS BIGINT)

	IF  @procRevNum < 00010001000200260000
	    ALTER TABLE #StoragePool DROP COLUMN IsWORMStorage;
	
	IF  @procRevNum < 00010001000200250000
	    ALTER TABLE #StoragePool DROP COLUMN StorageOfferingType, RegionId;
		
	IF  @procRevNum < 00010001000200160000
	    ALTER TABLE #StoragePool DROP COLUMN LibrarySubType
		
	IF  @procRevNum < 00010001000200140000
	    ALTER TABLE #StoragePool DROP COLUMN DeviceTypeId


	IF	@procRevNum < 00010001000200080601
		ALTER TABLE #StoragePool DROP COLUMN StatusCode
	
	IF	@procRevNum < 00010001000200070000
		ALTER TABLE #StoragePool DROP COLUMN MountPathType

	INSERT INTO #StoragePool
	EXEC MMGetStoragePools
	
	ALTER TABLE #StoragePool
	ADD Encryption nvarchar(512)
	
	
	IF OBJECT_ID('tempdb..#lt_ArchGroup') IS NOT NULL DROP TABLE #lt_ArchGroup
		CREATE TABLE #lt_ArchGroup (id int)
    
    INSERT INTO #lt_ArchGroup
        SELECT  DISTINCT AG.id FROM ArchGroup AG WITH(READUNCOMMITTED)
            WHERE (AG.flags & (256 | 16384 | 8388608)) > 0

	UPDATE #StoragePool
	set Encryption = 'Yes'	
	
	UPDATE #StoragePool
    set Encryption = 'No'
    where StoragePoolId in 
    (SELECT AG.Id
    FROM    #lt_ArchGroup TAG, ArchGroup AG WITH(READUNCOMMITTED), ArchGroupCopy AGC WITH(READUNCOMMITTED)
    WHERE   TAG.id = AG.id AND AG.id = AGC.archGroupId AND AG.defaultCopy = AGC.id
    AND  AGC.extendedFlags & 262144 /*CVA_ENCRYPT_ON_DEPENDENT_PRIMARY*/ = 0 
    AND AGC.flags & 8388608 /*CVA_AUXCOPY_REENCRYPT_DATA_FLAG */ = 0)
	

	CREATE TABLE #StoragePoolLibraryId (StoragePoolId INT, LibraryId INT)

	INSERT	INTO #StoragePoolLibraryId
	SELECT	StoragePoolId, L._ID
	FROM	#StoragePool CROSS APPLY (SELECT _ID FROM dbo.SplitIDs(Libraries)) L

	INSERT	INTO #StoragePoolMA
	SELECT	SPL.StoragePoolId, MPL.LibraryId, CL.id, CL.name
	FROM	#StoragePoolLibraryId SPL
			INNER JOIN MMMasterPool MPL WITH (NOLOCK) ON MPL.LibraryId = SPL.libraryId
			INNER JOIN MMDrivePool DPL WITH (NOLOCK) ON MPL.MasterPoolId = DPL.MasterPoolId
			INNER JOIN APP_Client CL WITH (NOLOCK) ON DPL.ClientId = CL.id
	ORDER BY SPL.StoragePoolId, CL.name

	DROP TABLE #StoragePoolLibraryId

	IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[MMSDSStoragePool]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		INSERT	INTO #HyperScaleMediaSide
		SELECT	DISTINCT AT.ApplianceType, MA.StoragePoolId, MA.LibraryId, MPT.MediaSideId,
				CASE 
					WHEN SDS.Flags & 2048 = 2048 THEN 1 -- MM_STORAGEPOOLINFO_GLUSTER_SP	2048
					WHEN SDS.Flags & 4096 = 4096 THEN 2 -- MM_STORAGEPOOLINFO_HEDVIG_SP		4096
				END
		FROM	MMMountPath MPT WITH (NOLOCK)
				INNER JOIN #StoragePoolMA MA ON MPT.LibraryId = MA.LibraryId
				INNER JOIN (
					SELECT	EntityId, intVal AS ApplianceType 
					FROM	MMEntityProp WITH (NOLOCK)
					WHERE	propertyName = 'MediaAgentApplianceType' AND EntityType = 10
					) AT ON MA.MediaAgentId = AT.EntityId
				INNER JOIN MMSDSStoragePool SDS ON SDS.GDSPId = MA.StoragePoolId AND MPT.LibraryId = SDS.LibraryId
				INNER JOIN MMMountPathToStorageDevice MSD WITH (NOLOCK) ON MSD.MountPathId = MPT.MountPathId
				INNER JOIN MMDiskHWInfo DHW WITH (NOLOCK) ON DHW.deviceId = MSD.DeviceId AND MA.MediaAgentId = DHW.hostId
	END
	ELSE
	BEGIN
		INSERT  INTO #HyperScaleMediaSide
		SELECT  DISTINCT AT.ApplianceType, MA.StoragePoolId, MA.LibraryId, MPT.MediaSideId, 1
		FROM    MMMountPath MPT WITH (NOLOCK)
				INNER JOIN #StoragePoolMA MA ON MPT.LibraryId = MA.LibraryId
				INNER JOIN (
					SELECT  EntityId, intVal AS ApplianceType 
					FROM    MMEntityProp WITH (NOLOCK)
					WHERE   propertyName = 'MediaAgentApplianceType' AND EntityType = 10
					) AT ON MA.MediaAgentId = AT.EntityId
	END

	INSERT	INTO #HyperScaleUsage
	SELECT	HS.ApplianceType, 0, 0, SUM(MS.TotalSpaceMB), SUM(MS.FreeBytesMB), SUM(ISNULL(W.DataWrittenMB, 0)), 0, 0, 0, 0, 0, HS.HyperScaleVersion
	FROM	MMMediaSide MS WITH (NOLOCK)
			INNER JOIN #HyperScaleMediaSide HS ON MS.MediaSideId = HS.MediaSideId
			LEFT OUTER JOIN (
				SELECT	MediaSideId, SUM(CAST(PhysicalBytesMB AS BIGINT)) AS DataWrittenMB
				FROM	MMVolume WITH (NOLOCK)
				WHERE	SiloStatus <> 3
				GROUP BY MediaSideId
				) W ON HS.MediaSideId = W.MediaSideId
	GROUP BY HS.ApplianceType, HS.HyperScaleVersion

	IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[MMSDSStoragePool]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		UPDATE	T SET NumStoragePools = S.NumStoragePools, NumMediaAgents = S.NumMediaAgents
		FROM	#HyperScaleUsage T
				INNER JOIN (
					SELECT	HS.ApplianceType, COUNT(DISTINCT HS.StoragePoolId) AS NumStoragePools, COUNT(DISTINCT MA.MediaAgentId) AS NumMediaAgents, HS.HyperScaleVersion
					FROM	#HyperScaleMediaSide HS
							INNER JOIN #StoragePoolMA MA ON HS.StoragePoolId = MA.StoragePoolId
							INNER JOIN MMSDSStoragePool SDS WITH (NOLOCK) ON SDS.GDSPId = MA.StoragePoolId
							INNER JOIN MMMountPath MP WITH (NOLOCK) ON MP.LibraryId = SDS.LibraryId
							INNER JOIN MMMountPathToStorageDevice MSD WITH (NOLOCK) ON MSD.MountPathId = MP.MountPathId
							INNER JOIN MMDiskHWInfo DHW WITH (NOLOCK) ON DHW.deviceId = MSD.DeviceId AND MA.MediaAgentId = DHW.hostId
					GROUP BY HS.ApplianceType, HS.HyperScaleVersion
				) S ON T.ApplianceType = S.ApplianceType AND T.HyperScaleVersion = S.HyperScaleVersion
	END
	ELSE
	BEGIN
		UPDATE  T SET NumStoragePools = S.NumStoragePools, NumMediaAgents = S.NumMediaAgents
		FROM    #HyperScaleUsage T
				INNER JOIN (
					SELECT  HS.ApplianceType, COUNT(DISTINCT HS.StoragePoolId) AS NumStoragePools, COUNT(DISTINCT MA.MediaAgentId) AS NumMediaAgents
					FROM    #HyperScaleMediaSide HS
							INNER JOIN #StoragePoolMA MA ON HS.StoragePoolId = MA.StoragePoolId
					GROUP BY HS.ApplianceType
				) S ON T.ApplianceType = S.ApplianceType
	END

	INSERT	INTO #HyperScaleJobs
	SELECT	HS.ApplianceType, ACM.jobId, ACM.commCellId, HS.HyperScaleVersion
	FROM	archChunkMapping ACM WITH (NOLOCK)
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
			INNER JOIN MMVolume V WITH (NOLOCK) ON AC.volumeId = V.VolumeId
			INNER JOIN #HyperScaleMediaSide HS ON V.MediaSideId = HS.MediaSideId
	GROUP BY HS.ApplianceType, ACM.jobId, ACM.commCellId, HS.HyperScaleVersion

	CREATE CLUSTERED INDEX HyperScaleJobs_idx ON #HyperScaleJobs (jobId, commCellId)
	
	UPDATE	T SET NumClients = S.NumClients
	FROM	#HyperScaleUsage T
			INNER JOIN (
				SELECT	J.ApplianceType, COUNT(DISTINCT A.clientId) AS NumClients, J.HyperScaleVersion
				FROM	APP_Application A WITH (NOLOCK)
						INNER JOIN JMBkpStats B WITH (NOLOCK) ON A.id = B.appId
						INNER JOIN #HyperScaleJobs J ON B.jobId = J.jobId AND B.commCellId = J.commCellId
				WHERE	A.subclientStatus&(2|4) = 0 AND A.appTypeId <> 106
				GROUP BY J.ApplianceType, J.HyperScaleVersion
			) S ON T.ApplianceType = S.ApplianceType AND T.HyperScaleVersion = S.HyperScaleVersion

	UPDATE	T SET NumVMs = S.NumVMs
	FROM	#HyperScaleUsage T
			INNER JOIN (
				SELECT	J.ApplianceType, COUNT(DISTINCT VM.VMclientId) AS NumVMs, J.HyperScaleVersion
				FROM	APP_VMProp VM WITH (NOLOCK)
						INNER JOIN #HyperScaleJobs J ON VM.jobId = J.jobId AND VM.commCellId = J.commCellId
				WHERE	VM.attrName = 'vmStatus'
				GROUP BY J.ApplianceType, J.HyperScaleVersion
			) S ON T.ApplianceType = S.ApplianceType AND T.HyperScaleVersion = S.HyperScaleVersion

	UPDATE	T SET NumMAs1To4Disks = S.NumMAs1To4Disks,
			NumMAs5To12Disks = S.NumMAs5To12Disks,
			NumMAs13To24Disks = S.NumMAs13To24Disks
	FROM	#HyperScaleUsage T
			INNER JOIN (
				SELECT SUM(CASE WHEN [diskCount] <= 4 THEN 1 ELSE 0 END) AS [NumMAs1To4Disks],
						SUM(CASE WHEN [diskCount] > 4 AND [diskCount] <= 12 THEN 1 ELSE 0 END) AS [NumMAs5To12Disks],
						SUM(CASE WHEN [diskCount] > 12 AND [diskCount] <= 24 THEN 1 ELSE 0 END) AS [NumMAs13To24Disks], 
					[ApplianceType], [HyperScaleVersion] 
				FROM (SELECT HW.hostId AS [hostId], 
							COUNT(HW.diskId) AS [diskCount],
							HM.ApplianceType AS [ApplianceType],
							HM.HyperScaleVersion AS [HyperScaleVersion]
							FROM MMDiskHWInfo HW
							INNER JOIN #StoragePoolMA MA ON MA.MediaAgentId = HW.hostId
							INNER JOIN #HyperScaleMediaSide HM ON HM.LibraryId = MA.LibraryId
							INNER JOIN MMMountPath MP WITH (NOLOCK) ON MP.LibraryId = MA.LibraryId
							INNER JOIN MMMountPathToStorageDevice MSD WITH (NOLOCK) ON MSD.MountPathId = MP.MountPathId AND HW.deviceId = MSD.DeviceId
						WHERE deviceOSPath NOT LIKE '/ws/ddb' AND flags&1 = 1
						GROUP BY hostId, ApplianceType, HyperScaleVersion) AS TMP
				GROUP BY TMP.[ApplianceType], TMP.HyperScaleVersion
			) S ON T.ApplianceType = S.ApplianceType AND T.HyperScaleVersion = S.HyperScaleVersion

END
ELSE
BEGIN
	ALTER TABLE #StoragePool
	ADD Encryption nvarchar(512)
END

INSERT	INTO #LibraryMA
SELECT	MPL.LibraryId, CL.name
FROM	MMMasterPool MPL WITH (NOLOCK)
		INNER JOIN MMDrivePool DPL WITH (NOLOCK) ON MPL.MasterPoolId = DPL.MasterPoolId
		INNER JOIN APP_Client CL WITH (NOLOCK) ON DPL.ClientId = CL.id
ORDER BY MPL.LibraryId, CL.name

IF OBJECT_ID('MMDiskHWInfo', 'U') IS NOT NULL
BEGIN
    IF COL_LENGTH('MMDiskHWInfo', 'mountPathUsageType') IS NULL
	INSERT  INTO #DisksPerMA
	SELECT  hostId, COUNT(diskId)
	FROM    MMDiskHWInfo WITH (NOLOCK)
        WHERE   deviceOSPath NOT LIKE '/ws/ddb' AND flags&1 = 1
	GROUP BY hostId
    ELSE
         EXEC sp_executesql N'INSERT INTO #DisksPerMA SELECT hostId, COUNT(diskId) FROM MMDiskHWInfo WITH (NOLOCK) WHERE mountPathUsageType = 2 AND flags&1 = 1 GROUP BY hostId'
END

IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[BLCostCategory]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN 
    SET @tempCostCategory = (
    SELECT L.LibraryId AS '@LibraryId',
            D.DriveID AS '@DriveId',
            D.AliasName AS '@DriveName',
            1 - (DriveSoftState & DriveEnabled & (1 - CleaningRequired) & (1 - DriveBroken)) AS '@Offline',
            D.OfflineReason AS '@OfflineReason',
            CASE WHEN D.OfflineTimeStamp > 0 THEN D.OfflineTimeStamp + @diff ELSE 0 END AS '@OfflineTimeStamp',
            DT.RatedThroughputInMBPerSec AS '@RatedThroughputMbps',
            D.DriveVendor AS '@Manufacturer', 
            D.DriveModel AS '@Model', 
            DT.DriveTypeName AS '@Type',
            D.SerialNumber AS '@SerialNumber',
            D.FirmwareRevision AS '@FirmwareRevision', 
             CASE WHEN DT.CostCategoryId > 0 THEN C.DisplayName ELSE 'Not Defined' END AS '@CostCategory'
    FROM    MMDrive D WITH (NOLOCK)
            INNER JOIN MMDriveType DT WITH (NOLOCK) ON D.DriveTypeId = DT.DriveTypeId
            INNER JOIN (
                SELECT	DISTINCT DC.DriveID, MP.LibraryId
                FROM	MMDriveController DC WITH (NOLOCK)
                	INNER JOIN MMDrivePool DP WITH (NOLOCK) ON DC.DrivePoolID = DP.DrivePoolID
                	INNER JOIN MMMasterPool MP WITH (NOLOCK) ON DP.MasterPoolId = MP.MasterPoolID
                ) DL ON D.DriveID = DL.DriveID
            INNER JOIN MMLibrary L WITH (NOLOCK) ON L.LibraryId = DL.LibraryId
            INNER JOIN BLCostCategory C WITH (NOLOCK) ON DT.CostCategoryId = C.CostCategoryID
    WHERE   D.DriveTypeId <> 10001 AND L.LibraryId > 0 AND L.LibraryTypeId != 8
    ORDER BY L.LibraryId, D.DriveID
    FOR XML PATH('Drive'), TYPE)    

    SET @tempCostCategory1 = (SELECT DisplayName FROM BLCostCategory
            WHERE CostCategoryID IN (SELECT CostCategoryID FROM MMMediaType WITH (NOLOCK) WHERE MediaTypeId = 10001))
END
ELSE
BEGIN
    SET @tempCostCategory = (
    SELECT  L.LibraryId AS '@LibraryId',
            D.DriveID AS '@DriveId',
            D.AliasName AS '@DriveName',
            1 - (DriveSoftState & DriveEnabled & (1 - CleaningRequired) & (1 - DriveBroken)) AS '@Offline',
            D.OfflineReason AS '@OfflineReason',
            CASE WHEN D.OfflineTimeStamp > 0 THEN D.OfflineTimeStamp + @diff ELSE 0 END AS '@OfflineTimeStamp',
            DT.RatedThroughputInMBPerSec AS '@RatedThroughputMbps',
            D.DriveVendor AS '@Manufacturer', 
            D.DriveModel AS '@Model', 
            DT.DriveTypeName AS '@Type',
            D.SerialNumber AS '@SerialNumber',
            D.FirmwareRevision AS '@FirmwareRevision', 
            'Not Defined' AS '@CostCategory'
    FROM    MMDrive D WITH (NOLOCK)
            INNER JOIN MMDriveType DT WITH (NOLOCK) ON D.DriveTypeId = DT.DriveTypeId
            INNER JOIN (
                SELECT	DISTINCT DC.DriveID, MP.LibraryId
                FROM	MMDriveController DC WITH (NOLOCK)
                	INNER JOIN MMDrivePool DP WITH (NOLOCK) ON DC.DrivePoolID = DP.DrivePoolID
                	INNER JOIN MMMasterPool MP WITH (NOLOCK) ON DP.MasterPoolId = MP.MasterPoolID
                ) DL ON D.DriveID = DL.DriveID
            INNER JOIN MMLibrary L WITH (NOLOCK) ON L.LibraryId = DL.LibraryId
    WHERE   D.DriveTypeId <> 10001 AND L.LibraryId > 0 AND L.LibraryTypeId != 8
    ORDER BY L.LibraryId, D.DriveID
    FOR XML PATH('Drive'), TYPE)

    SET @tempCostCategory1 = 'Not Defined'
END

CREATE TABLE #ClientVersion(ClientId INT, Version NVARCHAR(MAX))
DECLARE @sqlstr NVARCHAR(MAX)

IF object_id('Commserv.dbo.PatchSPVersion') IS NOT NULL
BEGIN
	IF EXISTS(SELECT 1 FROM SYS.COLUMNS WHERE NAME = N'UPNumber' AND OBJECT_ID = OBJECT_ID(N'simInstalledPackages'))
	BEGIN
		SET @sqlstr = 
		'INSERT INTO #ClientVersion
			SELECT sip.ClientID, 
			      (CAST(COALESCE(psp.SPMajor, 0) AS VARCHAR(10)) + ''.'' + CAST(COALESCE(sip.UPNumber, 0) AS VARCHAR(10))) AS Version 
			FROM    simInstalledPackages sip WITH (NOLOCK)
			JOIN 	PatchSPVersion psp WITH (NOLOCK) ON psp.id = sip.SPVersionID
			WHERE   sip.simPackageID IN (SELECT id FROM simPackage WITH (NOLOCK) WHERE Name = ''MediaAgent'')'
	END
	ELSE
	BEGIN
		SET @sqlstr = 
		'INSERT INTO #ClientVersion
			SELECT sip.ClientID, 
			      (CAST(COALESCE(psp.SPMajor, 0) AS VARCHAR(10)) + 
			      CASE WHEN SpMinorVersion > 0 THEN ''.'' + CAST(SpMinorVersion AS VARCHAR) ELSE '''' END) AS Version 
			FROM    simInstalledPackages sip WITH (NOLOCK)
			JOIN 	PatchSPVersion psp WITH (NOLOCK) ON psp.id = sip.SPVersionID
			WHERE   sip.simPackageID IN (SELECT id FROM simPackage WITH (NOLOCK) WHERE Name = ''MediaAgent'')'
	END
END
ELSE
BEGIN
	SET @sqlstr = 
	'INSERT INTO #ClientVersion
		SELECT ClientID, 
	          (CASE WHEN HighestSP > 0 THEN CAST(HighestSP AS VARCHAR(20)) ELSE '''' END 
	          +CASE WHEN SpMinorVersion > 0 THEN ''.'' + CAST(SpMinorVersion AS VARCHAR) ELSE '''' END) AS Version 
	    FROM    simInstalledPackages WITH (NOLOCK)
	    WHERE   simPackageID IN (SELECT id FROM simPackage WITH (NOLOCK) WHERE Name = ''MediaAgent'')'
END

EXEC sp_executesql @sqlstr

CREATE TABLE #MetallicContainers (
	DeviceId			INTEGER,
	LicenseType			INTEGER,
	RegionName			VARCHAR(64),
	RegionDisplayName	VARCHAR(256),
	StorageClass		INTEGER,
	ReplicationType		INTEGER,
	CredentialId		INTEGER,
	Container			VARCHAR(64),
	StorageAccount		VARCHAR(64),
	ApplicationId		VARCHAR(64)
)

IF @csReleaseId >= 16 AND OBJECT_ID('MMGetMetallicStorage', 'P') IS NOT NULL
BEGIN
	INSERT INTO #MetallicContainers
	EXEC MMGetMetallicStorage
END

CREATE TABLE #MetallicCloudStorageUsage (
	LicenseType			INTEGER,
	TotalSpaceMB		BIGINT,
	UsedSpaceMB			BIGINT,
)
	
IF @csReleaseId >= 16 AND OBJECT_ID('MMMetallicStorage', 'U') IS NOT NULL
BEGIN
	INSERT INTO #MetallicCloudStorageUsage
	SELECT	S.LicenseType, SUM(MS.TotalSpaceMB) AS TotalSpaceMB, SUM(S.UsedSpaceInBytes)/1024/1024 AS UsedSpaceMB
	FROM	MMMetallicStorage S WITH (NOLOCK)
			INNER JOIN MMDevice D WITH (NOLOCK) ON S.DeviceId = D.DeviceId AND D.DisplayDeviceTypeId = 400
			INNER JOIN MMMountPath MP WITH (NOLOCK) ON S.MountPathId = MP.MountPathId AND MP.MountPathTypeId = 7
			INNER JOIN MMMediaSide MS WITH (NOLOCK) ON MP.MediaSideId = MS.MediaSideId
	WHERE	MS.TotalSpaceMB > 0
	GROUP BY S.LicenseType
END

CREATE TABLE #ArchNumDevType(LibraryId INT, DeviceTypeId INT, DeviceId INT, ArchiveNumber INT, IsArchiveStorage INT)

INSERT INTO #ArchNumDevType
SELECT DISTINCT MP.LibraryId, MD.DeviceTypeId, MDC.DeviceId,
	CASE WHEN MDC.UserName LIKE '%@%//%' THEN
	     CAST(SUBSTRING(MDC.UserName, CHARINDEX('@', MDC.UserName, 0)+1, 
	          PATINDEX(N'%[^0-9]%', RIGHT(MDC.UserName, LEN(MDC.UserName)-CHARINDEX('@', MDC.UserName, 0)))-1) AS INT)
	ELSE 0 END AS ArchiveNumber, 0
FROM MMDeviceController MDC WITH (NOLOCK)
	INNER JOIN MMDevice MD WITH (NOLOCK) ON MD.DeviceId = MDC.DeviceId
	INNER JOIN MMMountPathToStorageDevice MPSD WITH (NOLOCK) ON MPSD.DeviceId = MD.DeviceId
	INNER JOIN MMMountPath MP WITH (NOLOCK) ON MP.MountPathId = MPSD.MountPathId
WHERE ((MDC.UserName LIKE '%@%//%'AND MD.DeviceTypeId IN (2, 3, 23)) OR (MD.DeviceTypeId IN (22, 28, 53)))

UPDATE #ArchNumDevType SET IsArchiveStorage = 1
WHERE  DeviceTypeId = 2 AND (ArchiveNumber IN (8, 16) OR ArchiveNumber > 8 AND ArchiveNumber <> 16)
    OR DeviceTypeId = 3 AND ArchiveNumber IN (3, 4, 5)
    OR DeviceTypeId = 23 AND ArchiveNumber >= 8
    OR DeviceTypeId IN (22, 28, 53)


IF NOT EXISTS(SELECT 1 FROM tempdb.sys.columns WHERE [object_id]=OBJECT_ID('tempdb..#StoragePool') AND name='DeviceTypeId')
	ALTER TABLE #StoragePool ADD DeviceTypeId INTEGER

SET @surveyXML = (SELECT
    (SELECT MA.ClientId AS '@MAClientId',
            CL.name AS '@MediaAgentName', 
            ISNULL(T.ApplianceType, 0) AS '@ApplianceType',
            1 - (MmHostSoftState & MmHostEnabled) AS '@Offline',
            MA.OfflineReason AS '@OfflineReason',
             CASE WHEN MA.OfflineTimeStamp > 0 THEN MA.OfflineTimeStamp + @diff ELSE 0 END AS '@OfflineTimeStamp',
            dbo.GetClientOSName(CL.id,NULL) AS '@OSName',
            (R.release + '.' + ISNULL(V.Version, '0.0')) AS '@ReleaseVersion',
            ISNULL(W.DataWrittenLastWeek, 0) AS '@DataWrittenLastWeek',
            D.NumOfDisks AS '@NumOfDisks',
            SEC.MAStatus AS '@RWSecure',
            LTRIM(STUFF((SELECT ', ' + CONVERT(NVARCHAR(128), LM.Libraries) FROM #librariesperMA LM 
                 WHERE LM.Mediaagentid = MA.clientid FOR XML PATH ('')), 1, 1, '')) AS '@Libraries'
    FROM    MMHost MA WITH (NOLOCK)
            INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = MA.ClientId AND CL.id > 1
            INNER JOIN simAllGalaxyRel R WITH (NOLOCK) ON R.id = CL.releaseId 
            LEFT OUTER JOIN #ClientVersion V ON V.ClientId = MA.ClientId
            LEFT OUTER JOIN (
                SELECT  ClientId, SUM(DataWritten) AS DataWrittenLastWeek
                FROM    MMDriveHistory WITH (NOLOCK)
                WHERE   HistoryType = 1
                    AND TimeStart > DATEADD(DAY, -7, GETUTCDATE())
                GROUP BY ClientId
                ) W ON MA.ClientId = W.ClientId
            LEFT OUTER JOIN (
                SELECT  EntityId, intVal AS ApplianceType
                FROM    MMEntityProp WITH (NOLOCK)
                WHERE   propertyName = 'MediaAgentApplianceType' AND entityType = 10
                ) T ON MA.ClientId = T.EntityId
            LEFT OUTER JOIN #DisksPerMA D ON MA.ClientId = D.MediaAgentId
            LEFT OUTER JOIN #MountPathsStatus SEC ON SEC.MAId = MA.ClientId
   FOR XML PATH('MediaAgent'), TYPE),

    (SELECT L.LibraryId AS '@LibraryId',
            L.AliasName AS '@LibraryName',
			L.LibraryEnabled AS '@Enabled',
            CASE WHEN CL.LibraryId IS NULL THEN L.LibraryTypeId ELSE -3 END AS '@LibraryTypeId',
            CASE WHEN A.LibraryId IS NULL THEN 0 ELSE 1 END AS '@IsArchiveStorage',
            1 - (L.LibrarySoftState & L.LibraryEnabled & (1 - L.LibraryBroken) & LC.OnLine) AS '@Offline',
            L.LibraryStatusReason AS '@OfflineReason',
            CASE WHEN L.OfflineTimeStamp > 0 THEN L.OfflineTimeStamp + @diff ELSE 0 END AS '@OfflineTimeStamp',
            ISNULL(W.DataWrittenLastWeek, 0) AS '@DataWrittenLastWeek',
			ISNULL(HD.TotalSpaceMB, ISNULL(F.TotalSpaceMB, -1)) AS '@TotalSpaceMB',
            ISNULL(HD.FreeBytesMB, ISNULL(F.FreeBytesMB, -1)) AS '@FreeSpaceMB',
            ISNULL(DW.DataWrittenMB, -1) AS '@DataWrittenMB',
            L.LowWaterMarkMB AS '@LowWaterMark',
            ISNULL(SG.ScratchPoolsBelowLWM, 0) AS '@ScratchPoolsBelowLWM',
            ISNULL(S.SpareInLibrary, 0) AS '@SpareInLibrary',
            ISNULL(S.SpareTotal, 0) AS '@SpareTotal',
            ISNULL(CAST(CL.CloudVendorTypeId AS VARCHAR(16)), L.LibraryVendor) AS '@Manufacturer', 
            L.LibraryModel AS '@Model', 
            L.SerialNumber AS '@SerialNumber',
            L.FirmwareRevision AS '@FirmwareRevision',
            ISNULL(TS.Slots, 0) AS '@Slots',
            ISNULL(TS.Slots, 0) - ISNULL(TS.OccupiedSlots, 0) AS '@EmptySlots',
            CASE WHEN (L.ExtendedAttributes & 33554432) /* MMS2_HIDE_LIBRARY*/ = 0 THEN 0 ELSE 1 END AS '@Hidden',
            L.LibraryInstallTime AS '@LibraryInstallTime',
			ISNULL(NM.NumMediaAgents, 0) AS '@NumMediaAgents',
            LTRIM(STUFF((SELECT ', ' + CONVERT(VARCHAR(256), LM.MediaAgent) FROM #LibraryMA LM 
					WHERE LM.LibraryId = L.LibraryId FOR XML PATH ('')), 1, 1, '')) AS '@MediaAgents'
    FROM    MMLibrary L WITH (NOLOCK)
            INNER JOIN (
                SELECT  LibraryId, MAX(LibraryControllerSoftState & LibraryControllerEnabled & LibraryControllerActive) AS OnLine
                FROM    MMLibraryController WITH (NOLOCK)
                GROUP BY LibraryId
                ) LC ON L.LibraryId = LC.LibraryId
            LEFT OUTER JOIN (
                SELECT  LibraryId 
                FROM    #ArchNumDevType 
                GROUP BY LibraryId
                HAVING  MAX(IsArchiveStorage) > 0
                ) A ON L.LibraryId = A.LibraryId
            LEFT OUTER JOIN #CloudVendors CL ON L.LibraryId = CL.LibraryId
            LEFT OUTER JOIN (
                SELECT  LibraryId, COUNT(SpareGroupId) AS ScratchPoolsBelowLWM
                FROM    MMSpareGroup WITH (NOLOCK)
                WHERE   SpareGroupType IN (1, 2) AND IsLowWaterMark > 0
                GROUP BY LibraryId
                ) SG ON L.LibraryId =  SG.LibraryId
            LEFT OUTER JOIN (
                SELECT  DISTINCT M.LibraryId, 
						CASE WHEN MP.MountPathTypeId = 7 AND MAX(MS.TotalSpaceMB) = 1048576 THEN -1 ELSE SUM(CAST(MS.TotalSpaceMB AS BIGINT)) END AS TotalSpaceMB, 
						CASE WHEN MP.MountPathTypeId = 7 AND MAX(MS.TotalSpaceMB) = 1048576 THEN -1 ELSE SUM(CAST(MS.FreeBytesMB AS BIGINT)) END AS FreeBytesMB
                FROM    MMMediaSide MS WITH (NOLOCK)
						INNER JOIN MMMountPath MP WITH (NOLOCK) ON MP.MediaSideId = MS.MediaSideId
                        INNER JOIN MMMedia M WITH (NOLOCK) ON MS.MediaId = M.MediaId
                        LEFT OUTER JOIN (
                            SELECT  MediaSideId FROM MMVolume WITH (NOLOCK) 
                            WHERE VolumeFlags IN (1, 5, 7) AND RecordingFormatId <> 10001
                            ) V ON MS.MediaSideId = V.MediaSideId
                WHERE   (M.MediaTypeId = 10001 OR V.MediaSideId IS NOT NULL)
                GROUP BY M.LibraryId, MP.MountPathTypeId
                ) F ON L.LibraryId = F.LibraryId
            LEFT OUTER JOIN (
                SELECT  LibraryId, SUM(TotalSpaceMB) AS TotalSpaceMB, SUM(FreeBytesMB) AS FreeBytesMB 
                FROM    (SELECT  LibraryId, MediaSideId, TotalSpaceMB, FreeBytesMB, 
                        ROW_NUMBER() OVER (PARTITION BY LibraryId, FreeBytesMBRange, HardDrive ORDER BY MediaSideId) AS RowId
                        FROM    #LibraryHardDrive) LHD
                WHERE   RowId = 1
                GROUP BY LibraryId
                ) HD ON L.LibraryId = HD.LibraryId
            LEFT OUTER JOIN (
                SELECT  LibraryId, SUM(DataWrittenMB) AS DataWrittenMB
                FROM    #MountPathSize MPS
						INNER JOIN MMMountPath MPT ON MPS.MediaSideId = MPT.MediaSideId
                GROUP BY LibraryId
                ) DW ON L.LibraryId = DW.LibraryId
            LEFT OUTER JOIN (
                SELECT  MP.LibraryID, SUM(DH.DataWritten) AS DataWrittenLastWeek
                FROM    MMDriveHistory DH WITH (NOLOCK)
                        INNER JOIN MMDrive D WITH (NOLOCK) ON DH.DriveID = D.DriveID
                        INNER JOIN MMMasterPool MP WITH (NOLOCK) ON D.MasterPoolID = MP.MasterPoolId
                WHERE   DH.HistoryType = 1
                    AND DH.TimeStart > DATEADD(DAY, -7, GETUTCDATE())
                GROUP BY MP.LibraryID
                ) W ON L.LibraryID = W.LibraryId
            LEFT OUTER JOIN (
                SELECT  LibraryId, COUNT(*) AS SpareTotal, SUM(CASE WHEN MediaLocation IN (1, 2) THEN 1 ELSE 0 END) AS SpareInLibrary
                FROM    MMMedia WITH (NOLOCK)
                WHERE   IsInMediaGroup = 0 
                    AND SpareGroupId IN (SELECT SpareGroupId FROM MMSpareGroup WITH (NOLOCK) WHERE SpareGroupType IN (1, 2))
                GROUP BY LibraryId
                ) S ON L.LibraryID = S.LibraryId
            LEFT OUTER JOIN (           
                SELECT  B.LibraryId, COUNT(*) AS Slots, SUM(IsOccupied) AS OccupiedSlots
                FROM    MMSlot SL WITH (NOLOCK) INNER JOIN MMBay B WITH (NOLOCK) ON SL.BayId = B.BayId
                WHERE   SL.SlotType = 1
                GROUP BY B.LibraryId
                ) TS ON L.LibraryId = TS.LibraryId
            LEFT OUTER JOIN (
				SELECT	LibraryId, COUNT(*) AS NumMediaAgents
				FROM	#LibraryMA
				GROUP BY LibraryId
				) NM ON L.LibraryId = NM.LibraryId
    WHERE   L.LibraryId > 0 AND L.LibraryTypeId != 8
    FOR XML PATH('Library'), TYPE),

    (SELECT LC.ClientId AS '@MAClientId',
            LC.LibraryId AS '@LibraryId',
            LC.LibraryControllerId AS '@ControllerId',
            LC.LibraryControllerName AS '@ControllerName',
            LC.LibraryControllerActive AS '@Active',
            LC.LibraryControllerEnabled AS '@Enabled',
            LC.SCSIId AS '@SCSIAddress'
    FROM    MMLibraryController LC WITH (NOLOCK)
            INNER JOIN MMLibrary L WITH (NOLOCK) ON L.LibraryId = LC.LibraryId
    WHERE   L.LibraryTypeId NOT IN (8)
    FOR XML PATH('LibraryController'), TYPE),

    (SELECT MP.LibraryId AS '@LibraryId',
            MountPathId AS '@MountPathId',
            MountPathName AS '@MountPathName',
            BaseFolder AS '@BaseFolder',
            DeviceName AS '@DeviceName',
            HD.HardDrive AS '@HardDrive',
            ISNULL(HD.Shared, 0) AS '@HardDriveShared',
            IsEnabled AS '@Enabled',
            IsOffline AS '@Offline',
            OfflineReason AS '@OfflineReason',
            OfflineTimeStamp AS '@OfflineTimeStamp',
            MaxConcurrentWriters AS '@MaxConcurrentWriters',
            MP.TotalSpaceMB AS '@TotalSpaceMB',
            CASE WHEN MP.FreeBytesMB < 0 AND MP.TotalSpaceMB > ISNULL(W.DataWrittenMB, 0) THEN (MP.TotalSpaceMB - ISNULL(W.DataWrittenMB, 0)) ELSE MP.FreeBytesMB END AS '@FreeSpaceMB',
            MagneticSpaceRsrvInMB AS '@ReservedSpaceMB',
            ISNULL(W.DataWrittenMB, 0) AS '@DataWrittenMB',
            @tempCostCategory1 AS '@CostCategory',
			MP.DeviceTypeId AS '@DeviceTypeId',
			MP.StorageClass AS '@StorageClass'
    FROM    #MountPathProp MP WITH (NOLOCK)
            LEFT OUTER JOIN #LibraryHardDrive HD ON MP.MediaSideId = HD.MediaSideId
            LEFT OUTER JOIN #MountPathSize W ON MP.MediaSideId = W.MediaSideId
    ORDER BY MP.LibraryId, MountPathId
    FOR XML PATH('MountPath'), TYPE),

    @tempCostCategory ,
    
    (SELECT DC.DriveID AS '@DriveId',
            DC.DriveID AS '@ControllerId',
            MAX(DC.DriveControllerSoftState&DC.DriveControllerSoftState&DC.DriveAccessible) AS '@Active'
    FROM    MMDriveController DC WITH (NOLOCK)
            INNER JOIN MMDrive D WITH (NOLOCK) ON D.DriveID = DC.DriveID
    WHERE   D.DriveTypeId <> 10001
    GROUP BY DC.DriveID
    FOR XML PATH('DriveController'), TYPE),

    (SELECT PL.StoragePoolId AS '@StoragePoolId',
            PL.StoragePoolName AS '@StoragePoolName',
            PL.StoragePoolType AS '@StoragePoolType',
            PL.Libraries AS '@LibraryIds',
            PL.LibraryType AS '@LibraryType',
            PL.TotalControllers AS '@TotalControllers',
            PL.ActiveControllers AS '@ActiveControllers',
            PL.LibOfflineReason AS '@LibOfflineReason',
            PL.MPOfflineReason AS '@MPOfflineReason',
            PL.MAOfflineReason AS '@MAOfflineReason',
            ISNULL(HS.ApplianceType, 0) AS '@ApplianceType',
            PL.NumMediaAgents AS '@NumMediaAgents',
            LTRIM(STUFF((SELECT ', ' + CONVERT(VARCHAR(256), PM.MediaAgent) FROM #StoragePoolMA PM 
                   WHERE PM.StoragePoolId = PL.StoragePoolId FOR XML PATH ('')), 1, 1, '')) AS '@MediaAgents',
			ISNULL(PL.DeviceTypeId,0) AS '@DeviceTypeId',
            ISNULL(EP.OfferingType, 0) AS '@OfferingType',
            ISNULL(PL.Encryption, 'N/A') AS '@Encryption'
    FROM    #StoragePool PL
            LEFT OUTER JOIN (
		SELECT StoragePoolId, SUM(DISTINCT ApplianceType) AS ApplianceType
		FROM #HyperScaleMediaSide
		GROUP BY StoragePoolId) HS ON PL.StoragePoolId = HS.StoragePoolId
            LEFT OUTER JOIN (
		SELECT EntityId, intVal AS OfferingType
		FROM MMEntityProp
		WHERE EntityType = 1 AND propertyName = 'MetallicStoragePoolOfferingType' AND modified = 0
		) EP ON PL.StoragePoolId = EP.EntityId
    FOR XML PATH('StoragePool'), TYPE),
    
    (SELECT ApplianceType AS '@ApplianceType',
            NumStoragePools AS '@NumStoragePools',
            NumMediaAgents AS '@NumMediaAgents',
            TotalSpaceMB AS '@TotalSpaceMB', 
            FreeSpaceMB AS '@FreeSpaceMB', 
            DataWrittenMB AS '@DataWrittenMB', 
            NumClients AS '@NumClients', 
            NumVMs AS '@NumVMs',
			NumMAs1To4Disks AS '@NumMAs1To4Disks',
			NumMAs5To12Disks AS '@NumMAs5To12Disks',
			NumMAs13To24Disks AS '@NumMAs13To24Disks',
			HyperScaleVersion AS '@HyperScaleVersion'
    FROM    #HyperScaleUsage
    FOR XML PATH('HyperScaleUsage'), TYPE),

	(SELECT DeviceId AS '@ContainerId',
            Container AS '@ContainerName'
    FROM    #MetallicContainers
    FOR XML PATH('MetallicContainer'), TYPE),

	(SELECT LicenseType AS '@LicenseType',
			TotalSpaceMB AS '@TotalSpaceMB',
			UsedSpaceMB AS '@UsedSpaceMB'
	FROM    #MetallicCloudStorageUsage
    FOR XML PATH('MetallicCloudStorageUsage'), TYPE)

FOR XML PATH ('MediaManagementSummary')
-- Replace <<RESULTS_NAME>> with specific name
)

DROP TABLE #ArchNumDevType
DROP TABLE #clientVersion
DROP TABLE #DisksPerMA
DROP TABLE #StoragePool
DROP TABLE #StoragePoolMA
DROP TABLE #LibraryMA
DROP TABLE #HyperScaleMediaSide
DROP TABLE #HyperScaleUsage
DROP TABLE #HyperScaleJobs
DROP TABLE #MountPathProp
DROP TABLE #MountPathSize
DROP TABLE #LibraryHardDrive
DROP TABLE #MetallicContainers
DROP TABLE #MetallicCloudStorageUsage
DROP TABLE #librariesperMA
DROP TABLE #MountPathsStatus

--------- END SURVEY QUERY ---------
--select CAST(@surveyXML AS XML)

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
    SELECT
        @queryId AS '@QueryId',
        @EndTime AS '@LogDate',
        (@EndTime - @LogDate) AS '@QueryRunningTime',
        @surveyXML
    FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

--------- END - GENERATED CODE ---------