Collection Evaluation Viewer (CEViewer) provided in the System Center Configuration Manager R2 Toolkit is commonly used to troubleshoot collection evaluation. The current version (2012 R2, published 6th December 2013) of the tool has a bug on the incremental evaluations pane that causes it to include collections that no longer have incremental evaluation enabled. Basically, there are more collections listed than there should be. If a collection previously performed an incremental evaluation, it will be displayed even though it’s not currently enabled for incremental evaluation. This can make troubleshooting a large number of collections that little bit more difficult. Not impossible, but quite inconvenient. Ain’t nobody got time for that! The bug was logged by Russ Rimmerman and marked as fixed on Connect, however a new tool is yet to be released.

In the meantime, I slightly modified the query posted on the connect site to get  restrict the results to collections currently enabled for incremental evaluation. I’ve joined the data on the v_collection table and filtered the results to a refreshtype of 4 or 6 (which is “incremental” evaluation or “incremental and scheduled” evaluation).


NOTE: While the query below is a simple select statement, you should evaluate it’s usage in a test environment before using it in production. If you’re environment is under heavy load, this may contribute to resource exhaustion.

The modified query is:

SELECT [t2].[CollectionName], [t2].[SiteID] as CollectionID, [t2].[value] AS [Seconds], [t2].[LastIncrementalRefreshTime], [t2].[IncrementalMemberChanges] AS [IncChanges], [t2].[LastMemberChangeTime] AS [MemberChangeTime]

FROM (

SELECT [t0].[CollectionName], [t0].[SiteID], DATEDIFF(Millisecond, [t1].[IncrementalEvaluationStartTime], [t1].[LastIncrementalRefreshTime]) * 0.001 AS [value], [t1].[LastIncrementalRefreshTime], [t1].[IncrementalMemberChanges], [t1].[LastMemberChangeTime], [t1].[IncrementalEvaluationStartTime], v1.[RefreshType]

FROM [dbo].[Collections_G] AS [t0]

INNER JOIN [dbo].[Collections_L] AS [t1] ON [t0].[CollectionID] = [t1].[CollectionID]

inner join v_Collection v1 on [t0].[siteid] = v1.CollectionID

) AS [t2]

WHERE ([t2].[IncrementalEvaluationStartTime] IS NOT NULL) AND ([t2].[LastIncrementalRefreshTime] IS NOT NULL) and (refreshtype='4' or refreshtype='6')

ORDER BY [t2].[value] DESC

To use the query, connect your SQL Management Studio to the site database, and run the query.

Example results:

Incremental Collection Evaluation SQL Results
Incremental Collection Evaluation SQL Results
Remember, Microsoft is listening! Use Connect to log bugs or the Configuration Manager User Voice site to request new features: