--Name:- DR Statistics
--Description:- Total Number of successful DR backups, Failed DR backups, Last date of Successful DR backup

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

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 19
DECLARE @surveyXML NVARCHAR(MAX)
--------- END - GENERATED CODE ---------

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

--------- Insert your SQL statements here 
DECLARE @partialsuccess_flag BIT
DECLARE @dr_succes INTEGER
DECLARE @dr_failure INTEGER
DECLARE @last_dr_success INTEGER
DECLARE @last_dr_failed INTEGER
DECLARE @last_dr_cwe INTEGER
DECLARE @full_bkp INTEGER
DECLARE @time_diff INTEGER
DECLARE @diff_bkp INTEGER
DECLARE @optype_DR_bkp INTEGER =11
DECLARE @full_DR_bkp_status INTEGER=1
DECLARE @diff_DR_bkp_status INTEGER=4
DECLARE @day_seconds INTEGER=24*60*60
DECLARE @minfull_bkp_date INTEGER
DECLARE @maxfull_bkp_date INTEGER
DECLARE @DRBkpLocation NVARCHAR(256)
SELECT @DRBkpLocation = value FROM GXGlobalParam WITH(READUNCOMMITTED) WHERE name = 'DRDumpLocation'

SET @partialsuccess_flag=0
-- if partial success is considered as  success set it as 1 else set it as 0
-- Calculate the number of failed/successful jobs and the last successful job id.
IF @partialsuccess_flag=0
  BEGIN
    SET @dr_failure= (SELECT COUNT(*) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType=@optype_DR_bkp AND status NOT IN (4,8,9,1))
    --- We haven't included job which are KILLED,STSKIPPED,DROPPED whose status flags are 4,8,9 respectively
    SET @dr_succes= (SELECT COUNT(*) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType=@optype_DR_bkp AND status =1)
    SET @last_dr_failed=(SELECT MAX(servEnd) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND status NOT IN (4,8,9,1,3))	
    SET @last_dr_success=(SELECT MAX(servEnd) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND status =1 )
	SET @last_dr_cwe=(SELECT MAX(servEnd) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND status =3 )
 

  END
ELSE
  BEGIN
    SET @dr_failure= (SELECT COUNT(*) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND status NOT IN (4,8,9,1,3))
      --- We haven't included job which are KILLED,STSKIPPED,DROPPED whose status flags are 4,8,9 respectively
    SET @dr_succes= (SELECT COUNT(*) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND (status =1 or status=3))
    SET @last_dr_failed=(SELECT MAX(servEnd) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND status NOT IN (4,8,9,1,3))	
    SET @last_dr_success=(SELECT MAX(servEnd) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND (status =1 ))
	SET @last_dr_cwe=(SELECT MAX(servEnd) FROM JMAdminJobStatsTable WITH (NOLOCK) where opType= @optype_DR_bkp AND status =3 )
  END
IF @dr_succes=0
	SET @last_dr_success=0
    -- @last_dr_success when set to 0 , it means the given date is invalid and there is no successfull DR backup
IF @dr_failure=0 
	SET @last_dr_failed=0
    -- @last_dr_failed when set to 0 , it means the given date is invalid and there is no failed DR backup

--Get frequencies of Full and Differential backups
SET @maxfull_bkp_date=(SELECT ISNULL(MAX(servStart),0) FROM JMAdminJobStatsTable WITH (NOLOCK) WHERE opType= @optype_DR_bkp AND ER_BkpLevel=@full_DR_bkp_status )
SET @minfull_bkp_date=(SELECT ISNULL(MIN(servStart),0) FROM JMAdminJobStatsTable WITH (NOLOCK) WHERE opType= @optype_DR_bkp AND ER_BkpLevel=@full_DR_bkp_status )
SET @time_diff= @maxfull_bkp_date-@minfull_bkp_date
SET @diff_bkp=(SELECT COUNT(*) FROM JMAdminJobStatsTable WITH (NOLOCK) WHERE opType= @optype_DR_bkp AND ER_BkpLevel=@diff_DR_bkp_status AND (servStart BETWEEN @minfull_bkp_date AND @maxfull_bkp_date))
SET @full_bkp=(SELECT COUNT(*) FROM JMAdminJobStatsTable WITH (NOLOCK) WHERE opType= @optype_DR_bkp AND ER_BkpLevel=@full_DR_bkp_status)
IF @full_bkp!=0
  BEGIN
   SET @full_bkp=@full_bkp-1
   END
 
DECLARE @DRJobDetails XML
--Get DR Backup Location of last 3 successful jobs
IF OBJECT_ID('tempdb..#DRJobId') IS NOT NULL
	DROP TABLE #DRJobId
IF OBJECT_ID('tempdb..#DRJobVolume') IS NOT NULL
	DROP TABLE #DRJobVolume
IF OBJECT_ID('tempdb..#DRJobDetails') IS NOT NULL
	DROP TABLE #DRJobDetails

