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

SET NOCOUNT ON

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

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


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

declare @lastCollectionTime DATETIME = NULL, @gxGlobalParamKey varchar(65), @isGxGlobalParamKeyPresent tinyint
DECLARE @isPrivateRunning varchar(256)='';

DECLARE @csReleaseId INT = 0
SELECT  @csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0
IF @csReleaseId = 16
    SELECT  @csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

DECLARE @freequencyMode	 NVARCHAR(MAX) = '0'
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	SELECT @freequencyMode = mode from #MetricsInputParams

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'))
	SET @gxGlobalParamKey = 'CommservSurveyPrivateLastCollectedAnomalyJobTimestamp'
else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3'))
	SET @gxGlobalParamKey = 'CommservSurveyDirectDipLastCollectedAnomalyJobTimestamp'
ELSE
	SET @gxGlobalParamKey = 'CommservSurveyPublicLastCollectedAnomalyJobTimestamp'

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @lastcollectionTimeSTR VARCHAR(256) = '';
	EXEC GetSurveyParamValue @gxGlobalParamKey, @lastcollectionTimeSTR OUTPUT
	SELECT @lastCollectionTime = CONVERT(DATETIME, @lastcollectionTimeSTR)
END
ELSE
BEGIN
	BEGIN TRY
		SELECT @lastCollectionTime = CONVERT(DATETIME, CAST(value AS VARCHAR)) FROM GXGlobalParam WHERE name = @gxGlobalParamKey
	END TRY
	BEGIN CATCH  
	END CATCH
END

IF @lastCollectionTime IS NULL
BEGIN
	SET @lastCollectionTime = DATEADD(DAY, -7, GETDATE())	
	SET @isGxGlobalParamKeyPresent = 0
END
ELSE
	SET @isGxGlobalParamKeyPresent = 1

IF OBJECT_ID('tempdb.dbo.#jobs') IS NOT NULL
	DROP TABLE #jobs
CREATE TABLE #jobs
(
	jobId INTEGER,
	commcellId INTEGER,
	jobState INTEGER,
	failureReason VARCHAR(256)
	PRIMARY KEY(jobId)
)
IF OBJECT_ID('tempdb.dbo.#errorMessages') IS NOT NULL
	DROP TABLE #errorMessages
CREATE TABLE #errorMessages
(
	messageId INTEGER,
	jobId	  INTEGER,
	failureId nvarchar(64),
	errorCode nvarchar(64),
	state			INTEGER,		
	PRIMARY KEY(messageId, jobId)
)

-- Variables
DECLARE @msgID INT = 0
DECLARE @subsystemID INT = 0
DECLARE @msgNum INT = 0
DECLARE @errorCodeStr nvarchar(64)
DECLARE @defaultCCId INT = 2

-- Form the configuration from input XML if present
DECLARE @topCount			INT = 30	
DECLARE @localeId			INT = 0
DECLARE @startDate			INT = dbo.getunixtime(@lastCollectionTime)
DECLARE @endDate			INT = dbo.getunixtime(GETDATE())
    
-- Get all running backup jobs
INSERT INTO #jobs
	SELECT jobId, commCellId, state, failureReason FROM RunningBackups WITH(NOLOCK)
		WHERE commCellId = @defaultCCId	AND guiAlertColorLevel >= 100000		
	UNION ALL
	SELECT jobId, commCellId, state, failureReason FROM RunningRestores WITH(NOLOCK)
		WHERE commCellId = @defaultCCId	AND guiAlertColorLevel >= 100000					
	UNION ALL
	SELECT jobId, commCellId, state, failureReason FROM RunningAdminJobs WITH(NOLOCK)
		WHERE commCellId = @defaultCCId	AND guiAlertColorLevel >= 100000		

--  Get all error message IDs from Failure Table grouped by jobs os that repeating error for same job are considered only once	
INSERT INTO #errorMessages(messageId, jobId,failureId, state)
	SELECT JFA.messageId, JFA.jobId, CAST( JFA.id as nvarchar(64)) + ',', CASE RB.jobState WHEN 3 THEN 1 ELSE 2 END -- 1 - Waiting, 2 - Pending
	FROM #jobs RB		
	INNER JOIN JMFailureReasonMsg JFA WITH(NOLOCK) ON JFA.jobId = RB.jobID AND JFA.commCellId = RB.commCellId 
				AND JFA.id = CAST(REPLACE(RIGHT(','+RB.failureReason, CHARINDEX(',',REVERSE(','+RB.failureReason),2)-1),',','') AS INT)
	WHERE RB.failureReason IS NOT NULL AND RB.failureReason <> '' AND RB.jobState IN (2,3) -- pending, waiting state
	
	
