TROUBLESHOOTING SLOW COLLECTION EVALUATION IN SCCM 2012

Collections are a critical part of SCCM 2012. They factor into almost everything from deployments to client settings, to… everything! It just so happens that collections are a unique breed of things that are easy to understand but difficult to master.

I’ve had (at least) my fair share of collection issues to troubleshoot in both simple and complex SCCM implementations, so I’ve decided to share with you some of the issues I’ve seen and what you can do about them.

ISSUE 1: CLIENTS WERE ADDED TO A COLLECTION VIA DIRECT MEMBERSHIP, BUT WON’T SHOW UP IN THE COLLECTION.

Cause: This is most likely a case where the limiting collection does not contain “the device” you added as a direct member.

Here is an example:

All Systems Collection (Contains “The Device”) Limited to Nothing

Dallas Devices (does not contain “The Device”) Limited to All Systems

IBM Laptop Devices (has a rule for “The Device”) Limited to Dallas Devices

Fix: In this scenario, the issue is that “The Device” is not part of “Dallas Devices”. The fix is to make sure that “The Device” in all the collections in the Limiting train.

ISSUE 2: IT IS TAKING DAYS FOR A “THE NEW CLIENT” TO FALL INTO “THE COLLECTION”.

Cause: Collection Update times are in a reverse time order in the Limiting train.

Here is an example:

All Systems Collection Updates Incrementally (Client added at 6:00 pm) Limited to Nothing

Dallas Devices Updates daily at 5:00 pm Limited to All Systems

IBM Laptop Devices Updates daily at 4:00 pm Limited to Dallas Devices

In the scenario above, let’s say that a new SCCM client is added to SCCM at 6:00 pm on Monday. It will fall into the “All Systems” collection within 30 minutes, so about 6:30 pm. At 5:00 pm on Tuesday, the client moves into the “Dallas Devices” collection. At 4:00 pm on Wednesday, the client moves into the “IBM Laptop Devices” collection. Why? The update times are get earlier as we move closer to the “IBM Laptop Devices” collection.

Fix: All collections times in the limiting train need to be in Ascending order from All Systems to the “IBM Laptop Devices” Collection.

Here is one example of a simple fix:

All Systems Collection Updates Incrementally (Client Added at 6:00 pm) Limited to Nothing

Dallas Devices Updates daily at 7:00 pm Limited to All Systems

IBM Laptop Devices Updates daily at 8:00 pm Limited to Dallas Devices

In the scenario above, clients will now move from “All Systems” to “IBM Laptop Devices” somewhere in the range of 2 hours if added before 6:00 pm, and as many as 24 hours if added after 8:00 pm.

ISSUE 3: COLLECTIONS ARE STUCK IN THE PROCESS OF UPDATING FOR HOURS BEFORE FINALLY RESOLVING.

Cause: There are too many collections with incremental update cycles. Think of the SCCM collection update process as a line for a ride at Disneyland. When a collection needs an update it gets added to a line called the “Collection Evaluator” where in most cases, they are processed using the first in/first out method. Simple, right? As long as the number of collections added to the line in a period of time is the same or less than the number being processed all is good.

Now, let’s say that a sudden power failure causes most of the other rides in the park to close down. Suddenly, we have a flood of new people lining up to ride because they don’t have anything else to do. Chances are that we develop quite a backlog and the line grows longer and longer.

For Collections, this is usually due to a high number of scheduled updates taking place together. By default, the collection update time is the same as the time it was created. This means that if an administrator typically creates collections every afternoon and he accepts the default time, all the collections update times will be clustered into the afternoon. You will see this same scenario if someone manually requests an update on many collections at once.

The last group that could cause an issue is collections with an incremental evaluation time. These are like people with a Fast-Pass, letting them skip the line to ride. While they are getting to ride, the regular line waits.

In the Collection world, all collections with an Incremental evaluation schedule update every 15 minutes by default. So every 15 minutes, all the normal collection evaluations have to wait.   When there are a large number of Incremental collections, the evaluator is unable to complete the cycle within 15 minutes, so the normal collections just sit there waiting, and waiting.

Fix:  Reduce your number of Incremental collections to less than 200.  If you are still having issues after that, consider increasing the interval between evaluations.  In a large environment I was in with many, many collections (thousands) I’ve seen it set as high as 90 minutes.

That brings us to the end of Part 1.  If you are seeing issues with your collection updates, take a look at your environment and see if you have any of the issues I’ve listed. In part 2, I will go a little deeper, even giving some SQL queries that can be run against the SCCM database to identify these issues, along with a few new ones.

Collections are a critical part of SCCM 2012. They factor into almost everything from deployments to client settings, to… everything! It just so happens that collections are a unique breed of things that are easy to understand but difficult to master.