CREATE TABLE #DRJobId(jobId INT, jobStatus INT, jobStartTime int default 0, jobEndTime int default 0, errorCode VARCHAR(32), failureReason NVARCHAR(512), failureErrorCode INT)

CREATE TABLE #DRJobVolume(jobId INT, archCopyId INT, volumeId INT)

CREATE TABLE #DRJobDetails(
	jobId INT,
	mediaId int,
	mediaSideId int,
	mediaTypeId int,
	libraryId int,
	libraryTypeId int,
	mediaLocation int,
	exportLocationId int,
	archCopyId int,
	libraryName nvarchar(256),
	CopyName nvarchar(256),
	StoragePolicyName nvarchar(256),
	barcode nvarchar(256),
	locationType nvarchar(256),
	locationName nvarchar(256))

INSERT INTO #DRJobId(jobId, jobStatus, jobStartTime, jobEndTime, errorCode, failureReason, failureErrorCode) --Last 3 success jobs
SELECT TOP 3 jobid, status, servStart, servEnd, '', CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END, failureErrorCode
FROM JMAdminJobStatsTable WITH(NOLOCK) WHERE optype = 11 AND status = 1 ORDER BY jobid DESC
IF (@last_dr_failed > @last_dr_success) --Insert failed job only if last DR job has failed
BEGIN
	INSERT INTO #DRJobId(jobId, jobStatus, jobStartTime, jobEndTime, errorCode, failureReason, failureErrorCode) --Last 1 failed job
	SELECT TOP 1 jobid, status, servStart, servEnd, '', CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END, failureErrorCode
	FROM JMAdminJobStatsTable WITH(NOLOCK) WHERE optype = 11 AND status NOT IN (4,8,9,1) ORDER BY jobid DESC
END
IF (@last_dr_cwe > @last_dr_success) --Insert cwe job only if last DR job is cwe
BEGIN
    INSERT INTO #DRJobId(jobId, jobStatus, jobStartTime, jobEndTime, errorCode, failureReason, failureErrorCode) --Last 1 failed job
    SELECT TOP 1 jobid, status, servStart, servEnd, '', CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END, failureErrorCode
    FROM JMAdminJobStatsTable WITH(NOLOCK) WHERE optype = 11 AND status NOT IN (4,8,9,1) ORDER BY jobid DESC
END

UPDATE	#DRJobId
SET		ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),
		FailureReason = dbo.NormalizeForXML(LEFT(dbo.JMGetLocalizedMessageFunc(0, A.FailureReason), 512))
FROM	#DRJobId A
		INNER JOIN JMFailureReasonMsg B WITH (NOLOCK) ON CAST(LEFT(A.FailureReason, LEN(A.FailureReason)-1) AS INT) = B.id
		INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON B.messageId = C.MessageID AND C.LocaleID = 0
WHERE	A.jobStatus <> 1 AND A.FailureReason <> ''

INSERT INTO #DRJobVolume
SELECT	J.jobId, ACM.archCopyId, AC.volumeId
FROM	#DRJobId J
		INNER JOIN archFile AF WITH (NOLOCK) ON J.jobId = AF.jobId AND AF.isValid = 1
		INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AF.id = ACM.archFileId AND AF.commCellId = ACM.commCellId
		INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
GROUP BY J.jobId, ACM.archCopyId, AC.volumeId

INSERT INTO #DRJobDetails(
	jobId,
	mediaId,
	mediaSideId,
	mediaTypeId,
	libraryId,
	libraryTypeId,
	mediaLocation,
	exportLocationId,
	archCopyId,
	libraryName,
	CopyName,
	StoragePolicyName,
	barcode,
	locationType,
	locationName
)
select distinct
	JV.jobId, 
	V.MediaId, 
	V.MediaSideId,
	M.MediaTypeId, 
	L.LibraryId, 
	L.LibraryTypeId, 
	M.MediaLocation, 
	M.ExportLocationId, 
	JV.archCopyId, 
	L.AliasName, 
	'', '',
	M.BarCode,
	'', '' 
FROM #DRJobVolume JV
	INNER JOIN MMVolume V WITH (NOLOCK) ON JV.volumeId = V.VolumeId
	INNER JOIN MMMedia M WITH (NOLOCK) ON V.MediaId = M.MediaId
	INNER JOIN MMLibrary L WITH (NOLOCK) ON M.LibraryId = L.LibraryId

UPDATE #DRJobDetails
SET StoragePolicyName = a.name, CopyName = b.name 
FROM archgroup a 
	INNER JOIN archGroupCopy b
		ON a.id = b.archGroupId
	INNER JOIN #DRJobDetails DR
		ON b.id = DR.archCopyId

IF OBJECT_ID('MMS2getMountPathNameGUIView', 'V') IS NOT NULL
	UPDATE #DRJobDetails
	SET locationType = CASE WHEN a.MountPathTypeId = 7 THEN 'Cloud' ELSE 'Mount Path' END, locationName = MountPathName 
	FROM #DRJobDetails DR
		INNER JOIN MMS2getMountPathNameGUIView a 
			ON a.MediaId = DR.mediaId
			AND DR.MediaTypeId = 10001
