Retrieve all Job Phase failures

An MSP customer, there're a lot of alerts which send out mails whenever any of them detects phase errors, but sometimes the job itself got succeeded, also in this case it's quite difficult to look into the phase errors since the information would be scattered at detailed job results, events, alerts, etc.

This query is created by a customer's requiest who want to track job **phase** errors at once, combining many information from various tables including event or error parameters.

So some verbose informations there, but you can easily detect when the phase failures happened, like one of MAs is having connection issue and affecting mlutiple jobs.

Here's the query, you can run it on CommServ DB, or creating customer reports on your Metrics Reporting could make your life easier.

use commserv

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

declare @tmp table (
	 jobid int
	,starttime bigint
	,endtime bigint
	,messageid int
	,occurred nvarchar(max)
	,message nvarchar(max)
)

insert into @tmp
select f.jobid, null, f.failureReasonTime, f.customMessageId, f.client, m.Message from JMFailureReasonMsg f
inner join EvLocaleMsgs m on m.MessageID = f.customMessageId and m.LocaleID = 0
where f.customMessageId != 0
and exists (select jobId from JMBkpAtmptStats s where s.jobid = f.jobid and s.status != 1)

insert into @tmp
select f.jobid, null, f.failureReasonTime, f.messageId, f.client, m.Message from JMFailureReasonMsg f
inner join EvLocaleMsgs m on m.MessageID = f.messageId and m.LocaleID = 0
where f.customMessageId = 0
and exists (select jobId from JMBkpAtmptStats s where s.jobid = f.jobid and s.status != 1)

insert into @tmp
select f.jobid, null, f.failureReasonTime, f.messageId, f.client, m.data from JMFailureReasonMsg f
inner join JMFailureReasonMsgParam m on m.msgId = f.id
and exists (select jobId from JMBkpAtmptStats s where s.jobid = f.jobid and s.status != 1)

insert into @tmp
select f.jobid, null, f.failureReasonTime, f.messageId, f.client, e.Message from JMFailureReasonMsg f
inner join JMFailureReasonMsgParam m on m.msgId = f.id
inner join EvLocaleMsgs e on e.MessageID = try_cast(m.data as bigint) and e.LocaleID = 0
and exists (select jobId from JMBkpAtmptStats s where s.jobid = f.jobid and s.status != 1)

insert into @tmp
select m.jobId_l, null, m.timeSource, m.id, null, c.Message from evMsg m
inner join EvMsgCache c on c.evMsgId = m.id and c.LocaleId = 0
where m.severity != 0
and exists (select jobId from JMBkpAtmptStats s where s.jobid = m.jobId_l)

insert into @tmp
select j.jobid, j.servStartDate, j.servEndDate, j.phase, null 
	,case j.status
		when 1 then 'Success' 
		when 2 then 'Failed'
		when 3 then 'Partial Success'
		when 4 then 'Killed'
		when 5 then 'Stopped'
		when 6 then 'Interrupted'
		when 9 then 'Failed to Start'
		else 'Unknown'
	 end + ' (' + convert(varchar(max), j.status) + ')' as 'Job Status'
from JMBkpAtmptStats j
 where j.status != 1


select distinct
	 t.jobid
	,apc.name as 'ClientName'
	,ag.App + ' ' + ag.appGroup + ' ' + ag.osGroup as 'AppType'
	,inst.name as 'Instance'
	,b.name as 'BackupSet'
	,apap.subclientName as 'Sublient'
	,st.bkpLevel
	,case st.status
		when 1 then 'Success' 
		when 2 then 'Failed'
		when 3 then 'Partial Success'
		when 4 then 'Killed'
		when 5 then 'Stopped'
		when 6 then 'Interrupted'
		when 9 then 'Failed to Start'
		else 'Unknown'
	 end + ' (' + convert(varchar(max), st.status) + ')' as 'Job Status'
	,format(DateAdd(second, t.starttime+3600*9, '1970-01-01'), 'yyyy/MM/dd HH:mm:ss') as 'starttime'  -- this is for JST timezone, change "*9" for your preferred timezone settings.
	,format(DateAdd(second, t.endtime+3600*9, '1970-01-01'), 'yyyy/MM/dd HH:mm:ss') as 'endtime'
	,t.occurred
	,t.messageid
	,t.message
--	,t.endtime
from @tmp t
inner join JMBkpStats st on st.jobId = t.jobId
inner join APP_Application apap on st.appId = apap.id
inner join APP_Client apc on apc.id = apap.clientId
inner join APP_InstanceName inst on inst.id = apap.instance
inner join App_AppTypeGroups ag on ag.appTypeId = apap.appTypeId
inner join APP_BackupSetName b on b.id = apap.backupSet

-- some filters to be ignored, for this customer VM backup CwE won't be useful.
where t.message not like '%1375731771%'
and t.message not like '%Found a multi-segment swap%'
and t.message not like '%File system metadata collection%'
--and t.message not like '%partial success%'
--and t.message not like '%failed (2)%'
and t.message not like '%unable to quiesce%'
and t.message not like '%snapshot may need to be manually removed%'
and t.message not like '%received failed message for job%'
and t.message not like '%network send failed%'

order by 
endtime desc
,t.jobid desc
--, t.endtime desc
, t.messageid desc

Hope this helps your Commvault life a bit,