--Name:- DR Backup Configuration
--Description:- Gives DR backup configuration. How often DR backups are running, last DR backup jobid, backups are going tape library or not.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 31
DECLARE @surveyXML NVARCHAR(MAX)

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

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

--------- Insert your SQL statements here 
DECLARE @lt_fullJobsToRetain varchar(256)
DECLARE @lt_useNetworkPath    varchar(10)
DECLARE @lt_logFilesToBackup varchar(max)
DECLARE @lt_writingToTape varchar(10)


-- This query gives DR backup configuration. How often DR backups are running, last DR backup jobid, backups are going tape library or not.

-- Number of full DR metadata dumps to retain
SELECT @lt_fullJobsToRetain = value
FROM GXGlobalParam WITH(READUNCOMMITTED) WHERE name = 'DRNumFulls'

-- Find whether metadata dumps are saved to network path or local file system path
SELECT @lt_useNetworkPath = (CASE WHEN value like '\\%' THEN 'true' ELSE 'false' END)
FROM GXGlobalParam WITH(READUNCOMMITTED) WHERE name = 'DRDumpLocation'

-- Find the patterns of the log files being backed up by DR backup job
SELECT @lt_logFilesToBackup = value
FROM GXGlobalParam WITH(READUNCOMMITTED) WHERE name = 'DRDumpClientLogWildCard'

-- Find if the target media is a tape or magnetic library
IF EXISTS 
(
      SELECT *  
      FROM APP_Application A WITH(READUNCOMMITTED) 
			INNER JOIN archGroupCopy B WITH(READUNCOMMITTED) ON A.dataArchGrpID = B.archGroupId 
            INNER JOIN MMDataPath C WITH(READUNCOMMITTED) ON B.id = C.CopyId
            INNER JOIN MMDrivePool D WITH(READUNCOMMITTED) ON C.DrivePoolId = D.DrivePoolId
      WHERE D.DrivePoolType <> 10001 AND A.appTypeId = 1000 AND A.dataArchGrpID > 1
)
BEGIN
      SET @lt_writingToTape = 'true'
END
ELSE
BEGIN
      SET @lt_writingToTape = 'false'
END

-- Last successful DR backup job for schedule which is not aged
DECLARE @schInfo  xml = NULL

SET @schInfo   =(SELECT (CASE C.freq_type WHEN 1 THEN 'one Time' WHEN 4 THEN 'daily' 
                        WHEN 8 THEN 'weekly' WHEN 16 THEN 'monthly' WHEN 32 THEN 'monthly' 
                        WHEN 64 THEN 'yearly' WHEN 128 THEN 'yearly' ELSE CONVERT(varchar(10), C.freq_type) END) '@scheduleName',
                        A.subTaskName As '@scheduleFullName',
                        jobdetails.jobId as '@lastJobId'
                  FROM TM_SubTask A  WITH(READUNCOMMITTED)
						INNER JOIN TM_PatternAssoc B WITH(READUNCOMMITTED) ON A.subTaskId = B.subTaskId
                        INNER JOIN TM_Pattern C WITH(READUNCOMMITTED) ON B.patternId = C.patternId
						AND A.deleted = 0 AND A.operationType = 4002 
						INNER JOIN TM_Task D WITH(READUNCOMMITTED) ON D.taskId = A.taskId
						AND D.deleted=0 AND D.disabled=0
					LEFT OUTER JOIN
					(
					SELECT  C.subTaskId AS 'subTaskId' , max(C.jobId) AS 'jobId'
					FROM APP_Application  A WITH(READUNCOMMITTED) INNER JOIN JMJobDataStats B WITH(READUNCOMMITTED) ON A.id = B.appId
					AND A.appTypeId = 1000 AND B.status = 100 AND (B.disabled & 256) = 0
					INNER JOIN JMAdminJobStatsTable C WITH(READUNCOMMITTED) ON C.jobId = B.jobId
					GROUP BY C.subTaskId) as  jobdetails
					on
					jobdetails.subTaskId =A.subTaskId
						AND EXISTS(SELECT 1 FROM APP_Application WITH(READUNCOMMITTED) WHERE appTypeId = 1000 AND dataArchGrpID > 1)
					
                  FOR XML PATH ('Schedule'))

