--Name:- CapacityUsageHistory
--Description: Collecting capacity usage history for last 12 months plus the current month in one result and data size per agent types.

SET NOCOUNT ON

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

--------- BEGIN SURVEY QUERY ---------
DECLARE @sqlstr NVARCHAR(MAX)
DECLARE @LastCollectionAttrName VARCHAR(256)
DECLARE @isPrivateRunning varchar(256) = ''
DECLARE @freequencyMode	 NVARCHAR(MAX) = '0'

DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2)

DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END


IF object_id('tempdb.dbo.#ClientAgentSize') is not null DROP TABLE #ClientAgentSize
IF object_id('tempdb.dbo.#CopySize') is not null DROP TABLE #CopySize
IF object_id('tempdb.dbo.#DedupeCopy') is not null DROP TABLE #DedupeCopy
IF object_id('tempdb.dbo.#VMCopySize') is not null DROP TABLE #VMCopySize
IF object_id('tempdb.dbo.#PartialJobSize') is not null DROP TABLE #PartialJobSize
IF object_id('tempdb.dbo.#UsageHistory') is not null DROP TABLE #UsageHistory
IF object_id('tempdb.dbo.#PeakUsageDetailsAggregate') is not null DROP TABLE #PeakUsageDetailsAggregate
IF object_id('tempdb.dbo.#DailyUsage') is not null DROP TABLE #DailyUsage
IF object_id('tempdb.dbo.#LastCollection') is not null DROP TABLE #LastCollection

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	SELECT @freequencyMode = mode from #MetricsInputParams

IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'

IF ((@isPrivateRunning = 'Metrics Reporting') OR (@freequencyMode = '1'))
	SET @LastCollectionAttrName = 'CommservSurveyPrivateCapacityUsageLastCollectionTime'
else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3'))
	SET @LastCollectionAttrName = 'CommservSurveyDirectDipCapacityUsageLastCollectionTime'
ELSE
	SET @LastCollectionAttrName = 'CommservSurveyPublicCapacityUsageLastCollectionTime'

DECLARE @CurrCollectionDate DATE
DECLARE @PrevCollectionDate DATETIME

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @PrevCollectionDateSTR VARCHAR(256)='';
	EXEC GetSurveyParamValue @LastCollectionAttrName, @PrevCollectionDateSTR OUTPUT 
	SELECT @PrevCollectionDate = CAST(@PrevCollectionDateSTR AS DATE)
END
ELSE
BEGIN
	SELECT  @PrevCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATE) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName
END

IF @PrevCollectionDate IS NULL
BEGIN
	SET @PrevCollectionDate = DATEADD(MM, DATEDIFF(MM,0,GETDATE()) - 6, 0)
	SET @PrevCollectionDate = DATEADD(DD, -1, @PrevCollectionDate)
END

IF OBJECT_ID('IsInvalidTimeManaged', 'FS') IS NOT NULL
BEGIN
	WHILE dbo.IsInvalidTimeManaged(@PrevCollectionDate, @csTimeZone) = 1
		SET @PrevCollectionDate = DATEADD(HOUR, 1, @PrevCollectionDate)
END

SET @CurrCollectionDate = @PrevCollectionDate

CREATE TABLE #DailyUsage (
	AsOfDate INT, BackupSizeMB BIGINT, ArchiveSizeMB BIGINT, SnapshotSizeMB BIGINT, ReplicationSizeMB BIGINT)

CREATE TABLE #LastCollection (CollectionDate DATETIME, CollectionTime INT)

INSERT INTO #LastCollection
SELECT @PrevCollectionDate, dbo.GetUnixTime(dbo.LocalToUTCTime(@PrevCollectionDate, @csTimeZone))

