Select Page

Get configuration manager update size before deploying

Get configuration manager update size before deploying
Advertisement

Configuration manager update size

This blog post will help you query your configuration manager update size, so that you have an idea about the network bandwidth consequences when deploying your updates.

By now you might be wondering, what is so special about this? You might think that you can go to your configuration manager console, and just highlight the details of one of the Windows updates and see the size of its package. Let us do this together and see what we will see.

I am opening the configuration manager console and browsing to Software Library > Software Updates > All Software Updates. What you see is a list of updates, but without information about the size of each update. You must double click each update, and go to the Content Information tab, and then see the size. Sometimes, an update might have many items in in the content information tab, and each item has size information.

configuration manager update size

 

If you have a Software Update Group that contains your updates that you want to deploy, you have no idea what the total size of updates in that group is. You need either to:

  • Download updates in your Software Update Group, and then go to the folder where updates are downloaded and check the size of that folder. That might work, but it is time consuming. What if you figured out that the size is too big, would go then and create a new Software Update Group, and pick fewer updates?
  • Double click each update inside your software update group, and check the Content Information tab, and start adding the size of each update.

A better way I believe is whenever I want to deploy software updates, I would start first by querying software updates in the previous month, and then pick those that I see fit and add them to a software update group for further deploy. What I want to see is the size of each update in that list.

The SQL query that solves this

I built an SQL query, that will get all software updates filtered by a time range (so you can say, give me all software updates between 1st of April and 1st of May), and this query will display the size of each update.

This becomes handy as the networking team will not allow me to deploy updates that are more than 3 GB in size that are targeting to certain offices with low internet bandwidth. BranchCache would be an option here, but this is a different conversation. So, now I get the overall view of all software updates, their severity, their size, and other information.

How updates are represented inside the database?

I almost gave up trying to go through all these tables inside the configuration manager database and trying to make sense of the JOINT statements.

I guess I understand how updates are represented inside the configuration manager database. It is important to learn that there is a concept of Software Bundle, and inside it, you will find one or more update files. Think of software bundles as a logical unit to group multiple update files.

What you see in the configuration manager console under software update, is the list of software bundle. If you double clicked any of those bundles, you can see the actual files under the content information tab. There might be one of more files there.

To build the SQL query, I must:

  • Get a list of all software bundles [available in v_UpdateCIs  database view]
  • For each bundle, I shall get a list of the actual files inside it. [available in v_UpdateContents database view]
  • For each file, I will get the size and other information about it. [available in the CI_ContentFiles database table.

Note that there is a One-to-Many relationship between software bundles and the actual files each bundle contains. This is why we need a mapping table v_UpdateContents , which lists each software bundle, and the list of actual files it contains.

Here you can see me querying the v_UpdateCIs view. This will get me all software bundles identified by CI_ID , which is an important identifier. You can see that for each of those bundles, I can see the ArticleID, Sevirity, IsSuperseded, and more information. This is what you see when you browse software updates inside the configuration manager console.

configuration manager update size 2

 

Now, I will go to the v_UpdateContents  view, where I can see for each software bundle (identified by CI_ID ), which files (identified by ContentCI_ID ) are member of that software bundle. This is just a mapping table between software bundles and the actual update files.

configuration manager update size 3

 

Finally, now that we know how to pull the actual files, and what software bundles they are member of, we can go and get some information (including the file size) about them. We can do that by going to the CI_ContentFiles table.

configuration manager update size 4

 

And here is a simple representation about the three tables, and their relationship:

configuration manager update size 5

configuration manager update size 5

 

The SQL Query that will bring all this together

I wrote this simple, yet great SQL query, that you can run against your SCCM database and it will get you a list of all your software updates (same view you see in your configuration manager console under software updates), but this time, with the size information for each software update item “Bundle”.

Just make sure you change this line [ WHERE UI.DatePosted BETWEEN '1/1/2015' AND '2/1/2015' ] to match your filter criteria.

Now, the results will look like this:

configuration manager update size 6

 

It comes with a PowerShell functionality

I am not sure why I did it, but seems like most network administrators are more comfortable with PowerShell than playing with SQL queries. I created a PowerShell wrapper [Get-CMSoftwareUpdateSize] that you can use to get you the same information. Detailed about the PowerShell wrapper can be found at Microsoft TechNet Gallery here. Running the script will get you a nice view like this, and a CSV file containing all updates and their sizes. The script need the following information to run:

  • StartDate: Filter updates from this date. Format is mm/dd/yyyy.
  • EndDate: Filter updates up to this date. Format is mm/dd/yyyy.
  • Path_to_CSV: path to the CSV generated output.
  • SQLConnectionString: to connect to your Config Manager Database.

Example would be:

Configuration Manager Report 7

 

Reference Articles

Microsoft documentation – Configuration Manager Software Updates Views is a great resource to look at. Also, you might want to check my amazing configuration manager report, that brings together detailed information about your SCCM devices, with built-in functionality to remove duplicate records and orphan information.

About The Author

Ammar Hasayen

Ammar is a digital transformer, cloud architect, public speaker and blogger. He is considered a trusted advisory with the ability to quickly navigate complex multi-cultural organizations and continuously improve and motivate cross-functional teams to achieve higher productivity, collaboration, revenue gain and cross-group knowledge sharing. His contributions to the tech community helped him get awarded the Microsoft Most Valuable Professional. Ammar appears in a lot of global conferences, and he has many publications about digital transformation and next generation technologies.

Leave a reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Microsoft MVP

AMMAR - MVP - DARK BLUE

About Ammar

Digital Transformation | Microsoft MVP | Cloud Architect | Azure | Microsoft 365 |Modern Workplace | Cyber-Security | Speaker | Blogger | I Pluralsight Author| Jordan | http://me.ahasayen.com

Speaking at Microsoft Ignite

Microsoft Ignite Speaker

My Pluralsight Course

Pin It on Pinterest