was successfully added to your cart.

Getting Software Updates right in ConfigMgr can be tough. There’s a lot to consider when building your patching cycle. In this post, we are going to focus on playing catchup. This can be a daunting task as you look at compliance reports. The flaw in these reports is that they don’t give you totals – many far out of date is my most out of date machine. I’m going to give you some SQL queries that you can run to determine this number, then provide some detail as to how I catch machines up.

Export SQL Results to CSV

First, let’s go over how to export your SQL results into a CSV. Doing this will allow you to open them in Excel, import them into PowerShell, etc. After the query executes and you have results, if you click the “Select All” button between the columns and rows it will select all of the results:

Capture

After that, if you right-click in the same spot, you will have the option to “Save Results As”. From here, you can save as a CSV.

Capture

Determine Missing Updates Per Computer

There’s a few views that deal with updates in the ConfigMgr database. We’re going to be using two of them here. Here’s a query that will list all machines and all missing updates:

 

select comp.Name0,upInfo.ArticleID,upInfo.BulletinID,upInfo.Title

from v_R_System comp,v_Update_ComplianceStatus stat,v_UpdateInfo upinfo

where comp.ResourceID = stat.ResourceID AND stat.CI_ID = upInfo.CI_ID AND stat.Status = 3 AND upInfo.ArticleID != ‘NULL’

ORDER BY comp.Name0

 

In my environment, this query generated just over 800k rows. Here’s what the output looks like:

Capture

In this example, Machine01 was missing 142 updates, while Machine02’s missing updates started at row 147. This query is not really helpful in determining the most out of date machine, because there’s just too much data. I provided this query because maybe you can find some use for it. You can export it to Excel using the instructions above.

Determining Your Most Out-of-Compliance Computer

Here is a better query for determining your most out of date computer:

 

select comp.Name0, count(comp.Name0) AS NumberOfMissingUpdates

from v_R_System comp,v_Update_ComplianceStatus stat,v_UpdateInfo upinfo

where comp.ResourceID = stat.ResourceID AND stat.CI_ID = upInfo.CI_ID AND stat.Status = 3 AND upInfo.ArticleID != ‘NULL’

GROUP BY comp.Name0

ORDER BY NumberOfMissingUpdates DESC

 

This will count the number of missing updates and display it next to the computer name:

Capture

I have some really old, stale objects in my environment, which is why the machine missing the most is missing 431.

Playing Catchup

Now that we know which machines are the most out of date, we can begin to breakup our Software Update group. I generally recommend that you try not to deploy more than about 30 updates to one machine at a time. That means that no machine should be installing more then 30, not that your Software Update group only needs to be 30 updates. If you get a machine installing more than about 30 updates, it can dramatically increase the time that it takes for those updates to run. If your OK with that, then go for it. I also get concerned about that many updates killing a machine.

 

Disclaimer

All content provided on this blog is for information purposes only. Windows Management Experts, Inc makes no representation as to accuracy or completeness of any information on this site. Windows Management Experts, Inc will not be liable for any errors or omission in this information nor for the availability of this information. It is highly recommended that you consult one of our technical consultants, should you need any further assistant.


Subscribe to our mailing list

* indicates required