

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

DECLARE @surveyXML NVARCHAR(MAX) = ''



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







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



--------- Insert your SQL statements here

DECLARE @isPrivateRunning varchar(256)='';

DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))

BEGIN

    SELECT @frequencyMode = mode from #MetricsInputParams

END

IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')

BEGIN

	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'

END

 

IF ((@frequencyMode ='1') OR  (@frequencyMode ='3') or (@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip'))

BEGIN

	IF OBJECT_ID('GetSurveyParamValue') IS NULL

	BEGIN

		IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = 'CommservSurveyScheduleIntervalOption')

		BEGIN

			IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)

				INSERT INTO GXGlobalParam (name, value) SELECT 'CommservSurveyScheduleIntervalOption', '1'

			ELSE

				EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyScheduleIntervalOption'', ''1'', 0, 0')

		END

	END

END



IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public

BEGIN

	DECLARE @isJobDetailsCollectionEnabled INT = 0

	IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL

	BEGIN

		DECLARE @isJobDetailsCollectionEnabledSTR VARCHAR(256) = '0';

		EXEC GetSurveyParamValue 'CommservSurveyPublicJobStatsEnabled', @isJobDetailsCollectionEnabledSTR OUTPUT

		SELECT	@isJobDetailsCollectionEnabled = CAST(@isJobDetailsCollectionEnabledSTR AS INT)

	END

	ELSE

		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 @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 @TIME_STAMP_NAME NVARCHAR(128) = 'CommservSurveyJobStatsCollectionTime'

declare @date datetime =GETDATE()

DECLARE @nDays INT = datediff(day,DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0),@date)+1

IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public

BEGIN

	SET @TIME_STAMP_NAME = 'CommservSurveyPublicJobStatsCollectionTime'

	SET @nDays = 3	--Go back 3 days

END

else IF ((@frequencyMode ='3') or (@isPrivateRunning = 'Metrics Direct Dip'))

begin

	SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIPJobStatsCollectionTime'

end



IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL

BEGIN

	DECLARE @LastCollectionTimeSTR VARCHAR(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())-@nDays, 0)

	SET	@LastCollection	= dbo.ClientLocalToUTCTime(@LastCollection, 2)

	DECLARE @CSFirstJobTime INT

	SELECT @CSFirstJobTime = MIN(servEndDate) FROM JMBkpStats B WITH (NOLOCK)	

	IF (@CSFirstJobTime < dbo.GetUnixTime(@LastCollection))

		SET @LastCollectionTime = dbo.GetUnixTime(@LastCollection) 

	ELSE

		SET @LastCollectionTime = @CSFirstJobTime

END



DECLARE @maxcollectionsec INT = 24*3600 --default set to collect max 24 hours

IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public

	SET @maxcollectionsec = @maxcollectionsec * 3	--Collect for maximum 3 days job in one run



IF	@LastCollectionTime < (@CurrentTime - @maxcollectionsec)

	SET @CurrCollectionTime = (@LastCollectionTime + @maxcollectionsec)



-- Check if any special group to consider for data collection.

-- Get the list of client group ids if exists

DECLARE @groupStr varchar(256);

DECLARE @groupStrName Nvarchar(MAX)='';



IF ((@frequencyMode ='1') OR  (@frequencyMode ='3') or (@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip'))

BEGIN

	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyPrivateSpecialClientGroup'

END

ELSE

BEGIN

	SELECT @groupStr = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveySpecialClientGroup'

END



SET @groupStr = @groupStr + ',';

declare @includeGroups table(groupID int);

DECLARE @endIndex integer=0

DECLARE @startIndex integer=0

DECLARE @groupId integer        



SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)

  

WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)

BEGIN

	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)

	if(@groupId > 0)

	BEGIN

		insert into @includeGroups values(@groupId);

		DECLARE @tmpStr NVARCHAR(100);

		SELECT @tmpStr = NAME FROM APP_ClientGroup WITH (NOLOCK) WHERE id = @groupId;

		IF(@tmpStr IS NOT NULL)

			SET @groupStrName = @groupStrName + ', ' + @tmpStr

	END

	SET @startIndex = @endIndex + 1

	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)

END



DECLARE @specialGroupExists INT  =0



IF EXISTS ( SELECT id FROM app_clientgroup WITH (NOLOCK) join @includeGroups on id=groupID)

	SET @specialGroupExists =1





IF object_id('tempdb.dbo.#ExcludedAppIds') is not null DROP TABLE #ExcludedAppIds 