DECLARE @useCommvaultCloud INT = 0, @useThirdpartyCloud INT = 0, @DRCloudLibId INT = 0, @cloudDeviceTypeId INT = 0

SELECT @useCommvaultCloud = (CASE WHEN ISNULL(CAST(value AS VARCHAR), '') = '' THEN 0 ELSE 1 END) FROM GXGlobalParam WITH(NOLOCK) WHERE name = 'DRCloudUserName'
SELECT @useThirdpartyCloud = (CASE WHEN ISNULL(CAST(value AS VARCHAR), '') IN ('', 'false') THEN 0 ELSE 1 END) FROM GXGlobalParam WITH(NOLOCK) WHERE name = 'DRCloudLibraryUploadSet'

IF @useThirdpartyCloud = 1
BEGIN
SELECT @DRCloudLibId = ISNULL(CAST(CAST(value AS VARCHAR) AS INT), 0) FROM GXGlobalParam WITH(NOLOCK) WHERE name='DRCloudLibraryID'

DECLARE @SQLQuery nvarchar(max) 
If EXISTS (SELECT 1 FROM sys.columns 
          WHERE Name = N'DisplayDevicetypeId'
          AND Object_ID = Object_ID(N'MMDevice'))
BEGIN
    SET @SQLQuery = N'SELECT @internalCloudDeviceTypeId = D.DisplayDeviceTypeId 
	FROM MMMountPath MP WITH(NOLOCK)
	INNER JOIN MMMountPathToStorageDevice MPSD WITH(NOLOCK)
		ON MP.MountPathId = MPSD.MountPathId
	INNER JOIN MMDevice D WITH(NOLOCK)
		ON MPSD.DeviceId = D.DeviceId
	INNER JOIN MMLibrary L WITH(NOLOCK)
		ON L.LibraryId = MP.LibraryId
		AND MP.LibraryId = @internalDRCloudLibId'
	END
 ELSE
   BEGIN
    SET @SQLQuery = N'SELECT @internalCloudDeviceTypeId = D.DeviceTypeId 
	FROM MMMountPath MP WITH(NOLOCK)
	INNER JOIN MMMountPathToStorageDevice MPSD WITH(NOLOCK)
		ON MP.MountPathId = MPSD.MountPathId
	INNER JOIN MMDevice D WITH(NOLOCK)
		ON MPSD.DeviceId = D.DeviceId
	INNER JOIN MMLibrary L WITH(NOLOCK)
		ON L.LibraryId = MP.LibraryId
		AND MP.LibraryId = @internalDRCloudLibId'
   END
EXEC sp_executesql @SQLQuery, N'@internalDRCloudLibId  int, @internalCloudDeviceTypeId int  output',
    @internalDRCloudLibId = @DRCloudLibId ,@internalCloudDeviceTypeId = @cloudDeviceTypeId output	
END

				  
				  
SET @surveyXML = (
 	SELECT  @lt_fullJobsToRetain '@fullJobsToRetain',
            @lt_useNetworkPath '@usingNetworkPath',
            @lt_logFilesToBackup '@logFilesToBackup',
            @lt_writingToTape '@writingToTape',
			@useCommvaultCloud AS '@useCommvaultCloud',
			@useThirdpartyCloud AS '@useThirdpartyCloud',
			@cloudDeviceTypeId AS '@thirdpartyCloudDeviceTypeId',			
            @schInfo AS 'schInfo'
	FOR XML PATH ('DRBackupConfigurationInfo')
)

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

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

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
--SELECT  CAST(@surveyXML AS XML)
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

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