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

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



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

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


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

DECLARE @csReleaseId int
SELECT	@csReleaseId = releaseId FROM APP_Client WITH (NOLOCK) WHERE id = 2
DECLARE @nstring NVARCHAR(MAX)

CREATE TABLE #GlobalParam (name NVARCHAR(64), value NVARCHAR(MAX))

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

-- CV_COMPONENT_COMMCELL_EXCLUDE_CMDLINE_FROM_SLA 3308
DECLARE @ExcludeCmdlineSubclients INT = 0

SELECT	@ExcludeCmdlineSubclients = longVal 
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3308 AND modified = 0

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' AND modified = 0)
   OR clientId IN (SELECT id FROM APP_Client WITH (NOLOCK) WHERE (status & (4096|268435456)) <> 0)
   OR (@ExcludeCmdlineSubclients > 0 AND subclientStatus & (64|4096) > 0)

-- Allow successful incremental/differential jobs clear full strikes
DECLARE @IncrClearFullStrike INT = 0

IF @csReleaseId >= 16
BEGIN
	SELECT	@IncrClearFullStrike = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3317 AND modified = 0
END

DECLARE @csTimeZone NVARCHAR(1024) = ''
SELECT	@csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
	SELECT	@csTimeZone = timeZone FROM APP_CommCell WHERE id = 2

	SELECT	@csTimeZone = TimeZoneStdName FROM SchedTimeZone
	WHERE	TimeZoneName= SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END

CREATE TABLE #TblStrikes (
		vmClientId INT, appId INT, isVM INT, strikeType INT, lastSuccJobId INT, nFailedJobs INT, failedSince DATETIME, 
		lastJobId INT, lastJobBkpLvl INT, lastJobStart INT, lastJobEnd INT, lastJobStatus INT, failureReason VARCHAR(256),lastSJobStart INT,lastSJobEnd INT)

CREATE TABLE #TblLastFailedJob (entityId INT, isVM INT, fullJobId INT, incrJobId INT)

CREATE TABLE #TblIndexingV2VMs (vmClientId INT, vmAppId INT)

IF OBJECT_ID('APP_VMBackupset', 'U') IS NOT NULL
	EXEC sp_executesql N'INSERT INTO #TblIndexingV2VMs SELECT S.VMClientId, A.id FROM APP_VMBackupset S WITH(NOLOCK) INNER JOIN APP_Application A WITH(NOLOCK) ON S.VMClientId = A.clientId AND S.ChildBackupSetId = A.backupset AND A.subclientStatus&8 > 0'

-- For IndexingV2 VMs
INSERT	INTO #TblLastFailedJob
SELECT	appId, 1, MAX(CASE WHEN bkpLevel IN (1, 1024, 32768) THEN jobId ELSE 0 END), MAX(CASE WHEN bkpLevel IN (1, 1024, 32768) THEN 0 ELSE jobId END)
FROM	JMBkpStats B WITH (NOLOCK)
		INNER JOIN #TblIndexingV2VMs VM ON B.appId = VM.vmAppId
WHERE	bkpLevel NOT IN (64, 128, 16384)
	AND status NOT IN (1, 3, 14) AND (status <> 9 OR failureErrorCode = 8)
	AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND commCellId = 2
GROUP BY appId

