--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- 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 = 204
DECLARE @surveyXML NVARCHAR(MAX)

--------- BEGIN SURVEY QUERY ---------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
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 WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) 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 WITH (NOLOCK) 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 EXISTS ( SELECT id FROM app_clientgroup WITH (NOLOCK) join @includeGroups on id=groupID)
	SET @specialGroupExists =1


DECLARE @queryStr NVARCHAR(MAX)
DECLARE @csReleaseId INT = (SELECT releaseId FROM APP_Client WHERE id = 2)

CREATE TABLE #GlobalParam (name NVARCHAR(64), value NVARCHAR(MAX))

IF @csReleaseId >= 15
BEGIN
	SET  @queryStr = N'INSERT INTO #GlobalParam 
	SELECT name, value FROM GXGlobalParam WHERE name IN (''MetricsReport_ClientGrowingDays'', ''VirtualMachineDeletionGracePeriod'') AND modified = 0'
END
EXEC sp_executesql @queryStr

CREATE TABLE #Subclient
(
	clientId  INT,
	appId     INT,
	appTypeId INT
)

CREATE TABLE #ActiveVMs
(
	clientId  INT
)

CREATE TABLE #SubclientGrowth
(
	nDaysAgo  INT,
	clientId  INT,
	appId     INT,
	appTypeId INT,
	appSizeMB BIGINT,
	growthMB  BIGINT
)

CREATE TABLE #AgentGrowth
(
	nDaysAgo  INT,
	appTypeId INT,
	appSizeMB BIGINT,
	growthMB  BIGINT
)

CREATE TABLE #ClientGrowth
(
	nDaysAgo  INT,
	clientId  INT,
	appSizeMB BIGINT,
	growthMB  BIGINT
)

CREATE TABLE #TopGrowingClient
(
	nDaysAgo  INT,
	clientId  INT
)

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)
   OR clientId IN (SELECT id FROM APP_Client WITH (NOLOCK) WHERE (status & (4096|268435456)) <> 0)

DECLARE @hideMask INT = (2 | 4)

DECLARE	@GrowingDays TABLE (nDays INT, toTime INT)

INSERT INTO @GrowingDays VALUES (0, 0), (7, 0), (30, 0)

INSERT INTO @GrowingDays 
SELECT	CAST(value AS INT), 0
FROM	#GlobalParam 
WHERE	name = 'MetricsReport_ClientGrowingDays' AND CAST(value AS INT) > 0 
	AND CAST(value AS INT) NOT IN (SELECT nDays FROM @GrowingDays)
	
UPDATE	@GrowingDays SET toTime = dbo.GetUnixTime(DATEADD(DAY, -nDays, GETUTCDATE()))

INSERT INTO #Subclient
SELECT A.clientId, A.id, A.appTypeId
FROM APP_Application A
	INNER JOIN APP_BackupSetName BS ON A.backupSet = BS.id
	INNER JOIN APP_InstanceName I ON A.instance = I.id
	INNER JOIN APP_iDAType AT ON A.appTypeId = AT.type
	INNER JOIN APP_IDAName ID ON ID.appTypeId = A.appTypeId AND ID.clientId = A.clientId
	INNER JOIN APP_Client CL ON A.clientId = CL.id
	LEFT OUTER JOIN #ExcludedAppIds E ON A.id = E.appId
WHERE A.appTypeId <> 1000 
	AND A.subclientStatus & @hideMask = 0 
	AND BS.status & @hideMask = 0 
	AND  I.status & @hideMask = 0 
	AND ID.status & @hideMask = 0 
	AND CL.status & @hideMask = 0 
	AND 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 E.appId IS NULL

IF @specialGroupExists = 1
	DELETE	S
	FROM	#Subclient S
		LEFT OUTER JOIN (
			SELECT 	DISTINCT CGA.clientId
			FROM 	APP_ClientGroupAssoc CGA INNER JOIN @includeGroups IG 
				ON CGA.clientGroupId = IG.groupID
			) C ON S.clientId = C.clientId
	WHERE	C.clientId IS NULL

