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