

--- Please follow the below comments to insert SQL statements.

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

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 24
DECLARE @surveyXML NVARCHAR(MAX)
--------- END - GENERATED CODE ---------



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

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

DECLARE @LongRunHours int = 8
DECLARE @LongWriteSeconds int = 8*3600
DECLARE @LongRunHoursForAllJobs INT = 12
DECLARE @LongRunSeconds int = 12*3600
DECLARE @LastDays      int = 30
DECLARE @LastDaysForAllJobs int = 1
DECLARE @TopN     int = 25
DECLARE @TimeRangeBegin INT = dbo.GetUnixTime(DATEADD(DAY, -@LastDays, GETUTCDATE()))
DECLARE @TimeRangeEnd int = 0x7FFFFFFF
DECLARE @now INT = dbo.GetUnixTime(GETUTCDATE())

DECLARE @csName	 VARCHAR(256)
 
DECLARE @csReleaseId int
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2
DECLARE @nstring NVARCHAR(MAX)

DECLARE @csNode INT;
SET @CSNode = ISNULL((select TOP 1 clientid from APP_Platform WITH (NOLOCK) where platformType = 1 order by clientid ASC), 0)
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


-- 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)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting')OR  (@freequencyMode ='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 @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 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

DECLARE @includeClients TABLE (clientId INT);

IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
BEGIN
	SET @specialGroupExists =1
	
	INSERT INTO @includeClients
	SELECT DISTINCT CGA.clientId 
	FROM @includeGroups CG INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientGroupId = CG.groupID	
END


-- Get subclients
DECLARE @FSTotalActiveSubclients INT = 0
DECLARE @FSLongRunningSubclients INT = 0
DECLARE @VSATotalActiveSubclients INT = 0
DECLARE @VSALongRunningSubclients INT = 0
DECLARE @appTotalActiveSubclients INT = 0
DECLARE @appLongRunningSubclients INT = 0

DECLARE @jobCount INT = 0
DECLARE @FSFulljobCount INT = 0
DECLARE @VSAFulljobCount INT = 0
DECLARE @appJobCount INT = 0

DECLARE @jobs XML
DECLARE @Anomalyjobs XML
DECLARE @FSFulljobs XML
DECLARE @VSAFulljobs XML
DECLARE @appjobs XML

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

CREATE TABLE #tblSubclients (appId INT, clientId INT, appTypeId INT, instanceId INT)
CREATE TABLE #tblSubclientStats (appId INT, clientId INT, appTypeId INT, instanceId INT, 
		longBkpJobCount INT, lastFullJobId INT, startTime INT, backupLevel INT, opType INT, 
		status VARCHAR(256), archGrpId INT, duration INT, writeTime INT, appSize BIGINT,
		isDedupEnabled INT, isSourceDeDupEnabled INT,  isDASHEnabled INT, endTime INT)

IF @specialGroupExists =1
	INSERT INTO #tblSubclients
	SELECT	A.id, A.clientId, A.appTypeId, A.instance
	FROM	APP_Application A WITH (NOLOCK) 
		INNER JOIN (
			SELECT DISTINCT appId AS appId FROM JMBkpStats WITH (NOLOCK)
			UNION
			SELECT DISTINCT applicationId AS appId FROM JMBkpJobInfo WITH (NOLOCK)
		) B ON A.id = B.appId
		INNER JOIN @includeClients IC ON A.clientId = IC.clientId
		INNER JOIN APP_Client C ON A.clientId = C.id AND C.status & 4096 = 0	--Exclude laptops
	WHERE	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
		AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
		AND A.subclientStatus&(16|32) = 0 
ELSE
	INSERT INTO #tblSubclients
	SELECT	A.id, A.clientId, A.appTypeId, A.instance
	FROM	APP_Application A WITH (NOLOCK)
		INNER JOIN (
			SELECT DISTINCT appId AS appId FROM JMBkpStats WITH (NOLOCK)
			UNION
			SELECT DISTINCT applicationId AS appId FROM JMBkpJobInfo WITH (NOLOCK)
		) B ON A.id = B.appId
		INNER JOIN APP_Client C ON A.clientId = C.id AND C.status & 4096 = 0	--Exclude laptops
	WHERE	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127) 
		AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
		AND A.subclientStatus&(16|32) = 0 

