SCCM 2012 R2 – Incremental Update Collections and how to find them.

The Problem: 

There is something about SCCM changing from SMS to SCCM that makes people think the old maxim – “Slow Moving Software” no longer applies. Combine this with the desire for “I want it now” and you end up with a bunch of device collections where the “Incremental Updates” feature is turned on.

“But Problem Resolution guy surely this is a new feature offered by Microsoft and they want us to use it, what harm could it possibly cause?”

What harm you ask? Well, it depends on your environment and if you’ve adhered to Microsoft best practices. Dependent on the number of collections with incremental updates enabled you could see anything from performance hits to collections that just won’t update at all.

We aren’t going to focus on the how or why collection evaluation works. We can save that topic for another day. Instead, we’re going to figure out how to identify all of the incremental collections in your environment so you can evaluate if they are needed.

The Solution:

Let me start by stating, “There is NO native way from the SCCM console to find all collections with incremental updates turned on”. In that case, we have to put on our thinking caps and go straight to the source, SQL. Since we know the information we want has to do with collections why don’t we start with a quick overview of the data available in V_Collection. For the following example I’ve chosen to limit my results to all values in V_Collection for the “All Systems” collection with a query that looks like this:

select * from V_collection where Name = ‘All Systems’

Which returns:

AllSystems

Take note of the columns in the red boxes as we are going to use them in a moment.

Now you may be wondering what exactly does “RefreshType” mean and why does he have it in a box. Simple, because that is the value used to determine the type of collection evaluation performed. SCCM Uses four values for the type of refreshes it has. That’s right FOUR of them, not six as it would appear.

When RefreshType = 1: No collection update evaluation is set either by incremental or by a standard schedule. A collection with this refresh type will ONLY evaluate if it’s been explicitly told to do so, OR if it’s parent collection evaluates.

When RefreshType = 2: A FULL evaluation schedule exists for the client and ONLY a full evaluation schedule exists. This collection will NOT update on an incremental basis nor does it have the “incremental updates” check box enabled. It WILL update if it’s parent collection updates.

When RefreshType = 4: This collection ONLY has incremental updates enabled and does NOT WILL NOT perform a Full Collection Evaluation. This collection WILL update when it’s parent collection updates.

When RefreshType = 6: This collection is configured for BOTH Incremental updates AND Full Collection Evaluation. This collection WILL update when it’s parent collection updates.

With that information let’s take a quick look at what we can accomplish with SQL. We only want to collect “relevant” information. For our example that’s “Name”, “Comment” and “RefreshType”:

Select V_Collection.Name
     , V_Collection.Comment
     , V_Collection.RefreshType
From V_Collection 
Where Name = 'All Systems'

This will give us a sample of what kind of data we will get back:

TinyData.png

A baby piece of data instead of all that other junk we just don’t care about. OK so now we’ve got the query for one specific collection. How about all collections that use incremental updates? With just a little clean up we get:

Select S.Name
     , S.Comment
     , S.RefreshType
From V_Collection as S
Where RefreshType = '4' or RefreshType = '6'

I promise this makes sense. I’m just lazy. In the above-snipped query I got tired of typing V_Collection over and over again and while we are ONLY using one table and we could just type the column I figured why not use some best practice and make a quick short “as” for V_Collection.

This will bring back ALL of our collections that have that refresh type of 4 or 6.

RefreshType.png

Wait you say, what if I want it to look PRETTY. OK fine you twisted my arm. We’ll make it pretty too.

Select S.Name
     , S.Comment
     , CASE S.RefreshType
          WHEN 4 THEN 'Incremental Update ONLY'
          WHEN 6 THEN 'Incremental AND Full Evaluation'
          Else 'Unknown'
      End AS RefreshType 
From V_Collection as S
Where RefreshType = '4' or RefreshType = '6'

There NOW instead of just having that weird Number 6 or Number 4 in the slot down there you can have it tell you what it REALLY means and all you have to do is select all that data and copy it to an Excel spread sheet.

PrettyData.png

So what was the point of all of this again? Now you have a list of all collections within your environment that use the incremental update feature in SCCM. Pretty neat right? Alright off with you, time to go run some SQL queries build a list and then go question everyone and to figure out why they are doing incremental updates!

 

 

 

No, really this is the end of the article. Shoo.

  1. Excatly what I was looking for !
    Thanks

    Like

    Reply

    1. Not a problem. Glad it was helpful.

      Like

      Reply

  2. Wonderful! Thank you. Now, have you ever tried converting the schedule column token to something that can be show in a report too?

    Like

    Reply

    1. Yup, the challenge is that the method to convert it is stored in WMI and not available via a SQL execution method. 😦

      Liked by 1 person

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: