Configmgr 2012 Report Applications packages assigned to specific collection

A question asked in the forum recently about,way to get list of applications ,packages assigned to specific collection.I looked at the default reports if there is any or similar which can be customized but cannot find.

It is hard to see always what apps/packages assigned to collection using manual method.Here is the SSRS /SQL query to get list of apps assigned.

This contains 2 queries 1) apps assigned and 2) packages assigned both excluding the software updates and endpoint protection updates.

I always use Configmgr 2012 R2 SQL views excel spreadsheet to find the correct view for what you need.For this report,application name is available from v_ApplicationAssignment and package name(legacy) from v_advertisement.



Applications Assigned:

select AA.ApplicationName,
case when NotifyUser=1 then 'Yes' Else 'No' End as 'Notify User',
case when UserUIExperience=1 then 'Yes' Else 'No' End as 'UserUIExperience',
case when AssignmentAction=2 then 'Install' Else 'Uninstall' End as 'Action',
case when ads.DeploymentIntent=2 then 'Avilable' Else 'Required' End as 'Purpose',
AA.CreationTime,AA.LastModificationTime,AA.LastModifiedBy from v_ApplicationAssignment AA,v_Collection coll ,vAppDeploymentStatus ADS
where (AA.CollectionID=coll.CollectionID) and (AA.AssignmentID=ADS.AssignmentID) and
coll.CollectionID=@collection and (AA.AssignmentName not like '%Software Update%' and AA.AssignmentName not like '%Endpoint Protection%')


Package Assigned:


select adv.AdvertisementName [Advertisement Name],adv.packageid [Package ID],
adv.ProgramName,adv.ExpirationTime,case when AssignedScheduleEnabled=0 then 'Available' Else 'Required' End as 'Purpose'
from v_Advertisement adv,v_Collection coll
where adv.CollectionID=coll.CollectionID
and coll.CollectionID =@collection



select CollectionID,name from v_Collection
order by name

Download the RDL file from Technet Gallary:

Some information about Notify User and UserUIExpeirnce from Phil .

Application Deployments which are marked as Available will have two options:

Display in Software Center and show all notifications
Display in Software Center, and only show notifications for computer restarts

Deployments marked as Required also have a third option to “Hide in Software Center and all notifications”

The NotifyUser value determines whether the user sees notifications for new applications in the system tray, and the UserUIExperience determines whether the user sees anything at all related to the application.  Thus an available deployment set to show all notifications would have a value of 1 for both, an Available or Required deployment set to only show notifications for reboots would have NotifyUser set to 0 and UserUIExperience set to 1, and a Required deployment set to hide everything would have both set to 0

Leave a Reply