SET @nstring = '
INSERT INTO #tblSubclientStats(appId , clientId , appTypeId , instanceId ,longBkpJobCount , lastFullJobId , startTime ,
								backupLevel , opType , status , archGrpId , duration , writeTime , appSize
								,isDedupEnabled, isSourceDeDupEnabled,  isDASHEnabled, endTime)

SELECT  B.appId, S.clientId, B.appType, S.instanceId, 
        COUNT(B.jobId),
        MAX(B.jobId), 
        0,0,0,0,0,0,0,0,0,0,0,0
FROM    JMBkpStats B WITH (NOLOCK) 
        INNER JOIN #tblSubclients S ON B.appId = S.appId
        LEFT OUTER JOIN APP_Client C WITH (NOLOCK) ON C.id = B.targetClientId 
WHERE   B.servEndDate > '+CAST(@TimeRangeBegin AS NVARCHAR(MAX))+' AND B.totalWriteTime >= '+CAST(@LongWriteSeconds AS NVARCHAR(MAX))+'
    AND B.status IN (1, 3, 14) AND B.commCellId = 2
    AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
    AND B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
    AND B.appType < 1000 AND B.appType NOT BETWEEN 600 AND 700
    @REPLACESTRING
    GROUP BY B.appId, S.clientId, B.appType, S.instanceId'
    
IF ( @csReleaseId > 14)
	SET @nstring = REPLACE(@nstring,'@REPLACESTRING','AND (B.bkpAttributesEx & 0x200 = 0 --Not edge backup
		    OR (B.bkpAttributesEx & 0x200 = 0x200 AND C.status & 4096 = 0) --Non laptop Edge backup
			)')
ELSE
	 SET @nstring = REPLACE(@nstring,'@REPLACESTRING','')

EXEC sp_executeSQL @nstring

UPDATE  #tblSubclientStats
SET     startTime = B.servStartDate, endTime = B.servEndDate, backupLevel = B.bkpLevel, opType = B.opType, 
        status = (CASE WHEN B.status = 1 THEN 'Completed' WHEN B.status IN (2,10,11,15) THEN 'Failed' WHEN B.status = 4 THEN 'Killed' 
                WHEN B.status IN (5,6) THEN 'Delayed' WHEN B.status IN (8,9,12,13) THEN 'Failed to Start'
                WHEN B.status = 3 THEN 'Completed with errors' WHEN B.status IN (14,16) THEN 'Completed with warnings' ELSE 'Unknown' END), 
        archGrpId = CASE WHEN B.dataArchGrpId > 0 THEN B.dataArchGrpId ELSE B.logArchGrpId END, 
        duration = (B.servEndDate - B.servStartDate), writeTime = B.totalWriteTime, appSize = B.totalUncompBytes
FROM    #tblSubclientStats S INNER JOIN JMBkpStats B WITH (NOLOCK) 
    ON B.jobId = S.lastFullJobId AND B.commCellId = 2


IF object_id('tempdb.dbo.#tblRunningJobs') is not null DROP TABLE #tblRunningJobs
CREATE TABLE #tblRunningJobs (appId INT, clientId INT, appTypeId INT, instanceId INT, jobId INT, startTime INT, 
		backupLevel INT, opType INT, status VARCHAR(256), archGrpId INT, duration INT, writeTime INT, appSize BIGINT)

SET @nstring = 
' INSERT INTO #tblRunningJobs
SELECT  B.applicationId, S.clientId, S.appTypeId, S.instanceId, J.jobId, J.jobStartTime, 
		B.bkpLevel, J.opType, dbo.GetJobStateName(J.state), 
		B.currentPolicy, ('+CAST (@now AS VARCHAR(MAX))+' - J.jobStartTime), totalMediaUsageTime, B.unCompBytesToXfer 