TRUNCATE TABLE #jobs
DECLARE @currentTime	INTEGER = DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())
DECLARE @pastNHours		INTEGER = 6 -- Default - Last six hours
IF @startDate = 0 AND @endDate = 0
BEGIN
	-- Default values
	SET @endDate = @currentTime
	SET @startDate = @currentTime - (@pastNHours * 60 * 60)
END
-- Get all backup jobs in Time frame
INSERT INTO #jobs
SELECT Bkp.jobId, Bkp.commCellId, status, failureReason 
FROM   JMBkpStats Bkp WITH(NOLOCK)
       INNER JOIN (
           SELECT O.jobId, MAX(O.attributeValueInt) AS attributeValueInt 
           FROM JMJobOptions O INNER JOIN JMBkpStats B ON O.jobId = B.jobId AND O.commCellId = B.commCellId
           WHERE O.attributeId = 76 AND ISNULL(O.attributeValueInt,0) > 0 
           	AND B.servEndDate BETWEEN @startDate AND @endDate AND (B.servEndDate - B.servStartDate) > 3600
		AND B.commCellId = @defaultCCId
		AND B.status IN (2,3,4,14,15) -- (_CVJobStatus::Fail,_CVJobStatus::PARTIALSUCCESS,_CVJobStatus::KILLED,_CVJobStatus::JMSUCCESSWITHWARNINGS,_CVJobStatus::SYSTEMFAILED)		
           GROUP BY O.jobId
       ) THRES ON Bkp.jobId = THRES.jobId AND Bkp.commCellId = @defaultCCId
WHERE  (Bkp.servEndDate - Bkp.servStartDate) > THRES.attributeValueInt --Jobs ran for more than 1 hour and exceeds threshold

--  Get all error message IDs from Failure Table grouped by jobs os that repeating error for same job are considered only once
INSERT INTO #errorMessages(messageId, jobId,failureId, state)
	SELECT JFA.messageId, JFA.jobId, CAST( JFA.id as nvarchar(64)) + ',', 3
	FROM #jobs RB		
	INNER JOIN JMFailureReasonMsg JFA WITH(NOLOCK) ON JFA.jobId = RB.jobID AND JFA.commCellId = RB.commCellId 
				AND JFA.id = CAST(REPLACE(RIGHT(','+RB.failureReason, CHARINDEX(',',REVERSE(','+RB.failureReason),2)-1),',','') AS INT)
	WHERE RB.failureReason IS NOT NULL AND RB.failureReason <> ''	
	
-- Add errorCode to table
UPDATE r
	SET errorCode = t.errorCode
FROM #errorMessages r
	INNER JOIN (
		SELECT
			m.MessageID messageId,
			CAST(m.SubsystemID AS nvarchar(12)) + ':' + CAST(m.MessageNum AS nvarchar(16)) errorCode
		FROM
			EvLocaleMsgs m WITH(READUNCOMMITTED)
			INNER JOIN (
				SELECT DISTINCT messageId FROM #errorMessages
			) e on
				m.MessageID = e.messageId
		WHERE
			m.LocaleID = @localeId
	) t ON
		r.messageId = t.messageId
						
SET @surveyXML = (SELECT (SELECT jobId AS '@jobId',
			errorCode AS '@errorCode',
			dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(@localeId, failureID)) AS '@errorDescription',						
			state AS '@state'
		FROM #errorMessages	
		FOR XML PATH('JobsAnomaly'), TYPE)		
		
		FOR XML PATH('Anomaly'))

IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @tempTimeStamp NVARCHAR(20)
	set @tempTimeStamp = CONVERT(VARCHAR(20),GETDATE(),120)
	EXEC SetSurveyParamValue @gxGlobalParamKey, @tempTimeStamp
END
ELSE IF @isGxGlobalParamKeyPresent = 0
BEGIN	
	IF @csReleaseId < 15
		INSERT INTO GXGlobalParam(name, value)
		VALUES(@gxGlobalParamKey, CAST(GETDATE() AS VARCHAR))	
	ELSE
	IF @isPrivateRunning = 'Metrics Reporting'
		EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyPrivateLastCollectedAnomalyJobTimestamp'', '''', 0, 0')
	ELSE
		EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyPublicLastCollectedAnomalyJobTimestamp'', '''', 0, 0')
END
ELSE
BEGIN
	UPDATE GXGlobalParam
	SET value = CONVERT(VARCHAR(20),GETDATE(),120)
	WHERE name = @gxGlobalParamKey
END

IF OBJECT_ID('tempdb.dbo.#jobs') IS NOT NULL
	DROP TABLE #jobs
IF OBJECT_ID('tempdb.dbo.#errorMessages') IS NOT NULL
	DROP TABLE #errorMessages


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

SET NOCOUNT OFF

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