DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10164
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Laptop] int,[Server] int,[Database] int,[Snap] int,[Filesystem] int,[VSA Backup] int,[VSA Replication] int,[Exchange Mailbox] int,[Exchange Journal] int,[Data Classification] int,[Archive] int,[FS IBMiVTL] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; IF OBJECT_ID(N''dbo.App_plan'', N''U'') IS NOT NULL
BEGIN
SELECT 
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 33554439 AND flag = 0) AS ''Laptop'' ,
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 33554437 AND flag = 0) AS ''Server'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 33579013 AND flag = 0) AS ''Database'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 67108869 AND flag = 0) AS ''Snap'' ,
(SELECT COUNT(*)  FROM APP_PLAN WHERE subType = 50331655 AND flag = 0) AS ''Filesystem'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 83886085 AND flag = 0) AS ''VSA Backup'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 83918853 AND flag = 0) AS ''VSA Replication'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 100859907 AND flag = 0) AS ''Exchange Mailbox'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 100794372 AND flag = 0) AS ''Exchange Journal'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 117506053 AND flag = 0) AS ''Data Classification'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 150994951 AND flag = 0) AS ''Archive'',
(SELECT COUNT(*) FROM APP_PLAN WHERE subType = 50331653 AND flag = 0) AS ''FS IBMiVTL''
END ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Laptop] AS '@Laptop',[Server] AS '@Server',[Database] AS '@Database',[Snap] AS '@Snap',[Filesystem] AS '@Filesystem',[VSA Backup] AS '@Col_-1779094434',[VSA Replication] AS '@Col_338498224',[Exchange Mailbox] AS '@Col_1731247031',[Exchange Journal] AS '@Col_-519183814',[Data Classification] AS '@Col_944019228',[Archive] AS '@Archive',[FS IBMiVTL] AS '@Col_1696555078' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10164'), 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'))