--- 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 = 152
DECLARE @surveyXML NVARCHAR(MAX)

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



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

--------- Insert your SQL statements here 
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 @csReleaseId INT = 0
SELECT  @csReleaseId = releaseId FROM APP_Client WHERE id = 2

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

IF @csReleaseId > 15 OR @csReleaseId = 15 AND @csSPVersion >= 7
BEGIN
    RETURN -- Use query 246
END

DECLARE @isPrivateRunning varchar(256)='';
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END
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
DECLARE @TIME_STAMP_NAME NVARCHAR(128)
IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@frequencyMode ='1'))
BEGIN
	SET @TIME_STAMP_NAME = 'CommservSurveyPrivateRestoreJobStatsCollectionTime'
END
else IF ((@isPrivateRunning = 'Metrics Direct Dip') or (@frequencyMode ='3'))
begin
	SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIPRestoreJobStatsCollectionTime'
end
else
begin
	SET @TIME_STAMP_NAME = 'CommservSurveyRestoreJobStatsCollectionTime'
end

SELECT	@LastCollectionTime = CAST(CAST(value AS VARCHAR(20)) AS INT) 
FROM	GXGlobalParam
WHERE	name = @TIME_STAMP_NAME

IF OBJECT_ID('tempdb..#PhysicalNodeInfo') IS NOT NULL
	DROP TABLE #PhysicalNodeInfo
	
CREATE TABLE #PhysicalNodeInfo (clientId INT, activePhysicalNodeName nvarchar(256))

INSERT INTO #PhysicalNodeInfo
SELECT componentNameId, CL.name 
FROM 
(SELECT componentNameId,MAX(attrVal) AS nodeId FROM APP_ClientProp AC WHERE AC.attrName = 'Active Physical Node' AND AC.attrVal <> componentNameId AND AC.modified = 0 GROUP BY componentNameId) AC
	INNER JOIN App_Client CL ON AC.nodeId = CL.id
UNION 
SELECT c.id AS ClusterClientId, MIN(c1.name) AS PhysicalClientName
FROM APP_Client AS c WITH (NOLOCK)
	INNER JOIN APP_Application AS app WITH (NOLOCK)
		ON c.simOperatingSystemId = 56 --Oracle RAC
		AND c.id = app.clientId
	INNER JOIN (SELECT instaceId, MIN(clientId) AS clientId FROM APP_OracleRacInstance AS ori WITH (NOLOCK) GROUP BY instaceId) AS i
		ON app.instance = i.instaceId 
	INNER JOIN APP_Client AS c1 WITH (NOLOCK)
		ON c1.id = i.clientId
GROUP BY c.id
UNION 
SELECT cp.componentNameId AS ClusterClientId, MIN(cp.attrName) AS PhysicalClientName
FROM APP_ClientProp AS cp WITH (NOLOCK)
WHERE cp.attrType = 125 --PROPERTY_DAG_MEMBERSERVER for Exchange DAG
	AND cp.modified = 0
GROUP BY cp.componentNameId