INSERT	INTO #TblLastFailedJob
SELECT	appId, 0, MAX(CASE WHEN bkpLevel IN (1, 1024, 32768) THEN jobId ELSE 0 END), MAX(CASE WHEN bkpLevel IN (1, 1024, 32768) THEN 0 ELSE jobId END)
FROM	JMBkpStats WITH (NOLOCK)
WHERE	bkpLevel NOT IN (64, 128, 16384)
	AND status NOT IN (1, 14) AND (status <> 9 OR failureErrorCode = 8)
	AND (status <> 3 OR appType NOT IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
	AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND commCellId = 2
	AND appType < 1000 AND appType NOT BETWEEN 600 AND 700 AND appType <> 106
GROUP BY appId

INSERT	INTO #TblStrikes
SELECT	0, F.entityId, F.isVM, 1, MAX(ISNULL(J.jobId, 0)), 0, NULL, F.fullJobId, 0, 0, 0, 0, NULL,NULL,NULL
FROM	#TblLastFailedJob F
		INNER JOIN APP_Application A WITH (NOLOCK) ON F.entityId = A.id
		LEFT OUTER JOIN JMBkpStats J WITH (NOLOCK) ON J.appId = F.entityId
	AND (bkpLevel IN (1, 1024, 32768) OR bkpLevel NOT IN (64, 128, 16384) AND @IncrClearFullStrike = 1)
	AND (status IN (1, 14) OR status = 3 AND appType IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
	AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND commCellId = 2
GROUP BY F.entityId, F.isVM, F.fullJobId
HAVING	MAX(ISNULL(J.jobId, 0)) < F.fullJobId

INSERT	INTO #TblStrikes
SELECT	0, F.entityId, F.isVM, 2, MAX(ISNULL(J.jobId, 0)), 0, NULL, F.incrJobId, 0, 0, 0, 0, NULL,NULL,NULL
FROM	#TblLastFailedJob F
		INNER JOIN APP_Application A WITH (NOLOCK) ON F.entityId = A.id
		LEFT OUTER JOIN JMBkpStats J WITH (NOLOCK) ON J.appId = F.entityId
	AND	bkpLevel NOT IN (64, 128, 16384)
	AND (status IN (1, 14) OR status = 3 AND appType IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
	AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND commCellId = 2
GROUP BY F.entityId, F.isVM, F.incrJobId
HAVING	MAX(ISNULL(J.jobId, 0)) < F.incrJobId

-- For IndexingV2 VMs
UPDATE	S SET vmClientId = VM.vmClientId
FROM	#TblStrikes S
		INNER JOIN #TblIndexingV2VMs VM ON S.appId = VM.vmAppId

DELETE	#TblLastFailedJob

CREATE TABLE #TblFailedJobStats (entityId INT, strikeType INT, nFailedJobs INT, failedSince INT)

INSERT	INTO #TblFailedJobStats
SELECT	F.appId, F.strikeType, COUNT(J.jobId), MIN(J.servStartDate)
FROM	JMBkpStats J WITH (NOLOCK) INNER JOIN #TblStrikes F ON J.appId = F.appId
WHERE	(F.strikeType = 1 AND J.bkpLevel IN (1, 1024, 32768) OR F.strikeType = 2 AND J.bkpLevel NOT IN (1, 64, 128, 16384, 1024, 32768))
	AND J.status NOT IN (1, 14) AND (J.status <> 9 OR J.failureErrorCode = 8)
	AND (J.status <> 3 OR J.appType NOT IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND J.commCellId = 2
	AND J.jobId > F.lastSuccJobId
GROUP BY F.appId, F.strikeType

UPDATE	#TblStrikes
SET		nFailedJobs = F.nFailedJobs, failedSince = dbo.UTCToLocalTime(dbo.GetDateTime(F.failedSince), @csTimeZone)
FROM	#TblStrikes A INNER JOIN #TblFailedJobStats F ON A.appId = F.entityId AND A.strikeType = F.strikeType

DELETE	#TblFailedJobStats

DELETE	#TblStrikes
FROM	#TblStrikes F 
	INNER JOIN #ExcludedAppIds E ON F.appId = E.appId

DELETE	#TblStrikes
FROM	#TblStrikes F 
	INNER JOIN APP_Application A WITH (NOLOCK) ON F.appId = A.id
WHERE	A.appTypeId >= 600 
	OR A.appTypeId IN (72, 84, 85, 107, 121, 122, 127) 
	OR A.appTypeId IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) AND A.subclientStatus&8 > 0
	OR A.subclientStatus & (2|4|16|32) > 0
	OR A.dataArchGrpId <= 1 AND A.logArchGrpId <= 1

DELETE	S FROM #TblStrikes S
	INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
	INNER JOIN (
	SELECT	DISTINCT CGA.ClientId
	FROM	APP_ComponentProp CP WITH (NOLOCK)
			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.ClientGroupId = CP.componentId
	WHERE	CP.componentType = 8 AND CP.propertyTypeId = 3301 AND CP.longVal = 1 AND CP.modified = 0
	) C ON A.ClientId = C.ClientId

DELETE	S FROM #TblStrikes S
	INNER JOIN APP_SubClientProp SP WITH (NOLOCK) ON S.appId = SP.componentNameId AND SP.attrName = 'Exclude From SLA' AND SP.attrVal = '1' AND SP.modified = 0 AND SP.cs_attrName = CHECKSUM(N'Exclude From SLA')

DELETE	S FROM #TblStrikes S INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
	INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON A.clientId = CP.componentNameId AND CP.attrName = 'Exclude From SLA' AND CP.attrVal = '1' AND CP.modified = 0

DELETE	S FROM #TblStrikes S
	INNER JOIN JMJobAction J WITH (NOLOCK) ON S.appId = J.appId
WHERE	J.opType = 4 AND J.action = 1

DELETE	S FROM #TblStrikes S
	INNER JOIN (
		SELECT	componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
		FROM	APP_SubClientProp
		WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) T ON S.appId = T.appId 
		INNER JOIN JMJobAction J WITH (NOLOCK) ON T.subclientPolicyAppId = J.appId
WHERE	J.opType = 4 AND J.action = 1

-- instanceID column was added to JMJobAction table in V11 SP6
IF COL_LENGTH('JMJobAction', 'instanceID') IS NOT NULL
	EXEC sp_executesql N'DELETE S FROM #TblStrikes S INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id INNER JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.instanceID = A.instance AND J.instanceID > 1'

DELETE	S FROM #TblStrikes S INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
		INNER JOIN JMJobAction J WITH (NOLOCK) ON A.clientId = J.clientId
WHERE	J.opType = 4 AND J.action = 1 AND J.appType IN (A.AppTypeId, 0)

DELETE	S FROM #TblStrikes S INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
		INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON A.clientId = CGA.clientId
		INNER JOIN JMJobAction J WITH (NOLOCK) ON CGA.clientGroupId = J.clientGroupId
			AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1 
			AND J.opType = 4 AND J.action = 1 

-- Strikes of V1 VMs
INSERT	INTO #TblLastFailedJob
SELECT	V.clientId, 1, MAX(CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN J.jobId ELSE 0 END), MAX(CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN 0 ELSE J.jobId END)
FROM	JMBkpStats J WITH (NOLOCK)
 		INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId AND V.status IN (1, 2, 4)
 		INNER JOIN APP_Client C WITH (NOLOCK) ON V.clientId = C.id
 		INNER JOIN APP_Application A ON J.appId = A.id AND (A.subclientStatus & (16|32|4|2) = 0)
 WHERE	J.appType = 106 AND J.commCellId = 2
 	AND J.bkpLevel NOT IN (64, 128, 16384)
 	AND J.status NOT IN (1, 14) AND (J.status <> 9 OR J.failureErrorCode = 8)
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
GROUP BY V.clientId

-- Strikes of IndexingV2 VMs have been inserted into #TblStrikes table before
DELETE	F
FROM	#TblLastFailedJob F
		INNER JOIN #TblIndexingV2VMs VM ON F.entityId = VM.vmClientId

INSERT INTO #TblStrikes
SELECT	F.entityId, 0, F.isVM, 1, MAX(ISNULL(J.jobId, 0)), 0, NULL, F.fullJobId, 0, 0, 0, 0, NULL,NULL,NULL
FROM	#TblLastFailedJob F
		INNER JOIN APP_Client C WITH (NOLOCK) ON F.entityId = C.id
		LEFT OUTER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON V.clientId = F.entityId AND V.status IN (0, 3)
		LEFT OUTER JOIN JMBkpStats J WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
	AND	J.appType = 106 AND J.commCellId = 2
	AND (J.bkpLevel IN (1, 1024, 32768) OR J.bkpLevel NOT IN (64, 128, 16384) AND @IncrClearFullStrike = 1)
	AND J.status IN (1, 3, 14) AND (J.status <> 9 OR J.failureErrorCode = 8) 
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
GROUP BY F.entityId, F.isVM, F.fullJobId
HAVING	MAX(ISNULL(J.jobId, 0)) < F.fullJobId

INSERT INTO #TblStrikes
SELECT	F.entityId, 0, F.isVM, 2, MAX(ISNULL(J.jobId, 0)), 0, NULL, F.incrJobId, 0, 0, 0, 0, NULL,NULL,NULL
FROM	#TblLastFailedJob F
		INNER JOIN APP_Client C WITH (NOLOCK) ON F.entityId = C.id
		LEFT OUTER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON V.clientId = F.entityId AND V.status IN (0, 3)
		LEFT OUTER JOIN JMBkpStats J WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
	AND	J.appType = 106 AND J.commCellId = 2
	AND J.bkpLevel NOT IN (64, 128, 16384)
	AND J.status IN (1, 3, 14) AND (J.status <> 9 OR J.failureErrorCode = 8) 
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
GROUP BY F.entityId, F.isVM, F.incrJobId
HAVING	MAX(ISNULL(J.jobId, 0)) < F.incrJobId

DROP TABLE #TblLastFailedJob 

INSERT	INTO #TblFailedJobStats
SELECT	F.VMClientId, F.strikeType, COUNT(J.jobId), MIN(J.servStartDate)
FROM	JMBkpStats J WITH (NOLOCK)
		INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId AND V.status IN (1, 2, 4)
		INNER JOIN #TblStrikes F ON V.clientId = F.VMClientId
		INNER JOIN APP_Application A ON J.appId = A.id AND (A.subclientStatus & (16|32|4|2) = 0)
WHERE	(F.strikeType = 1 AND J.bkpLevel IN (1, 1024, 32768) OR F.strikeType = 2 AND J.bkpLevel NOT IN (1, 64, 128, 16384, 1024, 32768))
	AND J.status NOT IN (1, 14) AND (J.status <> 9 OR J.failureErrorCode = 8)
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND J.commCellId = 2
	AND J.jobId > F.lastSuccJobId
GROUP BY F.VMClientId, F.strikeType
HAVING COUNT(J.jobId) > 0

UPDATE	#TblStrikes
SET		nFailedJobs = F.nFailedJobs, failedSince = dbo.UTCToLocalTime(dbo.GetDateTime(F.failedSince), @csTimeZone)
FROM	#TblStrikes A INNER JOIN #TblFailedJobStats F ON A.VMClientId = F.entityId AND A.strikeType = F.strikeType

DROP TABLE #TblFailedJobStats

-- Strikes of VSA backup jobs that the entries of VMs are not in JMQinetixUpdateStatus and APP_VMProp tables
CREATE TABLE #TblLastFailedVSAJob (clientId INT, appId INT, strikeType INT, lastSuccessJobId INT, lastFailedJobId INT, nFailedJobs INT, failedSince INT)

INSERT	INTO #TblLastFailedVSAJob (clientId , appId, strikeType, lastSuccessJobId, lastFailedJobId)
SELECT	A.clientId, J.appId, (CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 2 END),
		MAX(CASE WHEN J.status IN (1, 3, 14) THEN J.jobId ELSE 0 END), 
		MAX(CASE WHEN J.status IN (1, 3, 14) THEN 0 ELSE J.jobId END)
FROM	JMBkpStats J WITH (NOLOCK)
 		INNER JOIN APP_Application A ON J.appId = A.id AND (A.subclientStatus & (16|32|4|2) = 0)
 WHERE	J.appType = 106 AND J.commCellId = 2
 	AND J.bkpLevel NOT IN (64, 128, 16384)
 	AND (J.status <> 9 OR J.failureErrorCode = 8)
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
	AND A.instance NOT IN (SELECT id FROM APP_InstanceName WITH (NOLOCK) WHERE name = 'VMInstance')
GROUP BY A.clientId, J.appId, (CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 2 END)
HAVING MAX(CASE WHEN J.status IN (1, 3, 14) THEN J.jobId ELSE 0 END) < MAX(CASE WHEN J.status IN (1, 3, 14) THEN 0 ELSE J.jobId END)

IF @IncrClearFullStrike = 1
	DELETE	S1
	FROM	#TblLastFailedVSAJob S1
			INNER JOIN (
				SELECT	appId, lastSuccessJobId
				FROM	#TblLastFailedVSAJob
				WHERE	strikeType = 2
			) S2 ON S1.appId = S2.appId
	WHERE	S1.strikeType = 1 AND S1.lastFailedJobId < S2.lastSuccessJobId

DELETE	F
FROM	#TblLastFailedVSAJob F
		INNER JOIN #TblStrikes T ON F.clientId = T.vmClientId AND F.lastFailedJobId = T.lastJobId

DELETE	F
FROM	#TblLastFailedVSAJob F
		INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON F.lastFailedJobId = V.jobId AND V.commCellId = 2

UPDATE	T SET nFailedJobs = S.nFailedJobs, failedSince = S.failedSince
FROM	#TblLastFailedVSAJob T
		INNER JOIN (
			SELECT	F.appId, F.strikeType, COUNT(J.jobId) AS nFailedJobs, MIN(J.servStartDate) AS failedSince
			FROM	JMBkpStats J WITH (NOLOCK)
					INNER JOIN #TblLastFailedVSAJob F ON J.appId = F.appId
			WHERE	(F.strikeType = 1 AND J.bkpLevel IN (1, 1024, 32768) OR F.strikeType = 2 AND J.bkpLevel NOT IN (1, 64, 128, 16384, 1024, 32768))
				AND J.status NOT IN (1, 3, 14) AND (J.status <> 9 OR J.failureErrorCode = 8)
				AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND J.commCellId = 2
				AND J.jobId > F.lastSuccessJobId
			GROUP BY F.appId, F.strikeType
		) S ON T.appId = S.appId

-- Have isVM = 0 so that the strikes will be counted for subclients and servers but not for VMs
INSERT INTO #TblStrikes
SELECT	F.clientId, F.appId, 0, F.strikeType, F.lastSuccessJobId, F.nFailedJobs, 
		dbo.UTCToLocalTime(dbo.GetDateTime(F.failedSince), @csTimeZone), F.lastFailedJobId, 
		0, 0, 0, 0, NULL,NULL,NULL
FROM	#TblLastFailedVSAJob F

DROP TABLE #TblLastFailedVSAJob 

UPDATE	#TblStrikes 
SET		appId = J.appId, lastJobBkpLvl = J.bkpLevel, lastJobStart = J.servStartDate, lastJobEnd = J.servEndDate, lastJobStatus = J.status, failureReason = J.failureReason
FROM	#TblStrikes S INNER JOIN JMBkpStats J WITH (NOLOCK) ON S.lastJobId = J.jobId AND J.commCellId = 2

UPDATE	#TblStrikes 
SET		lastSJobStart = J.servStartDate, lastSJobEnd = J.servEndDate
FROM	#TblStrikes S INNER JOIN JMBkpStats J WITH (NOLOCK) ON S.lastSuccJobId = J.jobId AND J.commCellId = 2
AND J.commCellId = 2 and S.lastSuccJobId>0

IF @specialGroupExists = 1
	DELETE	S
	FROM	#TblStrikes S
			INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = S.appId
			LEFT OUTER JOIN (
				SELECT 	DISTINCT CGA.clientId 
				FROM 	APP_ClientGroupAssoc CGA WITH (NOLOCK) INNER JOIN @includeGroups IG 
					 ON CGA.clientGroupId = IG.groupID
				) CG ON CG.clientId IN (A.clientId, S.vmClientId)
	WHERE	CG.clientId IS NULL

-- Exclude old strikes
IF @csReleaseId >= 16
BEGIN
	DECLARE @noJobSince INT = -1
	DECLARE @maxDays INT = -1

	SELECT	@maxDays = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3302 AND modified = 0

	IF @maxDays > 0 AND @maxDays <= 9999
	BEGIN
		SET @noJobSince = dbo.GetUnixTime(DATEADD(DAY, -@maxDays, GETUTCDATE()))

		DELETE #TblStrikes WHERE lastJobStart < @noJobSince
	END

	DELETE	S FROM #TblStrikes S
		INNER JOIN (SELECT componentNameId, CAST(attrVal AS INT) AS beforeTime
			FROM APP_SubClientProp WITH (NOLOCK) 
			WHERE attrName = 'Ignore Strikes Before' AND cs_attrName = CHECKSUM(N'Ignore Strikes Before') 
				AND CAST(attrVal AS INT) > @noJobSince AND modified = 0
			) T ON S.appId = T.componentNameId AND S.lastJobStart < T.beforeTime
END

-- Exclude Deleted VMs
IF @csReleaseId >= 15
BEGIN
	DELETE	#TblStrikes
	FROM	(SELECT	DISTINCT componentNameId
			FROM	APP_ClientProp CP WITH (NOLOCK)
			WHERE	attrName = 'Virtual Machine Deletion Time' AND ISNULL(attrVal, '0') <> '0' AND modified = 0) T
	WHERE	#TblStrikes.vmClientId = T.componentNameId
END

DELETE	S FROM #TblStrikes S
	INNER JOIN (
		SELECT	DISTINCT CGA.ClientId
		FROM	APP_ComponentProp CP WITH (NOLOCK)
				INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.ClientGroupId = CP.componentId
		WHERE	CP.componentType = 8 AND CP.propertyTypeId = 3301 AND CP.longVal = 1 AND CP.modified = 0
		) C ON S.vmClientId = C.ClientId

DELETE	S FROM #TblStrikes S
	INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON S.vmClientId = CP.componentNameId AND CP.attrName = 'Exclude From SLA' AND CP.attrVal = '1' AND CP.modified = 0

-- Exclude VMs if the VSA subclient is deconfigured or disabled for backup or excluded from SLA
DELETE	#TblStrikes
FROM	#TblStrikes F INNER JOIN 
		(SELECT	S.appId
		FROM	APP_Application A WITH (NOLOCK) 
				INNER JOIN (SELECT DISTINCT appId FROM #TblStrikes WHERE VMClientId > 0) S ON A.id = S.appId AND A.appTypeId = 106
		WHERE	A.dataArchGrpId <= 1
			OR (A.subclientStatus & (2|4)) > 0
			OR EXISTS (SELECT * FROM JMJobAction WITH (NOLOCK) WHERE opType = 4 AND action = 1 AND (clientId = A.ClientId AND appType IN (A.AppTypeId, 0) OR appId = A.id))
			OR A.clientId IN (SELECT DISTINCT CGA.clientId FROM JMJobAction J WITH (NOLOCK) INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
					ON CGA.clientGroupId = J.clientGroupId
					AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1 
					AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND J.action = 1)
			OR A.id IN (SELECT componentNameId FROM APP_SubclientProp WITH (NOLOCK) WHERE attrName = 'Exclude From SLA' AND cs_attrName = CHECKSUM(N'Exclude From SLA') AND attrVal = '1' AND modified = 0)
		) T ON F.appId = T.appId AND F.vmClientId > 0

DELETE	#TblStrikes
FROM	#TblStrikes S
		INNER JOIN APP_Application A WITH (NOLOCK) ON A.clientId = S.vmClientId AND A.appTypeId <> 106
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.appId = A.id AND B.servStartDate > S.lastJobStart 
 		AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND B.commCellId = 2

CREATE TABLE #TblFailedJobIds (appId INT, strikeType INT, failedJobIds VARCHAR(64))

INSERT	INTO #TblFailedJobIds
SELECT	T.appId, T.strikeType, CAST(T.jobId AS VARCHAR(20))
FROM	(
	SELECT	S.appId, S.strikeType, J.jobId, ROW_NUMBER() OVER (PARTITION BY S.appId, S.strikeType ORDER BY J.jobId DESC) AS rowId
	FROM	JMBkpStats J
			INNER JOIN #TblStrikes S ON J.appId = S.appId
	WHERE	nFailedJobs >= 3
		AND J.bkpLevel NOT IN (64, 128, 16384) AND (CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 2 END) = S.strikeType
		AND J.status NOT IN (1, 14) AND (J.status <> 9 OR J.failureErrorCode = 8)
		AND (J.status <> 3 OR J.appType NOT IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
		AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND J.commCellId = 2
		AND J.jobId <= S.lastJobId) T
WHERE	rowId <= 3
ORDER BY rowId

INSERT	INTO #TblFailedJobIds
SELECT	T.appId, T.strikeType, CAST(T.jobId AS VARCHAR(20))
FROM	(
	SELECT	S.appId, S.strikeType, J.jobId, ROW_NUMBER() OVER (PARTITION BY S.appId, S.strikeType ORDER BY J.jobId DESC) AS rowId
	FROM	JMBkpStats J
			INNER JOIN #TblStrikes S ON J.appId = S.appId
	WHERE	nFailedJobs = 2
		AND J.bkpLevel NOT IN (64, 128, 16384) AND (CASE WHEN J.bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 2 END) = S.strikeType
		AND J.status NOT IN (1, 14) AND (J.status <> 9 OR J.failureErrorCode = 8)
		AND (J.status <> 3 OR J.appType NOT IN (SELECT type FROM APP_iDAType WITH (NOLOCK) WHERE isCWEjobValid = 1))
		AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND J.commCellId = 2
		AND J.jobId <= S.lastJobId) T
WHERE	rowId <= 2
ORDER BY rowId

INSERT	INTO #TblFailedJobIds
SELECT	appId, strikeType, CAST(lastJobId AS VARCHAR(20))
FROM	#TblStrikes
WHERE	nFailedJobs = 1


CREATE TABLE #TblFailureMsgIds (jobId INT, messageId INT, JPR varchar(20), failureReasonTime INT)

INSERT INTO #TblFailureMsgIds
SELECT	M.jobId, M.messageId, DBO.GetJPRFromMsgId(M.messageId), MAX(M.failureReasonTime) AS maxFailureReasonTime
FROM	JMFailureReasonMsg M WITH (NOLOCK) INNER JOIN #TblStrikes S ON M.jobId = S.lastJobId AND M.commCellId = 2 AND S.vmClientId = 0
GROUP BY M.jobId, M.messageId
ORDER BY M.jobId, maxFailureReasonTime DESC

DECLARE @NewLine  CHAR(1) = CHAR(10)
DECLARE @NewLine2 CHAR(1) = CHAR(13)
DECLARE @BR       CHAR(4) = '<BR>'


SET @surveyXML = (
SELECT
	CASE @specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120) ELSE N'All' END as '@clientGroups',
	(
	SELECT	F.nFailedJobs AS '@StrikeCounts',
		CASE WHEN F.vmClientId = 0 THEN A.clientId ELSE F.vmClientId END AS '@clientId',
		ISNULL(J.parentAppId, F.appId) AS '@SubclientId',
		F.isVM AS '@isVM',
		CASE WHEN F.lastJobBkpLvl = 1 AND A.appTypeId IN (45,56,57,58,67,73) THEN N'New Index'
		     WHEN F.lastJobBkpLvl = 2 AND A.appTypeId IN (4,5,16,70,81) THEN N'Transaction Log' 
		     WHEN F.lastJobBkpLvl = 2 AND A.appTypeId = 125 THEN N'Log Only'
		     WHEN F.lastJobBkpLvl = 4 AND A.appTypeId IN (37,62) THEN N'Delta'
		     WHEN F.lastJobBkpLvl = 0x100 AND A.appTypeId <> 104 THEN N'LOG NOTRUNC'
		     ELSE dbo.GetJobBackupTypeDisplay(F.lastJobBkpLvl) END AS '@backupType',
		F.failedSince AS '@FailedFrom',
		DATEDIFF(DAY, F.failedSince, GETDATE()) AS '@FailedDays',
		(SELECT failedJobIds + ', ' FROM #TblFailedJobIds
				WHERE appId = F.appId AND strikeType = F.strikeType FOR XML PATH('')) AS '@failedJobIds',
		F.lastJobId AS '@lasJobId',
		J.parentJobId AS '@parentJobId',
		dbo.UTCToLocalStringTime(dbo.GetDateTime(F.lastJobStart), @csTimeZone) AS '@lastJobStartTime',
		dbo.UTCToLocalStringTime(dbo.GetDateTime(F.lastJobEnd), @csTimeZone) AS '@lastJobEndTime',
		CASE F.lastJobStatus WHEN 3 THEN 'Completed with errors' WHEN 14 THEN 'Completed with warnings' ELSE dbo.GetJobStatusName (F.lastJobStatus) END AS '@lastJobStatus',
		ISNULL((SELECT JPR + ' ; ' FROM #TblFailureMsgIds
				WHERE jobId = F.lastJobId FOR XML PATH('')), '') AS '@errorCode',
		CASE WHEN F.failureReason = '' THEN (SELECT dbo.NormalizeForXML(LEFT(V.attrVal,1022)) FROM APP_VMProp V WITH (NOLOCK) WHERE V.VMclientId = F.vmClientId AND F.lastJobId = V.jobId AND V.attrName = 'vmFailureReason')
			ELSE (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(dbo.NormalizeForXML(LEFT(dbo.JMGetLocalizedMessageFunc(0, F.failureReason),1022)),',',' '),@NewLine,';'),@NewLine2,';'),'''',' '),@BR,'')) END AS '@FailureReason'
		,case when F.lastSuccJobId>0 then F.lastSuccJobId end AS '@LastSJobId'
		,case when lastSJobStart is not null then dbo.UTCToLocalStringTime(dbo.GetDateTime(F.lastSJobStart), @csTimeZone)end  AS '@lastSJobStartTime',
		case when lastSJobEnd is not null then dbo.UTCToLocalStringTime(dbo.GetDateTime(F.lastSJobEnd), @csTimeZone)end AS '@lastSJobEndTime'	
	FROM	#TblStrikes F
		INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = F.appId AND F.nFailedJobs > 0
		LEFT OUTER JOIN JMJobDataLink J WITH (NOLOCK) ON F.lastJobId = J.childJobId AND J.commCellId = 2 AND J.linkType = 7

	ORDER BY  F.nFailedJobs DESC

	FOR XML PATH ('Client'), TYPE
	)
	FOR XML PATH ('ContinuousFailedClients') 
	-- Replace <<RESULTS_NAME>> with specific name
)

DROP TABLE #GlobalParam
DROP TABLE #TblStrikes
DROP TABLE #TblFailedJobIds
DROP TABLE #TblFailureMsgIds
DROP TABLE #TblIndexingV2VMs
--------- 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 ---------