CREATE TABLE #ExcludedAppIds (appId INT)



INSERT INTO #ExcludedAppIds

SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) 

WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') 

  AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'))

  AND attrVal <> '0' AND modified = 0

UNION

SELECT id FROM APP_Application WITH (NOLOCK) 

WHERE clientId IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1')





CREATE TABLE #JobDetails (

        JobId INT, ParentJobId INT, TargetClientId INT, AppId INT, AppTypeId INT, 

        dataArchGrpId INT, logArchGrpId INT, StoragePolicy NVARCHAR(1026), PrimaryCopyId INT, OpType INT, BkpLevel INT, Status INT, StartDate INT, EndDate INT,

        ScanType INT, SizeChangePct INT, TransferTime INT, ProtectedObjects BIGINT, FailedObjects BIGINT, FailedFolders BIGINT,

        DataProtected BIGINT, DataCompressed BIGINT, DataTransferred BIGINT, DataWritten BIGINT, 

        ErrorCode VARCHAR(32), FailureReason NVARCHAR(512), SubTaskId INT, Attempts INT

        ,initFrom INT,NoOFPhases INT,failureErrorCode INT



)

CREATE TABLE #RunningJobs

(

	JobId INT,

	AppId INT,

	OpType INT,

	BkpLevel INT,

	StartDate INT,

	State INT,

	InitFrom INT,

	FailureReason NVARCHAR(MAX)

	

)



IF  (@specialGroupExists =1 )

BEGIN

	INSERT INTO #JobDetails

	SELECT	jobId, -1, B.targetClientId,

		B.appId, appType, 

		CASE WHEN B.dataBackedUp = 1 THEN B.dataArchGrpId ELSE 0 END, 

		CASE WHEN B.LogsBackedUp = 1 THEN B.logArchGrpId ELSE 0 END,'', 0,

		opType, bkpLevel, CASE displayStatus WHEN 0 THEN B.status ELSE displayStatus END,

		servStartDate, servEndDate,

		scanType, percentageChange, totalWriteTime, totalNumOfFiles,

		scanFileFailures + backupFileFailures, scanFolderFailures + backupFolderFailures,

		totalUncompBytes, totalCompBytes, nwTransBytes, 0, '',

		CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason

			 ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,

		subTaskId, numAttempts

		,B.initFrom ,0,b.failureErrorCode

	FROM	JMBkpStats B WITH (NOLOCK)

			INNER JOIN APP_Application A WITH (NOLOCK)

				ON B.appId = A.id 

			INNER JOIN APP_Client C WITH (NOLOCK)

				ON C.id = A.clientId

			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)

				ON CGA.clientId = C.id

			INNER JOIN @includeGroups CG 

				ON CGA.clientGroupId = CG.groupId

		LEFT OUTER JOIN #ExcludedAppIds E 

			ON B.appId = E.appId

	WHERE	servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime

	AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)

		AND E.appId IS NULL



	INSERT INTO #RunningJobs

		SELECT	B.jobId,J.applicationId,B.opType,J.bkpLevel, B.jobStartTime,B.STATE ,B.initFrom, dbo.JMGetLocalizedMessageFunc(0, B.failureReason)

		FROM JMJobInfo B 

		INNER JOIN JMBkpJobInfo J ON J.jobId = B.jobId

		AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)

			INNER JOIN APP_Application A WITH (NOLOCK) ON J.applicationId= A.id

			INNER JOIN APP_Client C WITH (NOLOCK)

				ON C.id = A.clientId			

			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)

				ON CGA.clientId = C.id

			INNER JOIN @includeGroups CG 

				ON CGA.clientGroupId = CG.groupId

		LEFT OUTER JOIN #ExcludedAppIds E 

			ON A.id = E.appId

	WHERE E.appId IS NULL	

END

ELSE