FROM	JMBkpJobInfo B WITH (NOLOCK) 
		INNER JOIN #tblSubclients S ON B.applicationId = S.appId
		INNER JOIN JMJobInfo J WITH (NOLOCK) 
		ON J.jobId = B.jobId AND J.commCellId = B.commcellId
			AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND ('+CAST (@now AS VARCHAR(MAX))+' - J.jobStartTime) >= '+CAST (@LongRunSeconds AS VARCHAR(MAX))+'
		LEFT OUTER JOIN APP_Client C WITH (NOLOCK) 
			ON C.id = B.targetClientId 
@REPLACESTRING'

IF ( @csReleaseId > 14)
	SET @nstring = REPLACE(@nstring,'@REPLACESTRING','WHERE (B.bkpAttributesEx & 0x200 = 0 --Not edge backup
		    OR (B.bkpAttributesEx & 0x200 = 0x200 AND C.status & 4096 = 0) --Non laptop Edge backup
			)')
ELSE
	 SET @nstring = REPLACE(@nstring,'@REPLACESTRING','')

EXEC sp_executeSQL @nstring

UPDATE  #tblSubclientStats
SET     longBkpJobCount += 1, lastFullJobId = RJ.jobId, backupLevel = RJ.backupLevel, opType = RJ.appTypeId, status = RJ.status, 
        archGrpId = RJ.archGrpId, duration = RJ.duration, writeTime = RJ.writeTime, appSize = RJ.appSize
FROM    #tblSubclientStats S INNER JOIN #tblRunningJobs RJ ON S.appId = RJ.appId
WHERE   RJ.backupLevel IN (1, 64, 128, 16384, 1024, 32768) AND RJ.writeTime >= @LongWriteSeconds


INSERT INTO #tblSubclientStats(appId , clientId , appTypeId , instanceId ,longBkpJobCount ,
			 lastFullJobId , startTime , backupLevel , opType , status , archGrpId , duration , writeTime , appSize
			 ,isDedupEnabled, isSourceDeDupEnabled,  isDASHEnabled, endTime)
SELECT	RJ.appId, S.clientId, RJ.appTypeId, RJ.instanceId, CASE WHEN RJ.writeTime >= @LongWriteSeconds THEN 1 ELSE 0 END, 
		RJ.jobId, RJ.startTime, RJ.backupLevel, RJ.opType, RJ.status, RJ.archGrpId, RJ.duration, RJ.writeTime, RJ.appSize
		,0,0,0,0
