--- Please follow the below comments to insert SQL statements.

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

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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



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

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

--	+===============================================+
--	|  Client Info:							|
--	+===============================================+	 
DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client  where id =2)
 
DECLARE @nLastdays INT = 30
DECLARE @nTopN INT = 25
DECLARE @TotalSize	BIGINT
DECLARE @1TB FLOAT = 1024.0*1024.0*1024.0*1024.0
DECLARE @MinSizeTB INT = 1

DECLARE @TimeRangeBegin INT = dbo.GetUnixTime(DATEADD(DAY, -@nLastDays, GETUTCDATE()))
DECLARE @TimeRangeEnd int = 0x7FFFFFFF

DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        

SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
  
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
	if(@groupId > 0)
	BEGIN
		insert into @includeGroups values(@groupId);
		DECLARE @tmpStr NVARCHAR(100);
		SELECT @tmpStr = NAME FROM APP_ClientGroup WHERE id = @groupId;
		IF(@tmpStr IS NOT NULL)
			SET @groupStrName = @groupStrName + ', ' + @tmpStr
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0

IF object_id('tempdb.dbo.#ExcludedAppIds') is not null DROP TABLE #ExcludedAppIds 
CREATE TABLE #ExcludedAppIds (appId INT)

INSERT INTO #ExcludedAppIds
SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) 
WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') 
  AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'))
  AND attrVal <> '0' AND modified = 0
UNION
SELECT id FROM APP_Application WITH (NOLOCK) 
WHERE clientId IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1' AND modified = 0)



CREATE TABLE #LastFullJob (clientId INT, appTypeId INT, appId INT, jobId INT)

INSERT INTO #LastFullJob
SELECT	A.clientId, A.appTypeId, B.appId, B.jobId
FROM 	APP_Application A WITH (NOLOCK) INNER JOIN (
	SELECT	appId, MAX(jobId) AS jobId
	FROM	JMBkpStats WITH (NOLOCK)
	WHERE	servEndDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd
		AND status IN (1, 3, 14) AND dataStatus = 0 AND commCellId = 2
		AND bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
	GROUP BY appId)  B ON B.appId = A.id
	LEFT OUTER JOIN #ExcludedAppIds E ON A.id = E.appId
WHERE	E.appId IS NULL


DECLARE @includeClients TABLE (clientId INT);


IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
BEGIN
	SET @specialGroupExists =1
	
	INSERT INTO @includeClients
	SELECT DISTINCT CGA.clientId 
	FROM @includeGroups CG INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientGroupId = CG.groupID	
END




DECLARE @LargestFSClients INT = 0
DECLARE @LargestNoArchiveFSClients INT = 0
DECLARE @noArchiveClients XML

DECLARE @LargestAppClients INT = 0
DECLARE @LargestNoSnapAppClients INT = 0
DECLARE @noSnapClients XML
DECLARE @AvgRestoreThroughput  FLOAT = 0

DECLARE @LargestEmailClients INT = 0
DECLARE @LargestNoArchiveEmailClients INT = 0
DECLARE @noArchiveEmailClients XML
DECLARE @clients XML
DECLARE @countsBySize XML

	
DECLARE @SpVersion BIGINT = 0
SELECT @SpVersion = CAST(revision AS BIGINT) FROM GxDBVersions where name = 'QS_CommCellUsageReport' AND ISNUMERIC(revision) = 1


DECLARE @Version INT 
SELECT TOP 1 @Version = CAST(release AS FLOAT) FROM simAllGalaxyRel WITH (NOLOCK) ORDER BY id DESC

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmpCommCellUsage'))
	DROP TABLE #tmpCommCellUsage

