Those of us that installed FEP 2010 are no doubt annoyed that we cannot see the details of the queries used by SCCM to populate the FEP created collections.
The following will require acess to the SQL instance that SCCM uses as the querys are to be executed driectly on that instance.
First of all we want a list of all the collections and their IDs:
SELECT [CollectionName] ,[CollectionID] ,[SiteID] FROM [Collections] WHERE [Flags] = 258 ORDER BY [CollectionName]
Once you have the CollectionID of the Collection you are interested in
Run the following:
SELECT [CollectionID] ,[QueryKey] ,[WQL] ,[SQL] FROM [Collection_Rules_SQL] where [CollectionID] =
Note: If you are looking at a sub-collection such as “Deployed Desktops” under “Deployment Succeeded” then you will see a reference to “_RES_COLL_CP1000XX” as an inner join, CP1000XX is the [SiteID] returned in the 1st query of the subset of results to run the subcollections rules against, in the instance of this example we really want to see the queries as applied to the parent as the child is looking for workstations on in the parents list. So we would refer to our 1st query copy the “CollectionID” of the parent using the “SiteID” to find it and then place the new CollectionID into the 2nd query.
Or for those of you that are more lazy. :D
SELECT [CollectionName] ,[Collections].[CollectionID] ,[SiteID] ,[QueryKey] ,[WQL] ,[SQL] FROM [Collection_Rules_SQL] JOIN [Collections] ON [Collection_Rules_SQL].CollectionID = [Collections].CollectionID WHERE [Collection_Rules_SQL].[CollectionID] IN ( SELECT [CollectionID] FROM [Collections] WHERE [Flags] = 258)