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

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



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

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

-- Finds SLA, strike count, backup job count history in last 7 days numbers

DECLARE @LastNDays int =30
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @nstring NVARCHAR(MAX)
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        


-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
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)
	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 (@ExcludeCmdlineSubclients > 0 AND subclientStatus & (64|4096) > 0)

IF OBJECT_ID('tempdb.dbo.#GlobalParam') IS NOT NULL DROP TABLE #GlobalParam
CREATE TABLE #GlobalParam (name NVARCHAR(64), value NVARCHAR(MAX))

-- SLA numbers

DECLARE	@csReleaseId	INT
DECLARE @csHighestSP	INT
DECLARE @nProtected		INT = 0
DECLARE @nNotProtected	INT = 0
DECLARE @nExcluded		INT = 0
DECLARE @nProtectedLaptops		INT = 0
DECLARE @nNotProtectedLaptops	INT = 0
DECLARE @nExcludedLaptops		INT = 0
DECLARE @nProtectedVMs		INT = 0
DECLARE @nNotProtectedVMs	INT = 0
DECLARE @nExcludedVMs		INT = 0
DECLARE @SLAEndDate	DATETIME
DECLARE @MultiDaySLA	INT = 0
DECLARE	@SpecifiedDays	INT = 0
DECLARE @SLATrend		TABLE (MonthBegin DATETIME, nClientsMet INT, nClientsMissed INT)

SELECT 	@csHighestSP = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2

SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

IF	@csReleaseId < 15
BEGIN
	CREATE TABLE #TblSubclient (
	ClientId		INT,
	SubclientName	NVARCHAR(256),
	Category		NVARCHAR(64)
	)

	CREATE TABLE #TblNotProtectedClient (ClientId INT)
	CREATE TABLE #TblProtectedClient (ClientId INT)

	IF	@specialGroupExists = 0
		INSERT INTO #TblSubclient
		SELECT	C.id,  Subclient, Category
		FROM	dbo.DataProtectionCoverage(@LastNDays, 0)
				INNER JOIN APP_Client C WITH (NOLOCK) ON C.name = Client
	ELSE
		INSERT INTO #TblSubclient
		SELECT	DISTINCT C.id,  Subclient, Category
		FROM	dbo.DataProtectionCoverage(@LastNDays, 0)
				INNER JOIN APP_Client C WITH (NOLOCK) ON C.name = Client
				INNER JOIN (SELECT CGA.clientId, CGA.clientGroupId
							FROM APP_ClientGroupAssoc CGA WITH (NOLOCK)
								INNER JOIN @includeGroups CG ON CGA.clientGroupId = CG.groupId
							) CGA ON CGA.clientId = C.id

	INSERT INTO #TblNotProtectedClient
	SELECT DISTINCT ClientId FROM #TblSubclient 
	WHERE Category = 'Not Protected'
	
	SET	@nNotProtected = @@ROWCOUNT
	
	INSERT INTO #TblProtectedClient
	SELECT DISTINCT S.ClientId FROM #TblSubclient S LEFT OUTER JOIN #TblNotProtectedClient N ON S.ClientId = N.ClientId
	WHERE S.Category = 'Protected' AND N.ClientId IS NULL
	
	SET	@nProtected = @@ROWCOUNT

	SELECT @nExcluded = COUNT(DISTINCT S.ClientId) FROM #TblSubclient S 
		LEFT OUTER JOIN #TblNotProtectedClient N ON S.ClientId = N.ClientId
		LEFT OUTER JOIN #TblProtectedClient P ON S.ClientId = P.ClientId
	WHERE S.Category = 'Excluded' AND N.ClientId IS NULL AND P.ClientId IS NULL

	DROP TABLE #TblNotProtectedClient
	DROP TABLE #TblProtectedClient
	DROP TABLE #TblSubclient
