Workflow email using SQL query output

Last post 01-15-2014, 12:52 PM by phokee. 2 replies.
Sort Posts: Previous Next
  • Workflow email using SQL query output
    Posted: 12-10-2013, 2:33 PM

    Hi,

    We have a DLO environment.  I've created an SQL query that exports clientname, clientowner and date of last backup for all clients that have not run a backup in 90 days.  This stored procedure is then piped into a workflow to send an email to the clientowner of each workstation.

    It's basically working, but it will only send out one email to whomever is in the row my xpath points to. I don't know how to make it loop through the SQL output.  "ForEach" looks promissing, but I'm not doing something correctly.

    For now I can send an email to a client owner in a specific row.  I can put a clientname in the email, but either have to point to a specific row or include all clientnames associated with a clientowner in the list.

    I'd like it to send an email to the clientowner including the clientname in each row, but I don't know how to do that with the workflow.  Below is my SQL query if that helps.  This is the SQL script before I create a stored procedure out of it.  I limited the output to only machines Phil and Jake are client owners of until I get this working 100%.

    USE CommServ;
    WITH CTE_OwnersByRow_Number
     AS
     (
          SELECT
             ROW_NUMBER() OVER (PARTITION BY o.ClientName ORDER BY o.UserName) AS "RowIndex", o.ClientName, o.UserName
          FROM
             CommcellClientsForOwner o
          WHERE
             o.UserName NOT IN ('Phil','Jake')
      )
    SELECT j.Client, MAX(j.EndTime)AS "Last Backup", o.UserName
    FROM dbo.CompletedBackupJobs(GETDATE() - 210, GETDATE(), 0, 0)j JOIN CTE_OwnersByRow_Number AS o
    ON o.ClientName = j.Client
    WHERE
        RowIndex = 1 AND (GETDATE()- j.EndTime > 90)
    GROUP BY
           j.Client, o.UserName

  • Re: Workflow email using SQL query output
    Posted: 12-12-2013, 9:47 AM

    Support helped me get this working.  I didn't understand how to use the "ForEach" control.  I was also using SQLQuery instead of "CommServeDBQuery".  I didn't have an Input defined in my "ForEach".  We put this value in for the Input so an email would go out to each clientowner in the list:

       xpath:{/workflow/CommServDBQuery_1/resultSets/row}

     

    Then, in the email, I was able to define which column of each row to use for

    To:   xpath:{/workflow/ForEach_2/row/col[3]}            

    Subject:    xpath:{/workflow/ForEach_2/row/col[1]} Needs a backup

     

    Body:  Your workstation, xpath:{/workflow/ForEach_2/row/col[1]}, has not backed up since xpath:{/workflow/ForEach_2/row/col[1]}.  Please run a backup at your earliest convenience.

  • Re: Workflow email using SQL query output
    Posted: 01-15-2014, 12:52 PM

    After the new year, I discovered the SQL query above was flawed and gave erratic results.  The query below works much better...

    --This query pulls the most recent backup date
    --Uses functions: CommcellClientsForOwner , CompletedBackupJobs
    --Inserts into #tempLastBackup table to output records with last backup date from 3 months ago

    USE CommServ;
    WITH CTE_OwnersByRow_Number
     AS
     (
          SELECT
             ROW_NUMBER() OVER (PARTITION BY o.ClientName ORDER BY o.UserName) AS "RowIndex", o.ClientName, o.UserName
          FROM
             CommcellClientsForOwner o
          WHERE
    --        o.UserName NOT IN 
              o.UserName IN ('phil','jake')
      )
    SELECT j.Client, MAX(CONVERT(datetime,(j.EndTime))) AS LastBackup, o.UserName
    INTO #temp_LastBackup
    FROM dbo.CompletedBackupJobs(GETDATE() - 365, GETDATE(), 0, 0)j JOIN CTE_OwnersByRow_Number AS o
    ON o.ClientName = j.Client
    WHERE
     RowIndex = 1 AND
     (
     (GETDATE() - (SELECT MAX(CONVERT(datetime,(j.EndTime)))
     FROM dbo.CompletedBackupJobs(GETDATE() - 365, GETDATE(), 0, 0)j JOIN CTE_OwnersByRow_Number AS o
     ON o.ClientName = j.Client) < 90)
     )
    GROUP BY
        j.Client, o.UserName

      
    select Client, convert(varchar(10),cast(LastBackup as datetime),101), Username
    from #temp_LastBackup(NOLOCK)
    where LastBackup < DATEADD(month, -3, GetDate()) -- output records with last backup from 3 months ago

    Drop table #temp_LastBackup

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