SCCM report User member of What collections For Application Deployment

If you in environment where in the deployment of Application is User Based and you are sometimes getting issues if User doesn’t get apps so you will have to check if User member of AD Security groups and then check if user added to right collection with right advertisement.

Here is Simple report that give you User member of What collections .Based on this you can get to know if user has right apps.

select
COLL.CollectionId,
FCM.Name
from
dbo.v_R_User U
join dbo.v_FullCollectionMembership FCM on R.ResourceID = a.ResourceID
join dbo.v_Collection COLL on COLL.CollectionID = FCM.CollectionID
Where
U.User_Name0 =@UserName

Prompt for UserName : select User_Name0 from v_R_User

This UserName(login Account or alias Account) you can get GAL.

For Sub collections and Path to identify where the collection Resides,check this out http://blogs.catapultsystems.com/mdowst/archive/2012/02/23/finding-sccm-sub-collections.aspx and http://eskonr.com/2009/07/sccm-report-computers-member-of-which-collection/