END
ELSE
BEGIN
	CREATE TABLE #tempSLA (date datetime, clientId int, appId int, fullJobsOnly int, appTypeId int, status int, category int, lastSeenTime int)
	CREATE TABLE #tempSLAClient (date datetime, days int, fullJobsOnly int, clientId int, isProtected int, category int)
	CREATE TABLE #ClientSLADays (clientId INT, clientGroupId INT, days INT)

	DECLARE	@ClientGroupSLADays TABLE (clientGroupId INT, days INT)
	
	IF  @isPrivateRunning = 'Metrics Reporting'
	AND EXISTS (SELECT * FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'Metrics Reporting - SLA for multiple days' AND CAST(value AS NVARCHAR(32)) = '1')
		SET @MultiDaySLA = 1

	IF @MultiDaySLA = 0
	BEGIN
		SELECT	@SpecifiedDays = longVal 
		FROM	APP_ComponentProp WITH (NOLOCK)
		WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3300 AND modified = 0

		IF @SpecifiedDays > 0 SET @LastNDays = @SpecifiedDays
	
		INSERT	INTO @ClientGroupSLADays
		SELECT	componentId, longVal 
		FROM	APP_ComponentProp WITH (NOLOCK)
		WHERE	componentType = 8 AND propertyTypeId = 3300 AND modified = 0

		IF EXISTS (SELECT * FROM @ClientGroupSLADays) SET @SpecifiedDays = -1
	END

	DECLARE	@currDate	DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
	DECLARE	@lastDate	DATETIME = (SELECT MAX(date) FROM RptSLAClient WITH (NOLOCK))

	IF @lastDate IS NOT NULL
	BEGIN
		SET @currDate = DATEADD(HOUR, DATEPART(HOUR, @lastDate), @currDate)
		IF  @currDate > GETDATE()
			SET @currDate = DATEADD(DAY, -1, @currDate)

		SET @lastDate = @currDate
	
		IF OBJECT_ID('RptSLASubclient', 'U') IS NOT NULL
		BEGIN
		    IF EXISTS (SELECT * FROM RptSLAClient WHERE days = -1)
		    BEGIN
				INSERT	INTO #tempSLAClient (date, days, fullJobsOnly, clientId, isProtected, category)
				SELECT	date, days, fullJobsOnly, clientId, CASE status WHEN 1 THEN 1 WHEN 3 THEN 3 ELSE 0 END, category
				FROM	RptSLAClient WITH (NOLOCK)
				WHERE	fullJobsOnly = 0 AND (days = -1 OR days > 0 AND @MultiDaySLA = 1 OR days = 0 AND status = 3)
					
				SET @LastNDays = -1
			END
		    ELSE
				INSERT	INTO #tempSLAClient (date, days, fullJobsOnly, clientId, isProtected, category)
				SELECT	date, days, fullJobsOnly, clientId, CASE status WHEN 1 THEN 1 WHEN 3 THEN 3 ELSE 0 END, category
				FROM	RptSLAClient WITH (NOLOCK)
				WHERE	fullJobsOnly = 0 AND (days = @LastNDays OR @MultiDaySLA = 1 OR @SpecifiedDays = -1 OR days = 0 AND status = 3)
		END
		ELSE
		IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'RptOneDaySLA' AND PARAMETER_NAME = '@i_days')
		   AND @MultiDaySLA = 0 AND @SpecifiedDays <> -1
			EXEC RptOneDaySLA @lastDate, @LastNDays
		ELSE
			EXEC RptOneDaySLA @lastDate
	END
	ELSE
		SET @lastDate = @currDate

	SET @SLAEndDate = DATEADD(DAY, 1, @lastDate)

	DROP TABLE #tempSLA
	DELETE #tempSLAClient WHERE fullJobsOnly <> 0

	-- ClientType: 1 for Server, 2 for Laptop, 4 for VM
	UPDATE	T SET fullJobsOnly = 1+(C.status&0x1000)/0x1000
	FROM #tempSLAClient T
		INNER JOIN APP_Client C WITH (NOLOCK) ON T.clientId = C.id

	UPDATE	T SET fullJobsOnly = 4
	FROM #tempSLAClient T
		INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON T.clientId = CP.componentNameId
			AND CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified =0

	IF EXISTS (SELECT * FROM @ClientGroupSLADays)
	BEGIN
		INSERT INTO #ClientSLADays
		SELECT	T.clientId, T.clientGroupId, T.days
		FROM	(SELECT CG.days, CGA.clientId, CGA.clientGroupId, ROW_NUMBER() OVER (PARTITION BY CGA.clientId ORDER BY CG.days) AS rowId
				FROM	@ClientGroupSLADays CG INNER JOIN APP_ClientGroupAssoc CGA ON CG.clientGroupId = CGA.clientGroupId) T
		WHERE	T.rowId = 1
	
		DELETE	T 
		FROM	#tempSLAClient T INNER JOIN #ClientSLADays D ON T.clientId = D.clientId
		WHERE	T.days > 0 AND T.days <> D.days
		
		DELETE	T 
		FROM	#tempSLAClient T LEFT OUTER JOIN #ClientSLADays D ON T.clientId = D.clientId
		WHERE	D.clientId IS NULL AND T.days > 0 AND T.days <> @LastNDays
	END
	ELSE
	IF	@MultiDaySLA = 0
		DELETE #tempSLAClient WHERE days > 0 AND days <> @LastNDays

	DELETE	T
	FROM	#tempSLAClient T
			INNER JOIN APP_Client CL WITH (NOLOCK) ON T.clientId = CL.id
	WHERE	T.isProtected <> 3
		AND ((CL.specialClientFlags & 34) = 34 --Pseudo CommServe Client
		 OR (CL.status & 268435456) > 0 --Edge Drive Pseudo Client
		 OR CL.id IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1'))

	DECLARE @Summary TABLE (clientType INT, days INT, status INT, counts INT)
	
	IF	@specialGroupExists = 0
	BEGIN
		INSERT INTO @Summary
		SELECT 	S.fullJobsOnly, S.days, CASE WHEN S.category IN (1, 13) THEN 1 WHEN S.category IN (2,3,4,9,12) THEN 2 ELSE 3 END, COUNT(S.clientId)
		FROM 	#tempSLAClient S INNER JOIN APP_Client C WITH (NOLOCK)
			ON S.clientId = C.id AND S.date = @lastDate
		WHERE	S.days <> 0 OR S.isProtected = 3
		GROUP BY S.fullJobsOnly, S.days, CASE WHEN S.category IN (1, 13) THEN 1 WHEN S.category IN (2,3,4,9,12) THEN 2 ELSE 3 END
	END
	ELSE
	BEGIN
		INSERT INTO @Summary
		SELECT	S.fullJobsOnly, S.days, CASE WHEN S.category IN (1, 13) THEN 1 WHEN S.category IN (2,3,4,9,12) THEN 2 ELSE 3 END, COUNT(DISTINCT S.clientId)
		FROM	#tempSLAClient S INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
			ON S.clientId = CGA.clientId AND CGA.clientGroupId IN (SELECT groupID FROM @includeGroups)
				AND S.date = @lastDate
		WHERE	S.days <> 0 OR S.isProtected = 3
		GROUP BY S.fullJobsOnly, S.days, CASE WHEN S.category IN (1, 13) THEN 1 WHEN S.category IN (2,3,4,9,12) THEN 2 ELSE 3 END
	END

	-- For 30 day SLA
	IF @MultiDaySLA > 0
	BEGIN
		SELECT	@nProtected    = SUM(counts) FROM @Summary WHERE status = 1 AND days = @LastNDays
		SELECT	@nNotProtected = SUM(counts) FROM @Summary WHERE status = 2 AND days = @LastNDays
		SELECT	@nExcluded     = SUM(counts) FROM @Summary WHERE status = 3 AND days IN (0, @LastNDays)

		SELECT	@nProtectedLaptops    = SUM(counts) FROM @Summary WHERE clientType = 2 AND status = 1 AND days = @LastNDays
		SELECT	@nNotProtectedLaptops = SUM(counts) FROM @Summary WHERE clientType = 2 AND status = 2 AND days = @LastNDays
		SELECT	@nExcludedLaptops     = SUM(counts) FROM @Summary WHERE clientType = 2 AND status = 3 AND days IN (0, @LastNDays)

		SELECT	@nProtectedVMs    = SUM(counts) FROM @Summary WHERE clientType = 4 AND status = 1 AND days = @LastNDays
		SELECT	@nNotProtectedVMs = SUM(counts) FROM @Summary WHERE clientType = 4 AND status = 2 AND days = @LastNDays
		SELECT	@nExcludedVMs     = SUM(counts) FROM @Summary WHERE clientType = 4 AND status = 3 AND days IN (0, @LastNDays)
	END
	ELSE
	BEGIN
		SELECT	@nProtected    = ISNULL(SUM(counts), 0) FROM @Summary WHERE status = 1
		SELECT	@nNotProtected = ISNULL(SUM(counts), 0) FROM @Summary WHERE status = 2
		SELECT	@nExcluded     = ISNULL(SUM(counts), 0) FROM @Summary WHERE status = 3

		SELECT	@nProtectedLaptops    = SUM(counts) FROM @Summary WHERE clientType = 2 AND status = 1
		SELECT	@nNotProtectedLaptops = SUM(counts) FROM @Summary WHERE clientType = 2 AND status = 2
		SELECT	@nExcludedLaptops     = SUM(counts) FROM @Summary WHERE clientType = 2 AND status = 3

		SELECT	@nProtectedVMs    = SUM(counts) FROM @Summary WHERE clientType = 4 AND status = 1
		SELECT	@nNotProtectedVMs = SUM(counts) FROM @Summary WHERE clientType = 4 AND status = 2
		SELECT	@nExcludedVMs     = SUM(counts) FROM @Summary WHERE clientType = 4 AND status = 3
	END

	DROP TABLE #tempSLAClient
	DROP TABLE #ClientSLADays

	-- From V11 SP12	
	IF OBJECT_ID('RptSLAHistory', 'U') IS NOT NULL
	BEGIN
		INSERT INTO @SLATrend
		SELECT TOP 12 M.m, H.nClientsMet, H.nClientsMissed
		FROM RptSLAHistory H WITH (NOLOCK) INNER JOIN 
			(
			SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0) AS m, MAX(date) AS d
			FROM RptSLAHistory WITH (NOLOCK)
			WHERE days = -1 AND fullJobsOnly = 0 AND date <= GETDATE()
			GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0)
			) M ON H.date = M.d
		WHERE H.days = -1 AND H.fullJobsOnly = 0
		ORDER BY M.m DESC

		-- Before V11 SP14
		IF NOT EXISTS (SELECT 1 FROM @SLATrend)
		INSERT INTO @SLATrend
		SELECT TOP 12 M.m, H.nClientsMet, H.nClientsMissed
		FROM RptSLAHistory H WITH (NOLOCK) INNER JOIN 
			(
			SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0) AS m, MAX(date) AS d
			FROM RptSLAHistory WITH (NOLOCK)
			WHERE days = 30 AND fullJobsOnly = 0 AND date <= GETDATE()
			GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, date), 0)
			) M ON H.date = M.d
		WHERE H.days = 30 AND H.fullJobsOnly = 0
		ORDER BY M.m DESC
	END
