--- Please follow the below comments to insert SQL statements.
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 24
DECLARE @surveyXML NVARCHAR(MAX)
--------- END - GENERATED CODE ---------
--------- BEGIN SURVEY QUERY ---------
--------- Insert your SQL statements here
DECLARE @LongRunHours int = 8
DECLARE @LongWriteSeconds int = 8*3600
DECLARE @LongRunHoursForAllJobs INT = 12
DECLARE @LongRunSeconds int = 12*3600
DECLARE @LastDays int = 30
DECLARE @LastDaysForAllJobs int = 1
DECLARE @TopN int = 25
DECLARE @TimeRangeBegin INT = dbo.GetUnixTime(DATEADD(DAY, -@LastDays, GETUTCDATE()))
DECLARE @TimeRangeEnd int = 0x7FFFFFFF
DECLARE @now INT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @csName VARCHAR(256)
DECLARE @csReleaseId int
SELECT @csReleaseId = releaseId FROM APP_Client WHERE id = 2
DECLARE @nstring NVARCHAR(MAX)
DECLARE @csNode INT;
SET @CSNode = ISNULL((select TOP 1 clientid from APP_Platform WITH (NOLOCK) where platformType = 1 order by clientid ASC), 0)
DECLARE @csTimeZone NVARCHAR(1024) = ''
SELECT @csTimeZone = dbo.GetClientTimeZone(2)
IF ISNULL(@csTimeZone, '') = ''
BEGIN
SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
WHERE TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END
-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyRunning'
END
IF ((@isPrivateRunning = 'Metrics Reporting')OR (@freequencyMode ='1'))
BEGIN
SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
SELECT @groupStr = value from GXGlobalParam where name ='CommservSurveySpecialClientGroup'
END
SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer
SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
if(@groupId > 0)
BEGIN
insert into @includeGroups values(@groupId);
DECLARE @tmpStr NVARCHAR(100);
SELECT @tmpStr = NAME FROM APP_ClientGroup WHERE id = @groupId;
IF(@tmpStr IS NOT NULL)
SET @groupStrName = @groupStrName + ', ' + @tmpStr
END
SET @startIndex = @endIndex + 1
SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END
DECLARE @specialGroupExists INT =0
DECLARE @includeClients TABLE (clientId INT);
IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
BEGIN
SET @specialGroupExists =1
INSERT INTO @includeClients
SELECT DISTINCT CGA.clientId
FROM @includeGroups CG INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON CGA.clientGroupId = CG.groupID
END
-- Get subclients
DECLARE @FSTotalActiveSubclients INT = 0
DECLARE @FSLongRunningSubclients INT = 0
DECLARE @VSATotalActiveSubclients INT = 0
DECLARE @VSALongRunningSubclients INT = 0
DECLARE @appTotalActiveSubclients INT = 0
DECLARE @appLongRunningSubclients INT = 0
DECLARE @jobCount INT = 0
DECLARE @FSFulljobCount INT = 0
DECLARE @VSAFulljobCount INT = 0
DECLARE @appJobCount INT = 0
DECLARE @jobs XML
DECLARE @Anomalyjobs XML
DECLARE @FSFulljobs XML
DECLARE @VSAFulljobs XML
DECLARE @appjobs XML
IF object_id('tempdb.dbo.#tblSubclients') is not null DROP TABLE #tblSubclients
IF object_id('tempdb.dbo.#tblSubclientStats') is not null DROP TABLE #tblSubclientStats
CREATE TABLE #tblSubclients (appId INT, clientId INT, appTypeId INT, instanceId INT)
CREATE TABLE #tblSubclientStats (appId INT, clientId INT, appTypeId INT, instanceId INT,
longBkpJobCount INT, lastFullJobId INT, startTime INT, backupLevel INT, opType INT,
status VARCHAR(256), archGrpId INT, duration INT, writeTime INT, appSize BIGINT,
isDedupEnabled INT, isSourceDeDupEnabled INT, isDASHEnabled INT, endTime INT)
IF @specialGroupExists =1
INSERT INTO #tblSubclients
SELECT A.id, A.clientId, A.appTypeId, A.instance
FROM APP_Application A WITH (NOLOCK)
INNER JOIN (
SELECT DISTINCT appId AS appId FROM JMBkpStats WITH (NOLOCK)
UNION
SELECT DISTINCT applicationId AS appId FROM JMBkpJobInfo WITH (NOLOCK)
) B ON A.id = B.appId
INNER JOIN @includeClients IC ON A.clientId = IC.clientId
INNER JOIN APP_Client C ON A.clientId = C.id AND C.status & 4096 = 0 --Exclude laptops
WHERE A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127)
AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
AND A.subclientStatus&(16|32) = 0
ELSE
INSERT INTO #tblSubclients
SELECT A.id, A.clientId, A.appTypeId, A.instance
FROM APP_Application A WITH (NOLOCK)
INNER JOIN (
SELECT DISTINCT appId AS appId FROM JMBkpStats WITH (NOLOCK)
UNION
SELECT DISTINCT applicationId AS appId FROM JMBkpJobInfo WITH (NOLOCK)
) B ON A.id = B.appId
INNER JOIN APP_Client C ON A.clientId = C.id AND C.status & 4096 = 0 --Exclude laptops
WHERE A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127)
AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
AND A.subclientStatus&(16|32) = 0
SET @nstring = '
INSERT INTO #tblSubclientStats(appId , clientId , appTypeId , instanceId ,longBkpJobCount , lastFullJobId , startTime ,
backupLevel , opType , status , archGrpId , duration , writeTime , appSize
,isDedupEnabled, isSourceDeDupEnabled, isDASHEnabled, endTime)
SELECT B.appId, S.clientId, B.appType, S.instanceId,
COUNT(B.jobId),
MAX(B.jobId),
0,0,0,0,0,0,0,0,0,0,0,0
FROM JMBkpStats B WITH (NOLOCK)
INNER JOIN #tblSubclients S ON B.appId = S.appId
LEFT OUTER JOIN APP_Client C WITH (NOLOCK) ON C.id = B.targetClientId
WHERE B.servEndDate > '+CAST(@TimeRangeBegin AS NVARCHAR(MAX))+' AND B.totalWriteTime >= '+CAST(@LongWriteSeconds AS NVARCHAR(MAX))+'
AND B.status IN (1, 3, 14) AND B.commCellId = 2
AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
AND B.bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
AND B.appType < 1000 AND B.appType NOT BETWEEN 600 AND 700
@REPLACESTRING
GROUP BY B.appId, S.clientId, B.appType, S.instanceId'
IF ( @csReleaseId > 14)
SET @nstring = REPLACE(@nstring,'@REPLACESTRING','AND (B.bkpAttributesEx & 0x200 = 0 --Not edge backup
OR (B.bkpAttributesEx & 0x200 = 0x200 AND C.status & 4096 = 0) --Non laptop Edge backup
)')
ELSE
SET @nstring = REPLACE(@nstring,'@REPLACESTRING','')
EXEC sp_executeSQL @nstring
UPDATE #tblSubclientStats
SET startTime = B.servStartDate, endTime = B.servEndDate, backupLevel = B.bkpLevel, opType = B.opType,
status = (CASE WHEN B.status = 1 THEN 'Completed' WHEN B.status IN (2,10,11,15) THEN 'Failed' WHEN B.status = 4 THEN 'Killed'
WHEN B.status IN (5,6) THEN 'Delayed' WHEN B.status IN (8,9,12,13) THEN 'Failed to Start'
WHEN B.status = 3 THEN 'Completed with errors' WHEN B.status IN (14,16) THEN 'Completed with warnings' ELSE 'Unknown' END),
archGrpId = CASE WHEN B.dataArchGrpId > 0 THEN B.dataArchGrpId ELSE B.logArchGrpId END,
duration = (B.servEndDate - B.servStartDate), writeTime = B.totalWriteTime, appSize = B.totalUncompBytes
FROM #tblSubclientStats S INNER JOIN JMBkpStats B WITH (NOLOCK)
ON B.jobId = S.lastFullJobId AND B.commCellId = 2
IF object_id('tempdb.dbo.#tblRunningJobs') is not null DROP TABLE #tblRunningJobs
CREATE TABLE #tblRunningJobs (appId INT, clientId INT, appTypeId INT, instanceId INT, jobId INT, startTime INT,
backupLevel INT, opType INT, status VARCHAR(256), archGrpId INT, duration INT, writeTime INT, appSize BIGINT)
SET @nstring =
' INSERT INTO #tblRunningJobs
SELECT B.applicationId, S.clientId, S.appTypeId, S.instanceId, J.jobId, J.jobStartTime,
B.bkpLevel, J.opType, dbo.GetJobStateName(J.state),
B.currentPolicy, ('+CAST (@now AS VARCHAR(MAX))+' - J.jobStartTime), totalMediaUsageTime, B.unCompBytesToXfer
FROM JMBkpJobInfo B WITH (NOLOCK)
INNER JOIN #tblSubclients S ON B.applicationId = S.appId
INNER JOIN JMJobInfo J WITH (NOLOCK)
ON J.jobId = B.jobId AND J.commCellId = B.commcellId
AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
AND ('+CAST (@now AS VARCHAR(MAX))+' - J.jobStartTime) >= '+CAST (@LongRunSeconds AS VARCHAR(MAX))+'
LEFT OUTER JOIN APP_Client C WITH (NOLOCK)
ON C.id = B.targetClientId
@REPLACESTRING'
IF ( @csReleaseId > 14)
SET @nstring = REPLACE(@nstring,'@REPLACESTRING','WHERE (B.bkpAttributesEx & 0x200 = 0 --Not edge backup
OR (B.bkpAttributesEx & 0x200 = 0x200 AND C.status & 4096 = 0) --Non laptop Edge backup
)')
ELSE
SET @nstring = REPLACE(@nstring,'@REPLACESTRING','')
EXEC sp_executeSQL @nstring
UPDATE #tblSubclientStats
SET longBkpJobCount += 1, lastFullJobId = RJ.jobId, backupLevel = RJ.backupLevel, opType = RJ.appTypeId, status = RJ.status,
archGrpId = RJ.archGrpId, duration = RJ.duration, writeTime = RJ.writeTime, appSize = RJ.appSize
FROM #tblSubclientStats S INNER JOIN #tblRunningJobs RJ ON S.appId = RJ.appId
WHERE RJ.backupLevel IN (1, 64, 128, 16384, 1024, 32768) AND RJ.writeTime >= @LongWriteSeconds
INSERT INTO #tblSubclientStats(appId , clientId , appTypeId , instanceId ,longBkpJobCount ,
lastFullJobId , startTime , backupLevel , opType , status , archGrpId , duration , writeTime , appSize
,isDedupEnabled, isSourceDeDupEnabled, isDASHEnabled, endTime)
SELECT RJ.appId, S.clientId, RJ.appTypeId, RJ.instanceId, CASE WHEN RJ.writeTime >= @LongWriteSeconds THEN 1 ELSE 0 END,
RJ.jobId, RJ.startTime, RJ.backupLevel, RJ.opType, RJ.status, RJ.archGrpId, RJ.duration, RJ.writeTime, RJ.appSize
,0,0,0,0
FROM #tblRunningJobs RJ LEFT OUTER JOIN #tblSubclientStats S ON S.appId = RJ.appId
WHERE S.appId IS NULL AND RJ.backupLevel IN (1, 64, 128, 16384, 1024, 32768) AND RJ.writeTime >= @LongWriteSeconds
UPDATE tSS
SET isDedupEnabled = (CASE WHEN AGC.dedupeFlags & 262144 = 0 THEN 0 /*CVA_SIDB_STORE_ENABLED_FLAG*/
WHEN AGC.dedupeFlags & 1048576 <> 0 THEN 0 /*CVA_DEDUP_INACTIVE_FLAG*/
WHEN CONVERT(INT, ASP.attrVal) = 0 THEN 0
ELSE 1
END)
FROM #tblSubclientStats tSS
INNER JOIN archGroup AG WITH (NOLOCK)
ON AG.id = tSS.archGrpId
INNER JOIN archGroupCopy AGC WITH (NOLOCK)
ON (AGC.id = AG.defaultCopy AND tSS.opType NOT IN (59, 65)) OR (AGC.id = AG.defaultSnapCopy AND tSS.opType IN (59, 65))
LEFT OUTER JOIN App_SubclientProp ASP WITH (NOLOCK)
ON ASP.componentNameId = tSS.appId
WHERE ASP.modified = 0 AND ASP.attrName = N'Single INstancing Option' AND ASP.cs_attrName = CHECKSUM(N'Single INstancing Option')
IF OBJECT_ID('archSubclientCopyDDBMap', 'U') IS NOT NULL
BEGIN
UPDATE tSS
SET isSourceDeDupEnabled = (CASE WHEN ISNULL(SSCount.NoOfPartitions, 0) > 1 AND (CL.releaseId <15) THEN 0
WHEN ISNULL(P.attrVal, 0) = 1 THEN 1
WHEN CONVERT(INT, ASP.attrVal) =3 THEN 1
WHEN (AGC.dedupeFlags & 524288) <> 0 AND (CL.releaseId > 13) THEN 1
ELSE 0 END),
isDASHEnabled = (CASE WHEN (AGC.dedupeFlags & 8388608) <> 0 THEN 1
ELSE 0 END)
FROM #tblSubclientStats tSS
INNER JOIN APP_Client CL WITH (NOLOCK)
ON CL.id = tSS.clientId
INNER JOIN archGroup AG WITH (NOLOCK)
ON AG.id = tSS.archGrpId
INNER JOIN archGroupCopy AGC WITH (NOLOCK)
ON (AGC.id = AG.defaultCopy AND tSS.opType NOT IN (59, 65)) OR (AGC.id = AG.defaultSnapCopy AND tSS.opType IN (59, 65))
LEFT OUTER JOIN archCopySIDBStore CS WITH (NOLOCK) ON AGC.id= CS.CopyId AND (CS.flags & 4) > 0
LEFT OUTER JOIN (SELECT Count(SubStoreId) as NoOfPartitions, SIDBStoreId FROM IdxSIDBSubStore GROUP BY SIDBStoreId) AS SSCount
ON SSCount.SIDBStoreId = CS.SIDBStoreId
LEFT OUTER JOIN App_SubclientProp ASP WITH (NOLOCK)
ON ASP.componentNameId = tSS.appId
LEFT OUTER JOIN APP_ClientProp P
ON tSS.clientId = P.componentNameId
AND P.modified = 0
AND P.attrName = 'Enable DeDuplication'
WHERE ASP.modified = 0 AND ASP.attrName = N'Single INstancing Option' AND ASP.cs_attrName = CHECKSUM(N'Single INstancing Option')
AND tSS.isDedupEnabled = 1
END
ELSE
BEGIN
UPDATE tSS
SET isSourceDeDupEnabled = (CASE WHEN ISNULL(SSCount.NoOfPartitions, 0) > 1 AND (CL.releaseId <15) THEN 0
WHEN ISNULL(P.attrVal, 0) = 1 THEN 1
WHEN CONVERT(INT, ASP.attrVal) =3 THEN 1
WHEN (AGC.dedupeFlags & 524288) <> 0 AND (CL.releaseId > 13) THEN 1
ELSE 0 END),
isDASHEnabled = (CASE WHEN (AGC.dedupeFlags & 8388608) <> 0 THEN 1
ELSE 0 END)
FROM #tblSubclientStats tSS
INNER JOIN APP_Client CL WITH (NOLOCK)
ON CL.id = tSS.clientId
INNER JOIN archGroup AG WITH (NOLOCK)
ON AG.id = tSS.archGrpId
INNER JOIN archGroupCopy AGC WITH (NOLOCK)
ON (AGC.id = AG.defaultCopy AND tSS.opType NOT IN (59, 65)) OR (AGC.id = AG.defaultSnapCopy AND tSS.opType IN (59, 65))
LEFT OUTER JOIN archCopySIDBStore CS WITH (NOLOCK) ON AGC.id= CS.CopyId -- AND (CS.flags & 4) > 0
LEFT OUTER JOIN (SELECT Count(SubStoreId) as NoOfPartitions, SIDBStoreId FROM IdxSIDBSubStore WHERE SealedTime = 0 GROUP BY SIDBStoreId) AS SSCount
ON SSCount.SIDBStoreId = CS.SIDBStoreId
LEFT OUTER JOIN App_SubclientProp ASP WITH (NOLOCK)
ON ASP.componentNameId = tSS.appId
LEFT OUTER JOIN APP_ClientProp P
ON tSS.clientId = P.componentNameId
AND P.modified = 0
AND P.attrName = 'Enable DeDuplication'
WHERE ASP.modified = 0 AND ASP.attrName = N'Single INstancing Option' AND ASP.cs_attrName = CHECKSUM(N'Single INstancing Option')
AND tSS.isDedupEnabled = 1
END
-- Get number of FS subclients that some full jobs were running for more than 8 hours in last 30 days
SET @FSTotalActiveSubclients = (
SELECT COUNT(DISTINCT SC.appId)
FROM #tblSubclients SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK)
ON SC.appTypeId = AG.appTypeId AND AG.appGroupId = 35) /*All FS Group*/
SET @FSLongRunningSubclients = (
SELECT COUNT(DISTINCT SC.appId)
FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK)
ON SC.appTypeId = AG.appTypeId AND AG.appGroupId = 35) /*All FS Group*/
SET @FSFulljobCount = (
SELECT COUNT(*)
FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK)
ON SC.appTypeId = AG.appTypeId AND AG.appGroupId = 35) /*All FS Group*/
SET @FSFulljobs = (
SELECT S.lastFullJobId as '@jobId', CL.id as '@clientId', CL.name as '@clientName', IT.type AS '@agentTypeId',
CASE WHEN IT.type IN (1, 11, 34, 42, 43) THEN 'Windows File System'
ELSE IT.name END as '@agentName',
dbo.GetJobBackupTypeDisplay(S.backupLevel) as '@backupType',
ROUND(S.duration/3600.0, 2) as '@durationHr',
ROUND(S.writeTime/3600.0, 2) as '@actualBKPDurationHr',
appSize as '@appSizeGB',
S.status as '@status',
S.isDedupEnabled AS '@isDeDupEnabled',
S.isSourceDeDupEnabled AS '@isSourceDeDupEnabled',
S.isDASHEnabled AS '@isDASHEnabled'
FROM (SELECT TOP (@topN) * FROM #tblSubclientStats
WHERE appTypeId IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35)
ORDER BY writeTime DESC) S
INNER JOIN APP_Client CL WITH (NOLOCK)
ON CL.id = S.clientId
INNER JOIN APP_iDAType IT WITH (NOLOCK)
ON IT.type = S.appTypeId
ORDER BY S.writeTime DESC
FOR XML PATH ('FSFulljobs'))
-- Get number of VSA subclients that some full jobs were running for more than 8 hours in last 30 days
SET @VSATotalActiveSubclients = (
SELECT COUNT(appId)
FROM #tblSubclients
WHERE appTypeId = 106) /*Virtual Server Agent*/
SET @VSALongRunningSubclients = (
SELECT COUNT(appId)
FROM #tblSubclientStats
WHERE appTypeId = 106) /*Virtual Server Agent*/
SET @VSAFulljobCount = (
SELECT COUNT(*)
FROM #tblSubclientStats
WHERE appTypeId = 106) /*Virtual Server Agent*/
SET @VSAFulljobs = (
SELECT S.lastFullJobId as '@jobId',
CL.id as '@clientId', CL.name as '@clientName',
IT.type AS '@agentTypeId', IT.name as '@agentName',
dbo.GetJobBackupTypeDisplay(S.backupLevel) as '@backupType',
ROUND(S.duration/3600.0, 2) as '@durationHr',
ROUND(S.writeTime/3600.0, 2) as '@actualBKPDurationHr',
appSize as '@appSizeGB',
S.status as '@status',
S.isDedupEnabled AS '@isDeDupEnabled',
S.isSourceDeDupEnabled AS '@isSourceDeDupEnabled',
S.isDASHEnabled AS '@isDASHEnabled',
CASE
WHEN I.attrVal = 100 OR I.attrVal = 101 THEN 'VMware'
WHEN I.attrVal = 102 THEN 'Hyper-V'
WHEN I.attrVal = 201 OR I.attrVal = 202 THEN 'XEN'
WHEN I.attrVal = 301 THEN 'Amazon'
ELSE 'Unknown'
END AS '@hypervisorType',
ISNULL(subClientP.attrVal,0) AS '@isSPEnabled',
CASE ISNULL(VMTranMode.attrVal, 0)
WHEN 0 THEN 'AUTO'
WHEN 1 THEN 'SAN'
WHEN 2 THEN 'Hot Add'
WHEN 3 THEN 'NAS'
WHEN 4 THEN 'NBD SSL'
WHEN 5 THEN 'NBD'
ELSE 'UNKNOWN'
END AS '@transportMode',
dbo.UTCToLocalStringTime(dbo.GetDateTime(S.startTime), @csTimeZone) AS '@startTime',
dbo.UTCToLocalStringTime(dbo.GetDateTime(S.endTime), @csTimeZone) AS '@endTime'
FROM (SELECT TOP (@topN) * FROM #tblSubclientStats
WHERE appTypeId = 106
ORDER BY writeTime DESC) S
INNER JOIN APP_Client CL WITH (NOLOCK)
ON CL.id = S.clientId
INNER JOIN APP_iDAType IT WITH (NOLOCK)
ON IT.type = S.appTypeId
INNER JOIN APP_InstanceProp I WITH (NOLOCK)
ON I.componentNameId = S.instanceId AND I.attrName = 'Virtual Server Instance Type' AND I.modified = 0
LEFT OUTER JOIN APP_SubClientProp subClientP
ON S.appId = subClientP.componentNameId
AND S.startTime > subClientP.created AND (subClientP.modified = 0 OR subClientP.modified >= S.startTime)
AND subClientP.attrName = 'Enable Snap Backups' AND subClientP.cs_attrName = CHECKSUM(N'Enable Snap Backups')
LEFT OUTER JOIN APP_SubClientProp VMTranMode
ON S.appId = VMTranMode.componentNameId
AND S.startTime > VMTranMode.created AND (VMTranMode.modified = 0 OR VMTranMode.modified >= S.startTime)
AND VMTranMode.attrName = 'Virtual Server Transport Mode' AND VMTranMode.cs_attrName = CHECKSUM(N'Virtual Server Transport Mode')
ORDER BY S.writeTime DESC
FOR XML PATH ('VSAjobs'))
-- Get number of app subclients that some full jobs were running for more than 8 hours in last 30 days
SET @appTotalActiveSubclients = (
SELECT COUNT(DISTINCT SC.appId)
FROM #tblSubclients SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK)
ON SC.appTypeId = AG.appTypeId AND SC.appTypeId <> 106 /*Non-VSA*/
AND SC.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WITH (NOLOCK) WHERE appGroupId = 35)) /*Non FS*/
SET @appLongRunningSubclients = (
SELECT COUNT(DISTINCT SC.appId)
FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK)
ON SC.appTypeId = AG.appTypeId AND SC.appTypeId <> 106 /*Non-VSA*/
AND SC.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WITH (NOLOCK) WHERE appGroupId = 35)) /*Non FS*/
SET @appJobCount = (
SELECT COUNT(*)
FROM #tblSubclientStats SC INNER JOIN APP_AppTypeGroupAssoc AG WITH (NOLOCK)
ON SC.appTypeId = AG.appTypeId AND SC.appTypeId <> 106 /*Non-VSA*/
AND SC.appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WITH (NOLOCK) WHERE appGroupId = 35)) /*Non FS*/
SET @appjobs = (
SELECT S.lastFullJobId as '@jobId', CL.id as '@clientId', CL.name as '@clientName', IT.type AS '@agentTypeId', IT.name as '@agentName',
dbo.GetJobBackupTypeDisplay(S.backupLevel) as '@backupType',
ROUND(S.duration/3600.0, 2) as '@durationHr',
ROUND(S.writeTime/3600.0, 2) as '@actualBKPDurationHr',
appSize as '@appSizeGB',
S.status as '@status',
S.isDedupEnabled AS '@isDeDupEnabled',
S.isSourceDeDupEnabled AS '@isSourceDeDupEnabled',
S.isDASHEnabled AS '@isDASHEnabled',
ISNULL(subClientP.attrVal,0) AS '@isSPEnabled'
FROM (SELECT TOP (@topN) * FROM #tblSubclientStats
WHERE appTypeId NOT IN (SELECT appTypeId FROM APP_AppTypeGroupAssoc A WHERE appGroupId = 35)
AND appTypeId <> 106
ORDER BY writeTime DESC) S
INNER JOIN APP_Client CL WITH (NOLOCK)
ON CL.id = S.clientId
INNER JOIN APP_iDAType IT WITH (NOLOCK)
ON IT.type = S.appTypeId
LEFT OUTER JOIN APP_SubClientProp subClientP
ON S.appId = subClientP.componentNameId
AND S.startTime > subClientP.created AND (subClientP.modified = 0 OR subClientP.modified >= S.startTime)
AND subClientP.attrName = 'Enable Snap Backups' AND subClientP.cs_attrName = CHECKSUM(N'Enable Snap Backups')
ORDER BY S.writeTime DESC
FOR XML PATH ('Appjobs'))
-- Get number of jobs running for more than 12 hours in last 24 hours
IF object_id('tempdb.dbo.#tblLongRunJobs') is not null DROP TABLE #tblLongRunJobs
CREATE TABLE #tblLongRunJobs (appId INT, clientId INT, appTypeId INT, instanceId INT, jobId INT, startTime INT,
backupLevel INT, opType INT, status VARCHAR(256), archGrpId INT, duration INT, writeTime INT, appSize BIGINT)
SET @TimeRangeBegin = dbo.GetUnixTime(DATEADD(DAY, -@LastDaysForAllJobs, GETUTCDATE()))
SET @nstring = '
INSERT INTO #tblLongRunJobs
SELECT S.appId, S.clientId, S.appTypeId, S.instanceId, B.jobId, B.servEndDate, B.bkpLevel, B.opType,
(CASE WHEN B.status = 1 THEN ''Completed'' WHEN B.status IN (2,10,11,15) THEN ''Failed'' WHEN B.status = 4 THEN ''Killed''
WHEN B.status IN (5,6) THEN ''Delayed'' WHEN B.status IN (8,9,12,13) THEN ''Failed to Start''
WHEN B.status = 3 THEN ''Completed with errors'' WHEN B.status IN (14,16) THEN ''Completed with warnings'' ELSE ''Unknown'' END),
CASE WHEN B.dataArchGrpId > 0 THEN B.dataArchGrpId ELSE B.logArchGrpId END,
(B.servEndDate - B.servStartDate), B.totalWriteTime, B.totalUncompBytes
FROM JMBkpStats B WITH (NOLOCK)
INNER JOIN #tblSubclients S WITH (NOLOCK) ON S.appId = B.appId AND B.servEndDate > '+CAST (@TimeRangeBegin AS NVARCHAR(MAX))+'
LEFT OUTER JOIN APP_Client C WITH (NOLOCK) ON C.id = B.targetClientId
WHERE (B.servEndDate - B.servStartDate) > '+CAST (@LongRunSeconds AS NVARCHAR(MAX))+'
AND B.status IN (1, 3, 14) AND B.commCellId = 2
AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
@REPLACESTRING
ORDER BY (B.servEndDate - B.servStartDate) DESC'
IF ( @csReleaseId > 14)
SET @nstring = REPLACE(@nstring,'@REPLACESTRING','AND (B.bkpAttributesEx & 0x200 = 0 --Not edge backup
OR (B.bkpAttributesEx & 0x200 = 0x200 AND C.status & 4096 = 0) --Non laptop Edge backup
)')
ELSE
SET @nstring = REPLACE(@nstring,'@REPLACESTRING','')
EXEC sp_executeSQL @nstring
INSERT INTO #tblLongRunJobs
SELECT R.*
FROM #tblRunningJobs R
LEFT OUTER JOIN #tblLongRunJobs L ON
L.jobId = R.jobId
WHERE L.jobId IS NULL AND R.duration > @LongRunSeconds
SET @jobCount = (SELECT COUNT(jobId) FROM #tblLongRunJobs)
SET @jobs = (
SELECT TOP (@topN) J.jobId as '@jobId', CL.id as '@clientId', CL.name as '@clientName', IT.type AS '@agentTypeId',
CASE WHEN IT.type IN (1, 11, 34, 42, 43) THEN 'Windows File System'
ELSE IT.name END as '@agentName',
dbo.GetJobBackupTypeDisplay(J.backupLevel) as '@backupType',
ROUND(J.duration/3600.0, 2) as '@durationHr',
ROUND(J.writeTime/3600.0, 2) as '@actualBKPDurationHr',
J.appSize as '@appSizeGB',
J.status as '@status'
FROM #tblLongRunJobs J
INNER JOIN #tblSubclients S WITH (NOLOCK) ON S.appId = J.appId
INNER JOIN APP_Client CL WITH (NOLOCK) ON CL.id = S.clientId
INNER JOIN APP_iDAType IT WITH (NOLOCK) ON IT.type = S.appTypeId
ORDER BY J.duration DESC
FOR XML PATH ('Job'))
--Below Anomaly jobs are also being collected in query 137 for private metrics scenario. Please consider any changes made here to be handled on both the places.
DECLARE @AnomalycurrentTime INT = DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())
IF OBJECT_ID('tempdb.dbo.#AnomalousJobs') IS NOT NULL DROP TABLE #AnomalousJobs
CREATE TABLE #AnomalousJobs (clientID int,jobId INT,percentageComplete DECIMAL(10,2),subClientName nvarchar(1024),delayReason nvarchar(MAX),runTime int,flag int, agenttype int, agentName nvarchar(1024), subclientid int, delayReasonRAW nvarchar(MAX), state nvarchar(1024),clientName nvarchar(1024),opType INT,bkpLevel INT, thresholdtime INT )
INSERT INTO #AnomalousJobs (clientID ,jobId ,percentageComplete ,subClientName ,delayReason ,runTime ,flag , agenttype , agentName , subclientid, delayReasonRAW , state ,clientname , opType,bkpLevel)
SELECT DISTINCT clientId,jobid,percentComplete,subClientName,delayReason,@AnomalycurrentTime - jobStartTime,1,appTypeId,appTypeName, applicationId,CASE WHEN LEN(failureReason)>0 THEN LEFT(failureReason,CHARINDEX(',',failureReason)-1) END,displayState,clientname,opType,bkpLevel from RunningBackups WHERE guiAlertColorLevel>=100000
UPDATE AJ SET thresholdTime = (JO.attributeValueInt + (JO.attributeValueInt * 0.05))/60
FROM #AnomalousJobs AJ
INNER JOIN JMJobOptions JO ON JO.jobId = AJ.jobId AND JO.attributeId = 76 /*JM_JOB_RUNNING_THRESHOLD_TIME_ID*/
UPDATE AJ SET delayReasonRAW = Msg.message
FROM #AnomalousJobs AJ
INNER JOIN (SELECT A.jobid,L.message FROM #AnomalousJobs A
INNER JOIN JMFailureReasonMsg F ON F.id = A.delayReasonRAW
INNER JOIN EvLocaleMsgs L ON L.messageId = F.messageId AND localeID = 0
)Msg ON Msg.jobId = AJ.jobId
UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'[^','')
UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%s]','')
UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%a]','')
UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%d]','')
UPDATE #AnomalousJobs SET delayReasonRAW = replace(delayReasonRAW,'%','')
UPDATE #AnomalousJobs SET delayReasonRAW = 'No delay reason'
Where LEN(delayReasonRAW)<2 OR delayReasonRAW is NULL
IF OBJECT_ID('NTGetPossibleCausesForLongRunTime', 'FN') IS NOT NULL
BEGIN
SET @nstring = N'UPDATE #AnomalousJobs SET delayReason = delayReason + ''
'' + dbo.NTGetPossibleCausesForLongRunTime(jobid,1,0)'
EXEC sp_executeSQL @nstring
END
SET @AnomalyJobs = (SELECT Aj.jobId as '@JobId',
CASE AJ.opType
WHEN 4 THEN (CASE WHEN AJ.agenttype = 67 THEN N'Compliance Archive' ELSE N'Backup' END)
WHEN 14 THEN N'Synthetic Full'
WHEN 18 THEN N'Application Commandline Backup'
WHEN 59 THEN N'Snap Backup'
WHEN 60 THEN N'Backup Copy'
WHEN 65 THEN N'Application Commandline Snap Backup'
WHEN 98 THEN N'Archive'
ELSE N'Unknown'
END AS '@Operation',
CASE AJ.State
WHEN 1 THEN 'Running'
WHEN 2 THEN 'Pending'
WHEN 3 THEN 'Waiting'
WHEN 4 THEN 'Completed'
WHEN 5 THEN 'Stopped'
WHEN 6 THEN 'Killpending'
WHEN 7 THEN 'Stoppedning'
WHEN 8 THEN 'Interruptpending'
WHEN 9 THEN 'Completed'
WHEN 10 THEN 'Fail'
WHEN 11 THEN 'Killed'
WHEN 12 THEN 'Partial Completed'
WHEN 13 THEN 'Kill pending'
WHEN 14 THEN 'Dormant'
WHEN 15 THEN 'Queued'
WHEN 16 THEN 'Dormant schedule wait'
WHEN 17 THEN 'Running Unverifiable'
ELSE ''
END AS '@State',
AJ.clientid AS '@clientid',
AJ.clientname as '@ClientName',
AJ.agenttype as '@agenttype',
AJ.agentName as '@agentName',
AJ.subclientid as '@subclientid',
AJ.subclientname as '@SubClientName',
AJ. percentageComplete as '@PercentageComplete',
AJ.delayReason as '@DelayReason',
AJ.runTime/60 as '@ElapsedTimeInMinutes',
thresholdTime as '@thresholdTime'
FROM #AnomalousJobs AJ
WHERE AJ.flag = 1
FOR XML PATH ('AnomalyJobs'))
SET @surveyXML = (
SELECT
CASE @specialGroupExists WHEN 1 THEN SUBSTRING(@groupStrName, 3, 5120) ELSE N'All' END as '@clientGroups',
@LastDays as '@LastDays',
@LongRunHours as '@LongRunHours',
@jobCount AS '@LongRunningJobs',
@FSTotalActiveSubclients AS '@TotalFSSCCount',
@FSLongRunningSubclients AS '@LongRunningFSSCCount',
@FSFulljobCount AS '@LongRunningFSFullJobs',
@VSATotalActiveSubclients AS '@TotalVSASCCount',
@VSALongRunningSubclients AS '@LongRunningVSASCCount',
@VSAFulljobCount AS '@LongRunningVSAFullJobs',
@appTotalActiveSubclients AS '@TotalOtherSCCount',
@appLongRunningSubclients AS '@LongRunningOtherSCCount',
@appJobCount AS '@LongRunningOtherFullJobs',
@TopN as '@TopN',
@jobs,
@FSFulljobs,
@VSAFulljobs,
@appjobs,
@AnomalyJobs
FOR XML PATH ('LongRunJobs')
-- Replace <> with specific name
)
IF object_id('tempdb.dbo.#tblSubclients') is not null DROP TABLE #tblSubclients
IF object_id('tempdb.dbo.#tblSubclientStats') is not null DROP TABLE #tblSubclientStats
IF object_id('tempdb.dbo.#tblRunningJobs') is not null DROP TABLE #tblRunningJobs
IF object_id('tempdb.dbo.#tblLongRunJobs') is not null DROP TABLE #tblLongRunJobs
IF OBJECT_ID('tempdb.dbo.#AnomalousJobs') IS NOT NULL DROP TABLE #AnomalousJobs
--------- END SURVEY QUERY ---------
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML =
(
SELECT
@queryId AS '@QueryId',
@EndTime AS '@LogDate',
(@EndTime - @LogDate) AS '@QueryRunningTime',
@surveyXML
FOR XML PATH('Rpt_CSSXMLDATA')
)
--select cast(@surveyXML as XML)
SET NOCOUNT OFF
--------- END - GENERATED CODE ---------