cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

WD_Approval Process table length of time at each level

We have new software requests as general catalog items in App Portal where a user can request new software or upgrades to be packaged.  The requests can have up to 6 levels of approval based on the type of request. I have reports that join the approval process table to the request view and pull out the current approval level, position and group index and we can see how long a request took from submission to final approval but there's a bottleneck of backlog and the business is asking what level(s) the backlog is occurring.  

Wondering if anyone has written any queries or views for a specific package id/request id to see how long the request was at each level approval, ie; submit date to approval at level 1, level 1 to level 2 length to approve etc?

(1) Solution

The attached SQL will create 6 views that can be used as App Portal "Legacy" Reports.  Be sure to change the "USE [AppPortal]" statement to reflect the name of your App Broker database if using something other than "AppPortal".

v_rep_PackageApprovalMapping simply shows you what approval workflows are attached to which catalog items.

v_rep_ApprovalProcessingTimes shows you how long the entire approval process took to complete for any completed approval workflows.  This is shown in days, where 0 means it was approved the same day the request was received.  Weekends are excluded from the calculation but holidays are not.  This is intended to be used for calculating business day SLA attainment.

v_rep_RequestApprovals shows raw approval data for each approval tied to each request.  This is likely what you're most interested in, since you can see how long each approval step took within a request.

v_rep_RequestApprovalsSummary_Completed shows stats for individual approvers for completed approvals.  The WHERE clause limits this summary to the approvals that were started during the previous calendar month.  So, if you run the report on March 9, 2023, it would show you the summary for February 2023.  Again, calculations exclude weekends, but not holidays.  The summary provides average and total processing times for each approver.

v_rep_RequestApprovalsSummary_Pending shows a count of how many approvals are pending for an individual approver.  Just like the "completed" summary, the WHERE clause limits this summary to the previous calendar month.

v_rep_RequestApprovalsSummary is a roll-up of both the "completed" and "pending" summary views into a single report.

For these reports, I've used the WD_User.UniqueName field for the Approver column.  If you prefer to use DisplayName or UserName or some other value from the WD_User table, you can update v_rep_RequestApprovals to use whatever you like for the "Approver" column.  Also note that I'm using the WD_Profile table for the ADGUID mapping/lookup, so if your WD_Profile table does not have a comprehensive list of users/GUIDs, you may need to consider something like Charlie's custom user sync and changing v_rep_RequestApprovals to JOIN WD_ApprovalProcess.ADGUID directly to WD_User.ADGUID instead of going through the WD_Profile table.

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

View solution in original post

(3) Replies

I vaguely recall doing something like that for a customer a few years back.  When I get a chance later, I'll take a look and see if I can find it.

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

The attached SQL will create 6 views that can be used as App Portal "Legacy" Reports.  Be sure to change the "USE [AppPortal]" statement to reflect the name of your App Broker database if using something other than "AppPortal".

v_rep_PackageApprovalMapping simply shows you what approval workflows are attached to which catalog items.

v_rep_ApprovalProcessingTimes shows you how long the entire approval process took to complete for any completed approval workflows.  This is shown in days, where 0 means it was approved the same day the request was received.  Weekends are excluded from the calculation but holidays are not.  This is intended to be used for calculating business day SLA attainment.

v_rep_RequestApprovals shows raw approval data for each approval tied to each request.  This is likely what you're most interested in, since you can see how long each approval step took within a request.

v_rep_RequestApprovalsSummary_Completed shows stats for individual approvers for completed approvals.  The WHERE clause limits this summary to the approvals that were started during the previous calendar month.  So, if you run the report on March 9, 2023, it would show you the summary for February 2023.  Again, calculations exclude weekends, but not holidays.  The summary provides average and total processing times for each approver.

v_rep_RequestApprovalsSummary_Pending shows a count of how many approvals are pending for an individual approver.  Just like the "completed" summary, the WHERE clause limits this summary to the previous calendar month.

v_rep_RequestApprovalsSummary is a roll-up of both the "completed" and "pending" summary views into a single report.

For these reports, I've used the WD_User.UniqueName field for the Approver column.  If you prefer to use DisplayName or UserName or some other value from the WD_User table, you can update v_rep_RequestApprovals to use whatever you like for the "Approver" column.  Also note that I'm using the WD_Profile table for the ADGUID mapping/lookup, so if your WD_Profile table does not have a comprehensive list of users/GUIDs, you may need to consider something like Charlie's custom user sync and changing v_rep_RequestApprovals to JOIN WD_ApprovalProcess.ADGUID directly to WD_User.ADGUID instead of going through the WD_Profile table.

Anything expressed here is my own view and not necessarily that of my employer, Flexera. If my reply answers a question you have raised, please click "ACCEPT AS SOLUTION".

Thank you Jim!  This is fantastic!