SCCM Report Applications packages assigned to specific collection with user experience settings

Recently there was a question asked in the forum about 'How to get list of applications ,packages assigned to specific collection with its deployment settings like user experience ,Deployment action like install or uninstall etc' .

If you are in need of identifying the list of applications or packages deployed to specific collection with its deployment properties, this post is for you.

To find list of applications with deployment type properties ,i looked at the default reports ,there isn't it any with this requirement,so started looking at my SCCM Configmgr SQL views documentation to find out the relevant SQL views that store information about deployment /advertisement properties.

After some search ,found that there are  3 SQL views that store the information about application deployment and they are:

1.v_ApplicationAssignment 2. vAppDeploymentStatus 3.v_advertisement.

so will use these 3 sql views to create custom report.

By using these views ,i will create 2 SQL queries  and later use them in single report 1) Application assigned to specific collection and 2) packages assigned specified collection .Both queries exclude software updates and endpoint protection updates.

How does the report look like:

SQL Code Applications Assigned to specific collection:

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%')

 

SQL Code Packages Assigned specific collection:

 

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

 

Prompt:

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