END


--- Strike count summary
DECLARE @Strike1SubClients INT
DECLARE @Strike2SubClients INT
DECLARE @Strike3SubClients INT
DECLARE @Strike1VMs INT
DECLARE @Strike2VMs INT
DECLARE @Strike3VMs INT
DECLARE @Strike1Clients INT
DECLARE @Strike2Clients INT
DECLARE @Strike3Clients INT
DECLARE @FullStrike1 INT
DECLARE @FullStrike2 INT
DECLARE @FullStrike3 INT

DECLARE @noJobSince INT = -1
DECLARE @maxDays INT = -1

-- Exclude old strikes
IF @csReleaseId >= 16
BEGIN
	SELECT	@maxDays = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3302 AND modified = 0

	IF @maxDays > 0
		SET @noJobSince = dbo.GetUnixTime(DATEADD(DAY, -@maxDays, GETUTCDATE()))
END

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

IF OBJECT_ID('tempdb.dbo.#TblClientStrikes') IS NOT NULL DROP TABLE #TblClientStrikes				
CREATE TABLE #TblClientStrikes (ClientId INT, StrikeCounts INT)

IF OBJECT_ID('tempdb.dbo.#TblVMStrikes') IS NOT NULL DROP TABLE #TblVMStrikes				
CREATE TABLE #TblVMStrikes (ClientId INT, strikeType INT, nFailedJobs INT)

