Troubleshooting Slow Collection Evaluation in SCCM 2012, Part 2 (AKA Rooting out issues with collections)

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, Troubleshooting Slow Collection Evaluation in SCCM 2012 (AKA Laggy Collections), we discussed 3 issues you may see that impact the performance of your collections and how to resolve them.


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:


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


Collection IDRule NameCheck StatusCollection StatusLimitTo Collection StatusExclude Collection NameExclude Collection Status
SCN00113DALOPSPC1IssueNot in CollectionIn CollectionOffice 2016 Pro ExclusionsIn Collection
SCN00113STLITPC03IssueNot in CollectionNot In CollectionNULLNot in Collection
SCN00113JOESLATESTSURFACEPROOKIn CollectionIn CollectionOffice 2016 Pro ExclusionsNot in Collection
SCN00114WHOSEPCISTHISANYWAYOKIn CollectionIn CollectionNULLNot 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.
  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”:


The results from the query will be similar to this:


SCN00113Office 2016 Pro Targets_Self0NULL
SCN00152Office 2016 Pro ExclusionsExclude0NULL
SCN00034All Dallas DevicesInclude0NULL
SCN00015All Desktop DevicesLimit To1NULL
SMS00001All SystemsLimit To2STLITPC03


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:


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




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:



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


SCN00243SCN00243Pilot Updates_Self0SCN002432016-02-05 01:51:00.000
SCN00243SCN00241Windows 7 PcsLimit To1SCN002412016-02-04 11:00:00.000
SCN00243SMS0001All SystemsLimit To2SMS000012016-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, Troubleshooting Slow Collection Evaluation in SCCM 2012 (AKA When Collections Loiter), I provide some additional SQL queries to tackle Issue #3: Collections are stuck in the process of updating for hours before finally resolving. We also take a look at how to see if you have collections updating at peak times, instead of spread evenly across your SCCM site.


If you think you need additional help with this or any of your SCCM issues, I’m happy to talk with you directly. Contact Model to set up some time to chat, and check out our consulting services to see how we can help you!

By |2017-07-15T07:45:16+00:00February 4th, 2016|SCCM, Uncategorized|0 Comments

About the Author:

steve bowman

Model Technology

Let us help you get your end point and data center strategy on cruise control!  Ask about our Calibration Assessment.


  • 12125 Woodcrest Executive Drive, Ste. 204 Creve Coeur, MO 63141
  • (314) 254-4138