SCCM 2012 SQL Query count updates from Software update groups if they are bigger than hard limit 1000 or not

In Configuration manager 2012,the update limit per update-deployment group has been increased (hard limit) to 1000 where was in Configmgr 2007,it was just 500 updates.More information about the hard limit and further questions ,worth reading the blog post by Kevin http://blogs.technet.com/b/server-cloud/archive/2012/02/20/managing-software-updates-in-configuration-manager-2012.aspx

The hard limit is --the list of software updates what you see in software update group from Configmgr console but not how what is downloaded to the source path.(I figure this in below SQL query)

With above reference,the max hard limit that deployment group can contains only 1000 updates ( am sure, no one reaches there due its to package size and replication issue over to the DP’s) but how to I track this information ? check each software update group from Configmgr 2012 console to know its count ? and I cannot do this every time when needed .

Here is the SQL query (you can create nice SSRS Report as well ) .to get count of software updates from each SUP Group.

with the below SQL query (output) ,I see 2 different things 1) Software updates available in Deployment Group and 2) Downloaded updates from the software updates available in Deployment group.

image

 

Why there is count mismatch ? Very simple.A single software update can have 2 or more content information which you can see it from the software update properties.

For ex: Cumulative Security Update for Internet Explorer 11 for Windows 7 (KB3021952) will have 2 content information ,both are required to download for deployment to the source path.

image

SQL Query:

select AL.Title [SU Group], count(DISTINCT upd.CI_ID ) 'Software updates Count',
count(upd.CI_ID ) 'Content downloaded Count'
from vSMS_CIRelation as cr
INNER JOIN fn_ListUpdateCIs(1033) upd ON  upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
INNER JOIN v_CIToContent CC ON cc.CI_ID=upd.CI_ID
INNER JOIN v_AuthListInfo AL ON al.CI_ID =cr.FromCIID
where CC.ContentDownloaded='1'
GROUP BY
AL.Title
ORDER BY 1

 

Happy checking Smile

Leave a Reply