BEGIN

	INSERT INTO #JobDetails

	SELECT	jobId, -1, B.targetClientId,

		B.appId, appType, 

		CASE WHEN B.dataBackedUp = 1 THEN B.dataArchGrpId ELSE 0 END, 

		CASE WHEN B.LogsBackedUp = 1 THEN B.logArchGrpId ELSE 0 END,'', 0,

		opType, bkpLevel, CASE displayStatus WHEN 0 THEN B.status ELSE displayStatus END,

		servStartDate, servEndDate,

		scanType, percentageChange, totalWriteTime, totalNumOfFiles,

		scanFileFailures + backupFileFailures, scanFolderFailures + backupFolderFailures,

		totalUncompBytes, totalCompBytes, nwTransBytes, 0, '',

		CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason

			 ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,

		subTaskId, numAttempts

		,B.initFrom ,0,b.failureErrorCode

	FROM	JMBkpStats B WITH (NOLOCK)

		LEFT OUTER JOIN #ExcludedAppIds E 

			ON B.appId = E.appId

	WHERE	servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime

	AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)

		AND E.appId IS NULL



	INSERT INTO #RunningJobs

	SELECT	B.jobId,J.applicationId,B.opType,J.bkpLevel, B.jobStartTime,B.STATE ,B.initFrom, dbo.JMGetLocalizedMessageFunc(0, B.failureReason)

	FROM JMJobInfo B 

	INNER JOIN JMBkpJobInfo J ON J.jobId = B.jobId

	AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)

		LEFT OUTER JOIN #ExcludedAppIds E 

			ON J.applicationId = E.appId

	WHERE E.appId IS NULL

END



UPDATE	J SET ParentJobId = L.parentJobId

FROM	#JobDetails J

		INNER JOIN JMJobDataLink L WITH (NOLOCK) ON J.JobId = L.childJobId AND L.linkType = 7 -- LINK_TYPE_FOR_VSA_V2_PARENT_CHILD 



UPDATE  J SET PrimaryCopyId = CASE WHEN J.OpType = 59 THEN G.defaultSnapCopy ELSE G.defaultCopy END

FROM    #JobDetails J 

        INNER JOIN archGroup G WITH (NOLOCK) ON J.logArchGrpId = G.id OR J.dataArchGrpId = G.id AND (J.dataArchGrpId > 0 OR J.logArchGrpId > 0)



UPDATE  J SET StoragePolicy = AG.name

FROM	#JobDetails J INNER JOIN archGroup AG WITH (NOLOCK) ON J.dataArchGrpId = AG.id WHERE J.dataArchGrpId > 0

UPDATE  J SET StoragePolicy = (CASE WHEN J.dataArchGrpId>0 THEN StoragePolicy+', ' ELSE '' END) + AG.name

FROM	#JobDetails J INNER JOIN archGroup AG WITH (NOLOCK) ON J.logArchGrpId = AG.id WHERE J.dataArchGrpId != J.logArchGrpId AND J.logArchGrpId > 0





CREATE TABLE #JobSizeOnMedia (JobId INT, DataWritten BIGINT)



INSERT INTO #JobSizeOnMedia

SELECT	A.jobId, SUM(A.sizeOnMedia)

FROM	JMJobDataStats A WITH (NOLOCK) 

		INNER JOIN #JobDetails B ON A.jobId = B.JobId AND A.archGrpCopyId = B.PrimaryCopyId AND A.commCellId = 2

GROUP BY A.jobId



UPDATE	#JobDetails SET DataWritten = S.DataWritten

FROM	#JobSizeOnMedia S

WHERE	#JobDetails.JobId = S.JobId



DROP TABLE #JobSizeOnMedia





CREATE TABLE #JobVMDetails (jobId INT, parentJobId INT, vmClientId INT, vmBackupStart INT, vmBackupEnd INT, vmStatus INT, 

		vmSize BIGINT, vmUsedSpace BIGINT, vmGuestSize BIGINT, vmIsGuestSizeValid INT, vmFailureReason NVARCHAR(1024))



INSERT	INTO #JobVMDetails

SELECT  P.jobId, 0, P.VMclientId, [vmBackupStartTime], [vmBackupEndTime], [vmStatus], [vmSize], [vmUsedSpace], [vmGuestSize], 

	CASE WHEN [BkpLevel] NOT IN (1, 64, 128, 1024, 16384, 32768) THEN 1 ELSE ISNULL([vmIsGuestSizeValid], 1) END, NULL

FROM    (SELECT V.jobId, V.VMclientId, J.BkpLevel, V.attrName, CAST(V.attrVal AS BIGINT) AS size

        FROM    APP_VMProp V WITH (NOLOCK) 

                INNER JOIN #JobDetails J WITH (NOLOCK) ON V.jobId = J.jobId AND V.commCellId = 2

        WHERE   V.attrName IN ('vmBackupStartTime', 'vmBackupEndTime', 'vmStatus', 'vmSize', 'vmUsedSpace', 'vmGuestSize', 'vmIsGuestSizeValid')) S

        PIVOT (MAX(size) FOR attrName IN ([vmBackupStartTime], [vmBackupEndTime], [vmStatus], [vmSize], [vmUsedSpace], [vmGuestSize], [vmIsGuestSizeValid])) AS P