-- Daily CommCell Capacity Usage
IF (OBJECT_ID('LicUsageDetailsHistory') IS NOT NULL)
BEGIN
	IF EXISTS (SELECT * FROM sys.columns WHERE name = N'SnapSize' AND OBJECT_ID = OBJECT_ID(N'LicUsageDetailsHistory'))
	BEGIN
		SET @sqlstr = N'
		DECLARE @PrevCollectionTime INT = (SELECT CollectionTime FROM #LastCollection)

		INSERT INTO #DailyUsage
		SELECT	date,
				(EnterpriseBackupSize + CoreBackupSize)/1024/1024, 
				(EnterpriseArchiveSize + CoreArchiveSize)/1024/1024,
				ISNULL(SnapSize,0)/1024/1024,
				ISNULL(ReplicationSize,0)/1024/1024
		FROM	LicUsageDetailsHistory
		WHERE	date > @PrevCollectionTime
		ORDER BY date'

		EXEC sp_executesql @sqlstr
	END
	ELSE
	BEGIN
		SET @sqlstr = N'
		DECLARE @PrevCollectionTime INT = (SELECT CollectionTime FROM #LastCollection)

		INSERT INTO #DailyUsage
		SELECT	date,
				(EnterpriseBackupSize + CoreBackupSize)/1024/1024, 
				(EnterpriseArchiveSize + CoreArchiveSize)/1024/1024,
				0,
				0
		FROM	LicUsageDetailsHistory
		WHERE	date > @PrevCollectionTime
		ORDER BY date'
		
		EXEC sp_executesql @sqlstr
	END
END

IF EXISTS (SELECT * FROM #DailyUsage)
	SELECT @CurrCollectionDate = dbo.UTCToLocalTime(dbo.GetDateTime(MAX(AsOfDate)), @csTimeZone) FROM #DailyUsage

-- Monthly CommCell Peak Capacity Usage
CREATE TABLE #UsageHistory (
	MonthDT DATETIME, EnterpriseBackupSizeMB BIGINT, CoreBackupSizeMB BIGINT, 
	EnterpriseArchiveSizeMB BIGINT, CoreArchiveSizeMB BIGINT, SnapSizeMB BIGINT, ReplicationSizeMB BIGINT)

CREATE TABLE #PeakUsageDetailsAggregate 
	(t_MonthStart DATETIME, t_BackupSizeMB BIGINT, t_ArchiveSizeMB BIGINT, t_SnapSizeMB BIGINT, t_ReplicationSizeMB BIGINT)

IF (OBJECT_ID('LicUsageDetailsHistory') IS NOT NULL)
BEGIN
	IF EXISTS (SELECT * FROM sys.columns WHERE name = N'SnapSize' AND OBJECT_ID = OBJECT_ID(N'LicUsageDetailsHistory'))
	BEGIN
		SET @sqlstr = N'
		DECLARE	@diff INT = DATEDIFF(SECOND, GETDATE(), GETUTCDATE())
		DECLARE	@now  INT = dbo.GetUnixTime(GETUTCDATE())

		INSERT INTO #UsageHistory
		SELECT	TOP 13 DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.GetDateTime(date - @diff)), 0) AS MonthDT,
				MAX(EnterpriseBackupSize + CoreBackupSize)/1024/1024, 
				0,
				MAX(EnterpriseArchiveSize + CoreArchiveSize)/1024/1024,
				0,
				ISNULL(MAX(SnapSize),0)/1024/1024,
				ISNULL(MAX(ReplicationSize),0)/1024/1024
		FROM	LicUsageDetailsHistory
		WHERE	date <= @now
		GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.GetDateTime(date - @diff)), 0)
		ORDER BY MonthDT DESC'

		EXEC sp_executesql @sqlstr
		
		IF EXISTS(SELECT TOP 1 MonthDT FROM #UsageHistory WHERE EnterpriseBackupSizeMB = 0 AND EnterpriseArchiveSizeMB = 0 AND SnapSizeMB = 0 AND ReplicationSizeMB = 0 )
		AND (OBJECT_ID('LicPeakUsageDetails') IS NOT NULL)
		BEGIN
			IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N'CapacityFlag' AND Object_ID = Object_ID(N'dbo.LicPeakUsageDetails'))
			BEGIN
				SET @sqlstr = N'
				INSERT INTO #PeakUsageDetailsAggregate
				SELECT MonthStart, ISNULL([0],0), ISNULL([1],0), ISNULL([2],0), ISNULL([3],0)
				FROM
				(SELECT MonthStart,nJobType,SUM(AppSizeMB) as AppSizeMB from LicPeakUsageDetails WITH (NOLOCK) WHERE MonthStart > = @startdate AND CapacityFlag = 0
				GROUP BY MonthStart, nJobType) T
				PIVOT ( MAX(AppSizeMB) FOR nJobType IN ( [0], [1], [2], [3]) ) AS P'
			END
			ELSE
			BEGIN
				SET @sqlstr = N'
				INSERT INTO #PeakUsageDetailsAggregate
				SELECT MonthStart, ISNULL([0],0), ISNULL([1],0), ISNULL([2],0), ISNULL([3],0)
				FROM
				(SELECT MonthStart,nJobType,SUM(AppSizeMB) as AppSizeMB from LicPeakUsageDetails WITH (NOLOCK) WHERE MonthStart > = @startdate
				GROUP BY MonthStart, nJobType) T
				PIVOT ( MAX(AppSizeMB) FOR nJobType IN ( [0], [1], [2], [3]) ) AS P'
			END
			DECLARE @monthstart DATETIME = '2017-09-01 00:00:00.000'
			DECLARE @ParmDefinition nvarchar(500); 
			SET @ParmDefinition = N'@startdate DATETIME'
			EXEC sp_executesql @sqlstr,@ParmDefinition, 
			@startdate = @monthstart

			SET @sqlstr = N'
			UPDATE #UsageHistory SET 
			EnterpriseBackupSizeMB = t_BackupSizeMB, EnterpriseArchiveSizeMB = t_ArchiveSizeMB, SnapSizeMB = t_SnapSizeMB, ReplicationSizeMB = t_ReplicationSizeMB
			FROM #PeakUsageDetailsAggregate WHERE t_MonthStart = MonthDT AND EnterpriseBackupSizeMB = 0'
			
			EXEC sp_executesql @sqlstr
		END			  
	END
	ELSE
	BEGIN
		SET @sqlstr = N'
		DECLARE	@diff INT = DATEDIFF(SECOND, GETDATE(), GETUTCDATE())
		DECLARE	@now  INT = dbo.GetUnixTime(GETUTCDATE())
		
		INSERT INTO #UsageHistory
		SELECT	TOP 13 DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.GetDateTime(date - @diff)), 0) AS MonthDT,
				MAX(EnterpriseBackupSize + CoreBackupSize)/1024/1024, 
				0,
				MAX(EnterpriseArchiveSize + CoreArchiveSize)/1024/1024,
				0,
				0,
				0
		FROM	LicUsageDetailsHistory
		WHERE	date <= @now
		GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.GetDateTime(date - @diff)), 0)
		ORDER BY MonthDT DESC'
		
		EXEC sp_executesql @sqlstr
	END