FROM	#tblRunningJobs RJ LEFT OUTER JOIN #tblSubclientStats S ON S.appId = RJ.appId
WHERE	S.appId IS NULL AND RJ.backupLevel IN (1, 64, 128, 16384, 1024, 32768) AND RJ.writeTime >= @LongWriteSeconds
                
          
UPDATE tSS
 SET isDedupEnabled = (CASE WHEN AGC.dedupeFlags & 262144 = 0 THEN 0 /*CVA_SIDB_STORE_ENABLED_FLAG*/
                        WHEN AGC.dedupeFlags & 1048576 <> 0 THEN 0 /*CVA_DEDUP_INACTIVE_FLAG*/
                        WHEN CONVERT(INT, ASP.attrVal) = 0 THEN 0
                        ELSE 1
                        END)
        FROM #tblSubclientStats tSS
        INNER JOIN archGroup AG WITH (NOLOCK) 
          ON AG.id = tSS.archGrpId
        INNER JOIN archGroupCopy AGC WITH (NOLOCK) 
          ON (AGC.id = AG.defaultCopy AND tSS.opType NOT IN (59, 65)) OR (AGC.id = AG.defaultSnapCopy AND tSS.opType IN (59, 65))
        LEFT OUTER JOIN App_SubclientProp ASP WITH (NOLOCK)
          ON ASP.componentNameId = tSS.appId
        WHERE ASP.modified = 0 AND ASP.attrName = N'Single INstancing Option' AND ASP.cs_attrName = CHECKSUM(N'Single INstancing Option')
        
	IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL
	BEGIN
		UPDATE tSS
		SET   isSourceDeDupEnabled =  (CASE WHEN ISNULL(SSCount.NoOfPartitions, 0) > 1 AND (CL.releaseId <15) THEN 0
											WHEN ISNULL(P.attrVal, 0) = 1 THEN 1
											WHEN CONVERT(INT, ASP.attrVal) =3 THEN 1
											WHEN (AGC.dedupeFlags & 524288) <> 0 AND (CL.releaseId > 13) THEN 1
											ELSE 0 END),
			  isDASHEnabled = (CASE WHEN (AGC.dedupeFlags & 8388608) <> 0 THEN 1
								ELSE 0 END)
		FROM #tblSubclientStats tSS
		INNER JOIN APP_Client CL WITH (NOLOCK) 
 			ON CL.id = tSS.clientId
		INNER JOIN archGroup AG WITH (NOLOCK) 
 			ON AG.id = tSS.archGrpId
		INNER JOIN archGroupCopy AGC WITH (NOLOCK) 
 			ON (AGC.id = AG.defaultCopy AND tSS.opType NOT IN (59, 65)) OR (AGC.id = AG.defaultSnapCopy AND tSS.opType IN (59, 65))
		LEFT OUTER JOIN archCopySIDBStore CS WITH (NOLOCK) ON AGC.id= CS.CopyId AND (CS.flags & 4) > 0
		LEFT OUTER JOIN (SELECT Count(SubStoreId) as NoOfPartitions, SIDBStoreId FROM IdxSIDBSubStore GROUP BY SIDBStoreId) AS SSCount
			ON SSCount.SIDBStoreId = CS.SIDBStoreId
		LEFT OUTER JOIN App_SubclientProp ASP WITH (NOLOCK)
			ON ASP.componentNameId = tSS.appId
		LEFT OUTER JOIN APP_ClientProp P 
 			ON tSS.clientId = P.componentNameId 
 			AND P.modified = 0 
			AND P.attrName = 'Enable DeDuplication'
		WHERE ASP.modified = 0 AND ASP.attrName = N'Single INstancing Option' AND ASP.cs_attrName = CHECKSUM(N'Single INstancing Option')
			AND tSS.isDedupEnabled = 1
	END
	ELSE
	BEGIN
		UPDATE tSS
		SET   isSourceDeDupEnabled =  (CASE WHEN ISNULL(SSCount.NoOfPartitions, 0) > 1 AND (CL.releaseId <15) THEN 0
											WHEN ISNULL(P.attrVal, 0) = 1 THEN 1
											WHEN CONVERT(INT, ASP.attrVal) =3 THEN 1
											WHEN (AGC.dedupeFlags & 524288) <> 0 AND (CL.releaseId > 13) THEN 1
											ELSE 0 END),
			  isDASHEnabled = (CASE WHEN (AGC.dedupeFlags & 8388608) <> 0 THEN 1
								ELSE 0 END)
		FROM #tblSubclientStats tSS
		INNER JOIN APP_Client CL WITH (NOLOCK) 
 			ON CL.id = tSS.clientId
		INNER JOIN archGroup AG WITH (NOLOCK) 
 			ON AG.id = tSS.archGrpId
		INNER JOIN archGroupCopy AGC WITH (NOLOCK) 
 			ON (AGC.id = AG.defaultCopy AND tSS.opType NOT IN (59, 65)) OR (AGC.id = AG.defaultSnapCopy AND tSS.opType IN (59, 65))
		LEFT OUTER JOIN archCopySIDBStore CS WITH (NOLOCK) ON AGC.id= CS.CopyId -- AND (CS.flags & 4) > 0
		LEFT OUTER JOIN (SELECT Count(SubStoreId) as NoOfPartitions, SIDBStoreId FROM IdxSIDBSubStore WHERE SealedTime = 0 GROUP BY SIDBStoreId) AS SSCount
			ON SSCount.SIDBStoreId = CS.SIDBStoreId
		LEFT OUTER JOIN App_SubclientProp ASP WITH (NOLOCK)
			ON ASP.componentNameId = tSS.appId
		LEFT OUTER JOIN APP_ClientProp P 
 			ON tSS.clientId = P.componentNameId 
 			AND P.modified = 0 
			AND P.attrName = 'Enable DeDuplication'
		WHERE ASP.modified = 0 AND ASP.attrName = N'Single INstancing Option' AND ASP.cs_attrName = CHECKSUM(N'Single INstancing Option')
			AND tSS.isDedupEnabled = 1
	END                        