In part 1 of this series, we discussed 3 issues you may see that impact the performance of your collections and how to resolve them. If you missed part 1, you can read it here.

In part 2, we are going to get a little more complicated. Let’s face it – most SCCM environments have a LOT of collections. Those collections have lots of rules. This means that sometimes issues are very difficult to find until you get a call from a customer asking why “such and such” just happened to “so and so”. To help you avoid this, I’m going to share with you some ways in which you can get an idea of how you can proactively check to see if these issues exist in your environment.

I’m assuming you’ve read part 1, so I won’t spend a lot of time describing the issues from the previous post.   Instead I’ll focus on how you can find them before they (or their effects) find you!

Note: Some of the SQL statements I am providing on this page can be resource intensive depending on the size of your site and the specs on your SQL server. If you would like to run these in your environment, please do so in a test environment first, or at least during a time of low usage.

ISSUE 1: CLIENTS WERE ADDED TO COLLECTIONS VIA DIRECT MEMBERSHIP, BUT WON’T SHOW UP IN THE MEMBER LIST.

While this is most likely due to the limiting collection not containing the device you were wanting to add, it could be much more complicated than that. What if the client is part of an exclusion rule? What if the issue is not the limiting collection, but the limiting collection of the limiting collection, etc..?

The first step is to run the following code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

--Return all collections that are missing a client where the client has been added via direct membership   Select crd.CollectionID, crd.RuleName, (Case when fcm.Name is null then 'Issue' else 'OK' end) as CheckStatus,   (Case when fcm.Name is not null then 'In Collection' else 'Not in Collection' end) as CollectionStatus,   (Case when fcmlc.Name is not null then 'In Collection' else 'Not in Collection' end) as LimitToCollectionStatus,   cle.CollectionName as ExcludeCollectionName,   (Case when fcme.Name is not null then 'In Collection' else 'Not in Collection' end) as ExcludeCollectionStatus   from v_CollectionRuleDirect crd   left join v_FullCollectionMembership fcm on crd.CollectionID = fcm.CollectionID and crd.ResourceID = fcm.ResourceID   left join v_Collections cl on crd.CollectionID = cl.SiteID   Left join v_FullCollectionMembership fcmlc on cl.LimitToCollectionID = fcmlc.CollectionID and crd.REsourceID = fcmlc.ResourceID   Left join vSMS_CollectionDependencies cd on crd.CollectionID = cd.DependentCollectionID and cd.RelationshipType = 3   left join v_Collections cle on cd.SourceCollectionID = cle.SiteID   Left join v_FullCollectionMembership fcme on cd.SourceCollectionID = fcme.CollectionID and crd.REsourceID = fcme.ResourceID

After running the code, you should get results back that are similar to this:

Collection ID	Rule Name	Check Status	Collection Status	LimitTo Collection Status	Exclude Collection Name	Exclude Collection Status
SCN00113	DALOPSPC1	Issue	Not in Collection	In Collection	Office 2016 Pro Exclusions	In Collection
SCN00113	STLITPC03	Issue	Not in Collection	Not In Collection	NULL	Not in Collection
SCN00113	JOESLATESTSURFACEPRO	OK	In Collection	In Collection	Office 2016 Pro Exclusions	Not in Collection
SCN00114	WHOSEPCISTHISANYWAY	OK	In Collection	In Collection	NULL	Not in Collection

The SQL code will look at every collection in your SCCM environment that has a direct membership rule, and return back the following. I want to add disclaimer that you will find some cases where you want the client to stay out of the collection. These results are subject to your judgement.

CollectionID:The Collection ID where the direct membership rule was found

Rule Name:The name of the Direct membership rule (usually the same as the client name)

Check Status:If the client for the rule was not found in the collection, you will see “Issue”. If the client is found, you will see “OK”.

Collection Status:Shows whether or not the client is in the collection for “CollectionID”

Limit To Collection Status:Shows whether or not the client is in the “Limit To” collection for “CollectionID”

Exclude Collection Name:If “Collection ID” contains an Exclude Collection rule, the name of the collection is listed here.

Exclude Collection Status:Shows whether or not the client is in the Excluded collection for “CollectionID”

Let look at the results above and see what we can figure out:

1. For Rule Name “DALOPSPC1” in collection “SCN00113”, there is a potential issue. The client is not in the collection, but is in the limiting collection. In this case the reason the client is missing is because it’s part of the “Office 2016 Pro Exclusions” collection that was excluded.  This is probably an intentional thing.
2. Rules Named “JOELATESTSURFACEPRO” and “WHOSEPCISTHISANYWAY” are both OK.
3. For Rule Name “STLITPC03” in collection “SCN00113”, there is a potential issue. The client is not in the collection, or the limiting collection, nor is it in an exclusion. In this case, we’d want to go down the train of limiting collections to see where the client went missing.  Depending on the depth of your collection structure, this could be painful. Luckily, I have some more code to assist with this!

