Need SQL schema details to pull reports of all orders and their status of installation, target machines , who submitted requests etc.
this will help us to analyze how many are failed on how long it took to complete an installation etc
‎Oct 27, 2021 10:42 AM
I do think the SLA Dashboard gives you the first part of your requirements. Here's a sample dashboard with data...
And then if you click one of the tiles (e.g. Failed deployments), you get the details you're looking for...
Request ID | Request | Request submitted date | User name | Target |
86641 | Microsoft Project Standard 2013 v2 (Install) | 5/6/2021 3:03:50 AM |
Adalhard.Schulte |
JKLLNLJ6J0WT2 |
86652 | 7-Zip 19.00 (Install) | 5/6/2021 3:28:44 AM |
Horatius.Shelby |
ZVZJS0LT62082 |
The dashboard can be exported as PDF, and the detail page can be exported as either PDF or Excel.
For the second part of your requirements, you'll want to create a couple of custom reporting views in the database using these queries...
v_rep_ApprovalProcessingTimes
SELECT pr.[RequestID],
pr.[DateTime] AS [OrderDate],
ap.[DateProcessed] AS [ApprovalDate],
DATEDIFF(DAY, pr.[DateTime], ap.[DateProcessed] - 2 * DATEDIFF(WEEK, pr.[DateTime], ap.[DateProcessed])) AS [ApprovalDays]
FROM WD_PackageRequests pr OUTER APPLY
(SELECT MAX( [DateProcessed]) AS [DateProcessed]
FROM WD_ApprovalProcess
WHERE RequestID_FK = pr.RequestID) ap
WHERE pr.Authorized IN (1,2)
v_rep_RequestStatus
SELECT [order].OrderName AS [Order],
pr.RequestID,
pr.PackageTitle,
CASE WHEN [RequestType] = 1 THEN 'Uninstall'
ELSE 'Install'
END AS RequestType,
CASE WHEN pr.Cancelled = 1 THEN 'Canceled'
WHEN Authorized = 0 THEN 'Pending Approval'
WHEN Authorized = 2 THEN 'Rejected'
WHEN LastStateID = -50 THEN 'In Progress'
WHEN LastStateID = 1 THEN 'Completed'
WHEN LastStateID = 5 THEN 'Failed'
ELSE CONVERT(nvarchar, LastStateID)
END AS Status,
pr.DateTime AS OrderDate,
CASE WHEN LastStateID = 1 THEN [LastStateTime]
WHEN Authorized = 2 THEN ap.ApprovalDate
ELSE NULL
END AS Completed,
CASE WHEN pr.Cancelled = 1 THEN NULL
WHEN LastStateID = 1 THEN DATEDIFF(DAY, [DateTime], [LastStateTime] - 2 * DATEDIFF(WEEK, [DateTime], [LastStateTime]))
WHEN Authorized = 2 THEN ap.ApprovalDays
ELSE DATEDIFF(DAY, [DateTime], GETUTCDATE() - 2 * DATEDIFF(WEEK, [DateTime], GETUTCDATE()))
END AS [Days],
LTRIM(ISNULL(requester.DisplayName, N'') + ' (' + ISNULL(requester.UserName, pr.UniqueRequesterName) + ')') AS [Requested By],
LTRIM(ISNULL(requestedfor.DisplayName, N'') + ' (' + ISNULL(requestedfor.UserName, pr.UniqueUserName) + ')') AS [Requested For],
pr.MachineName AS [Target Device]
FROM dbo.vMyRequestsCatalogUpdated AS pr INNER JOIN
dbo.WD_PackageRequestsOrderDetails AS [order] ON pr.OrderNo = [order].OrderNo LEFT OUTER JOIN
dbo.v_rep_ApprovalProcessingTimes AS ap ON pr.RequestID = ap.RequestID LEFT OUTER JOIN
dbo.WD_User AS requester ON pr.UniqueRequesterName = requester.UniqueName LEFT OUTER JOIN
dbo.WD_User AS requestedfor ON pr.UniqueUserName = requestedfor.UniqueName
WHERE pr.DateTime > DATEADD(MONTH, -6, GETUTCDATE())
You can then add the v_rep_RequestStatus view as a legacy report as described in the product documentation here.
Note: The v_rep_RequestStatus view is dependent on the v_rep_ApprovalProcessingTimes view, so you'll need to create both even if you only intend to use the v_rep_RequestStatus view as a report. Feel free to customize the v_rep_RequestStatus view to your liking/needs, but this should adequately demonstrate how you can leverage the existing view/tables for the data you're looking for. As you can see from the WHERE clause, the view is currently filtered to only requests submitted in the most recent 6 months from when the report is run. The [Days] column shows how long a request took to complete or how long the request has been waiting if still in progress. I included canceled requests, but you could just as easily filter those out using the WHERE clause if you don't want them in your report.
‎Nov 06, 2021 01:48 PM - edited ‎Nov 06, 2021 01:55 PM
App Broker does not have a published schema. Most of what you are looking for should be in the WD_PackageRequests table. Also, I might suggest that you look in the file "C:\Program Files (x86)\Flexera Software\App Portal\Web\ReportDefinition\AdminStudio.Reports.xml". This will show you the queries used by App Brokers out-of-the-box reports. Might help to guide you in the right direction.
‎Oct 27, 2021 10:59 AM
thanks for your reply, i need raw data and need to use joins along with the table shared, thank you
‎Oct 27, 2021 11:31 AM
Have you looked at the SLA Dashboard? This will show you percentage and count of successful, failed, and in progress requests over the time period specified by the date choosers. If you click on any of the tiles, it will then display the list of requests in that category so you can see the details.
‎Oct 27, 2021 11:23 AM
thank you, that wont fulfil my requirement
‎Oct 27, 2021 11:31 AM
Can you provide specifics of your requirements (perhaps even a mockup of what you'd like the report to contain)? What requirements aren't met by the dashboard? If you can provide specifics, it's more likely someone will be able to provide what you need. I've done some fairly detailed custom reports for other customers.
‎Oct 27, 2021 11:37 AM
Hello, thank you as mentioned above.
I am looking for all orders placed in past 6 months or an year and out of them how many are failed. including requestID, hostname, application name , requested user name etc
and also i am looking to get raw data of requests which are running for >2business working days or greater
‎Oct 28, 2021 04:45 AM
I do think the SLA Dashboard gives you the first part of your requirements. Here's a sample dashboard with data...
And then if you click one of the tiles (e.g. Failed deployments), you get the details you're looking for...
Request ID | Request | Request submitted date | User name | Target |
86641 | Microsoft Project Standard 2013 v2 (Install) | 5/6/2021 3:03:50 AM |
Adalhard.Schulte |
JKLLNLJ6J0WT2 |
86652 | 7-Zip 19.00 (Install) | 5/6/2021 3:28:44 AM |
Horatius.Shelby |
ZVZJS0LT62082 |
The dashboard can be exported as PDF, and the detail page can be exported as either PDF or Excel.
For the second part of your requirements, you'll want to create a couple of custom reporting views in the database using these queries...
v_rep_ApprovalProcessingTimes
SELECT pr.[RequestID],
pr.[DateTime] AS [OrderDate],
ap.[DateProcessed] AS [ApprovalDate],
DATEDIFF(DAY, pr.[DateTime], ap.[DateProcessed] - 2 * DATEDIFF(WEEK, pr.[DateTime], ap.[DateProcessed])) AS [ApprovalDays]
FROM WD_PackageRequests pr OUTER APPLY
(SELECT MAX( [DateProcessed]) AS [DateProcessed]
FROM WD_ApprovalProcess
WHERE RequestID_FK = pr.RequestID) ap
WHERE pr.Authorized IN (1,2)
v_rep_RequestStatus
SELECT [order].OrderName AS [Order],
pr.RequestID,
pr.PackageTitle,
CASE WHEN [RequestType] = 1 THEN 'Uninstall'
ELSE 'Install'
END AS RequestType,
CASE WHEN pr.Cancelled = 1 THEN 'Canceled'
WHEN Authorized = 0 THEN 'Pending Approval'
WHEN Authorized = 2 THEN 'Rejected'
WHEN LastStateID = -50 THEN 'In Progress'
WHEN LastStateID = 1 THEN 'Completed'
WHEN LastStateID = 5 THEN 'Failed'
ELSE CONVERT(nvarchar, LastStateID)
END AS Status,
pr.DateTime AS OrderDate,
CASE WHEN LastStateID = 1 THEN [LastStateTime]
WHEN Authorized = 2 THEN ap.ApprovalDate
ELSE NULL
END AS Completed,
CASE WHEN pr.Cancelled = 1 THEN NULL
WHEN LastStateID = 1 THEN DATEDIFF(DAY, [DateTime], [LastStateTime] - 2 * DATEDIFF(WEEK, [DateTime], [LastStateTime]))
WHEN Authorized = 2 THEN ap.ApprovalDays
ELSE DATEDIFF(DAY, [DateTime], GETUTCDATE() - 2 * DATEDIFF(WEEK, [DateTime], GETUTCDATE()))
END AS [Days],
LTRIM(ISNULL(requester.DisplayName, N'') + ' (' + ISNULL(requester.UserName, pr.UniqueRequesterName) + ')') AS [Requested By],
LTRIM(ISNULL(requestedfor.DisplayName, N'') + ' (' + ISNULL(requestedfor.UserName, pr.UniqueUserName) + ')') AS [Requested For],
pr.MachineName AS [Target Device]
FROM dbo.vMyRequestsCatalogUpdated AS pr INNER JOIN
dbo.WD_PackageRequestsOrderDetails AS [order] ON pr.OrderNo = [order].OrderNo LEFT OUTER JOIN
dbo.v_rep_ApprovalProcessingTimes AS ap ON pr.RequestID = ap.RequestID LEFT OUTER JOIN
dbo.WD_User AS requester ON pr.UniqueRequesterName = requester.UniqueName LEFT OUTER JOIN
dbo.WD_User AS requestedfor ON pr.UniqueUserName = requestedfor.UniqueName
WHERE pr.DateTime > DATEADD(MONTH, -6, GETUTCDATE())
You can then add the v_rep_RequestStatus view as a legacy report as described in the product documentation here.
Note: The v_rep_RequestStatus view is dependent on the v_rep_ApprovalProcessingTimes view, so you'll need to create both even if you only intend to use the v_rep_RequestStatus view as a report. Feel free to customize the v_rep_RequestStatus view to your liking/needs, but this should adequately demonstrate how you can leverage the existing view/tables for the data you're looking for. As you can see from the WHERE clause, the view is currently filtered to only requests submitted in the most recent 6 months from when the report is run. The [Days] column shows how long a request took to complete or how long the request has been waiting if still in progress. I included canceled requests, but you could just as easily filter those out using the WHERE clause if you don't want them in your report.
‎Nov 06, 2021 01:48 PM - edited ‎Nov 06, 2021 01:55 PM