UPDATE	J SET vmFailureReason = V.attrVal

FROM	#JobVMDetails J INNER JOIN APP_VMProp V WITH (NOLOCK) 

	 ON J.jobId = V.jobId AND V.commCellId = 2 AND J.vmClientId = V.VMclientId AND V.attrName = 'vmFailureReason'

WHERE	J.vmStatus <> 0





CREATE TABLE #ProtectedVMs (JobId INT, Protected INT, Failed INT)



INSERT INTO #ProtectedVMs

SELECT	jobId, SUM(CASE WHEN vmStatus = 0 THEN 1 ELSE 0 END), SUM(CASE WHEN vmStatus = 0 THEN 0 ELSE 1 END)

FROM	#JobVMDetails

GROUP BY jobId



UPDATE	#JobDetails SET ProtectedObjects = P.Protected, FailedObjects = P.Failed, FailedFolders = 0

FROM	#ProtectedVMs P

WHERE	#JobDetails.JobId = P.JobId



DROP TABLE #ProtectedVMs 





UPDATE	V SET JobId = J.JobId, ParentJobId = J.ParentJobId

FROM	#JobVMDetails V

		INNER JOIN #JobDetails J WITH (NOLOCK) ON V.jobId = J.ParentJobId AND V.vmClientId = J.TargetClientId





UPDATE	#JobDetails

SET		ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),

		FailureReason = LEFT(dbo.NormalizeForXML(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.phase) AS numOfPhases,COUNT(*) AS numOfAttempts

				FROM JMBkpAtmptStats A 	INNER JOIN #JobDetails B ON B.jobId = A.jobId AND 2= A.commCellId 

				GROUP BY A.jobId

			 ) A

ON A.jobId = B.JobId

			 



CREATE TABLE #JobDBSize (jobId INT, jobEndDate INT, dbName NVARCHAR(1024), dbSize BIGINT)



INSERT INTO #JobDBSize

SELECT J.JobId, J.EndDate, N2.name, SUM(I.backup_size)

FROM 	#JobDetails J

	INNER JOIN sqlDbBackupInfo I WITH (NOLOCK) ON I.jobId = J.JobId

	INNER JOIN sqlNames N WITH (NOLOCK) ON N.sqlId = I.sqlNameId and N.type = 1

	INNER JOIN sqlnames2 N2 WITH (NOLOCK) ON N2.id = N.sqlid AND N2.type = 1

GROUP BY J.JobId, J.EndDate, N2.name



CREATE TABLE #SybDBJobPageSize (JobId INT, PageSize INT)



INSERT INTO #SybDBJobPageSize

SELECT JD.jobId, CAST(attrVal AS INT) AS PageSize

FROM 

		#JobDetails JD

		INNER JOIN APP_Application A WITH (NOLOCK)

			ON JD.AppId = A.id

		INNER JOIN APP_InstanceProp I WITH (NOLOCK)

			ON I.componentNameId = A.instance

			AND I.attrName = 'SYBASE page size'

			AND	I.created < JD.StartDate

			AND (I.modified >= JD.StartDate OR I.modified = 0)



INSERT INTO #JobDBSize

SELECT J.JobId, MAX(J.EndDate), U.sybDatabaseName, (SUM(CAST(sybSysVsize AS BIGINT)) * S.PageSize)

FROM #SybDBJobPageSize S

	INNER JOIN #JobDetails J WITH (NOLOCK)

		ON S.JobId = J.JobId

	INNER JOIN sybSystemUsages U WITH (NOLOCK)

		ON U.sybBackupJobId_l = J.JobId

GROUP BY J.JobId, S.PageSize, U.sybDatabaseName	



DROP TABLE #SybDBJobPageSize



--Below Anomaly jobs are also being collected in query 24 for public metrics scenario. Please consider any changes made here to be handled on both the places.

DECLARE @AnomalycurrentTime INT = DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE()) 

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

   CREATE TABLE #AnomalousJobs (clientID int,jobId INT,percentageComplete DECIMAL(10,2),subClientName  nvarchar(1024),delayReason nvarchar(MAX),runTime int,flag int, agenttype int, agentName nvarchar(1024), subclientid int, delayReasonRAW nvarchar(MAX), state nvarchar(1024),clientName nvarchar(1024),opType INT,bkpLevel INT, thresholdtime INT )

   