IF OBJECT_ID('tempdb.dbo.#TblSubclientStrikes') IS NOT NULL DROP TABLE #TblSubclientStrikes				
CREATE TABLE #TblSubclientStrikes (ClientId INT, AppId INT, strikeType INT, nFailedJobs INT)

IF OBJECT_ID('tempdb.dbo.#TblStrikes') IS NOT NULL DROP TABLE #TblStrikes	
CREATE TABLE #TblStrikes (clientId INT, appId INT, isVM INT, appTypeId INT, instanceId INT, strikeType INT, lastSuccJobId INT, lastJobId INT, lastJobStart INT, nFailedJobs INT)

IF OBJECT_ID('tempdb.dbo.#TblLastSuccessJob') IS NOT NULL DROP TABLE #TblLastSuccessJob
CREATE TABLE #TblLastSuccessJob (entityId INT, jobId INT)

IF OBJECT_ID('tempdb.dbo.#TblLastFailedJob') IS NOT NULL DROP TABLE #TblLastFailedJob
CREATE TABLE #TblLastFailedJob (entityId INT, isVM INT, fullJobId INT, incrJobId INT)

IF OBJECT_ID('tempdb.dbo.#TblFailedJobStats') IS NOT NULL DROP TABLE #TblFailedJobStats
CREATE TABLE #TblFailedJobStats (entityId INT, strikeType INT, nFailedJobs INT)

