how to identify no: of failed attempts of a job using workflow

Last post 08-03-2019, 10:46 PM by Anthony.Hodges. 3 replies.
Sort Posts: Previous Next
  • how to identify no: of failed attempts of a job using workflow
    Posted: 08-01-2019, 1:54 AM

    Using Qcommands > List > JobHistory 

    I can list the job history for a subclient

    From this I want to retrieve the number of failed attempts

    can someone help me with this

  • Re: how to identify no: of failed attempts of a job using workflow
    Posted: 08-01-2019, 5:06 PM
    This is what we use in a CommServDBQuery. Then see the attachment we use for a decision element. Hopefully self-explanatory
    ;
    with c1 as
    (SELECT top 3 startdate
       ,clientname
       ,idataagent
       ,instance
       ,backupset
       ,subclient
       ,backuplevel
       ,jobstatus
    FROM CommcellBackupInfo
    WHERE clientname = 'xpath:{/workflow/inputs/client/clientName}'
    and iDataAgent = 'xpath:{/workflow/inputs/DataAgent}'
    and backupset = 'xpath:{/workflow/inputs/backupSet}'
    and Subclient = 'xpath:{/workflow/inputs/subClient}'
    order by jobid desc
    )
    select failcount = sum(case when jobstatus IN ('Failed','PartialSuccess') then 1 else 0 end)
    from c1

    Attachment: Capture_Descision.PNG
  • Re: how to identify no: of failed attempts of a job using workflow
    Posted: 08-02-2019, 12:01 AM

    Thanks for the response Larry :)

    So the logic what I wanted is just to identify last 2 consecutive attempts were failed/partialsuccess.

    if so add a condition, so here i would use top 2 instead of top 3 use the condition element (with resultset ==2

     

    And the response is Perfect :)

    Thanks a ton!

  • Re: how to identify no: of failed attempts of a job using workflow
    Posted: 08-03-2019, 10:46 PM

    Hi alligator89, what you are asking here requires creating a "Common Table Expression" over row_number().  The slang sqlspeak term is a "Gaps and Islands" query. Whilst the syntax is not rocket science, I would strongly recommend that you read up on what this entails first because it will be important for you to understand how to test that you are not returning false positives.

    https://www.sqlshack.com/overview-of-the-sql-row-number-function/

    If it were me, I would create a temporary table that inserted from a CTE collecting the Job Events, resultset, and also returns Row_Number() that is partioned OVER JobId, ResultSet.  That will at least give you a starting point to query for consecutive failed/paritalsuccess.

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2019 Commvault | All Rights Reserved. | Legal | Privacy Policy