Configmgr 2012 query how to get list of applications that requires approval

This is quick post on how to get list of Applications that are deployed to users requires approval . more Details ,refer https://social.technet.microsoft.com/Forums/en-US/644ff540-4eb0-49b6-97f4-094cada74c6a/viewing-deployments-that-require-approval?forum=configmanagerapps

Run the following SQL query to know apps that require Approval.

select app.displayname [Application Name],app.manufacturer [Manufacturer],app.numberofdeviceswithapp [No of Devices with this App],app.numberofuserswithrequest [No of Requests] from fn_ListApplicationCIs(1033) app,
v_UserTargetedApps uta
where uta.ci_id=app.ci_id
and uta.requireapproval=1
order by app.displayname

fn_ListApplicationCIs(1033) contains other important information ,so you can add all of them here if you need. To know what info ,fn_ListApplicationCIs(1033) has ,just run select top 5 * From fn_ListApplicationCIs(1033) ,gives you the first 5 rows .

To know further details about,who requested what or applications requested by whom and the computer name,use the following SQL Query:

Select
VRS.Netbios_Name0 AS [Requested Computer Name],
U.Full_User_Name0 AS [Requested User],
max(UR.CreationTime) AS [Requested Date],
Apps.DisplayName
From UserAppModelSoftwareRequest UR
Left Join UserMachineRelation UMR on UMR.RelationshipResourceID = UR.RelationshipResourceID
Left Join v_R_User U on U.Unique_User_Name0 = UMR.UniqueUserName
Left Join v_R_System VRS on VRS.ResourceID = UMR.MachineResourceID
Left Join fn_ListApplicationCIs(1033) Apps on Apps.ModelName = UR.AppID
group by VRS.Netbios_Name0 ,U.Full_User_Name0,Apps.DisplayName

Reference: http://myitforum.com/myitforumwp/2013/01/11/query-configmgr-database-for-applications-that-require-admin-approval/

Configmgr 2012 SQL Views http://eskonr.com/2013/10/download-sccm-configmgr-2012-r2-sql-views/