-- Please follow the below comments to insert SQL statements. --------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY --------- SET NOCOUNT ON BEGIN TRY DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) DECLARE @queryId AS INTEGER = 208 -- TODOTDO DECLARE @surveyXML NVARCHAR(MAX) = '' --------- END - GENERATED CODE --------- --------- BEGIN SURVEY QUERY --------- -------------------------------------------------------- --- CommservSurveyQuery_208.sql Ver 20170322_1328 --- -------------------------------------------------------- --------- Insert your SQL statements here DECLARE @NumDaysBack AS INT = 3 --- CLEAN UP TEMPORARY TABLES UP FRONT PLEASE ! ---- IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails IF object_id('tempdb.dbo.#RunningJobs') is not null DROP TABLE #RunningJobs DECLARE @csTimeZone NVARCHAR(1024) = '' SELECT @csTimeZone = dbo.GetClientTimeZone(2) IF ISNULL(@csTimeZone, '') = '' BEGIN SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2 SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK) WHERE TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255) END DECLARE @CurrentTime INT = dbo.GetUnixTime(GETUTCDATE()) DECLARE @CurrCollectionTime INT = @CurrentTime DECLARE @LastCollectionTime INT = 0 DECLARE @isPrivateRunning varchar(256)=''; DECLARE @frequencyMode NVARCHAR(MAX) ='0'; IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams')) SELECT @frequencyMode = mode from #MetricsInputParams IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning') SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyRunning' IF (@isPrivateRunning = 'Diagnostics and Usage') --Public BEGIN DECLARE @isJobDetailsCollectionEnabled INT = 0 SELECT @isJobDetailsCollectionEnabled = CAST(CAST(value AS VARCHAR(20)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyPublicJobStatsEnabled' IF (@isJobDetailsCollectionEnabled <> 1) --No need to collect job details BEGIN DECLARE @relId INT SELECT @relId = MAX(id) FROM simAllGalaxyRel IF (@relId <= 15) --V10 or below done have check for null value. So set outputxml with surveyxml as blank BEGIN SET @outputXML = ( SELECT @queryId AS '@QueryId', dbo.GetUnixTime(GETUTCDATE()) AS '@LogDate', 0 AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA') ) END RETURN END END DECLARE @TIME_STAMP_NAME varchar(80) IF ((@frequencyMode = '1') or (@isPrivateRunning = 'Metrics Reporting')) SET @TIME_STAMP_NAME = 'CommservSurveyPrivateAdminJobStatsCollectionTime' else IF ((@frequencyMode = '3') or (@isPrivateRunning = 'Metrics Direct Dip')) SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIPAdminJobStatsCollectionTime' ELSE SET @TIME_STAMP_NAME = 'CommservSurveyPublicAdminJobStatsCollectionTime' IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL BEGIN DECLARE @LastCollectionTimeSTR NVARCHAR(256) = '' EXEC GetSurveyParamValue @TIME_STAMP_NAME, @LastCollectionTimeSTR OUTPUT SELECT @LastCollectionTime = CAST(@LastCollectionTimeSTR AS INT) END ELSE BEGIN SELECT @LastCollectionTime = CAST(CAST(value AS VARCHAR(20)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = @TIME_STAMP_NAME END IF @LastCollectionTime = 0 BEGIN DECLARE @LastCollection DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE())-@NumDaysBack, 0) SET @LastCollection = dbo.ClientLocalToUTCTime(@LastCollection, 2) DECLARE @CSFirstJobTime INT SELECT @CSFirstJobTime = MIN(servEnd) FROM JMAdminJobStatsTable B WITH (NOLOCK) IF (@CSFirstJobTime < dbo.GetUnixTime(@LastCollection)) SET @LastCollectionTime = dbo.GetUnixTime(@LastCollection) ELSE SET @LastCollectionTime = @CSFirstJobTime END IF @LastCollectionTime < (@CurrentTime - @NumDaysBack*24*3600) SET @CurrCollectionTime = (@LastCollectionTime + @NumDaysBack*24*3600) DECLARE @releaseId INT SELECT @releaseId = MAX(id) FROM simAllGalaxyRel CREATE TABLE #JobDetails ( JobId INT, AppId INT, AppTypeId INT, ArchGrpId INT, OpType INT, subOpType INT, BkpLevel INT, Status INT, StartDate INT, EndDate INT, DataProtected BIGINT, DataTransferred BIGINT, DataWritten BIGINT, ErrorCode VARCHAR(32), FailureReason NVARCHAR(512), SubTaskId INT, Attempts INT, initFrom INT, NoOFPhases INT, failureErrorCode INT, workflowName NVARCHAR(256) ) CREATE TABLE #RunningJobs ( JobId INT, AppId INT, OpType INT, subOpType INT, BkpLevel INT, StartDate INT, State INT, InitFrom INT, FailureReason NVARCHAR(MAX) ) IF @releaseId > 14 --above v9 BEGIN INSERT INTO #JobDetails SELECT B.jobId, appId, 1000, B.archGrpID, B.opType, JS.subOpType, bkpLevel= ER_bkpLevel, displayStatus=CASE dataStatus WHEN 0 THEN B.status ELSE dataStatus END, servStartDate=servStart, servEndDate=servEnd, totalUncompBytes=totalSize, nwTransBytes, 0, '', CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END, subTaskId, numAttempts=0, B.initFrom, 0, b.failureErrorCode, CASE WHEN B.opType in (90,39) THEN (Select name from WF_Definition where WorkflowId = B.workFlowId) ELSE '' end FROM JMAdminJobStatsTable B INNER JOIN JMJobStats JS WITH (NOLOCK) ON B.jobId = JS.jobId AND JS.commCellId = B.commCellId WHERE servEnd BETWEEN @LastCollectionTime AND @CurrCollectionTime END ELSE BEGIN INSERT INTO #JobDetails SELECT B.jobId, appId, 1000, B.archGrpID, B.opType, JS.subOpType, bkpLevel= ER_bkpLevel, displayStatus=CASE dataStatus WHEN 0 THEN B.status ELSE dataStatus END, servStartDate=servStart, servEndDate=servEnd, totalUncompBytes=totalSize, nwTransBytes, 0, '', CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END, subTaskId, numAttempts=0, B.initFrom, 0, b.failureErrorCode, '' FROM JMAdminJobStatsTable B INNER JOIN JMJobStats JS WITH (NOLOCK) ON B.jobId = JS.jobId AND JS.commCellId = B.commCellId WHERE servEnd BETWEEN @LastCollectionTime AND @CurrCollectionTime END INSERT INTO #RunningJobs SELECT B.jobId,AppId=0,B.opType,B.subOpType,BkpLevel=J.ER_BkpLevel, B.jobStartTime,B.STATE, B.initFrom, dbo.JMGetLocalizedMessageFunc(0, B.failureReason) FROM JMJobInfo B INNER JOIN JMAdminJobInfoTable J WITH (NOLOCK) ON J.jobId = B.jobId UPDATE #JobDetails 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 #JobDetails 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.Status <> 1 AND A.FailureReason <> '' update #JobDetails SET NoOFPhases = A.numOfPhases, Attempts = A.numOfAttempts FROM #JobDetails B INNER JOIN (Select A.jobId, COUNT(DISTINCT A.phaseNum) AS numOfPhases,COUNT(*) AS numOfAttempts FROM JMAdminJobAttemptStatsTable A INNER JOIN #JobDetails B ON B.jobId = A.jobId AND 2= A.commCellId GROUP BY A.jobId ) A ON A.jobId = B.JobId SET @surveyXML = (SELECT (SELECT J.JobId AS '@JobId', J.Status AS '@Status', J.OpType AS '@OpType', J.subOpType AS '@SubOpType', J.BkpLevel AS '@BkpLevel', dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.StartDate), @csTimeZone)) AS '@StartDate', dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.EndDate), @csTimeZone)) AS '@EndDate', J.DataProtected AS '@DataProtected', J.ErrorCode AS '@ErrorCode', REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(J.FailureReason, ',',' '), CHAR(10),';'), CHAR(13),';'), '''',' '), CHAR(145),' '), CHAR(146),' '), '
','') AS '@FailureReason', dbo.NormalizeForXML(ISNULL(AG.name, 'N/A')) AS '@StoragePolicy', ISNULL(AG.id, '1') AS '@StoragePolicyID', T.taskName AS '@SchedulePolicy', ST.subTaskName AS '@ScheduleName', J.Attempts AS '@Attempts', J.initFrom AS '@InitFrom', J.NoOFPhases AS '@Phases', J.failureErrorCode AS '@failureErrorCode', J.workflowName AS '@workflowName' FROM #JobDetails J LEFT OUTER JOIN archGroup AG WITH (NOLOCK) ON J.ArchGrpId = AG.id LEFT OUTER JOIN TM_SubTask ST WITH (NOLOCK) ON J.SubTaskId = ST.subTaskId LEFT OUTER JOIN TM_Task T WITH (NOLOCK) ON ST.taskId = T.taskId ORDER BY J.JobId FOR XML PATH('JobStats'), TYPE), (SELECT H.JobId as '@JobId', H.OpType as '@OpType', H.subOpType AS '@SubOpType', H.BkpLevel as '@BkpLevel', dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(H.StartDate), @csTimeZone)) as '@StartDate', H.State as '@State', H.InitFrom as '@InitFrom', H.FailureReason as '@FailureReason' FROM #RunningJobs H ORDER BY H.StartDate FOR XML PATH ('RunningJobStats'),TYPE) FOR XML PATH ('JobDetails') ) DROP TABLE #JobDetails DROP TABLE #RunningJobs --------- END SURVEY QUERY --------- --------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY --------- 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') ) --select cast (@surveyXML as XML) -- UPDATE TIME STAMP TO GXGLOBALPARAM. IF NOT ALREADY PRESET, MUST ACCOUNT -- .. FOR OLDER VERSION OF GXGLOBALPARAM NOT HAVING AS MANY COLUMNS! IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL BEGIN EXEC SetSurveyParamValue @TIME_STAMP_NAME, '' END ELSE BEGIN DECLARE @DO_IT varchar(256) IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @TIME_STAMP_NAME) BEGIN IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15) INSERT INTO GXGlobalParam (name, value) SELECT @TIME_STAMP_NAME, '' ELSE BEGIN SET @DO_IT = 'INSERT INTO GXGlobalParam (name, value, created, modified) VALUES(''' + @TIME_STAMP_NAME + ''', '''',' + CONVERT(varchar(20), @logDate) -- Use Proper "create" date! + ',0)' EXEC (@DO_IT) END END END IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000); SET @ErrorMessage = ERROR_MESSAGE(); RAISERROR(@ErrorMessage,16,1); RETURN END CATCH IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL BEGIN EXEC SetSurveyParamValue @TIME_STAMP_NAME, @CurrCollectionTime END ELSE BEGIN UPDATE GXGlobalParam SET value = CAST(@CurrCollectionTime AS VARCHAR(20)) where name = @TIME_STAMP_NAME END SET NOCOUNT OFF --------- END - GENERATED CODE ---------