--- 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 = 30 DECLARE @surveyXML NVARCHAR(MAX) 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 @LastCollectionAttrName VARCHAR(256) DECLARE @freequencyMode NVARCHAR(MAX) = '0' IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams')) SELECT @freequencyMode = mode from #MetricsInputParams DECLARE @isPrivateRunning varchar(256)=''; IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning') BEGIN SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyRunning' END IF ((@isPrivateRunning = 'Metrics Reporting') OR (@freequencyMode = '1')) BEGIN SET @LastCollectionAttrName = 'CommservSurveyPrivateTop10ErrorsCollectionDate' END else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3')) BEGIN SET @LastCollectionAttrName = 'CommservSurveyDirectDipTop10ErrorsCollectionDate' END ELSE BEGIN SET @LastCollectionAttrName = 'CommservSurveyPublicTop10ErrorsCollectionDate' END DECLARE @nMonths INT DECLARE @lastCollectionDate DATETIME IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL BEGIN DECLARE @lastCollectionDateSTR VARCHAR(256)=''; EXEC GetSurveyParamValue @LastCollectionAttrName, @lastCollectionDateSTR OUTPUT IF @lastCollectionDateSTR <> '' BEGIN SELECT @lastCollectionDate = CAST(@lastCollectionDateSTR AS DATE) IF DATEDIFF(MONTH, @lastCollectionDate, GETDATE()) <= 0 SET @nMonths = 1 ELSE SET @nMonths = 2 END ELSE SET @nMonths = 2 END ELSE BEGIN IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName) BEGIN SELECT @lastCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATE) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName IF DATEDIFF(MONTH, @lastCollectionDate, GETDATE()) <= 0 SET @nMonths = 1 ELSE SET @nMonths = 2 END ELSE BEGIN SET @nMonths = 2 END END DECLARE @nowTime INT = dbo.GetUnixTime(GetUTCDate()) DECLARE @oneHour INT = 3600 DECLARE @secondsInADay INT = 24 * @oneHour DECLARE @nDays int = 1 -- This query is to get Top 10 Errors in last 1 day along with the number of jobs affected by each failure error code DECLARE @topNErrors INT = 10 DECLARE @fromTime INT = @nowTime - (@nDays * @secondsInADay) DECLARE @toTime INT = @nowTime IF OBJECT_ID('tempdb.dbo.#JobFailureReason') IS NOT NULL DROP TABLE #JobFailureReason CREATE TABLE #JobFailureReason (jobId INT, commCellId INT, failureReason VARCHAR(256)) IF OBJECT_ID('tempdb.dbo.#JobErrorMsg') IS NOT NULL DROP TABLE #JobErrorMsg CREATE TABLE #JobErrorMsg (messageId INT, jobId INT, failureId INT) IF OBJECT_ID('tempdb.dbo.#DrillDownFailureReason') IS NOT NULL DROP TABLE #DrillDownFailureReason CREATE TABLE #DrillDownFailureReason(messageId INT, numberOfJobs INT, errorCode NVARCHAR(64), ErrorAdvice NVARCHAR(1024)) DECLARE @FailureReason TABLE (messageId INT, numberOfJobs INT, errorCode NVARCHAR(64), errorMsg NVARCHAR(1024)) DECLARE @Top10Errors XML DECLARE @TopDrillDownJPR XML DECLARE @STARTDATE DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1 ,0) DECLARE @ENDDATE DATE DECLARE @i INT = 0 WHILE @i <= @nMonths BEGIN INSERT INTO #JobFailureReason SELECT J.jobId, J.commCellId, J.failureReason FROM JMJobInfo J WITH(NOLOCK) INNER JOIN JMBkpJobInfo B ON J.jobId = B.jobId WHERE J.state NOT IN (1,4,9,12,17,19) AND J.failureReason <> '' INSERT INTO #JobFailureReason SELECT jobId, commCellId, failureReason FROM JMBkpStats WITH(NOLOCK) WHERE failureReason <> '' AND servEndDate BETWEEN @fromTime AND @toTime AND opType IN (4, 14, 18, 30, 43, 59, 60, 65, 76, 87, 91, 94, 97, 98, 101) AND commCellId = 2 AND status IN (2, 4, 9) ------------------------------------------------------------------------------------ INSERT INTO #JobErrorMsg SELECT M.messageId, M.jobId, MAX(M.id) FROM #JobFailureReason J CROSS APPLY dbo.SplitIDString(J.failureReason) FR INNER JOIN JMFailureReasonMsg M ON J.jobId = M.jobId AND J.commCellId = M.commCellId AND M.id = CAST(FR._ID AS INT) GROUP BY M.messageId, M.jobId INSERT INTO @FailureReason SELECT TOP (@topNErrors) messageId, COUNT(*), '', '' FROM #JobErrorMsg M INNER JOIN #JobFailureReason B ON M.jobId = B.jobId GROUP BY messageId ORDER BY COUNT(*) DESC UPDATE R SET errorCode = CAST(M.SubsystemID AS NVARCHAR(12)) + ':' + CAST(M.MessageNum AS NVARCHAR(16)), errorMsg = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( M.Message ,'^', '...') ,'...1', '...'),'...2', '...'),'...3', '...'),'...4', '...'),'...5', '...'),'...6', '...'),'...7', '...'),'...8', '...'),'...9', '...'),'...0', '...') , '%s', ''), '%a', ''), '%d', ''), '%ld', ''), '%lld', ''), '%x', ''), '%lx', ''),'[...]','...'),'...','[...]') FROM @FailureReason R INNER JOIN EvLocaleMsgs M ON R.messageId = M.MessageID AND M.LocaleID = 0 ---below code is added to populate drill down failure information and saved into #DrillDownFailureReason ---------------- --------- now the Advice will be like "Media[]: Library is disabled. Advice: Please check the library status on the Console." use below code to polish data INSERT INTO #DrillDownFailureReason(messageId, numberOfJobs, errorCode, ErrorAdvice) select MessageID,count(jobID), ErroCode, CASE WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) > 0 THEN substring(Advice,charindex(']:',Advice)+2,len(Advice)) WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) = 0 THEN substring(Advice,charindex('Advice:',Advice),len(Advice)) ELSE ErrorMsg END as ErrorAdvice FROM (SELECT distinct R.MessageID as MessageID, R.JobID as JobId, F.errorCode as ErroCode, dbo.JMGetLocalizedMessageFunc(0,cast(R.failureId as nvarchar(max))+',') as Advice, F.errorMsg as ErrorMsg FROM #JobErrorMsg R INNER JOIN @FailureReason F ON R.messageID = F.messageID)T GROUP BY MessageID,ErroCode, CASE WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) > 0 THEN substring(Advice,charindex(']:',Advice)+2,len(Advice)) WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) = 0 THEN substring(Advice,charindex('Advice:',Advice),len(Advice)) ELSE ErrorMsg END -------for all list error code, using full error string -------------------------------------------------------------------------------- UPDATE D SET D.ErrorAdvice = dbo.JMGetLocalizedMessageFunc(0,cast(R.failureId as nvarchar(max))+',') FROM #DrillDownFailureReason D INNER JOIN #JobErrorMsg R ON D.messageId = R.messageID WHERE D.ErrorCode IN ('32:289','19:1335','72:151','18:106','82:158','91:139','91:113','18:146','94:13','32:487','32:392','19:1327','30:325') --" Mount path is offline. Advice: Please check if the mount path is online and accessible. The offline reason shown on mount path properties will indicate why it's unavailable. <br>Source: patchnext, Process: JobManager"-- UPDATE #DrillDownFailureReason SET ErrorAdvice = CASE WHEN charindex('Source:',ErrorAdvice) > 0 then replace(substring(ErrorAdvice,0,charindex('Source:',ErrorAdvice)),'
','') ELSE ErrorAdvice END ------------------------------------------------------------------------------------------------------------------------------------ IF @i = 0 BEGIN SET @Top10Errors = ( SELECT ( SELECT errorCode AS '@errorCode', dbo.NormalizeForXML(errorMsg) AS '@errorDescription', numberOfJobs AS '@numberOfJobsAffected', messageId AS '@messageId' FROM @FailureReason ORDER BY numberOfJobs DESC FOR XML PATH ('failedErrorList'), TYPE), (SELECT errorCode AS '@errorCode', dbo.NormalizeForXML(ErrorAdvice) AS '@AdviceDescription', numberOfJobs AS '@numberOfJobsAffected', messageId AS '@messageId' FROM #DrillDownFailureReason ORDER BY numberOfJobs DESC FOR XML PATH ('failedErrorAdviceList'), TYPE) FOR XML PATH('JobManager_TopMostJobErrorsResp') ) END ELSE BEGIN DECLARE @tempXML XML SET @tempXML = ( SELECT @STARTDATE AS '@MonthStart', errorCode AS '@errorCode', dbo.NormalizeForXML(errorMsg) AS '@errorDescription', numberOfJobs AS '@numberOfJobsAffected' FROM @FailureReason ORDER BY numberOfJobs DESC FOR XML PATH ('MonthlyFailedErrorList') ) IF @tempXML IS NOT NULL SET @Top10Errors.modify('insert sql:variable("@tempXML") into (JobManager_TopMostJobErrorsResp)[1]') END SET @ENDDATE = @STARTDATE SET @STARTDATE = DATEADD(MONTH, -1, @ENDDATE) SET @fromTime = dbo.GetUnixTime(dbo.LocalToUTCTime(CONVERT(DATETIME, DATEADD(DAY, 0, @STARTDATE)), @csTimeZone)) SET @toTime = dbo.GetUnixTime(dbo.LocalToUTCTime(CONVERT(DATETIME, DATEADD(DAY, 0, @ENDDATE)), @csTimeZone)) SET @i += 1 DELETE FROM #JobFailureReason DELETE FROM #JobErrorMsg DELETE FROM @FailureReason DELETE FROM #DrillDownFailureReason END --Update the last collection time DECLARE @nreleaseId INT =1 SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2) SET @surveyXML = cast((@Top10Errors) as nvarchar(max)) --------- 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', @nDays as '@LastNDays', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA') ) 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 IF @LastCollectionDate IS NULL SELECT @LastCollectionDate = GETDATE() EXEC SetSurveyParamValue @LastCollectionAttrName, @LastCollectionDate END ELSE BEGIN IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName) UPDATE GXGlobalParam SET value = CAST(@LastCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName ELSE BEGIN DECLARE @nstring NVARCHAR(MAX) IF(@nreleaseId >= 15 ) BEGIN SET @nstring ='insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(GETDATE() as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)' EXEC sp_executesql @nstring END ELSE IF(@nreleaseId = 14 ) BEGIN insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(GETDATE() as NVARCHAR(MAX))) END END END SET NOCOUNT OFF --------- END - GENERATED CODE ---------