List of recent Approvers

[NicOla]

I’m looking to get a list of our App Portal active approvers – these would be approvers who approved a request within the last 6 months.

Can anyone point me to the correct tables I need to be looking at so I can run a SQL query to get what I need.

Any SQL to do this would also be nice – maybe someone else needed to do this (or something like this) already.

Cheers.

[CharlesW · Accepted solution]

the following is a very rudimentary query that you could use to find out the names of those approvers who have approved a request in the last 6 months.. Approver information is stored in the DB as AD GUID's,, so this is usually not that helpful.. As such, I've joined the GUID's on the WD_Profile table, as this maps AD GUID's to unique user names, assuming that the user has accessed the site at least one time... 

select distinct ap.ADGUID, p.UniqueUserName as 'Active Approver Name' from WD_ApprovalProcess ap
inner join WD_Profile p on ap.ADGUID = p.ADGUID
where processed = 1 and DATEDIFF(MONTH,ap.DateProcessed,GETDATE()) < 6

I may have suggested this before to you, but take a look at the file "C:\Program Files (x86)\Flexera Software\App Portal\Web\ReportDefinition\AdminStudio.Reports.xml".. This file contains the report definitions for all of the reports under the reporting tab in App Portal... You can actually view the queries for each of the out-of-the-box reports. This often times will help to get you started in the right direction if you are creating your own queries. 

[NicOla]

Thanks Charles, that's great. You had previously mentioned the C:\Program Files (x86)\Flexera Software\App Portal\Web\ReportDefinition\AdminStudio.Reports.xml - I did forget, I've added it to my doco.

Cheers.