--- Please follow the below comments to insert SQL statements.
-- Copies with low disk space
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
-- modified

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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



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

--------- Insert your SQL statements here 

IF object_id('tempdb.dbo.#outTable') is not null DROP TABLE #outTable
IF object_id('tempdb.dbo.#AppSize') is not null DROP TABLE #AppSize
IF object_id('tempdb.dbo.#DataWritten') is not null DROP TABLE #DataWritten
IF object_id('tempdb.dbo.#DiskLibsByCopy') is not null DROP TABLE #DiskLibsByCopy
IF object_id('tempdb.dbo.#TempArchFileCopy') is not null DROP TABLE #TempArchFileCopy
IF object_id('tempdb.dbo.#LibsByDiskCopy') is not null DROP TABLE #LibsByDiskCopy
IF object_id('tempdb.dbo.#JobVolumeSize') is not null DROP TABLE #JobVolumeSize


DECLARE @lib XML

create  table #outTable
(
  libraryId integer,
  libraryAliasName NVARCHAR(255) ,
  totalValidDataGB FLOAT ,
  totalDataWrittenGB FLOAT ,
  totalAppSizeGB FLOAT ,
  totalFreeSpaceGB FLOAT ,
  totalReserveSpaceMB bigint ,
  totalSpaceGB FLOAT ,
 macroPrunableGB FLOAT ,
  avgConsumedMBPerDay BIGINT ,
  avgFreedMBPerDay BIGINT ,
  lowWatermark integer ,
  warningWatermark integer 
)
--This will turn off message: "xxx rows affected".
DECLARE @csReleaseId int
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2
DECLARE @nstring NVARCHAR(MAX)
SET NOCOUNT ON
	

IF (@csReleaseId >=15 )
BEGIN
	SET @nstring = N'
	INSERT INTO #outTable(libraryId,libraryAliasName,lowWatermark,warningWatermark)
	SELECT DISTINCT  LIB.LibraryId,LIB.AliasName,LIB.LowWaterMarkMB,LIB.WarningWatermark
	FROM MMDataPath DPath WITH(NOLOCK) 
		INNER JOIN MMDrivePool DPool WITH(NOLOCK) ON DPath.DrivePoolId = DPool.DrivePoolId
		INNER JOIN MMMasterPool MPool  WITH(NOLOCK) ON DPool.MasterPoolId = MPool.MasterPoolId
		INNER JOIN MMLibrary LIB WITH(NOLOCK) ON MPool.LibraryId = LIB.LibraryId AND LIB.LibraryTypeId = 3
		INNER JOIN MMLibraryController LC WITH(NOLOCK) ON LIB.LibraryId = LC.LibraryId AND DPool.ClientId = LC.ClientId'
END
ELSE
BEGIN
	SET @nstring = N'
	INSERT INTO #outTable(libraryId,libraryAliasName,lowWatermark,warningWatermark)
	SELECT DISTINCT  LIB.LibraryId,LIB.AliasName,LIB.LowWaterMarkMB,-1
	FROM MMDataPath DPath WITH(NOLOCK) 
		INNER JOIN MMDrivePool DPool WITH(NOLOCK) ON DPath.DrivePoolId = DPool.DrivePoolId
		INNER JOIN MMMasterPool MPool  WITH(NOLOCK) ON DPool.MasterPoolId = MPool.MasterPoolId
		INNER JOIN MMLibrary LIB WITH(NOLOCK) ON MPool.LibraryId = LIB.LibraryId AND LIB.LibraryTypeId = 3
		INNER JOIN MMLibraryController LC WITH(NOLOCK) ON LIB.LibraryId = LC.LibraryId AND DPool.ClientId = LC.ClientId'
END        
EXEC sp_executesql @nstring

DECLARE @30DaysAgo DATETIME = DATEADD(DAY, -30, GETUTCDATE())
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'