INSERT INTO #AnomalousJobs (clientID ,jobId ,percentageComplete ,subClientName  ,delayReason ,runTime ,flag , agenttype , agentName , subclientid, delayReasonRAW , state ,clientname , opType,bkpLevel)

SELECT clientId,jobid,percentComplete,subClientName,delayReason,@AnomalycurrentTime - jobStartTime,1,appTypeId,appTypeName, applicationId,CASE WHEN LEN(failureReason)>0 THEN LEFT(failureReason,CHARINDEX(',',failureReason)-1) END,displayState,clientname,opType,bkpLevel from RunningBackups WHERE guiAlertColorLevel>=100000



UPDATE AJ SET thresholdTime = (JO.attributeValueInt + (JO.attributeValueInt * 0.05))/60

FROM #AnomalousJobs AJ

INNER JOIN JMJobOptions JO ON JO.jobId = AJ.jobId AND JO.attributeId = 76 /*JM_JOB_RUNNING_THRESHOLD_TIME_ID*/ 



UPDATE AJ SET delayReasonRAW = Msg.message

FROM #AnomalousJobs AJ

INNER JOIN (SELECT A.jobid,L.message FROM #AnomalousJobs A  

	            INNER JOIN JMFailureReasonMsg F ON F.id = A.delayReasonRAW

				INNER JOIN EvLocaleMsgs L ON L.messageId = F.messageId AND localeID = 0

		   )Msg ON Msg.jobId = AJ.jobId



UPDATE #AnomalousJobs SET delayReason = delayReason + '<br><br>'+dbo.NTGetPossibleCausesForLongRunTime(jobid,1,0)

		   

UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'[^','')

UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%s]','')

UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%a]','')

UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%d]','')

UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%','')

UPDATE #AnomalousJobs SET delayReasonRAW = 'No delay reason'

Where LEN(delayReasonRAW)<2 OR delayReasonRAW is NULL