CREATE TABLE #tmpCommCellUsage (
	EnterpriseBackupSize BIGINT, CoreBackupSize BIGINT, EnterpriseArchiveSize BIGINT, CoreArchiveSize BIGINT, 
	SnapshotSize BIGINT, ReplicationSize BIGINT, nJobType INT, 
	appId INT, jobID INT, ClientName NVARCHAR(255), ClientId INT, AppTypeId INT, AppTypeName VARCHAR(255), 
	BackupSetName NVARCHAR(128), InstanceName NVARCHAR(512), SubclientName NVARCHAR(128), 
	SPId INT, SPName NVARCHAR(144), UncompBytes BIGINT, DedupEnabled INT, SecondaryEncryption INT, bEnterprise INT
)


IF @Version=11
BEGIN 
	DECLARE @LastCollectionDate DATETIME 
	SET  @LastCollectionDate=(SELECT MAX(StartDt)  from RptCapacityUsage)

	INSERT	#tmpCommCellUsage(appId, jobID, ClientName, ClientId, AppTypeId, AppTypeName, UncompBytes)
	SELECT	U.appId, 0, C.name, C.id, A.appTypeId, '', U.FrontEndSize  
	FROM	RptCapacityUsage U 
			INNER JOIN App_Application A ON A.id = U.appId AND A.AppTypeID <> 106
			INNER JOIN App_CLient C ON C.id = A.clientid 
	WHERE	U.startDT = @LastCollectionDate AND U.Type = 1 AND U.jobType IN (0,1)
	UNION ALL
	SELECT	U.appId, 0, C.name, U.vmClientId, 106, '', U.FrontEndSize  
	FROM	RptCapacityUsage U 
			INNER JOIN App_CLient C ON C.id = U.vmClientId AND U.vmClientId > 0
	WHERE	U.startDT = @LastCollectionDate AND U.Type = 1 AND U.jobType IN (0,1)

	SET @TotalSize = ISNULL((SELECT SUM(UncompBytes) FROM #tmpCommCellUsage), 0)
END 
ELSE 
BEGIN 
	IF @SpVersion > 0 
	BEGIN
		IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
		BEGIN
			ALTER TABLE #tmpCommCellUsage DROP COLUMN SnapshotSize
			ALTER TABLE #tmpCommCellUsage DROP COLUMN ReplicationSize
			ALTER TABLE #tmpCommCellUsage DROP COLUMN InstanceName
		END
		ELSE
		IF @SpVersion <= 10031000800050000 --10.0 SP4 and Below
		BEGIN
			ALTER TABLE #tmpCommCellUsage DROP COLUMN SnapshotSize
			ALTER TABLE #tmpCommCellUsage DROP COLUMN ReplicationSize
		END
	END
	INSERT INTO #tmpCommCellUsage
	EXEC QS_CommCellUsageReport '', 0, 1

	SET	@TotalSize = (SELECT top 1 (EnterpriseBackupSize+CoreBackupSize+EnterpriseArchiveSize+CoreArchiveSize) FROM #tmpCommCellUsage)
END 


ALTER TABLE #tmpCommCellUsage ADD servEndDate INT NULL
ALTER TABLE #tmpCommCellUsage ADD status INT NULL
ALTER TABLE #tmpCommCellUsage ADD bkpLevel INT NULL
ALTER TABLE #tmpCommCellUsage ADD dataStatus INT NULL


	UPDATE #tmpCommCellUsage 
		SET servEndDate = B.servEndDate,
		status = B.status,
		bkpLevel = B.bkpLevel,
		dataStatus = B.dataStatus
	FROM 
		#tmpCommCellUsage R
	INNER JOIN JMBkpStats B ON 
	R.jobId = B.jobId


DECLARE @tblsnapClients	TABLE 
(
	clientId int, 
	clientName nvarchar(255), 
	appTypeId int, 
	appTypeName nvarchar(255), 
	appSizeTB float, 
	lastRestoreThroughput bigint,
	recoveryDuration bigint
)



IF  (@specialGroupExists =1 )
BEGIN
	DELETE	T FROM #tmpCommCellUsage T 
			LEFT OUTER JOIN @includeClients I ON T.clientId = I.clientId
	WHERE	I.clientId IS NULL

	DELETE	T FROM #LastFullJob T 
			LEFT OUTER JOIN @includeClients I ON T.clientId = I.clientId
	WHERE	I.clientId IS NULL
END

CREATE TABLE #ClientSize (ClientId INT, FrontEndSizeGB BIGINT)
	
INSERT	INTO #ClientSize
SELECT	ClientId, SUM(UncompBytes)/1024/1024/1024
FROM	#tmpCommCellUsage
GROUP BY ClientId

SET @countsBySize = 
(
	SELECT	LowerLimitGB AS '@LowerLimitGB', 
			UpperLimitGB AS '@UpperLimitGB', 
			Range AS '@Range', 
			COUNT(ClientId) AS '@ClientCount', 
			SUM(FrontEndSizeGB) AS '@TotalSizeGB', 
			AVG(FrontEndSizeGB) AS '@AverageSizeGB'
	FROM	(SELECT 0 AS LowerLimitGB, 1 AS UpperLimitGB, '<1GB' AS Range
		UNION ALL SELECT 1, 10, '1GB-10GB' 	UNION ALL SELECT 10, 100, '10GB-100GB' 
		UNION ALL SELECT 100, 500, '100GB-500GB' UNION ALL SELECT 500, 1000, '500GB-1TB' 
		UNION ALL SELECT 1000, 10000, '1TB-10TB' UNION ALL SELECT 10000, 9223372036854775807, '>10TB') Buckets
			LEFT OUTER JOIN #ClientSize ON FrontEndSizeGB >= LowerLimitGB AND FrontEndSizeGB < UpperLimitGB
	GROUP BY LowerLimitGB, UpperLimitGB, Range
	ORDER BY LowerLimitGB DESC
	FOR XML PATH ('CountsBySize')
)
DROP TABLE #ClientSize

IF OBJECT_ID('tempdb..#TenantClients') IS NOT NULL DROP TABLE #TenantClients
CREATE TABLE #TenantClients (
       companyId INT,
       clientId INT
)

INSERT INTO #TenantClients
SELECT attrVal, 
	   componentNameId
FROM APP_ClientProp WITH (NOLOCK)
WHERE attrName = 'Installation Company Id' AND attrVal <> '0' AND modified = 0 AND LEN(attrVal) <= 10 AND ISNUMERIC(attrVal) = 1

    SET @clients = (
		SELECT * FROM (
			SELECT  U.clientId AS '@clientId' ,
					ISNULL(U.AppTypeId,0) AS '@appTypeId',
					clientName AS '@clientName',
					cast(sum(UncompBytes)/1024.0/1024.0/1024.0 AS DECIMAL(16,6)) AS '@applicationSizeGB',
					CASE 
						WHEN @TotalSize = 0 THEN 0
						ELSE cast(100.0*sum(UncompBytes)/@TotalSize AS DECIMAL(16,6)) 
					END AS '@ShareOfTotal',
					CAST(@TotalSize/1024.0/1024.0/1024.0 AS DECIMAL(16,6)) AS '@TotalSizeGB' 
			FROM #tmpCommCellUsage U
			INNER JOIN  (
				SELECT  TOP (@nTopN)
					CU.ClientId
				FROM    #tmpCommCellUsage CU
				INNER JOIN (
					SELECT jobId, appId, ROW_NUMBER() OVER(PARTITION BY appId ORDER BY jobId DESC) AS 'Row' FROM #tmpCommCellUsage 
				) SUB
				ON SUB.jobId = CU.jobId AND SUB.Row = 1 AND SUB.appId = CU.appId
				GROUP BY CU.ClientId
				ORDER BY sum(UncompBytes) DESC 
			) T
			ON T.ClientId = U.ClientId
			GROUP BY U.clientId, U.AppTypeId, clientName
			UNION
			SELECT  U.clientId AS '@clientId' ,
					ISNULL(U.AppTypeId,0) AS '@appTypeId',
					clientName AS '@clientName',
					cast(sum(UncompBytes)/1024.0/1024.0/1024.0 AS DECIMAL(16,6)) AS '@applicationSizeGB',
					CASE 
						WHEN @TotalSize = 0 THEN 0
						ELSE cast(100.0*sum(UncompBytes)/@TotalSize AS DECIMAL(16,6)) 
					END AS '@ShareOfTotal',
					CAST(@TotalSize/1024.0/1024.0/1024.0 AS DECIMAL(16,6)) AS '@TotalSizeGB' 
			FROM #tmpCommCellUsage U
			INNER JOIN  (
				SELECT ClientId FROM (
					SELECT  TC.companyId,
							CU.ClientId,
							ROW_NUMBER() OVER(PARTITION BY companyId ORDER BY SUM(UncompBytes) DESC) AS 'Rank'
					FROM    #tmpCommCellUsage CU
					INNER JOIN (
						SELECT jobId, appId, ROW_NUMBER() OVER(PARTITION BY appId ORDER BY jobId DESC) AS 'Row' FROM #tmpCommCellUsage 
					) SUB
					ON SUB.jobId = CU.jobId AND SUB.Row = 1 AND SUB.appId = CU.appId
					INNER JOIN #TenantClients TC ON TC.clientId = CU.ClientId
					GROUP BY TC.companyId, CU.ClientId
				) A
				WHERE A.Rank <= @nTopN
			) T
			ON T.ClientId = U.ClientId
			GROUP BY U.clientId, U.AppTypeId, clientName
		) LargestClients
        FOR XML PATH ('Client')
    )

IF OBJECT_ID('tempdb..#TenantClients') IS NOT NULL DROP TABLE #TenantClients

	SELECT @LargestFSClients = COUNT(*) FROM
	(	SELECT A.clientId
		FROM #LastFullJob A 
			INNER JOIN JMBkpStats B 
				ON B.jobId = A.jobId AND B.commCellId = 2
				AND A.appTypeId IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) /*File System Agents*/
		GROUP BY A.clientId
		HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB) AS sub

	SELECT @LargestNoArchiveFSClients = COUNT(sub.clientId) FROM
	(	SELECT A.clientId 
		FROM #LastFullJob A
			INNER JOIN JMBkpStats B ON B.jobId = A.jobId AND B.commCellId = 2
			INNER JOIN APP_Client C
				ON C.id = A.clientId
				AND C.id NOT IN (SELECT clientId FROM APP_IDAName WHERE appTypeId IN (58, 66, 73)) /*No FS Archiver Agent installed*/
			INNER JOIN APP_IDAName IDA
				ON IDA.clientId = A.clientId
				AND IDA.appTypeId = A.appTypeId
				AND A.appTypeId IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) /*File System Agents*/
			LEFT OUTER JOIN APP_IDAProp IDAP /*OnePass Disabled*/
				ON IDAP.componentNameId = IDA.id
				AND IDAP.attrName = 'Enable Archive' 
				AND IDAP.modified = 0
		WHERE ISNULL(IDAP.attrVal, '0') = '0'
		GROUP BY A.clientId
		HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB) AS sub

	SET @noArchiveClients =
	(
		SELECT TOP (@nTopN)
			A.clientId AS '@ClientId', 
			dbo.NormalizeForXML(C.name) AS '@ClientName',
			A.appTypeId AS '@AppTypeId',
			I.name AS '@AppTypeName',			
			SUM(B.totalUnCompBytes)/@1TB AS '@AppSizeTB'
		FROM #LastFullJob A
			INNER JOIN JMBkpStats B ON B.jobId = A.jobId AND B.commCellId = 2
			INNER JOIN APP_Client C
				ON C.id = A.clientId
				AND C.id NOT IN (SELECT clientId FROM APP_IDAName WHERE appTypeId IN (58, 66, 73)) /*No FS Archiver Agent installed*/
			INNER JOIN APP_IDAName IDA
				ON IDA.clientId = A.clientId
				AND IDA.appTypeId = A.appTypeId
				AND A.appTypeId IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) /*File System Agents*/
			INNER JOIN APP_iDAType I
				ON I.type = A.appTypeId				
			LEFT OUTER JOIN APP_IDAProp IDAP /*OnePass Disabled*/
				ON IDAP.componentNameId = IDA.id
				AND IDAP.attrName = 'Enable Archive' 
				AND IDAP.modified = 0
		WHERE ISNULL(IDAP.attrVal, '0') = '0'
		GROUP BY A.clientId, dbo.NormalizeForXML(C.name), A.appTypeId, I.name
		HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB
		ORDER BY SUM(B.totalUnCompBytes) DESC
		FOR XML PATH('NoArchiveClients')
	)
	 
	SELECT @LargestAppClients = COUNT(DISTINCT sub.clientId) FROM
	(SELECT A.clientId, A.appTypeId 
	FROM #LastFullJob A
		INNER JOIN JMBkpStats B ON B.jobId = A.jobId AND B.commCellId = 2
		INNER JOIN APP_iDAType IDA
			ON IDA.type = A.appTypeId
			AND A.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) /*File System Agents*/
	GROUP BY A.clientId, A.appTypeId, IDA.name
	HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB
	) AS sub

	SELECT @LargestNoSnapAppClients = COUNT(*) FROM
	(SELECT DISTINCT
		A.clientId AS 'ClientId', 
		dbo.NormalizeForXML(C.name) AS 'ClientName',
		A.appTypeId AS 'AppTypeId',
		IDA.name AS 'AppTypeName'
	FROM APP_Application A 
		INNER JOIN APP_Client C
			ON C.id = A.clientId
		INNER JOIN APP_iDAType IDA
			ON IDA.type = A.appTypeId
			AND A.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) /*File System Agents*/
		INNER JOIN #tmpCommCellUsage B 
			ON A.id = B.appId
			AND	B.servEndDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd
			AND B.servEndDate = (SELECT MAX(servEndDate) FROM JMBkpStats WITH (NOLOCK) WHERE appId = A.id AND status IN (1, 3, 14) AND bkpLevel = 1 AND dataStatus = 0)
			AND B.status IN (1, 3, 14) 
			AND B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
			AND B.dataStatus = 0			
		LEFT OUTER JOIN APP_ClientProp CP /*No IntelliSanp*/
			ON CP.componentNameId = A.clientId
			AND CP.attrName = 'Snap Backups Enabled' 
			AND CP.modified = 0			
	WHERE 	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
		AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
		AND A.subclientStatus&(16|32) = 0 
		AND ISNULL(CP.attrVal, '0') = '0'
	GROUP BY A.clientId, dbo.NormalizeForXML(C.name), A.appTypeId, IDA.name
	HAVING MAX(B.UncompBytes)/@1TB >= @MinSizeTB
	) AS sub

	INSERT INTO @tblsnapClients
		SELECT TOP (@nTopN)
			A.clientId, 
			dbo.NormalizeForXML(C.name),
			A.appTypeId,
			IDA.name,
			MAX(B.UncompBytes)/@1TB,
			0, 0
		FROM APP_Application A 
			INNER JOIN APP_Client C
				ON C.id = A.clientId
			INNER JOIN APP_iDAType IDA
				ON IDA.type = A.appTypeId
				AND A.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) /*File System Agents*/
			INNER JOIN #tmpCommCellUsage B 
				ON A.id = B.appId
				AND	B.servEndDate BETWEEN @TimeRangeBegin AND @TimeRangeEnd
				AND B.servEndDate = (SELECT MAX(servEndDate) FROM JMBkpStats WITH (NOLOCK) WHERE appId = A.id AND status IN (1, 3, 14) AND bkpLevel = 1 AND dataStatus = 0)
				AND B.status IN (1, 3, 14) 
				AND B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
				AND B.dataStatus = 0
			LEFT OUTER JOIN APP_ClientProp CP /*No IntelliSnap*/
				ON CP.componentNameId = A.clientId
				AND CP.attrName = 'Snap Backups Enabled' 
				AND CP.modified = 0
		WHERE 	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
			AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
			AND A.subclientStatus&(16|32) = 0 
			AND ISNULL(CP.attrVal, '0') = '0'
		GROUP BY A.clientId, dbo.NormalizeForXML(C.name), A.appTypeId, IDA.name
		HAVING MAX(B.UncompBytes)/@1TB >= @MinSizeTB
		ORDER BY MAX(B.UncompBytes) DESC

	UPDATE  @tblsnapClients
		SET lastRestoreThroughput = CASE WHEN ISNULL(duration, 0) = 0 THEN 0 ELSE (isnull(convert(bigint,sub1.totUncompBytes_h)*2147483648 +  convert(bigint,sub1.totUncompBytes_l),0) * 60 * 60)/duration END
		, recoveryDuration = duration
	FROM @tblsnapClients T INNER JOIN
		(SELECT sub.appType, sub.clientId, sub.servStartTime, JR.servEndTime, JR.totUncompBytes_l, JR.totUncompBytes_h, duration
		FROM 
			(SELECT J.appType, J.srcClientId AS clientId, MAX(J.servStartTime) AS servStartTime
			FROM @tblsnapClients T INNER JOIN
				JMRestoreStats J WITH (NOLOCK)ON
				T.appTypeId =  J.appType
				AND T.clientId = J.srcClientId
			GROUP BY J.appType, J.srcClientId) sub
				INNER JOIN JMRestoreStats JR ON
				JR.servStartTime = sub.servStartTime
				AND JR.appType = sub.appType
				AND JR.srcClientId = sub.clientId) sub1
			ON T.appTypeId = sub1.appType
			AND T.clientId = sub1.clientId
			

	SET @noSnapClients =
		(
		SELECT TOP (@nTopN)
			clientId AS '@ClientId', 
			dbo.NormalizeForXML(clientname) AS '@ClientName',
			appTypeId AS '@AppTypeId',
			appTypeName AS '@AppTypeName',
			appSizeTB AS '@AppSizeTB',
			lastRestoreThroughput/(1024.0*1024.0*1024.0) AS '@LastRestoreThroughputGBPerHR',
			recoveryDuration AS '@RecoverDurationTimeInSeconds'		
		FROM @tblsnapClients
		FOR XML PATH('NoSnapClients')
		)

	SELECT
		@AvgRestoreThroughput = 
		CASE 
			WHEN ISNULL(SUM((convert(bigint,J.totUncompBytes_h)*2147483648 +  convert(bigint,J.totUncompBytes_l))), 0) = 0 OR ISNULL(SUM(CONVERT(bigint,duration)), 0) = 0 THEN 0
			ELSE (SUM(convert(bigint,J.totUncompBytes_h)*2147483648 +  convert(bigint,J.totUncompBytes_l))*60.0*60.0/(SUM(CONVERT(bigint,duration)) * (1024.0 * 1024.0 * 1024.0)))
		END
	FROM JMRestoreStats J

	SELECT @LargestEmailClients = COUNT(sub.clientId) FROM
	(SELECT A.clientId
	FROM #LastFullJob A
		INNER JOIN JMBkpStats B ON B.jobId = A.jobId AND B.commCellId = 2
	WHERE A.appTypeId IN (54, 60, 52) /*Exchange MB and Lotus Notes MB*/
	GROUP BY A.clientId
	HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB) AS sub

	SELECT @LargestNoArchiveEmailClients = COUNT(sub.clientId) FROM
	(	SELECT A.clientId 
		FROM #LastFullJob A
			INNER JOIN JMBkpStats B ON B.jobId = A.jobId AND B.commCellId = 2
			INNER JOIN APP_Client C
				ON C.id = A.clientId
				AND C.id NOT IN (SELECT clientId FROM APP_IDAName WHERE appTypeId IN (45, 56, 57, 67, 90)) /*No Exchange or Lotus Notes Archiver Agent installed*/
				AND A.appTypeId IN (54, 60, 52) /*Exchange MB and Lotus Notes MB*/
			LEFT OUTER JOIN APP_SubClientProp SCP /*OnePass Disabled*/
				ON SCP.componentNameId = A.appId
				AND SCP.attrName = 'Turbo Mode Enable Stubbing' 
				AND SCP.cs_attrName = CHECKSUM(N'Turbo Mode Enable Stubbing') 
				AND SCP.modified = 0
		WHERE ISNULL(SCP.attrVal, '0') = '0'
		GROUP BY A.clientId
		HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB) AS sub

	SET @noArchiveEmailClients =
	(
		SELECT TOP (@nTopN)
			A.clientId AS '@ClientId', 
			dbo.NormalizeForXML(C.name) AS '@ClientName',
			A.appTypeId AS '@AppTypeId',
			I.name AS '@AppTypeName',			
			SUM(B.totalUnCompBytes)/@1TB AS '@AppSizeTB',
			SUM(convert(bigint,B.totalWriteTime))/(60.0*60.0) AS '@TotalWriteTimeHR'
		FROM #LastFullJob A
			INNER JOIN JMBkpStats B ON B.jobId = A.jobId AND B.commCellId = 2
			INNER JOIN APP_Client C
				ON C.id = A.clientId
				AND C.id NOT IN (SELECT clientId FROM APP_IDAName WHERE appTypeId IN (45, 56, 57, 67, 90)) /*No Exchange or Lotus Notes Archiver Agent installed*/
				AND A.appTypeId IN (54, 60, 52) /*Exchange MB and Lotus Notes MB*/
			INNER JOIN APP_iDAType I
				ON I.type = A.appTypeId				
			LEFT OUTER JOIN APP_SubClientProp SCP /*OnePass Disabled*/
				ON SCP.componentNameId = A.appId
				AND SCP.attrName = 'Turbo Mode Enable Stubbing' 
				AND SCP.cs_attrName = CHECKSUM(N'Turbo Mode Enable Stubbing') 
				AND SCP.modified = 0
		WHERE ISNULL(SCP.attrVal, '0') = '0'
		GROUP BY A.clientId, dbo.NormalizeForXML(C.name), A.appTypeId, I.name
		HAVING SUM(B.totalUnCompBytes)/@1TB >= @MinSizeTB
		ORDER BY SUM(B.totalUnCompBytes) DESC
		FOR XML PATH('NoArchiveEmailClients')
	)
	


DROP TABLE #tmpCommCellUsage
DROP TABLE #LastFullJob

 SET @surveyXML = (
	SELECT CASE @specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120) ELSE N'All' END as '@clientGroups',
		@nTopN AS '@TopN',
		@LargestFSClients AS '@LargestFSClients',
		@LargestNoArchiveFSClients AS '@LargestNoArchiveFSClients',
		@LargestAppClients AS '@LargestAppClients',
		@LargestNoSnapAppClients AS '@LargestNoSnapAppClients',
		@AvgRestoreThroughput  AS '@AvgRestoreThroughputGBPerHR',
		@LargestEmailClients AS '@LargestEmailClients',
		@LargestNoArchiveEmailClients AS '@LargestNoArchiveEmailClients',		
		@clients,
		@noArchiveClients,
		@noSnapClients,
		@noArchiveEmailClients,
		@countsBySize
	FOR XML PATH ('LargestClientStatisticsXML')
	)
 
 IF object_id('tempdb.dbo.#tempclientStats') is not null 
	DROP TABLE #tempclientStats
 
--------- 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')
)


SET NOCOUNT OFF

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