IF OBJECT_ID('tempdb.dbo.#TblIndexingV2VMs') IS NOT NULL DROP TABLE #TblIndexingV2VMs
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'

-- BEGIN All Time Strikes

-- 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	A.clientId, A.id, F.isVM, A.appTypeId, A.instance, 1, MAX(ISNULL(J.jobId, 0)), F.fullJobId, 0, 0
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 A.clientId, A.id, F.isVM, A.appTypeId, A.instance, F.fullJobId
HAVING	MAX(ISNULL(J.jobId, 0)) < F.fullJobId

INSERT	INTO #TblStrikes
SELECT	A.clientId, A.id, F.isVM, A.appTypeId, A.instance, 2, MAX(ISNULL(J.jobId, 0)), F.incrJobId, 0, 0
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 A.clientId, A.id, F.isVM, A.appTypeId, A.instance, F.incrJobId
HAVING	MAX(ISNULL(J.jobId, 0)) < F.incrJobId

DELETE	#TblLastFailedJob

INSERT	INTO #TblFailedJobStats
SELECT	F.appId, F.strikeType, COUNT(J.jobId)
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
FROM	#TblStrikes A INNER JOIN #TblFailedJobStats F ON A.appId = F.entityId AND A.strikeType = F.strikeType

DELETE	#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, 14) THEN J.jobId ELSE 0 END), 
		MAX(CASE WHEN J.status IN (1, 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)
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, 14) THEN J.jobId ELSE 0 END) < MAX(CASE WHEN J.status IN (1, 14) THEN 0 ELSE J.jobId END)

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, 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 appTypeId = 0 so that the strikes will be counted for subclients and servers but not for VMs
INSERT INTO #TblStrikes (clientId, appId, isVM, appTypeId, instanceId, strikeType, lastSuccJobId, lastJobId, lastJobStart, nFailedJobs)
SELECT	F.clientId, F.appId, 0, 106, 0, F.strikeType, F.lastSuccessJobId, F.lastFailedJobId, NULL, F.nFailedJobs
FROM	#TblLastFailedVSAJob F

DROP TABLE #TblLastFailedVSAJob 


UPDATE	#TblStrikes 
SET		lastJobStart = J.servStartDate
FROM	#TblStrikes S INNER JOIN JMBkpStats J WITH (NOLOCK) ON S.lastJobId = J.jobId AND J.commCellId = 2

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 #ExcludedAppIds E ON S.appId = E.appId

DELETE	S FROM #TblStrikes S INNER JOIN APP_Client C WITH (NOLOCK) ON S.clientId = C.id
WHERE	C.status&(4096|268435456) <> 0

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

DELETE	S FROM #TblStrikes S INNER JOIN APP_ClientProp CP WITH (NOLOCK) 
	 ON S.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 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 @csReleaseId = 16 AND @csHighestSP >= 6 OR @csReleaseId > 16
	EXEC sp_executesql N'DELETE S FROM #TblStrikes S INNER JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.instanceID = S.InstanceId AND J.instanceID > 1'

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

DELETE	S FROM #TblStrikes S
		INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.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 

IF @specialGroupExists = 1
	DELETE	S
	FROM	#TblStrikes S
			LEFT OUTER JOIN (
				SELECT 	DISTINCT CGA.clientId 
				FROM 	APP_ClientGroupAssoc CGA WITH (NOLOCK) INNER JOIN @includeGroups IG ON CGA.clientGroupId = IG.groupID
				) C ON C.clientId = S.clientId
	WHERE	C.clientId IS NULL

-- Exclude old strikes
IF @csReleaseId >= 16 AND @maxDays > 0
	DELETE #TblStrikes WHERE lastJobStart < @noJobSince

INSERT	INTO #TblSubclientStrikes
SELECT	clientId, appId, strikeType, MAX(nFailedJobs)
FROM	#TblStrikes
WHERE	isVM = 0
GROUP BY clientId, appId, strikeType

DELETE	#TblStrikes WHERE isVM = 0

SELECT	@Strike3SubClients = SUM(CASE WHEN nFailedJobs >= 3 THEN 1 ELSE 0 END),
		@Strike2SubClients = SUM(CASE WHEN nFailedJobs  = 2 THEN 1 ELSE 0 END),
		@Strike1SubClients = SUM(CASE WHEN nFailedJobs  = 1 THEN 1 ELSE 0 END)
