Posted by Eswar Koneti on September 18th, 2012
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.
join dbo.v_FullCollectionMembership FCM on R.ResourceID = a.ResourceID
join dbo.v_Collection COLL on COLL.CollectionID = FCM.CollectionID
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/