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:

--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:

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”:

--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: 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:

--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: 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:

--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 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!