--- 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 = 97
DECLARE @surveyXML NVARCHAR(MAX)

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



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

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

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 WITH (NOLOCK)  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 EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
	SET @specialGroupExists =1
	
CREATE TABLE #ClientsTbl (clientId INT, dedupProp INT);

DECLARE @NonMAClients INT = 0
DECLARE @NonMASubClients INT = 0
DECLARE @NonMASCWithSourceDeDup INT = 0
DECLARE @AvgDeDupSavings DECIMAL(32,2) = 0

IF  (@specialGroupExists =1 )
BEGIN
	INSERT INTO #ClientsTbl
	SELECT DISTINCT I.clientId, 0
	FROM APP_IDAName I WITH (NOLOCK)
		INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
			ON CGA.clientId = I.clientId /* Include Client Groups selected for Monitoring */
		INNER JOIN @includeGroups CG 
			ON CGA.clientGroupId = CG.groupId			
	WHERE I.clientId NOT IN (SELECT ClientId FROM MMHost WITH (NOLOCK)) /* Exclude Clients with Media Agent Installed */
		AND (I.appTypeId < 600 OR I.appTypeId = 1000) 
		AND I.appTypeId NOT IN (10, 13, 47, 50, 63, 79, 84, 124)
END
ELSE
BEGIN
	INSERT INTO #ClientsTbl
	SELECT DISTINCT I.clientId, 0
	FROM APP_IDAName I WITH (NOLOCK)
	WHERE I.clientId NOT IN (SELECT ClientId FROM MMHost WITH (NOLOCK)) /* Exclude Clients with Media Agent Installed */
		AND (I.appTypeId < 600 OR I.appTypeId = 1000) 
		AND I.appTypeId NOT IN (10, 13, 47, 50, 63, 79, 84, 124)
END

SET @NonMAClients = @@ROWCOUNT

UPDATE	#ClientsTbl SET dedupProp = CAST(P.attrVal AS INT)
FROM	#ClientsTbl T INNER JOIN APP_ClientProp P WITH (NOLOCK) 
	ON T.clientId = P.componentNameId AND P.attrName = 'Enable DeDuplication' AND P.modified = 0


CREATE TABLE #Subclient
(
	id				INT,
	clientId		INT,
	appTypeId		INT,
	instance		INT,	
	backupset		INT,
	subclientName	NVARCHAR(256),
	dedupEnabled	INT,
	clientDedupProp	INT,
	policyName		NVARCHAR(144),
	copyName		NVARCHAR(64),
	dedupOnSource	INT
)

INSERT	INTO #Subclient
SELECT	A.id, A.clientId, A.appTypeId, A.instance, A.backupSet, A.subclientName, 1, CT.dedupProp,
		AG.name, AGC.name, CASE WHEN (AGC.dedupeFlags & 524288) = 0 THEN 0 ELSE 1 END
FROM	#ClientsTbl CT
	INNER JOIN APP_Application A WITH (NOLOCK)
		ON A.clientId = CT.clientId
	INNER JOIN archGroup AG WITH (NOLOCK) 
		ON AG.id IN (A.dataArchGrpID, A.logArchGrpID) AND AG.id > 1
	INNER JOIN archGroupCopy AGC WITH (NOLOCK) 
		ON AGC.archGroupId = AG.id
	AND AGC.id = AG.defaultCopy
	AND ((AGC.dedupeFlags & 262144 /*$$(CVA_SIDB_STORE_ENABLED_FLAG)*/) > 0) /* DeDuplication Enabled in Storage Policy Copy */
	AND A.appTypeId NOT IN (10, 13, 47, 50, 63, 79, 84, 124)
	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 & (2|4|16|32)) = 0
	AND A.id NOT IN (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)
	AND A.clientId NOT IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1')	
	
SET @NonMASubClients = @@ROWCOUNT
DROP TABLE #ClientsTbl

