Getting report data into the workflow

Last post 05-06-2019, 1:33 PM by CharlesL. 7 replies.
Sort Posts: Previous Next
  • Getting report data into the workflow
    Posted: 04-11-2019, 6:01 AM

    I have ran multiple queries from the CommServ database that returns the data as a result set. That data I can successfully retreive into the workflow. However, when I run a CommServ database query for report information  I cannot get the workflow to see that information. The Qcommand that allows a report to be generated within the workflow only works with iDataAgents and not VSAs or VMs that are backed up via VADP. Also, I've tried unsuccessfully to pull the information in through XML fromat. I've even reduced the information to one VM with only one row of information and five columns. The only information I'm trying to receive is the last good backup and the date of the backup for one VM before decommissioning the VM. So when I run the CommServ query from within the workflow is there anyway for the workflow to see this data?


    Charles Lavender
  • Re: Getting report data into the workflow
    Posted: 04-11-2019, 7:26 AM

    which command are you using and do you get the desired output data if you run it manually?

  • Re: Getting report data into the workflow
    Posted: 04-11-2019, 7:44 AM

    It's about a 3 page SQL query that I run against the CommServ db and if I run it manually I get the required result.


    Charles Lavender
  • Re: Getting report data into the workflow
    Posted: 04-11-2019, 9:24 AM

    Can you try adding the following line at the beginning of your sql query

    SET NOCOUNT ON

    Regards

  • Re: Getting report data into the workflow
    Posted: 04-11-2019, 9:32 AM

    That's already set at the beginning of the query.


    Charles Lavender
  • Re: Getting report data into the workflow
    Posted: 04-11-2019, 10:21 AM

    Then it sounds like something is turning it off in the middle of the query.  Are you calling any stored procedures throughout the query which could be executing SET NOCOUNT OFF?

  • Re: Getting report data into the workflow
    Posted: 04-12-2019, 5:14 AM

    We use below SQL query to get last successful backup for client/VM, date, job id and other informations. Want to know whether if it helps you.

    Try to convert below query as ResulttoHtml and output to email using workflow.

    select DISTINCT clientname,idataagent,instance,backupset,subclient,data_sp,max(dateadd(s,startdateunixsec,'1970-01-01 07:00:00')) as 'Last Successful Backup',backuplevel,max(jobid) as 'Job id' from CommCellBackupInfo

    where startdate >= (dateadd(dd,-30,GETDATE())) and clientname = '$g' and jobstatus = 'Success'
    GROUP BY clientname,idataagent,instance,backupset,subclient,data_sp,backuplevel
    UNION
    select DISTINCT vmname as clientname,'Virtual Server' as idataagent,
    virtualizationclient as instance,backupset,subclient,data_sp
    ,max(dateadd(s,convert(int,startdateunixsec),'1970-01-01 07:00:00')) as 'Last Successful Backup',backuplevel,max(jobid) as 'Job id'
    from CommCellVMBackupInfo
    where startdate >= (dateadd(dd,-30,GETDATE())) and vmname = '$g' and vmstatus = 'Success'
    GROUP BY vmname,virtualizationclient,backupset,subclient,data_sp,backuplevel

     

    $g variable as client/VM name, Fetch lastest backup from previous 30 days history, time zone conversion.

    query written to our environment needs. 

     

    Also, you can try key nWFRESPONSETIMEOUT - it helps to wait for response if query execution time is longer.

  • Re: Getting report data into the workflow
    Posted: 05-06-2019, 1:33 PM

    I was able to modify this and it works in my environment.

     

    Much Appreciated!


    Charles Lavender
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