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 (getting configuration manager update size)? 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.
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.
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.
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.
And here is a simple representation about the three tables, and their relationship:
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.
SELECT CI.CI_ID , (SUM(FileSize)/1024)/1 as 'Size in KB', CI.ArticleID, CI.BulletinID, LOC.DisplayName, CI.CustomSeverity, CI.Severity, CI.MaxExecutionTime, TYP.CategoryTypeName, TYP.CategoryInstanceName, Case (UI.IsDeployed) When 0 Then 'No' Else 'Yes' End as 'Deployed', Case(UI.IsExpired) When 0 Then 'No' Else 'Yes' End as 'Expired', CASE(UI.Severity) When 2 Then 'Low' When 6 Then 'Moderate' When 8 Then 'Important' When 10 Then 'Critical' Else 'NA' End as 'Severity', UI.DatePosted, CASE(ui.IsSuperseded) When 0 Then 'No' Else 'Yes' End as 'Superseded' FROM v_UpdateContents JOIN v_UpdateCIs ON v_UpdateCIs.CI_ID = v_UpdateContents.CI_ID JOIN CI_ContentFiles ON v_UpdateContents.content_id = CI_ContentFiles.Content_ID JOIN CI_UpdateCIs AS CI ON CI.CI_ID = v_UpdateContents.CI_ID LEFT JOIN v_LocalizedCIProperties_SiteLoc AS LOC ON LOC.CI_ID=ci.CI_ID LEFT JOIN v_CICategoryInfo TYP ON TYP.CI_ID=ci.CI_ID AND TYP.CategoryTypeName = 'UpdateClassification' LEFT JOIN CI_UpdateInfo INF ON inf.CI_ID=ci.CI_ID LEFT JOIN v_UpdateInfo UI ON UI.CI_ID = ci.CI_ID WHERE UI.DatePosted BETWEEN '1/1/2015' AND '2/1/2015' GROUP BY CI.CI_ID , CI.ArticleID, CI.BulletinID, LOC.DisplayName, CI.CustomSeverity, CI.Severity, CI.MaxExecutionTime, TYP.CategoryTypeName, TYP.CategoryInstanceName, Case (UI.IsDeployed) When 0 Then 'No' Else 'Yes' End, Case(UI.IsExpired) When 0 Then 'No' Else 'Yes' End, CASE(UI.Severity) When 2 Then 'Low' When 6 Then 'Moderate' When 8 Then 'Important' When 10 Then 'Critical' Else 'NA' End, UI.DatePosted, CASE(UI.IsSuperseded) When 0 Then 'No' Else 'Yes' End ORDER BY DisplayName
Now, the results will look like this:
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:
.\Get-CMSoftwareUpdateSize -StartDate '2/1/2015/' -EndDate '1/1/2015' -path_to_Csv c:\ -SQLConnectionString "Server=SRV.Contoso.com,1433;Database=CM_JFK;Trusted_Connection=True"
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.
I hope by now you know how to get the Configuration Manager update size. Understanding the impact of knowing the Configuration Manager update size helps you plan a head your patching deployment. If you think getting the Configuration Manager update size is useful, then please leave a comment below.