I worked in an environment where distribution points are centralized and we had to plan our software update distribution via config manager 2012/R2 very carefuly, by making sure that a Software Update Group will not have over 300 MB of content.
If you browse the Config Manager Software Updates node, you can filter and sort the updates that you are interested in, but you cannot see the Size of those updates right away. You have to go to each update, check its properties, and see what content files are part of that specific update, and then calculate the size.
I created a SQL query that can filter updates by a time frame, and will report back the list of output updates along with the size of each update, so you can easily plan your Software Update Group membership.
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
Ofcourse, you have to change the line WHERE UI.DatePosted BETWEEN ‘1/1/2015’ AND ‘2/1/2015’ as you want.
I then integrated this query to a PowerShell Script that will take the following variables:
- 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.
The script will have two output:
- CSV File containing the output data.
- Grid View
Download the script
You can download the script from Microsoft Script Gallery here.
The main SQL view i used is v_UpdateCIs, which is according to Microsoft:
Lists all of the software updates configuration items, by CI_ID and CI_UniqueID. The information in this view is a subset of information from the v_ConfigurationItems view, retrieving all records where the configuration type is Software Updates or Software Updates Bundle (CIType=1 or 8), including article ID, bulletin ID, severity, date created, whether the update is deployed, and so on.
Second view is v_UpdateContents Which is confusing at first, because it contains items with content by CI_ID. This view will list Software Bundles and no individual updates. Software Bundle can contain many content files (ContentCI_ID column). This is where things become tricky. Check this TechNet Article.
Now we will use the ContentCI_ID column as a join factor to CI_ContentFiles table to get more information about those content files that a software bundle contains.
From there, it is only about keep joining views to get the actual size of those individual content files, and then a GROUP BY clause to group individual content files to the Software Bundle they belong to.