-- Get number of FS subclients that some full jobs were running for more than 8 hours in last 30 days
SET @FSTotalActiveSubclients = (
	SELECT COUNT(DISTINCT SC.appId) 
	FROM #tblSubclients SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK) 
		ON SC.appTypeId = AG.appTypeId AND AG.appGroupId = 35) /*All FS Group*/

SET @FSLongRunningSubclients = (
	SELECT COUNT(DISTINCT SC.appId) 
	FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK) 
		ON SC.appTypeId = AG.appTypeId AND AG.appGroupId = 35) /*All FS Group*/

SET @FSFulljobCount = (
	SELECT COUNT(*) 
	FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK) 
		ON SC.appTypeId = AG.appTypeId AND AG.appGroupId = 35) /*All FS Group*/

SET @FSFulljobs = (
 SELECT	S.lastFullJobId as '@jobId', CL.id as '@clientId', CL.name as  '@clientName', IT.type AS '@agentTypeId', 
		CASE WHEN IT.type IN (1, 11, 34, 42, 43) THEN 'Windows File System'
			 ELSE IT.name END as '@agentName', 
		dbo.GetJobBackupTypeDisplay(S.backupLevel) as '@backupType',
		ROUND(S.duration/3600.0, 2) as '@durationHr',
		ROUND(S.writeTime/3600.0, 2) as '@actualBKPDurationHr',
		appSize as '@appSizeGB',
		S.status as '@status', 
		S.isDedupEnabled AS '@isDeDupEnabled',
		S.isSourceDeDupEnabled AS '@isSourceDeDupEnabled',
		S.isDASHEnabled	AS '@isDASHEnabled'
 FROM	(SELECT TOP (@topN) * FROM #tblSubclientStats 
		WHERE appTypeId IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) 
		ORDER BY writeTime DESC) S
 	INNER JOIN APP_Client CL WITH (NOLOCK) 
 		ON CL.id = S.clientId
 	INNER JOIN APP_iDAType IT WITH (NOLOCK) 
 		ON IT.type = S.appTypeId
  ORDER BY S.writeTime DESC
FOR XML PATH ('FSFulljobs'))

-- Get number of VSA subclients that some full jobs were running for more than 8 hours in last 30 days
SET @VSATotalActiveSubclients = (
	SELECT COUNT(appId) 
	FROM #tblSubclients
	WHERE appTypeId = 106) /*Virtual Server Agent*/

SET @VSALongRunningSubclients = (
	SELECT COUNT(appId)   
	FROM #tblSubclientStats
	WHERE appTypeId = 106) /*Virtual Server Agent*/

SET @VSAFulljobCount = (
	SELECT COUNT(*)   
	FROM #tblSubclientStats
	WHERE appTypeId = 106) /*Virtual Server Agent*/

