Get Software Update Size from Configuration Manager 20120/R2 [Report/PowerShell]

Hi everyone,

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.

SQL query


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.

PowerShell Script

I then integrated this query to a PowerShell Script that will take the following variables:

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

 The script will have two output:

  • CSV File containing the output data.
  • Grid View

CM Update Size

Download the script

You can download the script from Microsoft Script Gallery here.

Info:

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.

4 comments on “Get Software Update Size from Configuration Manager 20120/R2 [Report/PowerShell]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s