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