SET @VSAFulljobs = (
 SELECT	S.lastFullJobId as '@jobId', 
		CL.id as '@clientId', CL.name as  '@clientName', 
		IT.type AS '@agentTypeId', IT.name as '@agentName', 
		dbo.GetJobBackupTypeDisplay(S.backupLevel) as '@backupType',
		ROUND(S.duration/3600.0, 2) as '@durationHr',
		ROUND(S.writeTime/3600.0, 2) as '@actualBKPDurationHr',
		appSize as '@appSizeGB',
		S.status as '@status', 
		S.isDedupEnabled AS '@isDeDupEnabled',
		S.isSourceDeDupEnabled AS '@isSourceDeDupEnabled',
		S.isDASHEnabled	AS '@isDASHEnabled',
		CASE 
			WHEN I.attrVal = 100 OR I.attrVal = 101 THEN 'VMware'
			WHEN I.attrVal = 102 THEN 'Hyper-V'
			WHEN I.attrVal = 201 OR I.attrVal = 202 THEN 'XEN'
			WHEN I.attrVal = 301 THEN 'Amazon'
			ELSE 'Unknown'
		END AS '@hypervisorType',
		 ISNULL(subClientP.attrVal,0) AS '@isSPEnabled',
		CASE ISNULL(VMTranMode.attrVal, 0) 
			WHEN 0 THEN 'AUTO'
			WHEN 1 THEN 'SAN'
			WHEN 2 THEN 'Hot Add'
			WHEN 3 THEN 'NAS'
			WHEN 4 THEN 'NBD SSL'
			WHEN 5 THEN 'NBD'
			ELSE 'UNKNOWN'
		END AS '@transportMode',
		dbo.UTCToLocalStringTime(dbo.GetDateTime(S.startTime), @csTimeZone) AS '@startTime',
		dbo.UTCToLocalStringTime(dbo.GetDateTime(S.endTime), @csTimeZone) AS '@endTime'
  FROM	(SELECT TOP (@topN) * FROM #tblSubclientStats 
		WHERE appTypeId = 106
		ORDER BY writeTime DESC) S
 	INNER JOIN APP_Client CL WITH (NOLOCK) 
 		ON CL.id = S.clientId
 	INNER JOIN APP_iDAType IT WITH (NOLOCK) 
 		ON IT.type = S.appTypeId
  	INNER JOIN APP_InstanceProp I WITH (NOLOCK)
 		ON I.componentNameId = S.instanceId AND I.attrName = 'Virtual Server Instance Type' AND I.modified = 0 		
 	LEFT OUTER JOIN APP_SubClientProp subClientP 
 		ON S.appId = subClientP.componentNameId 
 		AND S.startTime > subClientP.created AND (subClientP.modified = 0 OR subClientP.modified >= S.startTime)
		AND subClientP.attrName = 'Enable Snap Backups' AND subClientP.cs_attrName = CHECKSUM(N'Enable Snap Backups')
 	LEFT OUTER JOIN APP_SubClientProp VMTranMode 
 		ON S.appId = VMTranMode.componentNameId 
 		AND S.startTime > VMTranMode.created AND (VMTranMode.modified = 0 OR VMTranMode.modified >= S.startTime)
		AND VMTranMode.attrName = 'Virtual Server Transport Mode' AND VMTranMode.cs_attrName = CHECKSUM(N'Virtual Server Transport Mode')
 ORDER BY S.writeTime DESC
FOR XML PATH ('VSAjobs'))

-- Get number of app subclients that some full jobs were running for more than 8 hours in last 30 days
SET @appTotalActiveSubclients = (
	SELECT COUNT(DISTINCT SC.appId) 
	FROM #tblSubclients SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK) 
		ON SC.appTypeId = AG.appTypeId AND SC.appTypeId <> 106 /*Non-VSA*/
		AND SC.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WITH (NOLOCK) WHERE appGroupId = 35)) /*Non FS*/

SET @appLongRunningSubclients = (
	SELECT COUNT(DISTINCT SC.appId)  
	FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK) 
		ON SC.appTypeId = AG.appTypeId AND SC.appTypeId <> 106 /*Non-VSA*/
		AND SC.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WITH (NOLOCK) WHERE appGroupId = 35)) /*Non FS*/

SET @appJobCount = (
	SELECT COUNT(*)  
	FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK) 
		ON SC.appTypeId = AG.appTypeId AND SC.appTypeId <> 106 /*Non-VSA*/
		AND SC.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WITH (NOLOCK) WHERE appGroupId = 35)) /*Non FS*/

