Create custom alert for long-running jobs

  • 17 March 2023
  • 0 replies

Userlevel 2
Badge +3

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:


  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

    , as 'clientname'
    ,1.0 * (dbo.GetUnixTime(GETUTCDATE()) - ji.jobStartTime) / grp.avg_duration as 'exceeded'
    from jmbkpjobinfo bkji
    inner join JMJobInfo ji on bkji.jobid = ji.jobid
    inner join APP_Application apap on = bkji.applicationId
    inner join APP_Client apc on = 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)

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.

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.

0 replies

Be the first to reply!