Forefont Endpoint Protection Query Details

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] = 

Replacing with the “CollectionID” of the collection you are interested in.

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.

Update:

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)

2 thoughts to “Forefont Endpoint Protection Query Details”

  1. Much Appreciated ..
    Specially I fall into your last category of people :p

    I still can’t see a reason why the locked FEP queries, Thank God we have you anyway ;)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This blog is kept spam free by WP-SpamFree.