Workflow e-mail CommServDBQuery result in table

Last post 01-21-2014, 10:27 PM by PeteC. 4 replies.
Sort Posts: Previous Next
  • Workflow e-mail CommServDBQuery result in table
    Posted: 11-15-2013, 7:21 AM

    Hi,

    I have a workflow performing a DB query and I want to the result in table form in the e-mail body or attached as a csv.

    Does anyone know how to acchieve this?

  • Re: Workflow e-mail CommServDBQuery result in table
    Posted: 11-16-2013, 5:03 PM

    Hello Boekman

    Are you having an issue with formatting the sql query in your email? Or getting the email to contain the sql query results?

    For the email you could use a HTML format, and create an HTML table.  Then insert variables like these within each table columnn:

    xpath:{/workflow/CommServDBQuery_1/resultSets/row/col[1]}

    xpath:{/workflow/CommServDBQuery_1/resultSets/row/col[2]}

    xpath:{/workflow/CommServDBQuery_1/resultSets/row/col[3]}

     

    With some manipulation you should be able to output each column in the sql query to a column in your HTML table.

     

    To output the SQL query to csv, I can only think to do this using SQLCMD via the commandprompt or powershell.

    Using the executecommand workflow activity you could launch a command like this (may need to pass credentials or modify this syntax to fit your needs):

    sqlcmd -q "select col1,col2,col3 from table"-oc:\myfile.csv -h-1 -s","

     

    I would need to investigate how to attach the csv to an email.  I recall a way to "Pick a File" during the workflow.

    Let me know if this helps for now.

  • Re: Workflow e-mail CommServDBQuery result in table
    Posted: 11-18-2013, 5:44 AM

    Hi dmcvault,

    Yes, I would like to create a HTML table but the resutlset is not allways the same. This means the table needs to be created programatically.

    The SQL query i'm using to get the results is "exec QS_JobsinSPCopy" so there is no direct way to do a select on the collumns.

    Regards,
    Jeroen 

  • Re: Workflow e-mail CommServDBQuery result in table
    Posted: 01-21-2014, 8:24 PM

    I'm just starting to get into the Workflows and starting to learn JavaScript/beanshell but I would think you would need to define a variable for the workflow that will become your email body.

    You would need to populate this with <table> and <tr><th> for your column headings, then cycle through the query results with a ForEach workflow that appends the table rows to the existing variable.

    You'd then need to append the </table> to the variable.

    Then you should be able to send that variable as your email body in HTML format.

    I haven't tried this myself yet but I'm pretty sure I already have a use case internally here that would require similar functionality.

  • Re: Workflow e-mail CommServDBQuery result in table
    Posted: 01-21-2014, 10:27 PM

    Ok I managed to do this after some headbutting against a brick wall trying to assign valuables to a workflow variable. Looking at the included demo Workflows helped in this regard but it would be nice if there was a reference of script functions etc. on the CommVault Documentation site to help with this.

    Anyway, as a learning exercise I simply wanted a summary of how many associations there are for all my Schedule Policies.

    I start off with a string variable defined for the workflow called "EmailBody" which is assigned "<table><tr><th>Schedule Policy</th><th># of Associations</th></tr>".

    Then I execute a CommServeDBQuery which outputs all my Schedule Policy ID's and names.

    I then use a ForEach to cycle through each policy and run another CommServeDBQuery to get a count of SubClients using the Schedule Policy ID.

    After the query I run a script which simply appends the HTML row to the "EmailBody" workflow variable with.

    workflow.setVariable("EmailBody",xpath:{/workflow/variables/EmailBody}
    +"<tr><td>"
    +xpath:{/workflow/ForEach_1/row/col[@name="SPName"]}
    +"</td><td>"
    +xpath:{/workflow/CommServDBQuery_2/resultSets/row/col[@name="Count"]}
    +"</td></tr>");

    And finally I send an email at the end, although I have an OnStart script there to append the "</table>" text.

    workflow.setVariable("EmailBody",xpath:{/workflow/variables/EmailBody}+"</table>");

    That should be all you need to do to get a nicely formatted HTML body out of an SQL query.

    It's somewhat convoluted for such a simple query but it gives me a foundation for one of my automation tasks which will be to automatically select a Schedule Policy for new clients based on the number of associated SubClients.

    Hope that helps!

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