DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10282
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[FS2008Servers] int,[SQL2008Servers] int,[FS2008VMs] int,[SQL2008VMs] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @FS2008Servers  INT = 0
DECLARE @SQL2008Servers INT = 0
DECLARE @FS2008VMs      INT = 0
DECLARE @SQL2008VMs     INT = 0

IF OBJECT_ID(''tempdb.dbo.#VMClient'') IS NOT NULL DROP TABLE #VMClient
CREATE TABLE #VMClient (vmClientId INT)

INSERT	INTO #VMClient
SELECT	componentNameId FROM APP_ClientProp WITH (NOLOCK) 
WHERE	attrName = ''Virtual Server Discovered Clients'' AND attrval = ''1'' AND modified = 0

SELECT	@FS2008Servers = COUNT(DISTINCT I.clientId)
FROM	APP_iDAName I
		INNER JOIN APP_ClientProp CP ON I.clientId = CP.componentNameId 
			AND CP.attrName = ''Operating System Name'' 
			AND CP.attrVal LIKE ''%Windows Server 2008%'' AND CP.modified = 0
		LEFT OUTER JOIN #VMClient VM ON I.clientId = VM.vmClientId
WHERE	I.appTypeId IN (SELECT type FROM APP_iDAType WHERE name LIKE ''%File System%'') AND I.status&2 = 0
	AND VM.vmClientId IS NULL

SELECT	@SQL2008Servers = COUNT(DISTINCT A.clientId)
FROM	APP_Application A
		INNER JOIN APP_InstanceName INS ON A.instance = INS.id AND INS.status&2 = 0 
		INNER JOIN APP_InstanceProp P ON INS.id = P.componentNameId
			AND P.attrName = ''MSSQL Version'' AND P.attrVal LIKE ''10.50.%'' AND P.modified = 0
		LEFT OUTER JOIN #VMClient VM ON A.clientId = VM.vmClientId
WHERE	A.subclientStatus&(2|4) = 0
	AND VM.vmClientId IS NULL

DELETE	VM
FROM	#VMClient VM
		INNER JOIN APP_ClientProp CP WITH (NOLOCK) 
		ON VM.vmClientId = CP.componentNameId AND CP.attrName = ''Virtual Machine Deletion Time'' 
		AND ISNULL(CP.attrVal, ''0'') <> ''0'' AND CP.modified = 0

SELECT	@FS2008VMs = COUNT(DISTINCT I.clientId)
FROM	APP_iDAName I
		INNER JOIN APP_ClientProp CP ON I.clientId = CP.componentNameId 
			AND CP.attrName = ''Operating System Name'' 
			AND CP.attrVal LIKE ''%Windows Server 2008%'' AND CP.modified = 0
		INNER JOIN #VMClient VM ON I.clientId = VM.vmClientId
WHERE	I.appTypeId IN (SELECT type FROM APP_iDAType WHERE name LIKE ''%File System%'') AND I.status&2 = 0

SELECT	@SQL2008VMs = COUNT(DISTINCT A.clientId)
FROM	APP_Application A
		INNER JOIN APP_InstanceName INS ON A.instance = INS.id AND INS.status&2 = 0 
		INNER JOIN APP_InstanceProp P ON INS.id = P.componentNameId
			AND P.attrName = ''MSSQL Version'' AND P.attrVal LIKE ''10.50.%'' AND P.modified = 0
		INNER JOIN #VMClient VM ON A.clientId = VM.vmClientId
WHERE	A.subclientStatus&(2|4) = 0

SELECT	@FS2008Servers  AS FS2008Servers,
		@SQL2008Servers AS SQL2008Servers,
		@FS2008VMs      AS FS2008VMs,
		@SQL2008VMs     AS SQL2008VMs ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [FS2008Servers] AS '@FS2008Servers',[SQL2008Servers] AS '@SQL2008Servers',[FS2008VMs] AS '@FS2008VMs',[SQL2008VMs] AS '@SQL2008VMs' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10282'), 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'))