ELSE
	UPDATE #DRJobDetails
	SET locationType = CASE WHEN MP.MountPathTypeId = 7 THEN 'Cloud' ELSE 'Mount Path' END, locationName = MountPathName 
	FROM #DRJobDetails DR
		INNER JOIN MMMountPath MP 
			ON DR.mediaSideId = MP.MediaSideId
			AND DR.MediaTypeId = 10001

UPDATE #DRJobDetails 
SET 
	locationType = CASE 
						WHEN (DR.mediaLocation = 3 AND DR.libraryTypeId = 8 AND exportLocationId = 0) THEN 'Media Repository' 
						WHEN DR.mediaLocation = 3 THEN 'Export Location' 
						WHEN (DR.mediaLocation = 2) THEN 'Drive'
						ELSE 'Slot'
					END,
	locationName = CASE 
						WHEN (DR.mediaLocation = 3 AND DR.libraryTypeId = 8 AND exportLocationId = 0) THEN libraryName 
						WHEN DR.mediaLocation = 3 THEN (SELECT TOP 1 ExportLocation from MMExportLocation where ExportLocationId = DR.exportLocationId)
						WHEN (DR.mediaLocation = 2) THEN (SELECT TOP 1 DriveName from MMDrive where MediaId = mediaId)
						ELSE (SELECT TOP 1 SlotName from MMSlot where MediaId = mediaId)
					END 
FROM #DRJobDetails DR
WHERE DR.MediaTypeId <> 10001

SELECT @DRJobDetails = 
(SELECT DISTINCT
	J.jobId AS '@JobId', 
	J.jobStatus AS '@JobStatus',
	CASE WHEN ISNULL(J.jobStartTime,0) = 0 THEN 0 ELSE dbo.getunixtime(dbo.UTCToCellLocalTime(dbo.GetDateTime(ISNULL(J.jobStartTime,0)), 2)) END AS '@StartTime', 
	CASE WHEN ISNULL(J.jobEndTime,0) = 0 THEN 0 ELSE dbo.getunixtime(dbo.UTCToCellLocalTime(dbo.GetDateTime(ISNULL(J.jobEndTime,0)), 2)) END AS '@EndTime', 
	J.errorCode AS '@ErrorCode',
	J.failureErrorCode AS '@FailureErrorCode',
	J.failureReason AS '@FailureReason',	
	ISNULL(storagePolicyName, '') AS '@SPName', 
	ISNULL(copyName, '') AS '@SPCopyName', 
	ISNULL(libraryName, '') AS '@LibraryName', 
	ISNULL(barcode, '') AS '@Barcode', 
	ISNULL(locationType, '') AS '@LocationType', 
	ISNULL(locationName, '') AS '@LocationName'	
FROM #DRJobDetails DR RIGHT OUTER JOIN #DRJobId J
	ON J.jobId = DR.jobId
FOR XML PATH('LastDRJobDetails')
)
IF OBJECT_ID('tempdb..#DRJobId') IS NOT NULL
	DROP TABLE #DRJobId

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

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

 SET @surveyXML = (
	SELECT 
		@dr_succes AS '@DR_No_Of_Success', 
		@dr_failure AS '@DR_No_Of_Failure', 
		CASE WHEN ISNULL(@last_dr_success,0) = 0 THEN 0 ELSE dbo.getunixtime(dbo.UTCToCellLocalTime(dbo.GetDateTime(ISNULL(@last_dr_success,0)), 2)) END AS '@Last_Successful_Dr',
		CASE WHEN ISNULL(@last_dr_failed,0) = 0 THEN 0 ELSE dbo.getunixtime(dbo.UTCToCellLocalTime(dbo.GetDateTime(ISNULL(@last_dr_failed,0)), 2)) END AS '@Last_Failed_Dr',
		CASE WHEN ISNULL(@last_dr_cwe,0) = 0 THEN 0 ELSE dbo.getunixtime(dbo.UTCToCellLocalTime(dbo.GetDateTime(ISNULL(@last_dr_cwe,0)), 2)) END AS '@Last_Cwe_Dr',
		CASE WHEN (@full_bkp=0) THEN 0 ELSE CONVERT(DECIMAL(36,2), 1.0*@time_diff/@full_bkp/@day_seconds) END AS '@full_backup_time_period',
		CASE WHEN(@full_bkp=0) THEN 0 ELSE CONVERT(DECIMAL(36,2),(CAST(@diff_bkp AS DECIMAL) / CAST(@full_bkp AS DECIMAL))) END AS '@differential_backup_frequency',
		@DRBkpLocation AS '@DRBkpLocation',
		@DRJobDetails
	FOR XML PATH('DRstats'))

  --when 0 is set for full_backup_time_period (or) differential_backup_frequency then it implies the value is invalid 
 --------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
--SELECT CAST(@surveyXML AS XML)
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')
)

SET NOCOUNT OFF
--------- END - GENERATED CODE ---------
  