Let’s run the following code, using the values from the last query where we found an issue with “STLITPC03”:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

--Check a specific collection and client to see if the client is present in all the dependencies   Declare @ClientName as varchar(25)   Declare @TopCollectionID as varchar(8)   Set @ClientName = 'STLITPC03'   Set @TopCollectionID = 'SCN00113'   Select coltree.*, fcm.Name from (   Select c.SiteID as CollectionID, c.CollectionName, '_Self' as Relationship, 0 as Level   from v_Collections c   where c.SiteID = @TopCollectionID   Union   Select SourceCollectionID as CollectionID, c.Name as CollectionName, 'Include' as Relationship, 0 as Level   from vSMS_CollectionDependencies cdc   left join v_Collection c on cdc.SourceCollectionID = c.CollectionID   where cdc.DependentCollectionID = @TopCollectionID   and RelationshipType =2   Union   Select SourceCollectionID as CollectionID, c.Name as CollectionName, 'Exclude' as Relationship, 0 as Level   from vSMS_CollectionDependencies cdc   left join v_Collection c on cdc.SourceCollectionID = c.CollectionID   where cdc.DependentCollectionID = @TopCollectionID   and RelationshipType = 3   Union   Select cdc.SourceCollectionID as CollectionID, Name as CollectionName, 'Limit To' as Relationship, Level from vCollectionDependencyChain cdc   left join v_Collection c on cdc.SourceCollectionID = c.CollectionID   left join vSMS_CollectionDependencies cd on cdc.ImmediateSourceCollectionID = cd.SourceCollectionID and cdc.DependentCollectionID = cd.DependentCollectionID   where cdc.DependentCollectionID = @TopCollectionID   and cd.RelationshipType = 1   )ColTree   left join v_FullCollectionMembership fcm on ColTree.CollectionID = fcm.CollectionID and fcm.Name = @ClientName   order by Level, Relationship

The results from the query will be similar to this:

CollectionID	CollectionName	Relationship	Level	Name
SCN00113	Office 2016 Pro Targets	_Self	0	NULL
SCN00152	Office 2016 Pro Exclusions	Exclude	0	NULL
SCN00034	All Dallas Devices	Include	0	NULL
SCN00015	All Desktop Devices	Limit To	1	NULL
SMS00001	All Systems	Limit To	2	STLITPC03

CollectionID:CollectionID of the collection being checked

CollectionName:Name of the collection being checked

Relationship:Relationship to the collection you specified in the query (“SCN00113”)

Level:Number of degrees of separation (Just like the Kevin Bacon game)

Name:Will show the name of the client if found, otherwise it will show NULL

Looking at the table, the issue becomes obvious:

1. The client is not in the collection we checked, SCN00113.
2. The client is not in SCN00152, which is excluded from SCN00113. Not the issue.
3. The client is not in SCN00034, which is included in SCN00113. Probably not the issue, but could be.
4. The client is not in SCN00015 (All Desktop Devices), which is the limiting collection for SCN00113. Definitely an issue.
5. The client is in SMS0001 (All Systems) which is the limiting collection for SCN00015.
6. This shows us that the client is missing from the SCN00015 collection and must be added there for it to be present in SCN00113.

ISSUE 2: IT IS TAKING DAYS FOR A “THE NEW CLIENT” TO FALL INTO COLLECTIONS

This is where the update times for the collection’s “Limiting train” are in reverse order, causing a delay before the client can move into the collection.

To check for this issue, run the following SQL query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

--Show which collections have a limit train in reverse order   Select TopCollectionID, (Case when ((Sum(Case when Row = Level then 1 else 0 end)) = (Sum( Case when Row is not null then 1 else 0 end))) then 'OK' else 'Issue' end) as Tot from (   Select  (row_Number() Over (Partition by TopCollectionID Order by NextRefreshTime DESC)-1) as Row, * from (   Select c.SiteID as TopCollectionID, c.SiteID as CollectionID, c.CollectionName, '_Self' as Relationship, 0 asLevel   from v_Collections c   where c.SiteID in (Select CollectionID from v_Collection)   Union   Select distinct clf.SiteID as TopCollectionID, cdc.SourceCollectionID as SiteID, c.CollectionName, 'Limit To' as Relationship, cdc.Level   from vSMS_CollectionLimitation_Flat clf   left join v_Collections c on clf.Ancestor = c.SiteID   left join vCollectionDependencyChain cdc on clf.SiteID = cdc.DependentCollectionID and clf.Ancestor = cdc.SourceCollectionID   where clf.SiteID in (Select CollectionID from v_Collection)   and cdc.ImmediateSourceCollectionID in (Select Ancestor from vSMS_CollectionLimitation_Flat where SiteID in (Select CollectionID from v_Collection))   )data   left join (   Select c.SiteID, ccrc.NextRefreshTime from Collection_EvaluationAndCRCData ccrc left join v_Collections c on ccrc.CollectionID = c.CollectionID   ) crt on data.CollectionID = crt.SiteID   ) data2 group by TopCollectionID