SET @appjobs = (
 SELECT	S.lastFullJobId as '@jobId', CL.id as '@clientId', CL.name as  '@clientName', IT.type AS '@agentTypeId', IT.name as '@agentName', 
		dbo.GetJobBackupTypeDisplay(S.backupLevel) as '@backupType',
		ROUND(S.duration/3600.0, 2) as '@durationHr',
		ROUND(S.writeTime/3600.0, 2) as '@actualBKPDurationHr',
		appSize as '@appSizeGB',
		S.status as '@status', 
		S.isDedupEnabled AS '@isDeDupEnabled',
		S.isSourceDeDupEnabled AS '@isSourceDeDupEnabled',
		S.isDASHEnabled	AS '@isDASHEnabled',
		ISNULL(subClientP.attrVal,0) AS '@isSPEnabled'
 FROM	(SELECT TOP (@topN) * FROM #tblSubclientStats 
		WHERE appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35) 
			AND appTypeId  <> 106
		ORDER BY writeTime DESC) S
 	INNER JOIN APP_Client CL WITH (NOLOCK) 
 		ON CL.id = S.clientId
 	INNER JOIN APP_iDAType IT WITH (NOLOCK)
 		ON IT.type = S.appTypeId
  	LEFT OUTER JOIN APP_SubClientProp subClientP 
 		ON S.appId = subClientP.componentNameId 
 		AND S.startTime > subClientP.created AND (subClientP.modified = 0 OR subClientP.modified >= S.startTime)
		AND subClientP.attrName = 'Enable Snap Backups' AND subClientP.cs_attrName = CHECKSUM(N'Enable Snap Backups')
 ORDER BY S.writeTime DESC
FOR XML PATH ('Appjobs'))


-- Get number of jobs running for more than 12 hours in last 24 hours
IF object_id('tempdb.dbo.#tblLongRunJobs') is not null DROP TABLE #tblLongRunJobs
CREATE TABLE #tblLongRunJobs (appId INT, clientId INT, appTypeId INT, instanceId INT, jobId INT, startTime INT, 
		backupLevel INT, opType INT, status VARCHAR(256), archGrpId INT, duration INT, writeTime INT, appSize BIGINT)

SET @TimeRangeBegin = dbo.GetUnixTime(DATEADD(DAY, -@LastDaysForAllJobs, GETUTCDATE()))

SET @nstring = '
INSERT INTO #tblLongRunJobs
SELECT	S.appId, S.clientId, S.appTypeId, S.instanceId, B.jobId, B.servEndDate, B.bkpLevel, B.opType, 
		(CASE WHEN B.status = 1 THEN ''Completed'' WHEN B.status IN (2,10,11,15) THEN ''Failed'' WHEN B.status = 4 THEN ''Killed'' 
			WHEN B.status IN (5,6) THEN ''Delayed'' WHEN B.status IN (8,9,12,13) THEN ''Failed to Start''
			WHEN B.status = 3 THEN ''Completed with errors'' WHEN B.status IN (14,16) THEN ''Completed with warnings'' ELSE ''Unknown'' END), 
		CASE WHEN B.dataArchGrpId > 0 THEN B.dataArchGrpId ELSE B.logArchGrpId END, 
		(B.servEndDate - B.servStartDate), B.totalWriteTime, B.totalUncompBytes
FROM	JMBkpStats B WITH (NOLOCK)
    	INNER JOIN #tblSubclients S WITH (NOLOCK) ON S.appId = B.appId AND B.servEndDate > '+CAST (@TimeRangeBegin AS NVARCHAR(MAX))+'
		LEFT OUTER JOIN APP_Client C WITH (NOLOCK) ON C.id = B.targetClientId 
WHERE	(B.servEndDate - B.servStartDate) > '+CAST (@LongRunSeconds AS NVARCHAR(MAX))+'
	AND B.status IN (1, 3, 14) AND B.commCellId = 2
	AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
	@REPLACESTRING
ORDER BY (B.servEndDate - B.servStartDate) DESC'


IF ( @csReleaseId > 14)
	SET @nstring = REPLACE(@nstring,'@REPLACESTRING','AND (B.bkpAttributesEx & 0x200 = 0 --Not edge backup
		    OR (B.bkpAttributesEx & 0x200 = 0x200 AND C.status & 4096 = 0) --Non laptop Edge backup
			)')
