--- Please follow the below comments to insert SQL statements.
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON
BEGIN TRY
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 137
DECLARE @surveyXML NVARCHAR(MAX) = ''
--------- END - GENERATED CODE ---------
--------- BEGIN SURVEY QUERY ---------
--------- Insert your SQL statements here
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @frequencyMode NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
SELECT @frequencyMode = 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 ((@frequencyMode ='1') OR (@frequencyMode ='3') or (@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip'))
BEGIN
IF OBJECT_ID('GetSurveyParamValue') IS NULL
BEGIN
IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = 'CommservSurveyScheduleIntervalOption')
BEGIN
IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
INSERT INTO GXGlobalParam (name, value) SELECT 'CommservSurveyScheduleIntervalOption', '1'
ELSE
EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''CommservSurveyScheduleIntervalOption'', ''1'', 0, 0')
END
END
END
IF (@isPrivateRunning = 'Diagnostics and Usage') --Public
BEGIN
DECLARE @isJobDetailsCollectionEnabled INT = 0
IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
DECLARE @isJobDetailsCollectionEnabledSTR VARCHAR(256) = '0';
EXEC GetSurveyParamValue 'CommservSurveyPublicJobStatsEnabled', @isJobDetailsCollectionEnabledSTR OUTPUT
SELECT @isJobDetailsCollectionEnabled = CAST(@isJobDetailsCollectionEnabledSTR AS INT)
END
ELSE
SELECT @isJobDetailsCollectionEnabled = CAST(CAST(value AS VARCHAR(20)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyPublicJobStatsEnabled'
IF (@isJobDetailsCollectionEnabled <> 1) --No need to collect job details
BEGIN
DECLARE @relId INT
SELECT @relId = MAX(id) FROM simAllGalaxyRel
IF (@relId <= 15) --V10 or below done have check for null value. So set outputxml with surveyxml as blank
BEGIN
SET @outputXML =
(
SELECT
@queryId AS '@QueryId',
dbo.GetUnixTime(GETUTCDATE()) AS '@LogDate',
0 AS '@QueryRunningTime',
@surveyXML
FOR XML PATH('Rpt_CSSXMLDATA')
)
END
RETURN
END
END
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
DECLARE @CurrentTime INT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @CurrCollectionTime INT = @CurrentTime
DECLARE @LastCollectionTime INT = 0
DECLARE @TIME_STAMP_NAME NVARCHAR(128) = 'CommservSurveyJobStatsCollectionTime'
declare @date datetime =GETDATE()
DECLARE @nDays INT = datediff(day,DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0),@date)+1
IF (@isPrivateRunning = 'Diagnostics and Usage') --Public
BEGIN
SET @TIME_STAMP_NAME = 'CommservSurveyPublicJobStatsCollectionTime'
SET @nDays = 3 --Go back 3 days
END
else IF ((@frequencyMode ='3') or (@isPrivateRunning = 'Metrics Direct Dip'))
begin
SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIPJobStatsCollectionTime'
end
IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
DECLARE @LastCollectionTimeSTR VARCHAR(256) = '';
EXEC GetSurveyParamValue @TIME_STAMP_NAME, @LastCollectionTimeSTR OUTPUT
SELECT @LastCollectionTime = CAST(@LastCollectionTimeSTR AS INT)
END
ELSE
BEGIN
SELECT @LastCollectionTime = CAST(CAST(value AS VARCHAR(20)) AS INT)
FROM GXGlobalParam WITH (NOLOCK)
WHERE name = @TIME_STAMP_NAME
END
IF @LastCollectionTime = 0
BEGIN
DECLARE @LastCollection DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE())-@nDays, 0)
SET @LastCollection = dbo.ClientLocalToUTCTime(@LastCollection, 2)
DECLARE @CSFirstJobTime INT
SELECT @CSFirstJobTime = MIN(servEndDate) FROM JMBkpStats B WITH (NOLOCK)
IF (@CSFirstJobTime < dbo.GetUnixTime(@LastCollection))
SET @LastCollectionTime = dbo.GetUnixTime(@LastCollection)
ELSE
SET @LastCollectionTime = @CSFirstJobTime
END
DECLARE @maxcollectionsec INT = 24*3600 --default set to collect max 24 hours
IF (@isPrivateRunning = 'Diagnostics and Usage') --Public
SET @maxcollectionsec = @maxcollectionsec * 3 --Collect for maximum 3 days job in one run
IF @LastCollectionTime < (@CurrentTime - @maxcollectionsec)
SET @CurrCollectionTime = (@LastCollectionTime + @maxcollectionsec)
-- 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)='';
IF ((@frequencyMode ='1') OR (@frequencyMode ='3') or (@isPrivateRunning = 'Metrics Reporting') or (@isPrivateRunning = 'Metrics Direct Dip'))
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
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
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')
CREATE TABLE #JobDetails (
JobId INT, ParentJobId INT, TargetClientId INT, AppId INT, AppTypeId INT,
dataArchGrpId INT, logArchGrpId INT, StoragePolicy NVARCHAR(1026), PrimaryCopyId INT, OpType INT, BkpLevel INT, Status INT, StartDate INT, EndDate INT,
ScanType INT, SizeChangePct INT, TransferTime INT, ProtectedObjects BIGINT, FailedObjects BIGINT, FailedFolders BIGINT,
DataProtected BIGINT, DataCompressed BIGINT, DataTransferred BIGINT, DataWritten BIGINT,
ErrorCode VARCHAR(32), FailureReason NVARCHAR(512), SubTaskId INT, Attempts INT
,initFrom INT,NoOFPhases INT,failureErrorCode INT
)
CREATE TABLE #RunningJobs
(
JobId INT,
AppId INT,
OpType INT,
BkpLevel INT,
StartDate INT,
State INT,
InitFrom INT,
FailureReason NVARCHAR(MAX)
)
IF (@specialGroupExists =1 )
BEGIN
INSERT INTO #JobDetails
SELECT jobId, -1, B.targetClientId,
B.appId, appType,
CASE WHEN B.dataBackedUp = 1 THEN B.dataArchGrpId ELSE 0 END,
CASE WHEN B.LogsBackedUp = 1 THEN B.logArchGrpId ELSE 0 END,'', 0,
opType, bkpLevel, CASE displayStatus WHEN 0 THEN B.status ELSE displayStatus END,
servStartDate, servEndDate,
scanType, percentageChange, totalWriteTime, totalNumOfFiles,
scanFileFailures + backupFileFailures, scanFolderFailures + backupFolderFailures,
totalUncompBytes, totalCompBytes, nwTransBytes, 0, '',
CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,
subTaskId, numAttempts
,B.initFrom ,0,b.failureErrorCode
FROM JMBkpStats B WITH (NOLOCK)
INNER JOIN APP_Application A WITH (NOLOCK)
ON B.appId = A.id
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 servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime
AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
AND E.appId IS NULL
INSERT INTO #RunningJobs
SELECT B.jobId,J.applicationId,B.opType,J.bkpLevel, B.jobStartTime,B.STATE ,B.initFrom, dbo.JMGetLocalizedMessageFunc(0, B.failureReason)
FROM JMJobInfo B
INNER JOIN JMBkpJobInfo J ON J.jobId = B.jobId
AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
INNER JOIN APP_Application A WITH (NOLOCK) ON J.applicationId= A.id
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 A.id = E.appId
WHERE E.appId IS NULL
END
ELSE
BEGIN
INSERT INTO #JobDetails
SELECT jobId, -1, B.targetClientId,
B.appId, appType,
CASE WHEN B.dataBackedUp = 1 THEN B.dataArchGrpId ELSE 0 END,
CASE WHEN B.LogsBackedUp = 1 THEN B.logArchGrpId ELSE 0 END,'', 0,
opType, bkpLevel, CASE displayStatus WHEN 0 THEN B.status ELSE displayStatus END,
servStartDate, servEndDate,
scanType, percentageChange, totalWriteTime, totalNumOfFiles,
scanFileFailures + backupFileFailures, scanFolderFailures + backupFolderFailures,
totalUncompBytes, totalCompBytes, nwTransBytes, 0, '',
CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,
subTaskId, numAttempts
,B.initFrom ,0,b.failureErrorCode
FROM JMBkpStats B WITH (NOLOCK)
LEFT OUTER JOIN #ExcludedAppIds E
ON B.appId = E.appId
WHERE servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime
AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
AND E.appId IS NULL
INSERT INTO #RunningJobs
SELECT B.jobId,J.applicationId,B.opType,J.bkpLevel, B.jobStartTime,B.STATE ,B.initFrom, dbo.JMGetLocalizedMessageFunc(0, B.failureReason)
FROM JMJobInfo B
INNER JOIN JMBkpJobInfo J ON J.jobId = B.jobId
AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
LEFT OUTER JOIN #ExcludedAppIds E
ON J.applicationId = E.appId
WHERE E.appId IS NULL
END
UPDATE J SET ParentJobId = L.parentJobId
FROM #JobDetails J
INNER JOIN JMJobDataLink L WITH (NOLOCK) ON J.JobId = L.childJobId AND L.linkType = 7 -- LINK_TYPE_FOR_VSA_V2_PARENT_CHILD
UPDATE J SET PrimaryCopyId = CASE WHEN J.OpType = 59 THEN G.defaultSnapCopy ELSE G.defaultCopy END
FROM #JobDetails J
INNER JOIN archGroup G WITH (NOLOCK) ON J.logArchGrpId = G.id OR J.dataArchGrpId = G.id AND (J.dataArchGrpId > 0 OR J.logArchGrpId > 0)
UPDATE J SET StoragePolicy = AG.name
FROM #JobDetails J INNER JOIN archGroup AG WITH (NOLOCK) ON J.dataArchGrpId = AG.id WHERE J.dataArchGrpId > 0
UPDATE J SET StoragePolicy = (CASE WHEN J.dataArchGrpId>0 THEN StoragePolicy+', ' ELSE '' END) + AG.name
FROM #JobDetails J INNER JOIN archGroup AG WITH (NOLOCK) ON J.logArchGrpId = AG.id WHERE J.dataArchGrpId != J.logArchGrpId AND J.logArchGrpId > 0
CREATE TABLE #JobSizeOnMedia (JobId INT, DataWritten BIGINT)
INSERT INTO #JobSizeOnMedia
SELECT A.jobId, SUM(A.sizeOnMedia)
FROM JMJobDataStats A WITH (NOLOCK)
INNER JOIN #JobDetails B ON A.jobId = B.JobId AND A.archGrpCopyId = B.PrimaryCopyId AND A.commCellId = 2
GROUP BY A.jobId
UPDATE #JobDetails SET DataWritten = S.DataWritten
FROM #JobSizeOnMedia S
WHERE #JobDetails.JobId = S.JobId
DROP TABLE #JobSizeOnMedia
CREATE TABLE #JobVMDetails (jobId INT, parentJobId INT, vmClientId INT, vmBackupStart INT, vmBackupEnd INT, vmStatus INT,
vmSize BIGINT, vmUsedSpace BIGINT, vmGuestSize BIGINT, vmIsGuestSizeValid INT, vmFailureReason NVARCHAR(1024))
INSERT INTO #JobVMDetails
SELECT P.jobId, 0, P.VMclientId, [vmBackupStartTime], [vmBackupEndTime], [vmStatus], [vmSize], [vmUsedSpace], [vmGuestSize],
CASE WHEN [BkpLevel] NOT IN (1, 64, 128, 1024, 16384, 32768) THEN 1 ELSE ISNULL([vmIsGuestSizeValid], 1) END, NULL
FROM (SELECT V.jobId, V.VMclientId, J.BkpLevel, V.attrName, CAST(V.attrVal AS BIGINT) AS size
FROM APP_VMProp V WITH (NOLOCK)
INNER JOIN #JobDetails J WITH (NOLOCK) ON V.jobId = J.jobId AND V.commCellId = 2
WHERE V.attrName IN ('vmBackupStartTime', 'vmBackupEndTime', 'vmStatus', 'vmSize', 'vmUsedSpace', 'vmGuestSize', 'vmIsGuestSizeValid')) S
PIVOT (MAX(size) FOR attrName IN ([vmBackupStartTime], [vmBackupEndTime], [vmStatus], [vmSize], [vmUsedSpace], [vmGuestSize], [vmIsGuestSizeValid])) AS P
UPDATE J SET vmFailureReason = V.attrVal
FROM #JobVMDetails J INNER JOIN APP_VMProp V WITH (NOLOCK)
ON J.jobId = V.jobId AND V.commCellId = 2 AND J.vmClientId = V.VMclientId AND V.attrName = 'vmFailureReason'
WHERE J.vmStatus <> 0
CREATE TABLE #ProtectedVMs (JobId INT, Protected INT, Failed INT)
INSERT INTO #ProtectedVMs
SELECT jobId, SUM(CASE WHEN vmStatus = 0 THEN 1 ELSE 0 END), SUM(CASE WHEN vmStatus = 0 THEN 0 ELSE 1 END)
FROM #JobVMDetails
GROUP BY jobId
UPDATE #JobDetails SET ProtectedObjects = P.Protected, FailedObjects = P.Failed, FailedFolders = 0
FROM #ProtectedVMs P
WHERE #JobDetails.JobId = P.JobId
DROP TABLE #ProtectedVMs
UPDATE V SET JobId = J.JobId, ParentJobId = J.ParentJobId
FROM #JobVMDetails V
INNER JOIN #JobDetails J WITH (NOLOCK) ON V.jobId = J.ParentJobId AND V.vmClientId = J.TargetClientId
UPDATE #JobDetails
SET ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),
FailureReason = LEFT(dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, A.FailureReason)), 512)
FROM #JobDetails A
INNER JOIN JMFailureReasonMsg B WITH (NOLOCK) ON CAST(LEFT(A.FailureReason, LEN(A.FailureReason)-1) AS INT) = B.id
INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON B.messageId = C.MessageID AND C.LocaleID = 0
WHERE A.Status <> 1 AND A.FailureReason <> ''
update #JobDetails
SET NoOFPhases = A.numOfPhases,
Attempts = A.numOfAttempts
FROM #JobDetails B
INNER JOIN (Select A.jobId, COUNT(DISTINCT A.phase) AS numOfPhases,COUNT(*) AS numOfAttempts
FROM JMBkpAtmptStats A INNER JOIN #JobDetails B ON B.jobId = A.jobId AND 2= A.commCellId
GROUP BY A.jobId
) A
ON A.jobId = B.JobId
CREATE TABLE #JobDBSize (jobId INT, jobEndDate INT, dbName NVARCHAR(1024), dbSize BIGINT)
INSERT INTO #JobDBSize
SELECT J.JobId, J.EndDate, N2.name, SUM(I.backup_size)
FROM #JobDetails J
INNER JOIN sqlDbBackupInfo I WITH (NOLOCK) ON I.jobId = J.JobId
INNER JOIN sqlNames N WITH (NOLOCK) ON N.sqlId = I.sqlNameId and N.type = 1
INNER JOIN sqlnames2 N2 WITH (NOLOCK) ON N2.id = N.sqlid AND N2.type = 1
GROUP BY J.JobId, J.EndDate, N2.name
CREATE TABLE #SybDBJobPageSize (JobId INT, PageSize INT)
INSERT INTO #SybDBJobPageSize
SELECT JD.jobId, CAST(attrVal AS INT) AS PageSize
FROM
#JobDetails JD
INNER JOIN APP_Application A WITH (NOLOCK)
ON JD.AppId = A.id
INNER JOIN APP_InstanceProp I WITH (NOLOCK)
ON I.componentNameId = A.instance
AND I.attrName = 'SYBASE page size'
AND I.created < JD.StartDate
AND (I.modified >= JD.StartDate OR I.modified = 0)
INSERT INTO #JobDBSize
SELECT J.JobId, MAX(J.EndDate), U.sybDatabaseName, (SUM(CAST(sybSysVsize AS BIGINT)) * S.PageSize)
FROM #SybDBJobPageSize S
INNER JOIN #JobDetails J WITH (NOLOCK)
ON S.JobId = J.JobId
INNER JOIN sybSystemUsages U WITH (NOLOCK)
ON U.sybBackupJobId_l = J.JobId
GROUP BY J.JobId, S.PageSize, U.sybDatabaseName
DROP TABLE #SybDBJobPageSize
--Below Anomaly jobs are also being collected in query 24 for public 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 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 delayReason = delayReason + '
'+dbo.NTGetPossibleCausesForLongRunTime(jobid,1,0)
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
SET @surveyXML = (SELECT
(SELECT J.AppId AS '@AppId',
A.appTypeId AS '@AppTypeId',
dbo.NormalizeForXML(CL.name) AS '@ClientName',
T.name AS '@AgentName',
dbo.FixInstanceName(I.name, A.appTypeId) AS '@InstanceName',
dbo.NormalizeForXML(BS.name) AS '@BackupsetName',
dbo.NormalizeForXML(A.subclientName) AS '@SubclientName',
'' AS '@ClientGroups'
FROM (SELECT AppId FROM #JobDetails UNION SELECT AppId FROM #RunningJobs) J
INNER JOIN APP_Application A WITH (NOLOCK) ON J.AppId = A.id
INNER JOIN APP_Client CL WITH (NOLOCK) ON A.clientId = CL.id
INNER JOIN APP_iDAType T WITH (NOLOCK) ON A.appTypeId = T.type
INNER JOIN APP_InstanceName I WITH (NOLOCK) ON A.instance = I.id
INNER JOIN APP_BackupSetName BS WITH (NOLOCK) ON A.backupSet = BS.id
ORDER BY J.AppId
FOR XML PATH('Subclients'), TYPE),
(SELECT J.JobId AS '@JobId',
J.AppId AS '@AppId',
J.Status AS '@Status',
J.OpType AS '@OpType',
J.BkpLevel AS '@BkpLevel',
J.ScanType AS '@ScanType',
dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.StartDate), @csTimeZone)) AS '@StartDate',
dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.EndDate), @csTimeZone)) AS '@EndDate',
J.DataProtected AS '@DataProtected',
J.DataCompressed AS '@DataCompressed',
J.DataTransferred AS '@DataTransferred',
J.DataWritten AS '@DataWritten',
J.SizeChangePct AS '@SizeChangePct',
J.TransferTime AS '@TransferTime',
J.ProtectedObjects AS '@ProtectedObjects',
J.FailedObjects AS '@FailedObjects',
J.FailedFolders AS '@FailedFolders',
J.ErrorCode AS '@ErrorCode',
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(J.FailureReason,
',',' '), CHAR(10),';'), CHAR(13),';'), '''',' '), CHAR(145),' '), CHAR(146),' '), '
','') AS '@FailureReason',
dbo.NormalizeForXML(ISNULL(J.StoragePolicy, 'N/A')) AS '@StoragePolicy',
J.dataArchGrpId AS '@DataArchGroupId',
J.logArchGrpId AS '@LogArchGroupId',
T.taskName AS '@SchedulePolicy',
ST.subTaskName AS '@ScheduleName',
J.Attempts AS '@Attempts'
,J.initFrom AS '@InitFrom'
,J.NoOFPhases AS '@Phases'
,J.failureErrorCode AS '@failureErrorCode'
FROM #JobDetails J
LEFT OUTER JOIN TM_SubTask ST WITH (NOLOCK) ON J.SubTaskId = ST.subTaskId
LEFT OUTER JOIN TM_Task T WITH (NOLOCK) ON ST.taskId = T.taskId
ORDER BY J.JobId
FOR XML PATH('JobStats'), TYPE),
(SELECT jobId AS '@JobId',
parentJobId AS '@parentJobId',
vmClientId AS '@vmClientId',
vmBackupStart AS '@vmBackupStart',
vmBackupEnd AS '@vmBackupEnd',
vmStatus AS '@vmStatus',
vmSize AS '@vmSize',
vmUsedSpace AS '@vmUsedSpace',
vmGuestSize AS '@vmGuestSize',
vmIsGuestSizeValid AS '@vmIsGuestSizeValid',
LEFT(vmFailureReason, 1024) AS '@vmFailureReason'
FROM #JobVMDetails
ORDER BY JobId, vmClientId
FOR XML PATH('JobVMStats'), TYPE),
(SELECT jobId AS '@JobId',
dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(jobEndDate), @csTimeZone)) AS '@JobEndDate',
dbo.NormalizeForXML(dbName) AS '@DbName',
dbSize AS '@DbSize'
FROM #JobDBSize
ORDER BY JobId, dbName
FOR XML PATH('JobDBSize'), TYPE),
(SELECT
H.JobId as '@JobId',
H.AppId as '@AppId',
H.OpType as '@OpType',
H.BkpLevel as '@BkpLevel',
dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(H.StartDate), @csTimeZone)) as '@StartDate',
H.State as '@State',
H.InitFrom as '@InitFrom',
H.FailureReason as '@FailureReason'
FROM #RunningJobs H
ORDER BY H.StartDate
FOR XML PATH ('RunningJobStats'),TYPE),
(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'), TYPE)
FOR XML PATH ('JobDetails')
)
--select cast (@surveyXML as XML)
DROP TABLE #JobDetails
DROP TABLE #JobVMDetails
DROP TABLE #JobDBSize
DROP TABLE #RunningJobs
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')
)
IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @TIME_STAMP_NAME)
BEGIN
IF OBJECT_ID('SetSurveyParamValue') IS NULL
BEGIN
IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
INSERT INTO GXGlobalParam (name, value) SELECT @TIME_STAMP_NAME, ''
ELSE
EXEC QS_SetKeyIntoGlobalParamTbl @TIME_STAMP_NAME, 'Y', ''
END
END
IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SET @ErrorMessage = ERROR_MESSAGE();
RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH
IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
EXEC SetSurveyParamValue @TIME_STAMP_NAME, @CurrCollectionTime
END
ELSE
BEGIN
UPDATE GXGlobalParam SET value = CAST(@CurrCollectionTime AS VARCHAR(20))
WHERE name = @TIME_STAMP_NAME
END
SET NOCOUNT OFF
--------- END - GENERATED CODE ---------