DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10253
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[WindowsCount] int,[UNIXCount] int,[NASCount] int,[OtherCount] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


	IF OBJECT_ID(''tempdb.dbo.#backupsetCount'') IS NOT NULL 
	DROP TABLE #backupsetCount
	CREATE TABLE #backupsetCount (
							appId INT PRIMARY KEY,
							subclientName VARCHAR(512),
							appTypeId INT,
							appTypeName NVARCHAR(MAX),
							instanceId INT,
							instanceName VARCHAR(512),
							instanceStatus INT DEFAULT 0,
							backupsetId INT,
							backupsetName VARCHAR(512),
							clientId INT,
							clientName VARCHAR(512),
							clientType INT
							
	)

	--Get All client except NAS
	INSERT #backupsetCount(appId, appTypeId, instanceId, backupsetId, clientId, clientType)	
	SELECT DISTINCT subclient.id, subclient.appTypeId, subclient.instance, subclient.backupSet, subclient.clientId,
	(CASE WHEN subclient.appTypeId IN(select appTypeId from App_AppTypeGroups where app = ''File System'' AND osGroup = ''WINDOWS'') THEN 1
				WHEN subclient.appTypeId IN(select appTypeId from App_AppTypeGroups where app = ''File System'' AND osGroup = ''UNIX'') THEN 2
				ELSE 4 END
	)
	FROM APP_Application subclient 
	INNER JOIN APP_Client client ON client.id = subclient.clientId
	WHERE 
	(
		subclient.subclientStatus & 0x04 != 0x04		--Deleted subclient
		AND subclient.subclientStatus & 0x02 != 0x02 	--Deconfigured subclient
		AND subclient.subclientStatus & 0x200 != 0x200 	--Indexing subclient
		AND client.status != 0x0080 --NAS client
	)
	
	
	--Get NAS client
	INSERT #backupsetCount(appId, appTypeId, instanceId, backupsetId, clientId, clientType)
	SELECT DISTINCT subclient.id, subclient.appTypeId, subclient.instance, subclient.backupSet, subclient.clientId, 3
	FROM APP_Application subclient 
	INNER JOIN APP_Client client ON client.id = subclient.clientId
	WHERE 
	(
		subclient.subclientStatus & 0x04 != 0x04		--Deleted subclient
		AND subclient.subclientStatus & 0x02 != 0x02 	--Deconfigured subclient
		AND subclient.subclientStatus & 0x200 != 0x200 	--Indexing subclient
		AND client.status = 0x0080 --NAS client
	)

	
	UPDATE T
	SET 
		T.subclientName = subclient.subclientName,
		T.instanceName = instance.name,
		T.instanceStatus = instance.status,
		T.backupsetName = backupset.name,
		T.clientName = client.name,
		T.appTypeName = appType.displayName
	FROM #backupsetCount T
	LEFT JOIN APP_Application subclient ON subclient.id = T.appId
	LEFT JOIN APP_Client client ON client.id = T.clientId
	LEFT JOIN App_InstanceName instance ON instance.id = T.instanceid
	LEFT JOIN APP_BackupSetName backupset ON backupset.id = T.backupsetId
	LEFT JOIN APP_iDAType appType ON appType.type = T.appTypeId
	

	DELETE #backupsetCount
	WHERE appId IN(
				SELECT componentNameId FROM APP_SubClientProp WHERE attrName = N''DDB Backup'' AND attrVal = ''1'' AND modified = 0
	)

	--Remove duplicate backupsets
	DELETE FROM BKPSET
	FROM #backupsetCount AS BKPSET
	INNER JOIN (
				SELECT clientId, backupsetId, MAX(appID) AS maxSubclientID
				FROM #backupsetCount
				GROUP BY  
					clientId, backupsetId
				HAVING 
					COUNT(appID) >1

	)T ON
		BKPSET.backupsetId = T.backupsetId
		WHERE 
		BKPSET.appId <> T.maxSubclientID
-------------------------------------------------------------------------------------------

		SELECT SUM(CASE WHEN (clientType = 1) THEN  ClientCount ELSE 0 END)  WindowsCount,
		       SUM(CASE WHEN (clientType = 2) THEN  ClientCount ELSE 0 END)  UNIXCount,
		       SUM(CASE WHEN (clientType = 3) THEN  ClientCount ELSE 0 END)  NASCount,
	           SUM(CASE WHEN (clientType = 4) THEN ClientCount ELSE 0 END)  OtherCount
		 FROM 
		(
			SELECT clientType, COUNT(DISTINCT ClientID) ClientCount
			FROM #backupsetCount
			GROUP BY clientType, clientId, appTypeName
			HAVING COUNT(backupsetID) >1
		) B ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [WindowsCount] AS '@WindowsCount',[UNIXCount] AS '@UNIXCount',[NASCount] AS '@NASCount',[OtherCount] AS '@OtherCount' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10253'), type
)
  FOR XML PATH ('SurveyResults') 
)

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