--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 261
DECLARE @surveyXML NVARCHAR(MAX)

BEGIN TRY

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 

--==========================================================================================================
DECLARE @csReleaseId INT
SELECT  @csReleaseId = releaseId FROM APP_Client WHERE id = 2



declare @spVersion INT = 0
SELECT @spVersion = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) from simInstalledPackages where clientId = 2

IF (@csReleaseId < 16 OR (@csReleaseId = 16 AND @spVersion < 22))
GOTO EXIT_SCRIPT

IF OBJECT_ID('tempdb..#QuiseError') IS NOT NULL 
    DROP TABLE #QuiseError

IF OBJECT_ID('tempdb..#LatestJobIdPerVM') IS NOT NULL 
    DROP TABLE #LatestJobIdPerVM

create table #LatestJobIdPerVM (
    JobId INT, 
    vmId INT
    )

IF OBJECT_ID('tempdb..#tempBackupErrorCode') IS NOT NULL 
    DROP TABLE #tempBackupErrorCode

create table #tempBackupErrorCode (
    JobId INT, 
    vmId INT,
	errorFlag INT,
	failureReason NVARCHAR(MAX),
	vendor INT,
	guid varchar(256)
    )

declare @execString NVARCHAR(MAX)
SET @execString = 'INSERT INTO #LatestJobIdPerVM
select attrVal,componentNameid from app_clientprop where attrname = ''Last Backup JobID'''

EXEC sp_executesql @execString

INSERT #tempBackupErrorCode(JobId,vmId,errorFlag)  
select T.jobId,T.vmId,prop.attrVal  from app_vmprop prop
INNER JOIN #LatestJobIdPerVM T ON T.jobId = prop.jobId and prop.vmclientId= t.vmid 
WHERE prop.attrName = 'vmBackupErrorFlags' and CAST (prop.attrVal AS INT) > 0

INSERT #tempBackupErrorCode(JobId,vmId,errorFlag)  
select T.jobId,T.vmId,0  from app_vmprop prop
INNER JOIN #LatestJobIdPerVM T ON T.jobId = prop.jobId and prop.vmclientId= t.vmid 
LEFT OUTER JOIN #tempBackupErrorCode tempErr ON tempErr.vmId = T.vmId
WHERE prop.attrName = 'vmstatus' and CAST (prop.attrVal AS INT) = 3 AND tempErr.vmId IS NULL

DELETE VM FROM  #tempBackupErrorCode VM 
INNER JOIN APP_ClientProp ACP WITH (NOLOCK) 
        ON Vm.vmId = ACP.componentnameId AND ACP.attrName='Virtual Machine Deletion Time' AND ACP.modified=0 AND ACP.attrVal<>'0'

UPDATE T
SET failureReason = attrVal
FROM #tempBackupErrorCode T 
INNER JOIN APP_VMProp V WITH (NOLOCK) ON
V.jobId = T.JobId and V.VMclientId = T.VmId
where V.attrName = 'vmFailureReason'

UPDATE T
SET guid = attrVal
FROM #tempBackupErrorCode T 
INNER JOIN APP_VMProp V WITH (NOLOCK) ON
V.jobId = T.JobId and V.VMclientId = T.VmId
where V.attrName = 'vmGUID'

update t
set vendor = ISNULL(AIP.attrVal,0) from 
 APP_ClientProp ACPVM  WITH (NOLOCK) 
    INNER JOIN APP_ClientProp ACPI  WITH (NOLOCK)
        ON ACPI.componentNameId=ACPVM.componentNameId AND ACPI.attrName='Virtual Machine Instance ID' AND ACPI.modified=0
	INNER JOIN	APP_InstanceProp AIP WITH (NOLOCK)
    ON AIP.componentNameId=ACPI.attrVal AND AIP.attrName = 'Virtual Server Instance Type' AND AIP.modified=0
	INNER JOIN #tempBackupErrorCode t ON t.vmId = ACPVM.componentNameId 
		where  ACPVM.attrName='Virtual Server Discovered Clients' AND ACPVM.attrval='1' AND ACPVM.modified=0

SET @surveyXML = (
    SELECT vmId as '@VmClientId',
	 jobId as '@jobId',
	 errorFlag as '@errorFlag',
	 failureReason as '@failureReason',
	 vendor as '@vendorType',
	 guid as '@guid'
	 FROM #tempBackupErrorCode FOR XML PATH ('vmQuise'))

IF OBJECT_ID('tempdb..#QuiseError') IS NOT NULL 
    DROP TABLE #QuiseError

IF OBJECT_ID('tempdb..#LatestJobIdPerVM') IS NOT NULL 
    DROP TABLE #LatestJobIdPerVM

--==========================================================================================================


--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
     
END TRY
BEGIN CATCH  
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH
 
EXIT_SCRIPT: 

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')
)