DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10268
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[jobId] int,[appId] int,[bkpLevel] int,[duration] int,[timetaken] int,[status] int,[totalUncompBytes] bigint,[totalNumOfFiles] bigint,[totalBackupSize] bigint
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SELECT TOP 50 jmb.jobId,jmb.appId,jmb.bkpLevel,jmb.duration,(jmb.servEndDate-jmb.servStartDate) as timetaken,jmb.status,jmb.totalUncompBytes,jmb.totalNumOfFiles,jmb.totalBackupSize FROM JMBkpStats jmb WITH(NOLOCK) INNER JOIN (
				SELECT DISTINCT jbas.jobid FROM JMBkpAtmptStats jbas WITH(NOLOCK)
				INNER JOIN JMBkpStats jbs on jbs.jobid = jbas.jobid and jbas.number = 1
				INNER JOIN APP_SubClientProp asp on asp.componentNameId = jbs.appid 
				and asp.attrname = ''Use block level backup'' and asp.attrVal = 1 and asp.modified = 0 
				and jbs.status in (1,14)
			
			EXCEPT

			SELECT DISTINCT jbas.jobid from JMBkpAtmptStats jbas WITH(NOLOCK) where jbas.number > 1)A 
			
			on A.jobid = jmb.jobid ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [jobId] AS '@jobId',[appId] AS '@appId',[bkpLevel] AS '@bkpLevel',[duration] AS '@duration',[timetaken] AS '@timetaken',[status] AS '@status',[totalUncompBytes] AS '@totalUncompBytes',[totalNumOfFiles] AS '@totalNumOfFiles',[totalBackupSize] AS '@totalBackupSize' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10268'), 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')) 
