DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10399
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[ESX Server Count] int,[ESX Version] nvarchar(max)
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; declare @last90days int =  dbo.getunixtime(Getutcdate()-90)


;with cte as
(
select count(distinct prop2.attrval) as [ESX Server Count], prop1.attrval as [ESX Version]
from app_vmprop prop1 with (nolock)
inner join app_vmprop prop2 with (nolock)
on prop1.VMclientId = prop2.VMclientId
and prop1.jobid = prop2.jobid
and prop1.attrName =''vmHostVersion''
and prop2.attrName = ''vmHost''
inner join app_clientprop cp with (nolock)
on cp.attrName =''Virtual Machine Instance Id''
and cp.modified =0
and cp.componentNameId = prop1.VMclientId
inner join app_instanceprop ip with (nolock)
on ip.attrName =''Virtual Server Instance Type''
and ip.componentNameId = cp.attrval
and ip.attrVal = 101
inner join jmbkpstats bkpstats with (nolock)
on bkpstats.jobid = prop2.jobid
and bkpstats.servEndDate >= @last90days
group by prop1.attrval

)
select * from cte where [ESX Version] !=''''
union 
select count(distinct vmprop.attrval) as [ESX Server Count], cp.attrval as [ESX Version]
from app_clientprop cp with (nolock)
inner join app_clientprop cp2 with (nolock)
on cp2.componentNameId = cp.componentnameid
and cp2.attrName = ''Last Backup JobID''
and cp.attrname =''Virtual Machine Host Version''
and cp2.modified =0
and cp.modified =0
inner join app_vmprop vmprop with (nolock)
on vmprop.jobId = cp2.attrVal
and vmprop.attrname =''vmHost''
inner join app_clientprop cp3 with (nolock)
on cp3.attrName =''Virtual Machine Instance Id''
and cp3.modified =0
and cp3.componentNameId = cp.componentNameId
inner join app_instanceprop ip with (nolock)
on ip.attrName =''Virtual Server Instance Type''
and ip.componentNameId = cp3.attrval
and ip.attrVal = 101
inner join jmbkpstats bkpstats with (nolock)
on bkpstats.jobid = cp2.attrval
and bkpstats.servEndDate >= @last90days
where  cp.attrval !=''''
group by cp.attrval ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [ESX Server Count] AS '@Col_-2063492888',[ESX Version] AS '@Col_-1051896830' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10399'), 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'))