INSERT INTO #SubclientGrowth
SELECT	T.nDays, S.clientId, S.appId, S.appTypeId, CAST(T.totalUncompBytes AS BIGINT)/1024/1024, 0
FROM	#Subclient S
		INNER JOIN
		(SELECT D.nDays, B.appId,B.totalUncompBytes, ROW_NUMBER() OVER (PARTITION BY D.nDays, B.appId ORDER BY B.servStartDate DESC) AS descNo
		FROM	JMBkpStats B
				INNER JOIN @GrowingDays D ON servStartDate < D.toTime
		WHERE	(status IN (1, 14) OR status = 3 AND appType IN (SELECT type FROM APP_iDAType WHERE isCWEjobValid = 1))
			AND bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
			AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND appType <> 106
		) T ON S.appId = T.appId
WHERE	descNo = 1

CREATE TABLE #VMClient (clientId INT)

INSERT	INTO #VMClient
SELECT	DISTINCT Q.clientId
FROM	JMQinetixUpdateStatus Q
		INNER JOIN JMBkpStats B ON B.jobId = Q.jobId AND B.commCellId = Q.commCellId
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND B.bkpLevel IN (1, 2, 4, 256, 1024, 32768) 

INSERT INTO #ActiveVMs
SELECT	C.id
FROM	APP_Client C
		INNER JOIN #VMClient J ON C.id = J.clientId
		INNER JOIN APP_ClientProp CP ON C.id = CP.componentNameId
		AND	CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified =0
		LEFT OUTER JOIN (
		SELECT	DISTINCT componentNameId
		FROM	APP_ClientProp
		WHERE	attrName = 'Virtual Machine Deletion Time' AND ISNULL(attrVal, '0') <> '0' AND modified = 0
		) T ON C.id = T.componentNameId
WHERE	T.componentNameId IS NULL

DROP TABLE #VMClient

IF @specialGroupExists = 1
	DELETE	V
	FROM	#ActiveVMs V
		LEFT OUTER JOIN (
			SELECT 	DISTINCT CGA.clientId
			FROM 	APP_ClientGroupAssoc CGA INNER JOIN @includeGroups IG 
				ON CGA.clientGroupId = IG.groupID
			) C ON V.clientId = C.clientId
	WHERE	C.clientId IS NULL	

INSERT INTO #SubclientGrowth
SELECT	T.nDays, S.clientId, T.appId, 106, CAST(T.attrVal AS FLOAT)/1024/1024, 0
FROM	#ActiveVMs S
		INNER JOIN
		(SELECT D.nDays, V.clientId, B.appId, P.attrVal, ROW_NUMBER() OVER (PARTITION BY D.nDays, V.clientId ORDER BY B.servStartDate DESC) AS descNo
		FROM	#ActiveVMs V 
				INNER JOIN APP_VMProp P ON V.clientId = P.VMclientId AND P.attrName = 'vmGuestSize'
				INNER JOIN JMBkpStats B ON P.jobId = B.jobId AND P.commCellId = B.commCellId
				INNER JOIN @GrowingDays D ON servStartDate < D.toTime
		WHERE	status IN (1, 3, 14)
			AND bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
			AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND appType = 106
		) T ON S.clientId = T.clientId
WHERE	descNo = 1

DROP TABLE #Subclient
DROP TABLE #ActiveVMs

-- Only count full size of VSA backup if a VM also protected via other agents
DELETE  S 
FROM    #SubclientGrowth S
        INNER JOIN (
            SELECT DISTINCT clientId FROM #SubclientGrowth WHERE appTypeId = 106
        ) C ON S.clientId = C.clientId
WHERE   appTypeId <> 106

UPDATE	A SET growthMB = (B.appSizeMB - A.appSizeMB)
FROM 	#SubclientGrowth A INNER JOIN (SELECT clientId, appId, appSizeMB FROM #SubclientGrowth WHERE nDaysAgo = 0) B 
		ON A.clientId = B.clientId AND (A.appId = B.appId OR A.appTypeId = 106) AND A.nDaysAgo > 0

