prashantmirya
Level 4

SQL queries

Jump to solution

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

0 Kudos
1 Solution

I do think the SLA Dashboard gives you the first part of your requirements.  Here's a sample dashboard with data...

SLA Dashboard.jpg

 

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.

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

7 Replies
CharlesW
Flexera
Flexera

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.

0 Kudos

thanks for your reply, i need raw data and need to use joins along with the table shared, thank you

0 Kudos
jdempsey
Moderator Moderator
Moderator

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.

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".
0 Kudos

thank you, that wont fulfil my requirement

0 Kudos

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.

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".

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

0 Kudos

I do think the SLA Dashboard gives you the first part of your requirements.  Here's a sample dashboard with data...

SLA Dashboard.jpg

 

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.

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".