FROM	(SELECT	clientId, appId, MAX(nFailedJobs) AS nFailedJobs
		FROM	#TblSubclientStrikes
		GROUP BY clientId, appId) S

SELECT @FullStrike3 = COUNT(appId) from #TblSubclientStrikes where strikeType = 1 AND nFailedJobs > 2
SELECT @FullStrike2 = COUNT(appId) from #TblSubclientStrikes where strikeType = 1 AND nFailedJobs = 2
SELECT @FullStrike1 = COUNT(appId) from #TblSubclientStrikes where strikeType = 1 AND nFailedJobs = 1

INSERT	INTO #TblClientStrikes
SELECT	ClientId, MAX(nFailedJobs)
FROM	#TblSubclientStrikes
GROUP BY ClientId

DROP TABLE #TblSubclientStrikes

SELECT	@Strike3Clients = SUM(CASE WHEN StrikeCounts >= 3 THEN 1 ELSE 0 END),
		@Strike2Clients = SUM(CASE WHEN StrikeCounts  = 2 THEN 1 ELSE 0 END),
		@Strike1Clients = SUM(CASE WHEN StrikeCounts  = 1 THEN 1 ELSE 0 END)
FROM	(SELECT	clientId, MAX(StrikeCounts) AS StrikeCounts
		FROM	#TblClientStrikes
		GROUP BY clientId) S

DROP TABLE #TblClientStrikes

-- Strikes of 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 AND C.status&(4096|268435456) = 0
 		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, 106, 0, 1, MAX(ISNULL(J.jobId, 0)), F.fullJobId, 0, 0
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, 106, 0, 2, MAX(ISNULL(J.jobId, 0)), F.incrJobId, 0, 0
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.clientId, F.strikeType, COUNT(J.jobId)
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.clientId
		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.clientId, F.strikeType
HAVING COUNT(J.jobId) > 0

UPDATE	#TblStrikes
SET		nFailedJobs = F.nFailedJobs
FROM	#TblStrikes A INNER JOIN #TblFailedJobStats F ON A.clientId = F.entityId AND A.strikeType = F.strikeType

DELETE #TblFailedJobStats

UPDATE	#TblStrikes 
SET		appId = J.appId, lastJobStart = J.servStartDate
FROM	#TblStrikes S INNER JOIN JMBkpStats J WITH (NOLOCK) ON S.lastJobId = J.jobId AND J.commCellId = 2

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
				) C ON C.clientId IN (A.clientId, S.clientId)
	WHERE	C.clientId IS NULL

-- 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.clientId = 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.ClientId = C.ClientId

DELETE	S FROM #TblStrikes S INNER JOIN APP_ClientProp CP WITH (NOLOCK) 
	 ON S.clientId = 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 clientId > 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 attrVal = '1' AND modified = 0)
		) T ON F.appId = T.appId AND F.clientId > 0

DELETE	#TblStrikes
FROM	#TblStrikes S
		INNER JOIN APP_Application A WITH (NOLOCK) ON A.clientId = S.clientId 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
WHERE	S.appTypeId = 106

-- Exclude old strikes
IF @csReleaseId >= 16 AND @maxDays > 0
	DELETE #TblStrikes WHERE lastJobStart < @noJobSince

IF @csReleaseId >= 16
	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 CAST(attrVal AS INT) > @noJobSince AND modified = 0
			) T ON S.appId = T.componentNameId AND S.lastJobStart < T.beforeTime

INSERT	INTO #TblVMStrikes
SELECT	clientId, strikeType, MAX(nFailedJobs)
FROM	#TblStrikes
GROUP BY clientId, strikeType

DELETE	#TblStrikes

SELECT	@Strike3VMs = SUM(CASE WHEN nFailedJobs >= 3 THEN 1 ELSE 0 END),
		@Strike2VMs = SUM(CASE WHEN nFailedJobs  = 2 THEN 1 ELSE 0 END),
		@Strike1VMs = SUM(CASE WHEN nFailedJobs  = 1 THEN 1 ELSE 0 END)
FROM	(SELECT	clientId, MAX(nFailedJobs) AS nFailedJobs
		FROM	#TblVMStrikes
		GROUP BY clientId) S