SET @surveyXML = (SELECT

	(SELECT	J.AppId AS '@AppId',

		A.appTypeId AS '@AppTypeId',

		dbo.NormalizeForXML(CL.name) AS '@ClientName',

		T.name AS '@AgentName',

		dbo.FixInstanceName(I.name, A.appTypeId) AS '@InstanceName',

		dbo.NormalizeForXML(BS.name) AS '@BackupsetName',

		dbo.NormalizeForXML(A.subclientName) AS '@SubclientName',

		'' AS '@ClientGroups'

	FROM	(SELECT AppId FROM #JobDetails UNION SELECT AppId FROM #RunningJobs) J

		INNER JOIN APP_Application A WITH (NOLOCK) ON J.AppId = A.id

		INNER JOIN APP_Client CL WITH (NOLOCK) ON A.clientId = CL.id

		INNER JOIN APP_iDAType T WITH (NOLOCK) ON A.appTypeId = T.type

		INNER JOIN APP_InstanceName I WITH (NOLOCK) ON A.instance = I.id

		INNER JOIN APP_BackupSetName BS WITH (NOLOCK) ON A.backupSet = BS.id

	ORDER BY J.AppId

	FOR XML PATH('Subclients'), TYPE),



	(SELECT	J.JobId AS '@JobId',

		J.AppId AS '@AppId',

		J.Status AS '@Status',

		J.OpType AS '@OpType',

		J.BkpLevel AS '@BkpLevel',

		J.ScanType AS '@ScanType',

		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.DataCompressed AS '@DataCompressed',

		J.DataTransferred AS '@DataTransferred',

		J.DataWritten AS '@DataWritten',

		J.SizeChangePct AS '@SizeChangePct',

		J.TransferTime AS '@TransferTime',

		J.ProtectedObjects AS '@ProtectedObjects',

		J.FailedObjects AS '@FailedObjects',

		J.FailedFolders AS '@FailedFolders',

		J.ErrorCode AS '@ErrorCode',

		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(J.FailureReason,

			',',' '), CHAR(10),';'), CHAR(13),';'), '''',' '), CHAR(145),' '), CHAR(146),' '), '<BR>','') AS '@FailureReason',

		dbo.NormalizeForXML(ISNULL(J.StoragePolicy, 'N/A')) AS '@StoragePolicy',

		J.dataArchGrpId AS '@DataArchGroupId',

		J.logArchGrpId AS '@LogArchGroupId',

		T.taskName AS '@SchedulePolicy',

		ST.subTaskName AS '@ScheduleName',

		J.Attempts AS '@Attempts'

		,J.initFrom AS '@InitFrom'

		,J.NoOFPhases AS '@Phases'

		,J.failureErrorCode AS '@failureErrorCode'

	FROM	#JobDetails J

		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	jobId AS '@JobId',

		parentJobId AS '@parentJobId',

		vmClientId AS '@vmClientId',

		vmBackupStart AS '@vmBackupStart',

		vmBackupEnd AS '@vmBackupEnd',

		vmStatus AS '@vmStatus',

		vmSize AS '@vmSize',

		vmUsedSpace AS '@vmUsedSpace',

		vmGuestSize AS '@vmGuestSize',

		vmIsGuestSizeValid AS '@vmIsGuestSizeValid',

		LEFT(vmFailureReason, 1024) AS '@vmFailureReason'

	FROM	#JobVMDetails

	ORDER BY JobId, vmClientId

	FOR XML PATH('JobVMStats'), TYPE),



	(SELECT	jobId AS '@JobId',

		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(jobEndDate), @csTimeZone)) AS '@JobEndDate',

		dbo.NormalizeForXML(dbName) AS '@DbName',

		dbSize AS '@DbSize'

	FROM	#JobDBSize

	ORDER BY JobId, dbName

	FOR XML PATH('JobDBSize'), TYPE),



	(SELECT 

		

          H.JobId as '@JobId',

          H.AppId as '@AppId',

          H.OpType       as   '@OpType',

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



	(SELECT Aj.jobId as '@JobId', 

	   CASE AJ.opType 

			WHEN 4 THEN (CASE WHEN AJ.agenttype = 67 THEN N'Compliance Archive' ELSE N'Backup' END) 

			WHEN 14 THEN N'Synthetic Full' 

			WHEN 18 THEN N'Application Commandline Backup' 

			WHEN 59 THEN N'Snap Backup' 

			WHEN 60 THEN N'Backup Copy' 

			WHEN 65 THEN N'Application Commandline Snap Backup' 

			WHEN 98 THEN N'Archive'

			ELSE N'Unknown' 

	   END AS '@Operation',

	   CASE AJ.State

		   WHEN 1 THEN 'Running'

		   WHEN 2 THEN 'Pending'

		   WHEN 3 THEN 'Waiting'

		   WHEN 4 THEN 'Completed'

		   WHEN 5 THEN 'Stopped'

		   WHEN 6 THEN 'Killpending'

		   WHEN 7 THEN 'Stoppedning'

		   WHEN 8 THEN 'Interruptpending'

		   WHEN 9 THEN 'Completed'

		   WHEN 10 THEN 'Fail'

		   WHEN 11 THEN 'Killed'

		   WHEN 12 THEN 'Partial Completed'

		   WHEN 13 THEN 'Kill pending'

		   WHEN 14 THEN 'Dormant'

		   WHEN 15 THEN 'Queued'

		   WHEN 16 THEN 'Dormant schedule wait'

		   WHEN 17 THEN 'Running Unverifiable'

		   ELSE ''

		   END AS '@State', 

		   AJ.clientid AS '@clientid',

		   AJ.clientname as '@ClientName',

		   AJ.agenttype as '@agenttype',

		   AJ.agentName as '@agentName',

		   AJ.subclientid as '@subclientid',

		   AJ.subclientname as '@SubClientName', 

		   AJ. percentageComplete as '@PercentageComplete', 

		   AJ.delayReason as '@DelayReason', 

		   AJ.runTime/60 as '@ElapsedTimeInMinutes',

		   thresholdTime as '@thresholdTime'

		   FROM #AnomalousJobs AJ

	   WHERE AJ.flag = 1

	   FOR XML PATH ('AnomalyJobs'), TYPE)

FOR XML PATH ('JobDetails')

)

--select cast (@surveyXML as XML)

DROP TABLE #JobDetails

DROP TABLE #JobVMDetails

DROP TABLE #JobDBSize

DROP TABLE #RunningJobs

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

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

)



IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @TIME_STAMP_NAME)

BEGIN

	IF OBJECT_ID('SetSurveyParamValue') IS NULL

	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

			EXEC QS_SetKeyIntoGlobalParamTbl @TIME_STAMP_NAME, 'Y', ''

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