END


-- Data size per agent types and per client groups
CREATE TABLE #ClientAgentSize (clientId INT, appTypeId INT, dataReadMB FLOAT, dataWrittenMB FLOAT)

IF (COL_LENGTH('RptStorageUsage', 'allAppSize') IS NOT NULL)
BEGIN
	SET @sqlstr = N'
	DECLARE @MaxDate DATETIME = (SELECT MAX(startDT) FROM RptStorageUsage WITH (NOLOCK) WHERE type = 1)

	INSERT	INTO #ClientAgentSize
	SELECT	CASE WHEN U.vmClientId > 0 THEN U.vmClientId ELSE A.clientId END, A.appTypeId, 
			SUM(U.allAppSize)/1024.0/1024.0, SUM(U.allMediaSize)/1024.0/1024.0
	FROM	RptStorageUsage U WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK) ON U.appId = A.id
	WHERE	U.startDT = @MaxDate
	GROUP BY CASE WHEN U.vmClientId > 0 THEN U.vmClientId ELSE A.clientId END, A.appTypeId'

	EXEC sp_executesql @sqlstr
END
ELSE
BEGIN
	--------------------------------------------
	-- BEGIN of partial job size calculation --
	--------------------------------------------
	CREATE TABLE #PartialJobSize (jobId INT, commCellId INT, archGrpCopyId INT, appId INT, appType INT, 
			totalAppSizeMB BIGINT, totalBackupSizeMB BIGINT, partialBackupSizeMB BIGINT, dataWrittenMB BIGINT)

	INSERT	INTO #PartialJobSize
	SELECT	jobId, commCellId, archGrpCopyId, appId, 0, 0, 0, 0, SUM(sizeOnMedia)/1024/1024
	FROM	JMJobDataStats WITH (NOLOCK)
	WHERE	commCellId = 2 AND status IN (101,102,103) AND (disabled&256) = 0
	GROUP BY jobId, commCellId, archGrpCopyId, appId

	CREATE INDEX #PartialJobSize_jobId_commCellId_archGrpCopyId_idx ON #PartialJobSize (jobId, commCellId, archGrpCopyId)

	UPDATE	P SET dataWrittenMB += J.sizeOnMedia/1024/1024
	FROM	#PartialJobSize P
			INNER JOIN JMJobDataStats J WITH (NOLOCK) ON P.jobId = J.jobId AND P.commCellId = J.commCellId AND P.archGrpCopyId = J.archGrpCopyId
	WHERE	J.status = 100

	DELETE	#PartialJobSize WHERE dataWrittenMB = 0

	UPDATE	#PartialJobSize
	SET		appType = J.appType, totalAppSizeMB = J.totalUncompBytes/1024/1024, totalBackupSizeMB = J.totalBackupSize/1024/1024
	FROM	JMBkpStats J WITH (NOLOCK)
	WHERE	#PartialJobSize.jobId = J.jobId AND #PartialJobSize.commCellId = J.commCellId

	UPDATE	#PartialJobSize
	SET		appType = APP.appTypeId, totalAppSizeMB = J.totalBackupSize/1024/1024, totalBackupSizeMB = J.totalBackupSize/1024/1024
	FROM	JMAdminJobStatsTable J WITH (NOLOCK)
			INNER JOIN APP_Application APP WITH (NOLOCK) ON J.appID = APP.id
	WHERE	#PartialJobSize.jobId = J.jobId AND #PartialJobSize.commCellId = J.commCellId

	CREATE TABLE #PartialJobCopySize (jobId INT, commCellId INT, archGrpCopyId INT, partialBackupSizeMB BIGINT)

	INSERT	INTO #PartialJobCopySize
	SELECT J.jobId, J.commCellId, J.archGrpCopyId, SUM(AFC.physicalSize)/1024/1024
	FROM	#PartialJobSize J
			INNER JOIN archFile AF WITH (NOLOCK)
			ON AF.jobId = J.jobId AND AF.commCellId = J.commCellId
			INNER JOIN archFileCopy AFC WITH (NOLOCK) 
			ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId AND AFC.archCopyId = J.archGrpCopyId
	GROUP BY J.jobId, J.commCellId, J.archGrpCopyId

	UPDATE	#PartialJobSize
	SET		partialBackupSizeMB = T.partialBackupSizeMB
	FROM	#PartialJobCopySize T
	WHERE	#PartialJobSize.jobId = T.jobId AND #PartialJobSize.commCellId = T.commCellId AND #PartialJobSize.archGrpCopyId = T.archGrpCopyId
	--------------------------------------------
	-- END of partial job size calculation --
	--------------------------------------------

	--------------------------------------------
	-- BEGIN of copy size per app calculation --
	--------------------------------------------
	CREATE TABLE #CopySize (archGrpCopyId INT, appId INT, isAged INT, dataReadMB BIGINT, dataWrittenMB BIGINT)

	INSERT	INTO #CopySize
	SELECT	J.archGrpCopyId, J.appId, J.disabled&256, SUM(B.totalUncompBytes)/1024/1024, SUM(B.totalBackupSize)/1024/1024
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN JMJobDataStats J WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 1
	WHERE	J.archGrpId = B.dataArchGrpId AND J.status IN (100, 102, 103) AND J.commCellId = 2
	GROUP BY J.archGrpCopyId, J.appId, J.disabled&256

	INSERT	INTO #CopySize
	SELECT	J.archGrpCopyId, J.appId, J.disabled&256, SUM(B.totalUncompBytes)/1024/1024, SUM(B.totalBackupSize)/1024/1024
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN JMJobDataStats J WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 4
			LEFT OUTER JOIN JMJobDataStats K WITH (NOLOCK) ON K.jobId = J.jobId AND K.commCellId = J.commCellId AND K.archGrpCopyId = J.archGrpCopyId AND K.dataType = 1
	WHERE	J.archGrpId = B.logArchGrpId AND B.dataBackedUp = 0 AND J.status IN (100, 102, 103) AND K.jobId IS NULL
		AND J.commCellId = 2
	GROUP BY J.archGrpCopyId, J.appId, J.disabled&256

	INSERT	INTO #CopySize
	SELECT	J.archGrpCopyId, J.appId, J.disabled&256, SUM(A.totalBackupSize)/1024/1024, SUM(A.totalBackupSize)/1024/1024
	FROM	JMAdminJobStatsTable A WITH (NOLOCK)
			INNER JOIN JMJobDataStats J WITH (NOLOCK) ON J.jobId = A.jobId AND J.commCellId = A.commCellId AND J.dataType = 1 AND J.status IN (100, 102, 103)
	WHERE	J.commCellId = 2
	GROUP BY J.archGrpCopyId, J.appId, J.disabled&256

	INSERT	INTO #CopySize
	SELECT	archGrpCopyId, appId, 0, -SUM(1.0*totalAppSizeMB*(totalBackupSizeMB-partialBackupSizeMB)/totalBackupSizeMB), -SUM(totalBackupSizeMB-partialBackupSizeMB)
	FROM	#PartialJobSize
	WHERE	appType <> 106  AND totalBackupSizeMB > 0
	GROUP BY archGrpCopyId, appId
	--------------------------------------------
	-- END of copy size per app calculation --
	--------------------------------------------

	--------------------------------------------
	-- BEGIN of dedup ratio calculation --
	--------------------------------------------
	CREATE TABLE #DedupeCopy (
		archGrpCopyId INT, activeStoreId INT, dedupeFlags INT, 
		dataReadMB BIGINT, dataWrittenMB BIGINT, dedupRatio FLOAT, dedupSaving DECIMAL(10,2)
		)

	IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL
	BEGIN
		INSERT	INTO #DedupeCopy
		SELECT	id, CS.SIDBStoreId, dedupeFlags, dataReadMB, 0, 1, 0
		FROM	(SELECT archGrpCopyId, SUM(dataReadMB) AS dataReadMB FROM #CopySize GROUP BY archGrpCopyId) S 
				INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON S.archGrpCopyId = AGC.id
				INNER JOIN archCopySIDBStore CS WITH (READUNCOMMITTED) ON CS.copyId = AGC.id AND ((CS.flags & 6) = 6) /*IDX_COPY_DDB_DEFAULT = 2, IDX_COPY_DDB_ACTIVE = 4)*/
		WHERE	((AGC.dedupeFlags & 262144) > 0)
	END
	ELSE
	BEGIN
		INSERT	INTO #DedupeCopy
		SELECT	id, CS.SIDBStoreId, dedupeFlags, dataReadMB, 0, 1, 0
		FROM	(SELECT archGrpCopyId, SUM(dataReadMB) AS dataReadMB FROM #CopySize GROUP BY archGrpCopyId) S 
				INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON S.archGrpCopyId = AGC.id
				INNER JOIN archCopySIDBStore CS WITH (READUNCOMMITTED) ON CS.copyId = AGC.id --AND ((CS.flags & 6) = 6) /*IDX_COPY_DDB_DEFAULT = 2, IDX_COPY_DDB_ACTIVE = 4)*/
				INNER JOIN IdxSIDBStore SS WITH (READUNCOMMITTED) ON CS.SIDBStoreId = SS.SIDBStoreId AND SS.SealedTime = 0
		WHERE	((AGC.dedupeFlags & 262144) > 0)
	END
	-- Calculate average dedupe ratio for each torage policy copy including active and sealed dedup stores.
	-- All storage policy copies using the same GDSP have same dedup ratio.
	-- Include all volumes except physically deleted volumes for calculating dedup ratio.
	;WITH CopyWriteSize (activeStoreId, dataWrittenMB) AS
	(
		SELECT	ActiveStoreId, SUM(SS.PhysicalBytesMB)
		FROM	(SELECT DISTINCT C.SIDBStoreId ActiveStoreId, S.SIDBStoreId SIDBStoreId
				FROM archGroupCopy C WITH (NOLOCK) INNER JOIN archCopySIDBStore S WITH (NOLOCK) ON C.id = S.CopyId
				) T
				INNER JOIN (
					SELECT	MediaSideId, SIDBStoreId, SUM(CAST(V.PhysicalBytesMB AS BIGINT)) AS PhysicalBytesMB
					FROM	MMVolume V WITH (NOLOCK)
					WHERE	SIDBStoreId > 0 AND (Attributes & 512) = 0 AND SiloStatus <> 3
					GROUP BY MediaSideId, SIDBStoreId
				) SS ON T.SIDBStoreId = SS.SIDBStoreId
		GROUP BY ActiveStoreId
	)

	UPDATE #DedupeCopy 
	SET dedupRatio = 1.0*W.dataWrittenMB/R.dataReadMB
	FROM
		(SELECT	activeStoreId, SUM(dataReadMB) AS dataReadMB
			FROM	#DedupeCopy
			GROUP BY activeStoreId) R
			INNER JOIN CopyWriteSize W ON W.activeStoreId = R.activeStoreId
	WHERE	#DedupeCopy.activeStoreId = R.activeStoreId AND R.dataReadMB > 0 AND W.dataWrittenMB < R.dataReadMB

	UPDATE	#CopySize SET dataWrittenMB = (R.dataReadMB*C.dedupRatio)
	FROM	#CopySize R
			INNER JOIN #DedupeCopy C ON R.archGrpCopyId = C.archGrpCopyId AND C.dedupRatio < 1
	--------------------------------------------
	-- END of dedup ratio calculation --
	--------------------------------------------

	INSERT	INTO #ClientAgentSize
	SELECT	APP.clientId, APP.appTypeId, SUM(dataReadMB), SUM(dataWrittenMB)
	FROM	#CopySize C
			INNER JOIN APP_Application APP WITH (NOLOCK) ON C.appId = APP.id AND APP.appTypeId <> 106
	WHERE C.isAged = 0
	GROUP BY APP.clientId, APP.appTypeId

	DROP TABLE #CopySize

	--------------------------------------------
	-- BEGIN of VM size calculation --
	--------------------------------------------
	CREATE TABLE #VMCopySize (archGrpCopyId INT, vmClientId INT, isAged INT, dataReadMB BIGINT, dataWrittenMB BIGINT)

	INSERT	INTO #VMCopySize
	SELECT	J.archGrpCopyId, V.VMclientId, J.disabled&256, 
			SUM(CAST(V.attrVal AS BIGINT))/1024/1024 as dataReadMB, 
			SUM((1.0*B.totalBackupSize/B.totalUncompBytes)*CAST(V.attrVal AS BIGINT))/1024/1024 as dataWrittenMB
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN JMJobDataStats J WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType = 1
			INNER JOIN APP_VMProp V WITH (NOLOCK) ON V.jobId = B.jobId AND V.commCellId = B.commCellId 
						AND V.attrName = 'vmUsedSpace' AND V.attrVal <> '0'
			LEFT OUTER JOIN APP_VMProp X WITH (NOLOCK) ON X.jobId = V.jobId AND X.commCellId = V.commCellId AND X.VMclientId = V.VMclientId
						AND X.attrName = 'vmStatus' AND X.attrVal NOT IN ('0', '3')
	WHERE	J.status = 100	AND J.commCellId = 2
		AND B.totalUncompBytes > 0
		AND X.jobId IS NULL
	GROUP BY J.archGrpCopyId, V.VMclientId, J.disabled&256

	IF EXISTS (SELECT jobId FROM #PartialJobSize WHERE appType = 106)
	BEGIN
		INSERT	INTO #VMCopySize
		SELECT	P.archGrpCopyId, V.VMclientId, 0, 
				SUM((1.0*P.partialBackupSizeMB/P.totalBackupSizeMB)*CAST(V.attrVal AS BIGINT))/1024/1024, 
				SUM((1.0*P.partialBackupSizeMB/P.totalAppSizeMB)*CAST(V.attrVal AS BIGINT))/1024/1024
		FROM	#PartialJobSize P WITH (NOLOCK)
				INNER JOIN APP_VMProp V WITH (NOLOCK) ON V.jobId = P.jobId AND V.commCellId = P.commCellId 
							AND V.attrName = 'vmUsedSpace' AND V.attrVal <> '0'
				LEFT OUTER JOIN APP_VMProp X WITH (NOLOCK) ON X.jobId = V.jobId AND X.commCellId = V.commCellId AND X.VMclientId = V.VMclientId
							AND X.attrName = 'vmStatus' AND X.attrVal NOT IN ('0', '3')
		WHERE	P.totalAppSizeMB > 0 AND P.totalBackupSizeMB > 0
			AND X.jobId IS NULL
		GROUP BY P.archGrpCopyId, V.VMclientId
	END

	UPDATE	#VMCopySize SET dataWrittenMB = (R.dataReadMB*C.dedupRatio)
	FROM	#VMCopySize R
			INNER JOIN #DedupeCopy C ON R.archGrpCopyId = C.archGrpCopyId AND C.dedupRatio < 1

	INSERT	INTO #ClientAgentSize 
	SELECT	vmClientId, 106, SUM(dataReadMB), SUM(dataWrittenMB) 
	FROM	#VMCopySize 
	WHERE	isAged = 0
	GROUP BY vmClientId
	--------------------------------------------
	-- END of VM size calculation --
	--------------------------------------------

	DROP TABLE #PartialJobCopySize
	DROP TABLE #PartialJobSize
	DROP TABLE #DedupeCopy
END


SET @surveyXML=(
	SELECT
		(SELECT	dbo.UTCToLocalTime(dbo.GetDateTime(AsOfDate), @csTimeZone) AS '@AsOfDate',
			BackupSizeMB AS '@BackupSizeMB', 
			ArchiveSizeMB AS '@ArchiveSizeMB',
			SnapshotSizeMB AS '@SnapshotSizeMB',
			ReplicationSizeMB AS '@ReplicationSizeMB'
		FROM	#DailyUsage
		FOR XML PATH('CommCellCapacityUsage'), TYPE),

		(SELECT	MonthDT AS '@Month',
			EnterpriseBackupSizeMB AS '@EnterpriseBackupSizeMB', 
			CoreBackupSizeMB AS '@CoreBackupSizeMB',
			EnterpriseArchiveSizeMB AS '@EnterpriseArchiveSizeMB',
			CoreArchiveSizeMB AS '@CoreArchiveSizeMB',
			SnapSizeMB AS '@SnapSizeMB',
			ReplicationSizeMB AS '@ReplicationSizeMB'
		FROM	#UsageHistory
		FOR XML PATH('CapacityUsageHistory'), TYPE),

		(SELECT	S.appTypeId AS '@AppTypeId', 
			(CASE WHEN I.type = 38 AND C.simOperatingSystemId = 118 THEN 'IBM-ISeries' WHEN I.type = 38 AND C.simOperatingSystemId = 49 THEN 'OpenVMS' ELSE I.name END) AS '@AppTypeName', 
			CAST(SUM(S.dataReadMB) AS BIGINT) AS '@AppSizeMB', 
			CAST(SUM(S.dataWrittenMB) AS BIGINT) AS '@SizeOnMediaMB',
			COUNT(DISTINCT S.clientId) AS '@NumOfClients'
		FROM	#ClientAgentSize S
			INNER JOIN APP_Client C ON S.ClientId = C.id
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON S.appTypeId = I.type
		GROUP BY S.appTypeId, 
			(CASE WHEN I.type = 38 AND C.simOperatingSystemId = 118 THEN 'IBM-ISeries' WHEN I.type = 38 AND C.simOperatingSystemId = 49 THEN 'OpenVMS' ELSE I.name END)
		FOR  XML PATH('AgentDataSize'), TYPE),

		(SELECT	ISNULL(CGA.clientGroupId, 0) AS '@ClientGroupId', 
			CAST(SUM(S.dataReadMB) AS BIGINT) AS '@AppSizeMB', 
			CAST(SUM(S.dataWrittenMB) AS BIGINT) AS '@SizeOnMediaMB',
			COUNT(DISTINCT S.clientId) AS '@NumOfClients'
		FROM	#ClientAgentSize S
			LEFT OUTER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId
		GROUP BY ISNULL(CGA.clientGroupId, 0)
		FOR  XML PATH('ClientGroupDataSize'), TYPE)
	FOR XML PATH('')
)

DROP TABLE #ClientAgentSize
DROP TABLE #UsageHistory
DROP TABLE #DailyUsage
DROP TABLE #LastCollection
DROP TABLE #PeakUsageDetailsAggregate


--------- 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')
)
	 
END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

--Update the last collection time
IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	EXEC SetSurveyParamValue @LastCollectionAttrName, @CurrCollectionDate
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
		UPDATE GXGlobalParam SET value = CAST(@CurrCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName
	ELSE
	BEGIN
		IF @nreleaseId >= 15
		BEGIN
			SET @sqlstr = N'insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(@CurrCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @sqlstr
		END
		ELSE
		BEGIN
			insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(@CurrCollectionDate as NVARCHAR(MAX)))
		END
	END
END

SET NOCOUNT OFF

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