SELECT @FullStrike3 += COUNT(clientId) from #TblVMStrikes where strikeType = 1 AND nFailedJobs > 2
SELECT @FullStrike2 += COUNT(clientId) from #TblVMStrikes where strikeType = 1 AND nFailedJobs = 2
SELECT @FullStrike1 += COUNT(clientId) from #TblVMStrikes where strikeType = 1 AND nFailedJobs = 1

DROP TABLE #TblVMStrikes

-- END All Time Strikes

-- 7 days backup success rate

DECLARE @today  datetime = GETUTCDATE() 
DECLARE @days INT  =7*24*60*60
DECLARE @TotalJobs BIGINT
DECLARE @FailedJobs BIGINT
DECLARE @SuccessfulJobs BIGINT

IF  (@specialGroupExists =1 )
BEGIN
	SELECT	@FailedJobs = SUM(CASE WHEN B.status in (2,4,9)  THEN 1 ELSE 0 END) ,
		@TotalJobs = COUNT(*) ,
		@SuccessfulJobs = SUM(CASE WHEN B.status IN (1, 3, 14) THEN 1 ELSE 0 END)
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK)
				ON B.appId = A.id AND (B.status <> 9 OR B.failureErrorCode = 8) AND B.failureErrorCode NOT IN (7,11,12)  --(ACTIVITYDISABLED, SCHEDSKIPPEDLATE, SCHEDSKIPPEDHOLIDAY)
			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	B.servStartDate >= (DATEDIFF(s, '1970-01-01 00:00:00', @today) - @days )		
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND B.status IN (1,2,3,4,9,14)
			AND 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 
			AND E.appId IS NULL
END
ELSE
BEGIN
	SELECT	@FailedJobs = SUM(CASE WHEN B.status in (2,4,9)  THEN 1 ELSE 0 END) ,
		@TotalJobs = COUNT(*) ,
		@SuccessfulJobs = SUM(CASE WHEN B.status IN (1, 3, 14) THEN 1 ELSE 0 END)
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK)
				ON B.appId = A.id AND (B.status <> 9 OR B.failureErrorCode = 8) AND B.failureErrorCode NOT IN (7,11,12)  --(ACTIVITYDISABLED, SCHEDSKIPPEDLATE, SCHEDSKIPPEDHOLIDAY)
    			INNER JOIN APP_Client C WITH (NOLOCK)
				ON C.id = A.clientId
			LEFT OUTER JOIN #ExcludedAppIds E
				ON B.appId = E.appId
	WHERE	B.servStartDate >= (DATEDIFF(s, '1970-01-01 00:00:00', @today) - @days )			
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND B.status IN (1,2,3,4,9,14)
			AND 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 
			AND E.appId IS NULL
END


-- DeDup information
DECLARE @totalAppSizeGB FLOAT = 0
DECLARE @totalMediaSizeGB FLOAT = 0
DECLARE @sqlstr NVARCHAR(MAX)
CREATE TABLE #UsageDate (startDT DATETIME, allAppSizeGB FLOAT, allMediaSizeGB FLOAT)

IF COL_LENGTH('RptStorageUsage', 'allAppSize') IS NOT NULL 
BEGIN
	SET @sqlstr = N'INSERT INTO #UsageDate SELECT MAX(startDT), 0, 0 FROM RptStorageUsage WHERE type = 1 AND allAppSize > 0'
	EXEC sp_executesql @sqlstr	
END