INSERT INTO #AgentGrowth
SELECT  nDaysAgo, appTypeId, SUM(appSizeMB), SUM(growthMB)
FROM    #SubclientGrowth
WHERE	nDaysAgo > 0
GROUP BY nDaysAgo, appTypeId

INSERT INTO #ClientGrowth
SELECT  nDaysAgo, clientId, SUM(appSizeMB), SUM(growthMB)
FROM    #SubclientGrowth
WHERE	nDaysAgo > 0
GROUP BY nDaysAgo, clientId

INSERT	INTO #TopGrowingClient
SELECT	T.nDaysAgo, T.clientId
FROM	(SELECT	nDaysAgo, clientId, ROW_NUMBER() OVER (PARTITION BY nDaysAgo ORDER BY growthMB DESC) AS descNo
		FROM	#ClientGrowth
		WHERE	nDaysAgo > 0 AND growthMB > 0
		) T
WHERE	descNo <= 25


DECLARE	@GrowthBucket TABLE (rangeGB VARCHAR(16), fromMB BIGINT, toMB BIGINT)

INSERT INTO @GrowthBucket VALUES 
('< -10', -9223372036854775808, -10000), 
('(-10, -5)',  -10000, -5000), 
('(-5, -1)',    -5000, -1000), 
('(-1, -0.5)',  -1000, -500), 
('(-0.5, -0.1)', -500, -100), 
('(-0.1, 0)',    -100, 0), 
('(0, 0.1)',        0, 100), 
('(0.1, 0.5)',    100, 500), 
('(0.5, 1)',      500, 1000), 
('(1, 5)',       1000, 5000), 
('(5, 10)',      5000, 10000), 
('> 10',        10000, 9223372036854775807)

SET @surveyXML = (
  SELECT
	(SELECT nDaysAgo    AS '@nDaysAgo',
	        appTypeId   AS '@appTypeId',
	        appSizeMB   AS '@appSizeMB',
	        growthMB    AS '@growthMB'
	FROM    #AgentGrowth
	ORDER BY nDaysAgo, growthMB
	FOR XML PATH('AgentGrowth'), TYPE),

	(SELECT D.nDays     AS '@nDaysAgo', 
	        G.rangeGB   AS '@rangeGB',
	        G.fromMB    AS '@fromMB',
	        G.toMB      AS '@toMB', 
	        ISNULL(C.nClients, 0) AS '@nClients'
	FROM    (SELECT nDays FROM @GrowingDays WHERE nDays > 0) D CROSS JOIN @GrowthBucket G 
	        LEFT OUTER JOIN (
	        SELECT A.nDaysAgo, B.fromMB, B.toMB, COUNT(*) AS nClients
	        FROM   #ClientGrowth A, @GrowthBucket B 
	        WHERE  A.nDaysAgo > 0 AND A.growthMB BETWEEN B.fromMB AND B.toMB
	        GROUP BY A.nDaysAgo, B.fromMB, B.toMB		
			) C ON D.nDays = C.nDaysAgo AND G.fromMB = C.fromMB AND G.toMB = C.toMB
	ORDER BY D.nDays, G.fromMB
	FOR XML PATH('ClientGrowthBucket'), TYPE),

	(SELECT DISTINCT
	        C.nDaysAgo  AS '@nDaysAgo',
	        C.clientId  AS '@clientId',
	        C.appId     AS '@appId',
	        C.appSizeMB AS '@appSizeMB',
	        C.growthMB  AS '@growthMB'
	FROM    #SubclientGrowth C
	        INNER JOIN #TopGrowingClient T ON C.clientId = T.clientId AND C.nDaysAgo = T.nDaysAgo
	ORDER BY C.nDaysAgo, C.clientId, C.appId
	FOR XML PATH('TopGrowingClients'), TYPE)

  FOR XML PATH('')
)

DROP TABLE #GlobalParam
DROP TABLE #AgentGrowth
DROP TABLE #ClientGrowth
DROP TABLE #SubclientGrowth
DROP TABLE #TopGrowingClient

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

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
--SELECT CAST(@surveyXML AS XML)

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