After running this query, you should see results similar to the following:

TopCollectionID	Tot
SCN00231	OK
SCN00243	Issue
SCN00151	OK

TopCollectionID:CollectionID at the top of the limiting train.

Tot:If the limiting train is in the correct time order (lowest first and TopCollectionID last) you will see “OK”, otherwise you will see “Issue”

It is important to note, that this will not always indicate an issue. For example, if your train updates between 6pm and 7pm daily, and you run this query at 6:30pm, your results may be inaccurate. The best way to verify is to run this a few times over a day or two and look to see which collections are in a constant Issue state.

If you want to look at the order for a given collection, you can run the following SQL code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

--Listing of a specific collection's limit to update times   Select * from (   Select c.SiteID as TopCollectionID, c.SiteID as CollectionID, c.CollectionName, '_Self' as Relationship, 0 as Level   from v_Collections c   where c.SiteID in (Select CollectionID from v_Collection)   Union   Select distinct clf.SiteID as TopCollectionID, cdc.SourceCollectionID as SiteID, c.CollectionName, 'Limit To' as Relationship, cdc.Level   from vSMS_CollectionLimitation_Flat clf   left join v_Collections c on clf.Ancestor = c.SiteID   left join vCollectionDependencyChain cdc on clf.SiteID = cdc.DependentCollectionID and clf.Ancestor = cdc.SourceCollectionID   where clf.SiteID in (Select CollectionID from v_Collection)   and cdc.ImmediateSourceCollectionID in (Select Ancestor from vSMS_CollectionLimitation_Flat where SiteID in (Select CollectionID from v_Collection))   )data   left join (   Select c.SiteID, ccrc.NextRefreshTime from Collection_EvaluationAndCRCData ccrc left join v_Collections c on ccrc.CollectionID = c.CollectionID   ) crt on data.CollectionID = crt.SiteID where TopCollectionID = 'SCN00243'

After running this query, you should see results similar to the following:

TopCollectionID	CollectionID	CollectionName	Relationship	Level	SiteID	NextRefreshTime
SCN00243	SCN00243	Pilot Updates	_Self	0	SCN00243	2016-02-05 01:51:00.000
SCN00243	SCN00241	Windows 7 Pcs	Limit To	1	SCN00241	2016-02-04 11:00:00.000
SCN00243	SMS0001	All Systems	Limit To	2	SMS00001	2016-02-04 10:00:00.000

TopCollectionID:CollectionID at the top of the limiting train.

CollectionID:CollectionID of the collection being tested.

CollectionName:Name of collection for CollectionID

Relationship:_self means itself, ‘Limit to’ is a part of the limiting chain

Level:Number of tiers below the “Top Collection” (Top collection is set to 0)

SiteID:Duplicate for CollectionID

NextRefreshTime:The next scheduled time for the collection for CollectionID

Let’s take a look at what the issue could be (assuming a daily schedule on both):

We see that “Windows 7 PCs” collection is set to update at 11:00 am on 2/4/2016, but the collection it is feeding will have already updated at 1:51 am that same day. This means that any client added to the Windows 7 PCs collection after 10am on any day, will take over 24 hours to join the Pilot Updates collection. Here’s the walkthrough:

1. New client is added to All Systems at 11:30am on 2/4/2016. All systems is a special built in collection, so it updates quickly.
2. The Pilot updates collection updates at 1:51 AM on 2/5/2016. Since the client is not yet in the “Windows 7 Pcs” collection, we don’t see the new client.
3. The Windows 7 Pcs collection is updated at 11:00 am on 2/5/2016. The client moves into it as planned.
4. The Pilot updates collection updates at 1:51 AM on 2/6/2016. The client moves into it, since it is now part of the “Windows 7 PCs” collection.

In summary, the client is added to SCCM at 11:30am on 2/4/2016 and does not fall into the Pilot Updates collection until 1:51 am on 2/6/2016 more than a day and half later!

This brings us to the end of Part 2. In part 3, I will provide some additional SQL queries to tackle Issue #3: Collections are stuck in the process of updating for hours before finally resolving.

We will also take a look at how to see if you have collections updating at peak times, instead of spread evenly across your SCCM site.

Source Links 1:http://model-technology.com/troubleshooting-slow-collection-evaluation-in-sccm-2012-part-1-aka-laggy-collections/

Source Links 2: http://model-technology.com/troubleshooting-slow-collection-evaluation-in-sccm-2012-part-2-aka-rooting-out-collection-issues/