-- 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 ((@isPrivateRunning = 'Metrics Reporting')OR  (@frequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
declare @includeClients table(cid int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        
DECLARE @MAXINT integer = 0x7FFFFFFF        

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 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 join @includeGroups on id=groupID)
BEGIN
	SET @specialGroupExists =1
	INSERT INTO @includeClients
	SELECT
		DISTINCT C.id
	FROM
		APP_Client C WITH (NOLOCK)
		JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
			ON CGA.clientId = C.id
		JOIN @includeGroups CG 
			ON CGA.clientGroupId = CG.groupId
END

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

create table #RestJobDetails (
	Jobid INT,
	JobStatus INT,
	SrcClientId INT,
	SrcClientName NVARCHAR(512),
	DestClientId INT,
	DestClientName NVARCHAR(512),
	AppTypeId INT,
	AppType NVARCHAR(512),
	InstanceId INT,
	Instance NVARCHAR(512),
	BkpsetId INT,
	Bkpset NVARCHAR(512),
	StartTime INT,
	EndTime INT,
	Duration INT,
	TotFiles2xfer BIGINT,
	TotSuccess BIGINT,
	SkippedObjects BIGINT,
	FailedObjects BIGINT,
	TotCompMB BIGINT,
	TotUnCompMB BIGINT,
	ErrorCode VARCHAR(32),
	FailedReason NVARCHAR(512),
	OpType INT,
	SubOpType INT,
	RstAttributes BIGINT,
	InitFrom INT,
	UserName NVARCHAR(512),
	JobDescription	NVARCHAR(MAX),
	BackupTime DATETIME
) 

IF  (@specialGroupExists =1 )
BEGIN
INSERT INTO #RestJobDetails
	SELECT   
		RS.jobid,
		RS.status,
		RS.srcClientId,
		SC.name,
		RS.destClientId,
		DC.name,
		RS.appType,
		APT.name,
		RS.instanceId,
		ISNULL(ITN.name, ''),
		RS.bkpsetId,
		ISNULL(BN.name, ''),
		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servStartTime), @csTimeZone)), 
        dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servEndTime), @csTimeZone)),  
		RS.duration,
		CAST(convert(real, RS.totFiles2xfer_h) * 214748.3648*10000 + convert(real, RS.totFiles2xfer_l) AS BIGINT),
		CAST(convert(real, RS.totSuccess_h) * 214748.3648*10000 + convert(real, RS.totSuccess_l) AS BIGINT),
		RS.totSkipped,
		RS.totFailed,
		CAST(convert(real, RS.totCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		CAST(convert(real, RS.totUnCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totUnCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		'',
		CASE 
			WHEN CHARINDEX(',', RS.failureReason) = LEN(RS.failureReason) THEN RS.failureReason
			ELSE RIGHT(RS.failureReason, CHARINDEX(',', REVERSE(RS.failureReason), 2)-1) 
		END,
		RS.opType,
		JS.subOpType,
		RS.rstattributes,
		RS.initFrom,
		RS.username,
		JS.jobDescription,
		NULL
	FROM JMRestoreStats RS WITH (NOLOCK)
		JOIN JMJobStats JS WITH (NOLOCK)
			ON RS.jobId = JS.jobId
		JOIN APP_Client DC WITH (NOLOCK) 
			ON RS.destClientId = DC.id 
		JOIN APP_Client SC WITH (NOLOCK) 
			ON RS.srcClientId = SC.id
		JOIN @includeClients AS IC
			ON IC.cid = SC.id
		JOIN APP_iDAType APT WITH (NOLOCK)
			ON RS.appType = APT.type 
		LEFT OUTER JOIN APP_InstanceName ITN WITH (NOLOCK)
			ON RS.instanceId = ITN.id  
		LEFT OUTER JOIN APP_BackupSetName BN WITH (NOLOCK)
			ON RS.bkpsetId = BN.id 
	WHERE RS.destClientId > 1 AND RS.servEndTime BETWEEN @LastCollectionTime AND @CurrCollectionTime
		AND RS.opType NOT IN (12, 52, 80, 105)
END
ELSE
BEGIN
INSERT INTO #RestJobDetails
	SELECT   
		RS.jobid,
		RS.status,
		RS.srcClientId,
		SC.name,
		RS.destClientId,
		DC.name,
		RS.appType,
		APT.name,
		RS.instanceId,
		ISNULL(ITN.name, ''),
		RS.bkpsetId,
		ISNULL(BN.name, ''),
		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servStartTime), @csTimeZone)), 
        dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(RS.servEndTime), @csTimeZone)),  
		RS.duration,
		CAST(convert(real, RS.totFiles2xfer_h) * 214748.3648*10000 + convert(real, RS.totFiles2xfer_l) AS BIGINT),
		CAST(convert(real, RS.totSuccess_h) * 214748.3648*10000 + convert(real, RS.totSuccess_l) AS BIGINT),
		RS.totSkipped,
		RS.totFailed,
		CAST(convert(real, RS.totCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		CAST(convert(real, RS.totUnCompBytes_h/(1024.0*1024.0)) * 214748.3648*10000 + convert(real, RS.totUnCompBytes_l/(1024.0*1024.0)) AS BIGINT),
		'',
		CASE 
			WHEN CHARINDEX(',', RS.failureReason) = LEN(RS.failureReason) THEN RS.failureReason
			ELSE RIGHT(RS.failureReason, CHARINDEX(',', REVERSE(RS.failureReason), 2)-1) 
		END,
		RS.opType,
		JS.subOpType,
		RS.rstattributes,
		RS.initFrom,
		RS.username,
		JS.jobDescription,
		NULL
	FROM JMRestoreStats RS WITH (NOLOCK)
		JOIN JMJobStats JS WITH (NOLOCK)
			ON RS.jobId = JS.jobId
		JOIN APP_Client DC WITH (NOLOCK) 
			ON RS.destClientId = DC.id 
		JOIN APP_Client SC WITH (NOLOCK) 
			ON RS.srcClientId = SC.id 
		JOIN APP_iDAType APT WITH (NOLOCK)
			ON RS.appType = APT.type 
		LEFT OUTER JOIN APP_InstanceName ITN WITH (NOLOCK)
			ON RS.instanceId = ITN.id  
		LEFT OUTER JOIN APP_BackupSetName BN WITH (NOLOCK)
			ON RS.bkpsetId = BN.id 
	WHERE RS.destClientId > 1 AND RS.servEndTime BETWEEN @LastCollectionTime AND @CurrCollectionTime
		AND RS.opType NOT IN (12, 52, 80, 105)
END


IF OBJECT_ID('tempdb..#RestoredBackupTime') IS NOT NULL DROP TABLE #RestoredBackupTime
CREATE TABLE #RestoredBackupTime (RestoreJobId INT, BackupTime DATETIME)

INSERT	INTO #RestoredBackupTime
SELECT	M.jobId_l, 
		CAST(P.data AS datetime)
FROM	EvMsg M WITH(NOLOCK) 
		INNER JOIN evParam P WITH(NOLOCK) ON M.id = P.evMsgId
		INNER JOIN #RestJobDetails RJ ON M.jobId_l = RJ.Jobid AND M.commCellId = 2
		INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON M.messageId = C.MessageID AND C.LocaleID = 0
		WHERE C.SubsystemID=13 AND C.MessageNum=216 AND P.position = 4 AND M.type&4=0

UPDATE	R SET BackupTime = S.BackupTime
FROM	#RestJobDetails R INNER JOIN 
		(SELECT	RestoreJobId, MIN(BackupTime) AS BackupTime
		FROM	#RestoredBackupTime
		GROUP BY RestoreJobId
		) S ON R.Jobid = S.RestoreJobId
		
IF OBJECT_ID('tempdb..#RestoredBackupTime') IS NOT NULL DROP TABLE #RestoredBackupTime


UPDATE	#RestJobDetails
SET		ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),
		FailedReason = LEFT(dbo.JMGetLocalizedMessageFunc(0, A.FailedReason), 512)
FROM	#RestJobDetails A
		INNER JOIN JMFailureReasonMsg B WITH (NOLOCK) ON CAST(LEFT(A.FailedReason, LEN(A.FailedReason)-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.FailedReason <> ''

SET @surveyXML = (
	SELECT   
		Jobid AS '@Jobid',
		JobStatus AS '@JobStatus',
		SrcClientId AS '@SrcClientId',
		dbo.NormalizeForXML(SrcClientName) AS '@SrcClientName',
		(SELECT dbo.NormalizeForXML(activePhysicalNodeName) FROM #PhysicalNodeInfo WHERE clientId = SrcClientId) AS '@SrcPhysicalClientName',
		DestClientId AS '@DestClientId',
		dbo.NormalizeForXML(DestClientName) AS '@DestClientName',		
		(SELECT dbo.NormalizeForXML(activePhysicalNodeName) FROM #PhysicalNodeInfo WHERE clientId = DestClientId) AS '@DestPhysicalClientName',
		AppTypeId AS '@AppTypeId',
		AppType AS '@AppType',
		InstanceId AS '@InstanceId',
		dbo.NormalizeForXML(Instance) AS '@Instance',
		BkpsetId AS '@BkpsetId',
		dbo.NormalizeForXML(Bkpset) AS '@Bkpset',
		StartTime AS '@StartTime', 
		EndTime AS '@EndTime',  
		Duration AS '@Duration',
		TotFiles2xfer AS '@TotFiles2xfer',
		TotSuccess AS '@TotSuccess',
		SkippedObjects AS '@SkippedObjects',
		FailedObjects AS '@FailedObjects',
		TotCompMB AS '@TotCompMB',
		TotUnCompMB AS '@TotUnCompMB',
		ErrorCode AS '@ErrorCode',
		dbo.NormalizeForXML(FailedReason) AS '@FailedReason',
		OpType AS '@OpType',
		SubOpType AS '@SubOpType',
		RstAttributes AS '@RstAttributes',
		InitFrom AS '@InitFrom',
		UserName AS '@UserName',
		dbo.NormalizeForXML(JobDescription) AS '@JobDescription',
		BackupTime AS '@BackupTime',
		1 AS '@inCSTimeZone'
	FROM #RestJobDetails
	FOR XML PATH('RestoreJobStats')
)

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

--------- 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 EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
		INSERT INTO GXGlobalParam (name, value) SELECT @TIME_STAMP_NAME, ''
	ELSE
		EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT '''+@TIME_STAMP_NAME+''', '''', 0, 0')
END

	 
END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

UPDATE	GXGlobalParam SET value = CAST(@CurrCollectionTime AS VARCHAR(20))
WHERE	name = @TIME_STAMP_NAME

SET NOCOUNT OFF

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