DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10335
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Total Count] int,[Deferred Catalog Enabled(Total Count)] int,[Backup Copy Enabled (Total Count)] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SELECT  
		COUNT(*) AS ''Total Count'',
		CASE WHEN COUNT(*) > 0 THEN SUM(C3.IsDeferredCatalog) ELSE 0 END AS ''Deferred Catalog Enabled(Total Count)'',
		CASE WHEN COUNT(*) > 0 THEN SUM(C3.IsBackupCopy) ELSE 0 END AS ''Backup Copy Enabled (Total Count)''
FROM
(
SELECT		C2.*,
			CASE WHEN ARCHDEFERCAT.archGroupId IS NULL THEN 0 ELSE 1 END AS ''IsDeferredCatalog'',
			CASE WHEN ARCHMAT.archGroupId IS NULL THEN 0 ELSE 1 END  AS ''IsBackupCopy'',
			CASE WHEN C2.ISSubclient = 1  AND C2.SubclientEngine = 3 THEN 1 ELSE 0 END AS ''ISNetappAtSubclient'',
			CASE WHEN C2.ISClient= 1  AND C2.ClientOS = 16 THEN 1 ELSE 0 END AS ''ISNetAppAtClient''
FROM
(SELECT		ClientID ,
			ClientName,
			ClientOS,
			ISClient,
			SPolicyID,
			AppID,
			AppType,
			[Enable Snap Backups] AS ''ISSubClient'',
			[Snap Engine] AS ''SubclientEngine''
FROM
(SELECT		DISTINCT P1.*,
			ISNULL(CLIENTPROP.attrVal,0) AS ''ISClient'',
			APP.id as ''AppID'',
			APP.dataArchGrpID AS ''SPolicyID'',
			ISNULL(APP.appTypeId,0) AS ''AppType'',
			APPPROP.attrName, --AS ''Subclient Prop'',
			APPPROP.attrVal --AS ''Subclient Prop Val''
FROM
(SELECT		DISTINCT CLIENT.id AS ClientID, 
			CLIENT.name AS ClientName, 
			CLIENT.simOperatingSystemId AS ClientOS
FROM		App_Client CLIENT 
INNER JOIN	APP_IDAName IDANAME ON CLIENT.id = IDANAME.clientId
WHERE		(CLIENT.status & (CAST(128 AS BIGINT) | CAST(134217728 AS BIGINT)) <> 0)
AND			IDANAME.appTypeId IN (33,29) /*CV_APPTYPE_WINDOWS_FILESYSTEM*/ /*CV_APPTYPE_LINUX_FS*/
AND			CLIENT.simOperatingSystemId IN (16,19)) AS P1
LEFT JOIN	APP_ClientProp CLIENTPROP ON P1.ClientID = CLIENTPROP.componentNameId  /*NetApp*/ AND CLIENTPROP.attrName = ''Snap Backups Enabled'' AND CLIENTPROP.modified = 0
LEFT JOIN	APP_Application	APP ON P1.ClientID = APP.clientId AND APP.appTypeId IN (33,29) AND ((APP.subclientStatus & 4) <> 4)
LEFT JOIN	APP_SubClientProp APPPROP ON APP.id = APPPROP.componentNameId AND APPPROP.attrName IN (''Enable Snap Backups'', ''Snap Engine'') AND APPPROP.modified = 0
WHERE APPPROP.attrName IS NOT NULL) AS C1
PIVOT
(
MAX(attrVal)
FOR attrName IN ([Enable Snap Backups], [Snap Engine]) 
) AS pvt) AS C2
LEFT JOIN	ArchDeferredCatalogProp ARCHDEFERCAT ON C2.SPolicyID = ARCHDEFERCAT.archGroupId
LEFT JOIN	archMaterializeSnapshotProp ARCHMAT ON C2.SPolicyID = ARCHMAT.archGroupId
--WHERE C2.SubclientEngine = 3
--WHERE C2.[IntelliSnap Enabled at subclient] = 1 AND C2.[Subclient Snap Engine] = 3
--WHERE (C2.ClientOS = 19 AND C2.[Subclient Snap Engine] = 3 /*NETApp*/) OR (C2.ClientOS = 16 AND C2.[IntelliSnap Enabled at subclient] = 1 AND C2.[Subclient Snap Engine] = 3)
--ORDER BY ClientID
) AS C3
WHERE C3.ISNetappAtSubclient = 1 AND (C3.ISNetAppAtClient = 1 OR C3.ClientOS = 19) ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Total Count] AS '@Col_-573082381',[Deferred Catalog Enabled(Total Count)] AS '@Col_-2013757819',[Backup Copy Enabled (Total Count)] AS '@Col_1480260018' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10335'), 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'))