IF EXISTS (SELECT * FROM #UsageDate WHERE startDT IS NOT NULL)
BEGIN
	SET @sqlstr = N'
	UPDATE	#UsageDate SET allAppSizeGB = S.allAppSizeGB, allMediaSizeGB = S.allMediaSizeGB
	FROM	(SELECT	SUM(allAppSize)/1024.0/1024.0/1024.0 AS allAppSizeGB, SUM(allMediaSize)/1024.0/1024.0/1024.0 AS allMediaSizeGB
			FROM	RptStorageUsage U WITH(NOLOCK) INNER JOIN #UsageDate T ON U.startDT = T.startDT AND U.type = 1
					INNER JOIN archGroupCopy C WITH(NOLOCK) ON U.copyId = C.id AND ((C.dedupeFlags & 262144) > 0)) S'
	EXEC sp_executesql @sqlstr
	
	SELECT	@totalAppSizeGB = allAppSizeGB, @totalMediaSizeGB = allMediaSizeGB
	FROM	#UsageDate
END
ELSE
BEGIN
	SELECT	@totalMediaSizeGB = ISNULL(
		(SELECT	SUM(CAST(PhysicalBytesMB AS BIGINT))/1024.0
		FROM	MMVolume WITH(NOLOCK)
		WHERE	SIDBStoreId > 0 AND SiloStatus <> 3 AND PhysicalBytesMB > 0 AND (Attributes & 512) = 0
		), 0)
		
	SELECT	@totalAppSizeGB = ISNULL(
		(SELECT	SUM((1.0*AFC.physicalSize/J.totalBackupSize)*J.totalUncompBytes)/1024.0/1024.0/1024.0
		FROM	archFileCopy AFC WITH(NOLOCK) 
			INNER JOIN archFile AF WITH(NOLOCK) 
				ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AF.isValid = 1
			INNER JOIN JMBkpStats J WITH(NOLOCK) 
				ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId AND J.status in (1, 3, 14) AND J.totalBackupSize > 0
			INNER JOIN (SELECT id FROM archGroupCopy WHERE ((dedupeFlags & 262144) > 0)) C 
				ON AFC.archCopyId = C.id AND AFC.physicalSize > 0 --AND (AFC.flags & 256) = 0
		), 0)
		
	SELECT	@totalAppSizeGB += ISNULL(
		(SELECT	SUM(AFC.physicalSize)/1024.0/1024.0/1024.0
		FROM	archFileCopy AFC WITH(NOLOCK) 
			INNER JOIN archFile AF WITH(NOLOCK) 
				ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AF.isValid = 1
			INNER JOIN JMAdminJobStatsTable J WITH(NOLOCK) 
				ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId AND J.status in (1, 3, 14) AND J.totalBackupSize > 0
			INNER JOIN (SELECT id FROM archGroupCopy WHERE ((dedupeFlags & 262144) > 0)) C 
				ON AFC.archCopyId = C.id AND AFC.physicalSize > 0 --AND (AFC.flags & 256) = 0
		), 0)
END

DROP TABLE #UsageDate

----------------------------------
----------------------------------

 SET @surveyXML = (
	SELECT 	@SLAEndDate AS '@SLAEndDate',
		@nProtected AS '@ProtectedClients',
		@nNotProtected AS '@NotProtectedClients',
		@nExcluded AS '@ExcludedClients',
		@nProtectedLaptops AS '@ProtectedLaptops',
		@nNotProtectedLaptops AS '@NotProtectedLaptops',
		@nExcludedLaptops AS '@ExcludedLaptops',
		@nProtectedVMs AS '@ProtectedVMs',
		@nNotProtectedVMs AS '@NotProtectedVMs',
		@nExcludedVMs AS '@ExcludedVMs',
		@TotalJobs AS '@TotalJobs',
		@FailedJobs  AS '@FailedJobs',
		@SuccessfulJobs AS '@SuccessfulJobs',
		@Strike3Clients as '@Strike3Clients',
		@Strike2Clients as '@Strike2Clients',
		@Strike1Clients as '@Strike1Clients',
		@Strike3SubClients as '@Strike3SubClients',
		@Strike2SubClients as '@Strike2SubClients',
		@Strike1SubClients as '@Strike1SubClients',
		@Strike3VMs as '@Strike3VMs',
		@Strike2VMs as '@Strike2VMs',
		@Strike1VMs as '@Strike1VMs',
		@FullStrike3 as '@FullStrike3',
		@FullStrike2 as '@FullStrike2',
		@FullStrike1 as '@FullStrike1',
		@noJobSince AS '@StrikeDays',
		@totalAppSizeGB as '@TotalAppSizeGB',
		@totalMediaSizeGB as '@TotalMediaSizeGB',
		(SELECT days AS '@days', 
			SUM(CASE WHEN status = 1 THEN counts ELSE 0 END) AS '@ProtectedClients', 
			SUM(CASE WHEN status = 2 THEN counts ELSE 0 END) AS '@NotProtectedClients',
			SUM(CASE WHEN status = 3 THEN counts ELSE 0 END) AS '@ExcludedClients'
		FROM @Summary
		WHERE @MultiDaySLA = 1
		GROUP BY days
		ORDER BY days
		FOR XML PATH ('MultiDaySLA'), TYPE
		),
		
		(SELECT MonthBegin AS '@MonthBegin', 
			nClientsMet AS '@nClientsMet', 
			nClientsMissed AS '@nClientsMissed'
		FROM @SLATrend
		ORDER BY MonthBegin
		FOR XML PATH ('SLATrend'), TYPE
		)		
	FOR XML PATH ('CommCellSummaryXML')
	)
  

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