DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10395
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Backup Status] nvarchar(max),[Affected AWS VMs] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @affectedAWSVMs INT = -1
IF EXISTS(SELECT id FROM APP_ClientProp (NOLOCK) WHERE attrName = ''Cloud Hypervisor Type'' AND attrVal = ''301'' AND modified = 0)  -- AWS client
       BEGIN
                    SET @affectedAWSVMs = 0

                    -- get SP19 or higher SP install time
                    DECLARE @history TABLE (t_rel INT, t_sp INT, t_time INT)
                    DECLARE @xmlHistory XML = ISNULL((SELECT cast(stringVal as xml) FROM APP_ComponentProp (NOLOCK) WHERE propertyTypeId = 3624 AND componentId = 2 AND modified = 0), '''')

                    INSERT INTO @history
                    SELECT ISNULL(XTbl.XProp.value(''@Release'', ''integer''), 0), ISNULL(XTblsp.XProp.value(''@sp'', ''integer''), 0), ISNULL(XTblsp.XProp.value(''@installTime'', ''integer''), 0)
                    FROM @xmlHistory.nodes(''PatchInstallHistory'') AS XTbl(XProp)
                    CROSS APPLY @xmlHistory.nodes(''PatchInstallHistory/SPVer'') AS XTblsp(XProp)
                    UNION
                    SELECT (SELECT TOP 1 release FROM SimAllGalaxyRel (NOLOCK) ORDER BY id desc), highestSP, dbo.getUNIXTime(MIN(optime)) FROM patchInstallHistory (NOLOCK) WHERE clientID = 2 GROUP BY highestSP

                    --check if there was any installations prior to SP19
                    IF NOT EXISTS (SELECT * FROM @history WHERE t_sp < 19)
                           GOTO EXIT_RESULT;

                    DECLARE @sp19orhigherInstallTime INT = ISNULL((SELECT  Min(t_time) from @history where t_rel = 11 and t_sp >= 19), 0)                    
                                 DECLARE @isSP19Setup INT = 0

                    --check if CS is on SP19
                    IF NOT EXISTS (SELECT * FROM @history WHERE t_sp > 19)
                        SET @isSP19Setup = 1

                    -- if CS is on SP19 or higher
                    IF (@sp19orhigherInstallTime > 0)
                           BEGIN
                                 -- get aws instances
								 IF OBJECT_ID(''tempdb.dbo.#awsInstanceList'') IS NOT NULL DROP TABLE #awsInstanceList
								 CREATE TABLE #awsInstanceList    ( instanceId INT)

                                 INSERT INTO #awsInstanceList
                                 SELECT componentNameId FROM APP_InstanceProp (NOLOCK) WHERE attrName = ''Virtual Server Instance Type'' AND attrVal = ''301'' AND modified = 0

								 IF OBJECT_ID(''tempdb.dbo.#vmClientListWithEBSBackup'') IS NOT NULL DROP TABLE #vmClientListWithEBSBackup
								 CREATE TABLE #vmClientListWithEBSBackup    ( vmClientId INT)
								 IF OBJECT_ID(''tempdb.dbo.#vmClientListWithFullBackup'') IS NOT NULL DROP TABLE #vmClientListWithFullBackup
								 CREATE TABLE #vmClientListWithFullBackup    ( vmClientId INT, jobStartTime INT)
                                                     
                                DECLARE @fullBackupVMCount INT = 0   

								declare @sqlQuery nvarchar(max)
								SET @sqlQuery = ''INSERT INTO #vmClientListWithEBSBackup
                                            SELECT DISTINCT VMclientId from APP_VMProp PROP  (NOLOCK) INNER JOIN JMBkpStats JOB (NOLOCK) ON PROP.clientJobId  = JOB.jobId
                                            INNER JOIN APP_ClientProp Clientprop (NOLOCK) ON PROP.VMclientId = Clientprop.componentNameId
                                            INNER JOIN #awsInstanceList temp ON Clientprop.attrVal = temp.instanceId AND Clientprop.attrName = ''''Virtual Machine Instance ID'''' AND Clientprop.modified = 0 
                                            WHERE PROP.attrname = ''''vmTransportMode'''' AND PROP.attrVal IN (''''EBS-DIRECT-API'''', ''''Amazon EBS direct'''') 
                                                                    
                                            -- From the list of EBS backed up VMs, identify VMs that has run a full backup after SP19
											INSERT INTO #vmClientListWithFullBackup
                                            SELECT DISTINCT PROP.VMclientId, MIN(JOB.servStartDate) FROM APP_VMProp PROP (NOLOCK) INNER JOIN JMBkpStats JOB (NOLOCK) ON PROP.clientJobId  = JOB.jobId 
                                            INNER JOIN #vmClientListWithEBSBackup VM ON PROP.VMclientId = VM.vmClientId
                                            WHERE JOB.bkpLevel = 1 group by PROP.VMclientId''
								IF(@isSP19Setup = 1)
									SET @sqlQuery = REPLACE(@sqlQuery,''clientJobId'',''jobId'')
								EXEC sp_executesql @sqlQuery

								set @fullBackupVMCount = (select count(*) from #vmClientListWithFullBackup where jobStartTime > @sp19orhigherInstallTime)

                                 --Compute affected VM count
                                 SET @affectedAWSVMs = ISNULL((SELECT count(vmClientId) FROM #vmClientListWithEBSBackup), 0) - @fullBackupVMCount
                           END
       END

EXIT_RESULT:
DECLARE @status VARCHAR(20)
IF @affectedAWSVMs = 0 SET @status = ''Good'' ELSE IF @affectedAWSVMs = -1 SET @status = ''N/A'' ELSE SET @status = ''Needs Attention''
SELECT @status AS ''Backup Status'', @affectedAWSVMs AS ''Affected AWS VMs'' ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Backup Status] AS '@Col_2113880080',[Affected AWS VMs] AS '@Col_-331333811' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10395'), 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'))