Hello App Broker Board Readers,
I need to pull a list of all requests from the last 6 months.
I see our out of the box reporting tab generates a report for us called “Requests in Last 30 Days” do you know if there is a way to get requests in the last 6 months (or even all requests ever).
I’d need to get at least the Package Title, Date of Request and the User Name of the Target User.
I have access to the database so can run a SQL Query … but am not 100% confident I know the correct tables to use.
Cheers 😊
Mar 03, 2020 04:52 AM
I think that you have a couple of different options... The first would be to simply go to the my requests tab, select a date range, and the "show all requests" checkbox.. This will get you a list of requests which have not been canceled.. If you then go to the advanced tab, you should be able to set any filters that you want to apply, and finally select and export format. This will allow you to export the list of requests into a csv, pdf, excel, or word format for the desired date range.
If this does not suit your needs, then a DB query can be used.. The tables you would likely query would be the WD_PackageRequests table, which contains a listing of all requests which have ever been submitted.. In addition, you would likely join on the WD_Webpackages table, which contains information specific to the catalog items.. There is lots of interesting information in the WD_packageRequests table.
One thing that I wanted to point out is that all of the report queries which are run behind the scenes can be found in a file named C:\Program Files (x86)\Flexera Software\App Portal\Web\ReportDefinition\AdminStudio.Reports.xml. In this case, open the file in a text editor and look for the report name of "Requests in Last 30 Days". You will see that the actual query being run by the report is as follows:
SELECT RequestID,PackageTitle,[Datetime],UserName,OrderNo FROM dbo.WD_PackageRequests AS PR left outer join WD_WebPackages WP
on PR.PackageID_FK = WP.PackageID WHERE [DateTime] > GetDate() - 29 Order By PackageTitle
In theory, you could actually change the query so that the date range is 180 days, or you can simply run the query against your App Portal DB. Since App Portal does not really document it's reporting mechanism, use caution if you attempt to modify this file.. At the very least, be sure to create a backup of the original.. If nothing else, looking over the queries in this file is a great way to get you started with writing a custom query.
Mar 03, 2020 07:57 AM
I think that you have a couple of different options... The first would be to simply go to the my requests tab, select a date range, and the "show all requests" checkbox.. This will get you a list of requests which have not been canceled.. If you then go to the advanced tab, you should be able to set any filters that you want to apply, and finally select and export format. This will allow you to export the list of requests into a csv, pdf, excel, or word format for the desired date range.
If this does not suit your needs, then a DB query can be used.. The tables you would likely query would be the WD_PackageRequests table, which contains a listing of all requests which have ever been submitted.. In addition, you would likely join on the WD_Webpackages table, which contains information specific to the catalog items.. There is lots of interesting information in the WD_packageRequests table.
One thing that I wanted to point out is that all of the report queries which are run behind the scenes can be found in a file named C:\Program Files (x86)\Flexera Software\App Portal\Web\ReportDefinition\AdminStudio.Reports.xml. In this case, open the file in a text editor and look for the report name of "Requests in Last 30 Days". You will see that the actual query being run by the report is as follows:
SELECT RequestID,PackageTitle,[Datetime],UserName,OrderNo FROM dbo.WD_PackageRequests AS PR left outer join WD_WebPackages WP
on PR.PackageID_FK = WP.PackageID WHERE [DateTime] > GetDate() - 29 Order By PackageTitle
In theory, you could actually change the query so that the date range is 180 days, or you can simply run the query against your App Portal DB. Since App Portal does not really document it's reporting mechanism, use caution if you attempt to modify this file.. At the very least, be sure to create a backup of the original.. If nothing else, looking over the queries in this file is a great way to get you started with writing a custom query.
Mar 03, 2020 07:57 AM
Thanks a million!
The SQL Query worked for me. I just copied it, edited it and ran it against the AppPortal DB and it gave me the report i need, perfect!
Thanks for the extra info too - all useful to know.
Mar 03, 2020 04:41 PM