CREATE TABLE #LibraryHardDrive (LibraryId INT, MediaSideId INT, TotalSpaceMB BIGINT, FreeBytesMB BIGINT, FreeBytesMBRange BIGINT, HardDrive NVARCHAR(1024), RowId INT, Shared INT)
CREATE TABLE #MediaSideDataWritten (LibraryId INT, MediaSideId INT, DataWrittenMB BIGINT)
CREATE TABLE #DataWritten (LibraryId INT, DataWrittenMB BIGINT, TotalSpaceMB BIGINT, FreeSpaceMB BIGINT, ReserveSpaceMB BIGINT)
CREATE TABLE #AppSize (LibraryId INT, AppSizeMB BIGINT, ValidDataSizeMB BIGINT)

INSERT  INTO #LibraryHardDrive
SELECT	T.LibraryId, T.MediaSideId, T.TotalSpaceMB, T.FreeBytesMB, T.FreeBytesMBRange, T.HardDrive,
		ROW_NUMBER() OVER (PARTITION BY T.LibraryId, 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

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

INSERT	INTO #MediaSideDataWritten
SELECT	MP.LibraryId, MP.MediaSideId, SUM(CAST(V.PhysicalBytesMB AS BIGINT))
FROM	MMVolume V WITH (NOLOCK) INNER JOIN MMMountPath MP WITH (NOLOCK) ON V.MediaSideId = MP.MediaSideId
WHERE	(V.Attributes & 512) = 0 AND V.SiloStatus <> 3
GROUP BY MP.LibraryId, MP.MediaSideId

IF OBJECT_ID('MMMetallicStorage', 'U') IS NOT NULL
BEGIN
	UPDATE	DW SET DataWrittenMB = S.UsedSpaceInBytes/1024/1024
	FROM	#MediaSideDataWritten DW
		INNER JOIN MMMountPath MP WITH (NOLOCK) ON DW.MediaSideId = MP.MediaSideId AND MP.MountPathTypeId = 7
		INNER JOIN MMMetallicStorage S WITH (NOLOCK) ON MP.MountPathId = S.MountPathId
		INNER JOIN MMDevice D WITH (NOLOCK) ON S.DeviceId = D.DeviceId AND D.DisplayDeviceTypeId = 400
END
			
INSERT	INTO #DataWritten
SELECT	MPT.LibraryId, 0, 
		SUM(CASE WHEN MPT.MountPathTypeId = 7 AND MPT.MaxDataToWriteMB > 0 AND @UpdateCloudMountPathSize = 1 THEN CAST(MPT.MaxDataToWriteMB AS BIGINT) 
			WHEN MPT.MountPathTypeId = 7 AND MS.TotalSpaceMB = 1048576 THEN 0
			WHEN MPT.IsEnabled > 0 AND D.DeviceEnabled > 0 THEN CAST(MS.TotalSpaceMB AS BIGINT) 
			ELSE ISNULL(CAST(S.DataWrittenMB AS BIGINT),0) END),  
		SUM(CASE WHEN MPT.MountPathTypeId = 7 AND MPT.MaxDataToWriteMB > ISNULL(S.DataWrittenMB, 0) AND @UpdateCloudMountPathSize = 1 
				 THEN CAST(MPT.MaxDataToWriteMB AS BIGINT) - ISNULL(CAST(S.DataWrittenMB AS BIGINT),0)
			WHEN MPT.MountPathTypeId = 7 AND MS.TotalSpaceMB = 1048576 THEN 0
			WHEN MPT.IsEnabled > 0 AND D.DeviceEnabled > 0 THEN CAST(MS.FreeBytesMB AS BIGINT) 
			ELSE 0 END),
		SUM(ISNULL(CASE WHEN MPT.MountPathTypeId = 7 THEN 0 
			WHEN MPT.IsEnabled > 0 AND D.DeviceEnabled > 0 THEN CAST(MPT.MagneticSpaceRsrvInMB AS BIGINT) ELSE 0 END,0))
FROM	MMMountPath MPT WITH (NOLOCK)
		INNER JOIN MMMountPathToStorageDevice MPD WITH (NOLOCK) ON MPT.MountPathId = MPD.MountPathId
		INNER JOIN MMDevice D WITH (NOLOCK) ON MPD.DeviceId = D.DeviceId
		INNER JOIN MMMediaSide MS WITH (NOLOCK) ON MPT.MediaSideId = MS.MediaSideId
		LEFT OUTER JOIN #LibraryHardDrive HD ON MPT.LibraryId = HD.LibraryId AND MPT.MediaSideId = HD.MediaSideId
		LEFT OUTER JOIN #MediaSideDataWritten S ON MPT.MediaSideId = S.MediaSideId
WHERE	HD.RowId IS NULL OR HD.RowId = 1 OR @MountPathsNotShareDiskSpace = 1
GROUP BY MPT.LibraryId

UPDATE	DW SET DataWrittenMB = S.DataWrittenMB
FROM	#DataWritten DW
		INNER JOIN (
			SELECT	LibraryId, SUM(DataWrittenMB) AS DataWrittenMB
			FROM	#MediaSideDataWritten
			GROUP BY LibraryId
			) S ON DW.LibraryId = S.LibraryId

UPDATE	#outTable 
SET		totalDataWrittenGB = DW.DataWrittenMB/1024.0, totalFreeSpaceGB = DW.FreeSpaceMB/1024.0, 
		totalReserveSpaceMB = DW.ReserveSpaceMB, totalSpaceGB = DW.TotalSpaceMB/1024.0
FROM	#DataWritten DW
WHERE	#outTable.libraryId = DW.LibraryId

CREATE TABLE #LibsByDiskCopy (archGroupCopyId INT, NumOfLibs INT)

INSERT	INTO #LibsByDiskCopy
SELECT	S.archGroupCopyId, COUNT(DISTINCT ISNULL(MPT.LibraryId, 0))
FROM	MMVolume V WITH (NOLOCK)
		INNER JOIN archStream S WITH (NOLOCK) ON V.MediaGroupId = S.mediaGroupId
		LEFT OUTER JOIN MMMountPath MPT WITH (NOLOCK) ON V.MediaSideId = MPT.MediaSideId
WHERE	V.RecordingFormatId = 10001 AND V.PhysicalBytesMB > 0
GROUP BY S.archGroupCopyId
HAVING SUM(ISNULL(MPT.LibraryId, 0)) > 0


CREATE TABLE #DrivePollLib (DrivePoolId INT, LibraryId INT)

INSERT	INTO #DrivePollLib
SELECT	DISTINCT DPL.DrivePoolId, MPT.LibraryId 
FROM 	MMDrivePool DPL WITH (NOLOCK) INNER JOIN MMMountPath MPT WITH (NOLOCK) ON DPL.MasterPoolId = MPT.MasterPoolId
and mpt.MountPathName <> 'CVDiskFolder' 

IF COL_LENGTH('archFileCopy','unCompSize') IS NOT NULL
BEGIN
	SET @nstring = N'
	INSERT	INTO #AppSize
	SELECT	L.LibraryId, SUM(S.appSizeMB), SUM(S.physicalSizeMB)
	FROM	(SELECT	drivePoolId, 
					SUM(CASE WHEN unCompSize > 0 THEN CAST(unCompSize AS FLOAT) 
						WHEN ISNULL(totalBackupSize, 0) > 0 THEN (CAST(physicalSize AS FLOAT)/totalBackupSize)*totalUncompBytes 
						ELSE CAST(physicalSize AS FLOAT) END)/1024/1024 AS appSizeMB,
					SUM(CAST(PhysicalSize AS FLOAT))/1024/1024 AS physicalSizeMB
			FROM	archFileCopy AFC WITH (NOLOCK)
					INNER JOIN archFile AF WITH (NOLOCK) ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId 
						AND AF.fileType IN (1,4) AND AF.isValid = 1
					LEFT OUTER JOIN JMBkpStats B WITH (NOLOCK) ON AF.jobId = B.jobId AND AF.commCellId = B.commCellId
			GROUP BY drivePoolId) S
			INNER JOIN #DrivePollLib L ON S.drivePoolId = L.DrivePoolId
	GROUP BY L.LibraryId'

	EXEC sp_executesql @nstring
END
ELSE
BEGIN
	CREATE TABLE #TempArchFileCopy (archFileId INT, commCellId INT, archCopyId INT)

	IF EXISTS (SELECT archGroupCopyId FROM #LibsByDiskCopy WHERE NumOfLibs > 1)
	BEGIN
		INSERT	INTO #TempArchFileCopy
		SELECT	ACM.archFileId, ACM.commCellId, ACM.archCopyId
		FROM	archChunkMapping ACM WITH (NOLOCK)
				INNER JOIN archChunk AC WITH (NOLOCK) ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
				INNER JOIN MMVolume V WITH (NOLOCK) ON V.VolumeId = AC.volumeId AND V.RecordingFormatId = 10001
				LEFT OUTER JOIN MMMountPath MPT WITH (NOLOCK) ON MPT.MediaSideId = V.MediaSideId
		WHERE	ACM.archCopyId IN (SELECT archGroupCopyId FROM #LibsByDiskCopy WHERE NumOfLibs > 1)
		GROUP BY ACM.archFileId, ACM.commCellId, ACM.archCopyId
		HAVING COUNT(DISTINCT ISNULL(MPT.LibraryId, 0)) > 1
	END

	INSERT	INTO #AppSize
	SELECT	L.LibraryId, SUM(S.appSizeMB), SUM(S.physicalSizeMB)
	FROM	(SELECT	AFC.drivePoolId, 
					SUM(CASE WHEN totalBackupSize > 0 THEN (1.0*PhysicalSize/totalBackupSize/1024/1024)*totalUncompBytes ELSE 1.0*PhysicalSize/1024/1024 END) AS appSizeMB,
					SUM(1.0*AFC.PhysicalSize/1024/1024) AS physicalSizeMB
			FROM	JMBkpStats B WITH (NOLOCK)
				INNER JOIN archFile AF WITH (NOLOCK) ON B.jobId = AF.jobId AND B.commCellId = AF.commCellId AND AF.isValid = 1 AND B.status IN (1, 3, 14)
				INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AFC.PhysicalSize > 0
			WHERE	AFC.archCopyId NOT IN (SELECT DISTINCT archGroupCopyId FROM #LibsByDiskCopy WHERE NumOfLibs > 1)
			GROUP BY AFC.drivePoolId) S
		INNER JOIN #DrivePollLib L ON S.drivePoolId = L.DrivePoolId
	GROUP BY L.LibraryId

	INSERT	INTO #AppSize
	SELECT	L.LibraryId, SUM(S.physicalSizeMB), SUM(S.physicalSizeMB)
	FROM	(SELECT	AFC.drivePoolId, SUM(1.0*AFC.physicalSize/1024/1024) AS physicalSizeMB
			FROM	JMAdminJobStatsTable A WITH (NOLOCK)
			INNER JOIN archFile AF WITH (NOLOCK) ON A.jobId = AF.jobId AND A.commCellId = AF.commCellId AND AF.isValid = 1 AND A.status IN (1, 3, 14)
			INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AFC.PhysicalSize > 0
			WHERE	AFC.archCopyId NOT IN (SELECT DISTINCT archGroupCopyId FROM #LibsByDiskCopy WHERE NumOfLibs > 1)
			GROUP BY AFC.drivePoolId) S
		INNER JOIN #DrivePollLib L ON S.drivePoolId = L.DrivePoolId
	GROUP BY L.LibraryId

	IF	EXISTS (SELECT * FROM #TempArchFileCopy)
	BEGIN
		INSERT	INTO #AppSize
		SELECT	L.LibraryId, SUM(S.appSizeMB), SUM(S.physicalSizeMB)
		FROM	(SELECT	AFC.drivePoolId, 
				SUM(CASE WHEN totalBackupSize > 0 THEN (1.0*PhysicalSize/totalBackupSize/1024/1024)*totalUncompBytes ELSE 1.0*PhysicalSize/1024/1024 END) AS appSizeMB,
				SUM(1.0*AFC.physicalSize/1024/1024) AS physicalSizeMB
				FROM	JMBkpStats B WITH (NOLOCK)
					INNER JOIN archFile AF WITH (NOLOCK) ON B.jobId = AF.jobId AND B.commCellId = AF.commCellId AND AF.isValid = 1 AND B.status IN (1, 3, 14)
					INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AFC.PhysicalSize > 0
							AND AFC.archCopyId IN (SELECT archGroupCopyId FROM #LibsByDiskCopy WHERE NumOfLibs > 1)
					LEFT OUTER JOIN #TempArchFileCopy T ON AFC.archFileId = T.archFileId AND AFC.commCellId = T.commCellId AND AFC.archCopyId = T.archCopyId
				WHERE	T.archFileId IS NULL
				GROUP BY AFC.drivePoolId) S
			INNER JOIN #DrivePollLib L ON S.drivePoolId = L.DrivePoolId
		GROUP BY L.LibraryId

		INSERT	INTO #AppSize
		SELECT	L.LibraryId, SUM(S.physicalSizeMB), SUM(S.physicalSizeMB)
		FROM	(SELECT	AFC.drivePoolId, SUM(1.0*AFC.physicalSize/1024/1024) AS physicalSizeMB
				FROM	JMAdminJobStatsTable A WITH (NOLOCK)
					INNER JOIN archFile AF WITH (NOLOCK) ON A.jobId = AF.jobId AND A.commCellId = AF.commCellId AND AF.isValid = 1 AND A.status IN (1, 3, 14)
					INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AFC.PhysicalSize > 0
							AND AFC.archCopyId IN (SELECT archGroupCopyId FROM #LibsByDiskCopy WHERE NumOfLibs > 1)
					LEFT OUTER JOIN #TempArchFileCopy T ON AFC.archFileId = T.archFileId AND AFC.commCellId = T.commCellId AND AFC.archCopyId = T.archCopyId
				WHERE	T.archFileId IS NULL
				GROUP BY AFC.drivePoolId) S
			INNER JOIN #DrivePollLib L ON S.drivePoolId = L.DrivePoolId
		GROUP BY L.LibraryId

		CREATE TABLE #JobVolumeSize (jobId INT, commCellId INT, volumeId INT, physicalSize BIGINT)
		
		INSERT INTO #JobVolumeSize
		SELECT AF.jobId, AF.commCellId, AC.volumeId, SUM(ACM.physicalSize) AS physicalSize
		FROM	archFile AF WITH (NOLOCK)
			INNER JOIN #TempArchFileCopy AFC ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON ACM.archFileId = AFC.archFileId AND ACM.commCellId = AFC.commCellId AND ACM.archCopyId = AFC.archCopyId
			INNER JOIN archChunk AC WITH (NOLOCK) ON AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
		GROUP BY AF.jobId, AF.commCellId, AC.volumeId	

		CREATE INDEX #JobVolumeSize_IDX ON #JobVolumeSize (volumeId)

		INSERT	INTO #AppSize
		SELECT	MPT.LibraryId, 
				SUM(CASE WHEN J.totalUncompBytes > 0 AND J.totalBackupSize > 0 THEN (1.0*J.totalUncompBytes/J.totalBackupSize/1024/1024)*JV.physicalSize ELSE 1.0*JV.physicalSize/1024/1024 END) AS AppSizeMB, 
				SUM(1.0*JV.physicalSize/1024/1024) AS ValidDataSizeMB
		FROM	JMBkpStats J WITH (NOLOCK)
				INNER JOIN #JobVolumeSize JV ON J.jobId = JV.jobId AND J.commCellId = JV.commCellId
				INNER JOIN MMVolume V WITH (NOLOCK) ON V.VolumeId = JV.volumeId
				INNER JOIN MMMountPath MPT WITH (NOLOCK) ON V.MediaSideId = MPT.MediaSideId and mpt.MountPathName <> 'CVDiskFolder' 
		WHERE	J.dataStatus <> 2 AND (J.totalUncompBytes > 0 OR J.totalBackupSize > 0) AND V.RecordingFormatId = 10001
		GROUP BY MPT.LibraryId	

		INSERT	INTO #AppSize
		SELECT	MPT.LibraryId, 
				SUM(1.0*JV.physicalSize/1024/1024) AS AppSizeMB, 
				SUM(1.0*JV.physicalSize/1024/1024) AS ValidDataSizeMB
		FROM	JMAdminJobStatsTable J WITH (NOLOCK)
				INNER JOIN #JobVolumeSize JV ON J.jobId = JV.jobId AND J.commCellId = JV.commCellId
				INNER JOIN MMVolume V WITH (NOLOCK) ON V.VolumeId = JV.volumeId
				INNER JOIN MMMountPath MPT WITH (NOLOCK) ON V.MediaSideId = MPT.MediaSideId and mpt.MountPathName <> 'CVDiskFolder' 
		WHERE	J.dataStatus <> 2 AND J.totalBackupSize > 0 AND V.RecordingFormatId = 10001
		GROUP BY MPT.LibraryId	

		DROP TABLE #JobVolumeSize
	END

	DROP TABLE #TempArchFileCopy
END

UPDATE	#outTable
SET		totalValidDataGB = T.ValidDataSizeGB, totalAppSizeGB = T.AppSizeGB
FROM	(SELECT	LibraryId, SUM(AppSizeMB/1024.0) AS AppSizeGB, SUM(ValidDataSizeMB/1024.0) AS ValidDataSizeGB
		FROM	#AppSize
		GROUP BY LibraryId) T
WHERE	#outTable.libraryId = T.LibraryId

UPDATE	#outTable
SET		macroPrunableGB = T.MacroPrunableGB
FROM	(SELECT	M.LibraryId, SUM(S.MacroPrunableGB) AS MacroPrunableGB
		FROM	MMMedia M WITH (NOLOCK)
				INNER JOIN (
				SELECT	MediaId, SUM(CAST(PhysicalBytesMB/1024.0 AS FLOAT)) AS MacroPrunableGB
				FROM	MMVolume WITH (NOLOCK) 
				WHERE	RecordingFormatId = 10001 AND siloStatus <> 3 AND (Attributes & 512) = 0
					AND SIDBStoreId > 0
					AND SIDBStoreId IN (
						SELECT	DISTINCT S.SIDBStoreId
						FROM	IdxSIDBStore S WITH (NOLOCK) 
								LEFT OUTER JOIN archCopySIDBStore C WITH (NOLOCK) ON S.SIDBStoreId = C.SIDBStoreId
						WHERE	S.SIDBStoreId > 0 AND ((S.flags & 256) > 0 OR C.SIDBStoreId IS NULL))
				GROUP BY MediaId
				) S ON M.MediaId = S.MediaId
		GROUP BY M.LibraryId
		) T
WHERE	#outTable.LibraryId = T.LibraryId

UPDATE	#outTable
SET	avgConsumedMBPerDay = T.DataWrittenMB/(DATEDIFF(DAY, EarliestDate, GETUTCDATE()) + 1), 
	avgFreedMBPerDay  = T.CapacityFreedMB/(DATEDIFF(DAY, EarliestDate, GETUTCDATE()) + 1)
FROM	(SELECT LibraryId, MIN(TimeStart) AS EarliestDate, SUM(DataWritten) AS DataWrittenMB, SUM(CapacityFreedMB) AS CapacityFreedMB
	FROM    MMLibraryHistory WITH (NOLOCK)
	WHERE   TimeStart > @30DaysAgo
	GROUP BY LibraryId) T
WHERE	#outTable.libraryId = T.LibraryId
	
UPDATE #outTable
SET   totalSpaceGB	 =  -1,
	  totalFreeSpaceGB =  -1,
	  totalReserveSpaceMB = 0
FROM #outTable as outt,MMMountPath MP WITH (NOLOCK)	   
WHERE MP.LibraryId=outt.libraryId
AND MP.MountPathTypeId=7 AND outt.totalSpaceGB <= 0

UPDATE #outTable						
SET totalReserveSpaceMB = 0
WHERE totalReserveSpaceMB IS NULL OR totalReserveSpaceMB<1

INSERT	INTO #outTable
SELECT	0, '', totalValidDataGB, totalDataWrittenGB, totalAppSizeGB, 
		FreeBytesMB/1024.0, totalReserveSpaceMB, TotalSpaceMB/1024.0, 
		macroPrunableGB, avgConsumedMBPerDay, avgFreedMBPerDay, 0, 0
FROM	(SELECT	SUM(totalValidDataGB) AS totalValidDataGB, 
				SUM(totalDataWrittenGB) AS totalDataWrittenGB,
				SUM(totalAppSizeGB) AS totalAppSizeGB,
				SUM(ISNULL(macroPrunableGB,0)) AS macroPrunableGB,
				SUM(ISNULL(avgConsumedMBPerDay,0)) AS avgConsumedMBPerDay,
				SUM(ISNULL(avgFreedMBPerDay,0)) AS avgFreedMBPerDay
		FROM	#outTable) S,
		(SELECT	SUM(TotalSpaceMB) AS TotalSpaceMB, SUM(FreeBytesMB) AS FreeBytesMB, SUM(MagneticSpaceRsrvInMB) AS totalReserveSpaceMB
		FROM	(SELECT	MediaSideId, TotalSpaceMB, FreeBytesMB, FreeBytesMBRange, HardDrive,
						ROW_NUMBER() OVER (PARTITION BY TotalSpaceMB, FreeBytesMBRange, HardDrive ORDER BY MediaSideId) AS RowId
				FROM	#LibraryHardDrive) MS
				INNER JOIN MMMountPath MP WITH (NOLOCK) ON MS.MediaSideId = MP.MediaSideId
		WHERE	RowId = 1) T

DROP TABLE #LibraryHardDrive
DROP TABLE #DrivePollLib
DROP TABLE #LibsByDiskCopy
DROP TABLE #MediaSideDataWritten
DROP TABLE #DataWritten
DROP TABLE #AppSize


SET @lib =( SELECT  libraryId			     AS '@LibraryId', 
 dbo.NormalizeForXML(libraryAliasName)	     AS '@LibraryName', 
CAST(totalValidDataGB  AS NUMERIC(20,2))	 AS '@totalValidDataGB', 
CAST(totalDataWrittenGB  AS NUMERIC(20,2))   AS '@totalDataWrittenGB', 
CAST(totalAppSizeGB   AS NUMERIC(20,2))		 AS '@totalAppSizeGB', 
CAST(totalFreeSpaceGB   AS NUMERIC(20,2))    AS '@totalFreeSpaceGB', 
					totalReserveSpaceMB		 AS '@totalReserveSpaceMB', 
CAST(totalSpaceGB   AS NUMERIC(20,2))		 AS '@totalSpaceGB', 
					lowWatermark             AS '@lowWatermark', 
					warningWatermark         AS '@warningWatermark',					
CAST(ISNULL(macroPrunableGB, 0) AS NUMERIC(20,2)) AS '@macroPrunableGB',
 avgConsumedMBPerDay AS '@avgConsumedMBPerDay',
 avgFreedMBPerDay    AS '@avgFreedMBPerDay'
			FROM #outTable ORDER BY LibraryId	
			for xml path('DiskLib'))
--SELECT * FROM #outTable
IF object_id('tempdb.dbo.#outTable') is not null DROP TABLE #outTable
SET @surveyXML = (
SELECT

@lib
FOR XML PATH ('DiskLibDetails') 
-- Replace <<RESULTS_NAME>> with specific name
)


--------- END SURVEY QUERY ---------

--------- 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')
)

--SELECT cast (@surveyXML as XML)

SET NOCOUNT OFF

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