Create custom alert for long-running jobs

Related to my previous topic:

, this can be used for custom alert to detect long-running jobs from average.

# Please refer to the following for creating custom alert in general, this is for SP16 (still my customers are on this stage) but basically applicable for newer releases:

# https://documentation.commvault.com/commvault/v11_sp16/article?p=5308.htm

1. start adding a new alert rule

2. 

   name it as you like

3. 

   Put the query below

4. 

   actual query as follows:
    

   set nocount on
   
   set transaction isolation level read uncommitted
   
   select 
   	 bkji.jobId
   	,bkji.applicationId
   	,apc.name as 'clientname'
   	,apap.subclientName
   	,bkji.bkpLevel
   	,1.0 * (dbo.GetUnixTime(GETUTCDATE()) - ji.jobStartTime) / grp.avg_duration as 'exceeded'
   	,grp.avg_duration
   	,grp.count_job
   from jmbkpjobinfo bkji
   inner join JMJobInfo ji on bkji.jobid = ji.jobid
   inner join APP_Application apap on apap.id = bkji.applicationId
   inner join APP_Client apc on apc.id = apap.clientId
   inner join (
   	select appId, bkpLevel, avg(duration) as avg_duration, avg(totalBackupSize) as avg_totalBackupSize, count(jobid) as count_job from JMBkpStats
   	where status = 1
   	group by appId, bkpLevel
   ) as grp on grp.appId = bkji.applicationId and grp.bkpLevel = bkji.bkpLevel
   order by bkji.jobId desc
   
   set nocount off
   

    

5. specify output column (to be used later)

6. 

   specify frequency to run the query (equivalent to specify frequency of detecting alerts), 15 min would be enough for practical use case.

7. 

   specify commcell (just choose your commcell, this alert would work only for commcell level)

8. 

   specify who can use this (aka. create new actual alert)

9. 

This timing you’re ready to create a new alert for specific settings like the following process.

1. create a new alert

2. 

   name the alert as you like, but choose the custom alert rules just created

3. 

   specify alert criteria, for instance you can specify “clientname” “contains” criteria to monitor only the specific client, also “exceeded” “greater than” and any number (say, 2) to detect the jobs which is running as twice as longer than average.

4. 

   

Other alert configuration are the same as normal ones (just preferred notification types, security, etc.).
Also this query has a column to indicate total number of jobs for average calculation, so if there’s very small number of jobs on which the average job duration is not so meaningful, you can exclude that one as well.

My customer is using this to identify specific delay for specific tenant users who frequently raising queries (complaints?) whenever they finds job “delay” per their feelings.

So immediately after the customer receives this alert, they can start investigating the potential issues, at least secure the logs for further analysis.

Hoping this helps a bit for your Commvault life.