ELSE
	 SET @nstring = REPLACE(@nstring,'@REPLACESTRING','')


EXEC sp_executeSQL @nstring


INSERT INTO #tblLongRunJobs
SELECT	R.*
FROM	#tblRunningJobs R
LEFT OUTER JOIN #tblLongRunJobs L ON 
L.jobId = R.jobId 
WHERE L.jobId IS NULL AND 	R.duration > @LongRunSeconds

SET @jobCount = (SELECT COUNT(jobId) FROM #tblLongRunJobs)

SET @jobs = (
 SELECT	TOP (@topN) J.jobId as '@jobId', CL.id as '@clientId', CL.name as  '@clientName', IT.type AS '@agentTypeId', 
		CASE WHEN IT.type IN (1, 11, 34, 42, 43) THEN 'Windows File System'
			 ELSE IT.name END as '@agentName', 
		dbo.GetJobBackupTypeDisplay(J.backupLevel) as '@backupType',
		ROUND(J.duration/3600.0, 2) as '@durationHr',
		ROUND(J.writeTime/3600.0, 2) as '@actualBKPDurationHr',
		J.appSize as '@appSizeGB',
		J.status as '@status'
 FROM	#tblLongRunJobs J
 		INNER JOIN #tblSubclients S WITH (NOLOCK) ON S.appId = J.appId
 		INNER JOIN APP_Client CL WITH (NOLOCK)  ON CL.id = S.clientId
 		INNER JOIN APP_iDAType IT WITH (NOLOCK)  ON IT.type = S.appTypeId
 ORDER BY J.duration DESC
FOR XML PATH ('Job'))

--Below Anomaly jobs are also being collected in query 137 for private 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 DISTINCT 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 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

IF OBJECT_ID('NTGetPossibleCausesForLongRunTime', 'FN') IS NOT NULL
BEGIN
	SET @nstring = N'UPDATE #AnomalousJobs SET delayReason = delayReason + ''<br><br>'' + dbo.NTGetPossibleCausesForLongRunTime(jobid,1,0)'
	EXEC sp_executeSQL @nstring
END


SET @AnomalyJobs = (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'))

SET @surveyXML = (
	SELECT
	CASE @specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120) ELSE N'All' END as '@clientGroups',
	@LastDays as '@LastDays',
	@LongRunHours as '@LongRunHours',
	@jobCount AS '@LongRunningJobs',
	@FSTotalActiveSubclients AS '@TotalFSSCCount',
	@FSLongRunningSubclients AS '@LongRunningFSSCCount',
	@FSFulljobCount AS '@LongRunningFSFullJobs',
	@VSATotalActiveSubclients AS '@TotalVSASCCount',	
	@VSALongRunningSubclients AS '@LongRunningVSASCCount',	
	@VSAFulljobCount AS '@LongRunningVSAFullJobs',
	@appTotalActiveSubclients AS '@TotalOtherSCCount',	
	@appLongRunningSubclients AS '@LongRunningOtherSCCount',	
	@appJobCount AS '@LongRunningOtherFullJobs',
	@TopN as '@TopN',
	@jobs,
	@FSFulljobs,
	@VSAFulljobs,
	@appjobs,
	@AnomalyJobs
	FOR XML PATH ('LongRunJobs') 
	-- Replace <<RESULTS_NAME>> with specific name
)

IF object_id('tempdb.dbo.#tblSubclients') is not null DROP TABLE #tblSubclients
IF object_id('tempdb.dbo.#tblSubclientStats') is not null DROP TABLE #tblSubclientStats
IF object_id('tempdb.dbo.#tblRunningJobs') is not null DROP TABLE #tblRunningJobs
IF object_id('tempdb.dbo.#tblLongRunJobs') is not null DROP TABLE #tblLongRunJobs
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')
)
--select cast(@surveyXML as XML)
SET NOCOUNT OFF

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