UPDATE	#Subclient SET dedupEnabled = 0
FROM	#Subclient T INNER JOIN APP_SubClientProp P WITH (NOLOCK)
	ON T.id = P.componentNameId AND P.attrName = 'Single Instancing Option' 
		AND cs_attrName = CHECKSUM(N'Single Instancing Option') AND P.attrVal = '0' AND P.modified = 0

CREATE TABLE #SubclientSize (appId INT, lastJobId INT, protectedBytes BIGINT, nwTransBytes BIGINT)

INSERT	INTO #SubclientSize
SELECT	J.appId, MAX(J.jobId) AS jobId, 0, 0
FROM	#Subclient S INNER JOIN JMBkpStats J WITH (NOLOCK)
	ON J.appId = S.id AND J.status IN (1, 3, 14) AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
WHERE	J.commCellId = 2
GROUP BY J.appId

UPDATE	#SubclientSize SET protectedBytes = B.totalUncompBytes, nwTransBytes = B.nwTransBytes
FROM	#SubclientSize T
		INNER JOIN JMBkpStats B WITH (NOLOCK) 
			ON B.jobId = T.lastJobId AND B.commCellId = 2

SELECT	@NonMASCWithSourceDeDup = COUNT(id)
FROM	#Subclient
WHERE	dedupEnabled > 0 
	AND (clientDedupProp = 0 AND dedupOnSource > 0 OR clientDedupProp = 1)

SELECT	@AvgDeDupSavings = CASE 
			WHEN ISNULL(SUM(protectedBytes), 0) = 0 THEN 0
			ELSE 100.0*(SUM(protectedBytes) - SUM(nwTransBytes))/SUM(protectedBytes) END
FROM	#SubclientSize SS INNER JOIN #Subclient S ON SS.appId = S.id 
WHERE	dedupEnabled > 0 
	AND (clientDedupProp = 0 AND dedupOnSource > 0 OR clientDedupProp = 1)

SET @surveyXML = (
	SELECT 
		CASE @specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120) ELSE N'All' END as '@ClientGroups',
		@NonMAClients AS '@NonMAClients', 
		@NonMASubClients AS '@NonMASubClients', 
		@NonMASCWithSourceDeDup AS '@NonMASCWithSourceDeDup', 
		@AvgDeDupSavings AS '@AvgDeDupSavings',

		-- Subclients without source dedup
		(SELECT	dbo.NormalizeForXML(CL.name) AS '@client',
				I.name AS '@agent',
				dbo.FixInstanceName(INS.name, S.appTypeId) AS '@instance',
				dbo.NormalizeForXML(BS.name) AS '@backupset',
				dbo.NormalizeForXML(S.subclientName) AS '@subclient',
				S.dedupEnabled AS '@subclientDedupProp',
				S.clientDedupProp AS '@clientDedupProp',
				dbo.NormalizeForXML(S.policyName) AS '@storagePolicy',
				dbo.NormalizeForXML(S.copyName) AS '@primaryCopy',
				S.dedupOnSource AS '@dedupOnSource',
				SS.protectedBytes AS '@protectedBytes',
				SS.nwTransBytes AS '@nwTransBytes'				
		FROM	#Subclient S
			INNER JOIN APP_Client CL WITH (NOLOCK) ON S.clientId = CL.id
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON S.appTypeId = I.type
			INNER JOIN APP_InstanceName INS WITH (NOLOCK) ON S.instance = INS.id
			INNER JOIN APP_BackupSetName BS WITH (NOLOCK) ON S.backupset = BS.id
			LEFT OUTER JOIN #SubclientSize SS ON SS.appId = S.id
		WHERE	dedupEnabled <= 0  OR( dedupOnSource <= 0 AND  clientDedupProp <> 1) OR (dedupOnSource >0 AND clientDedupProp>0 AND clientDedupProp<>1 ) 
		FOR XML PATH('Subclient'), TYPE
		)
	FOR XML PATH('SourceDeDupEfficiency')
)

DROP TABLE #Subclient
DROP TABLE #SubclientSize

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