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
Configmgr 2012 SQL Views http://eskonr.com/2013/10/download-sccm-configmgr-2012-r2-sql-views/
3 Comments
Invalid object name 'v_UserTargetedApps'.
have you enabled the application catalog ? can you try this query to confirm if the view exist or not in your database ? select * from v_UserTargetedApps .if that doesn't exist,something wrong with your database (if you have already enabled the app catalog role and assigned apps to users).
Use your PrimarySite DB in case you have a CentralAdminSite, the view does not exist on the CAS DB 🙁