Share This Post

Filtering ConfigMgr Software Updates in SQL

The Datacenter environment that I manage with ConfigMgr for security updates consists of only Windows server platforms, running English builds, and no Itanium architecture.  So when it came time to build automation for authorizing security updates I wanted to ensure that only updates applicable to my environment were approved for deployment.

Creating this filtered list in SQL turned out to be much more complicated than I had anticipated but this query was the result.

It’s expensive to authorize unnecessary updates because they have associated resource costs:

  • Agents use processing time on the unnecessary updates to see if they apply to them
  • ConfigMgr infrastructure servers waste disk space storing unnecessary update files
  • Replication of ConfigMgr update packages takes longer because of unnecessary files

ConfigMgr relies on the Windows Server Update Services (WSUS) catalog and agent to handle security update patching; WSUS natively allows you to filter the update catalog and remove unneeded languages and some of the client/desktop platform updates but currently there isn’t any way to filter updates by architecture, 32bit or 64bit or specifically exclude just the 64bit Itanium architecture.

I was able to overcome this hurdle by filtering in SQL (using PATINDEX()) against the title of the update or the content web URL (Itanium patches have a pattern of %ia64%).  Of course there are a few update publishers who don’t have consistent publishing titles or they bundle updates for all platforms/architectures into a single update (.Net/SQL updates are the worst offenders); to solve this I have additional PATINDEX() checks to make sure the title/URL of the update being filtered doesn’t also contain the string pattern “server”.

The next problem was finding a distinct list of updates in the database; ConfigMgr normalizes the update metadata across a different tables and each security update gets broken into many rows.  For example, each security update for the Windows Server platforms will show up as at least 8 unique rows:

Security Update for Windows Server 2003 x64 Edition (KBxxxxxxx)
Security Update for Windows Server 2003 for Itanium-based Systems (KBxxxxxxx)
Security Update for Windows Server 2003 (KBxxxxxxx)
Security Update for Windows Server 2008 x64 Edition (KBxxxxxxx)
Security Update for Windows Server 2008 R2 for Itanium-based Systems (KBxxxxxxx)
Security Update for Windows Server 2008 (KBxxxxxxx)
Security Update for Windows Server 2008 R2 x64 Edition (KBxxxxxxx)
Security Update for Windows Server 2008 for Itanium-based Systems (KBxxxxxxx)

Another interesting tidbit I learned about ConfigMgr software updates data storage is that there are three CI (configuration items) fields that make up a distinct software update:

  • CI_UniqueID (you’d think we could stop here since this field is named unique…)
    • Sourced from the [v_UpdateInfo] view
  • CI_ID
    • Also sourced from the [v_UpdateInfo] view
    • Joined to the [v_UpdateContents] view to get the Content_ID
  • Content_ID
    • Sourced from the [CI_ContentFiles] view joined using the Content_ID

The information returned from this query is key to our automated patch authorization tools because it provides ConfigMgr’s Software Update feature with all the information it needs to programmatically authorize and download the necessary files for the security updates.

Update Assignments authorize security updates using the CI_ID field

Update file download URLs come from the Content_ID field and get downloaded to folders with a name equal to the CI_UniqueID

The downloaded update file path is used as a parameter, along with theContent_ID, by the AddUpdateContent() method from the WMI object [SMS_SoftwareUpdatesPackage] when adding security updates to a deployment package

Filtering updates in SQL prior to authorization also helps cut down on the number of CIs we need to process, download and replicate around the network.

Here are the results from a recent run of the filtering query:


So, out of the 4,214 update CI rows spanning 700 security bulletins in the database we only require 731 CIs to deploy the 92 required security updates applicable to our Datacenter.

The ConfigMgr console abstracts the translation of security bulletins to their many CIs; as mentioned in a previous blog post (link) I used the SMS_Provider.log to discover the data queries being used and then replicate the console functionality in our automation tools.


Download link.

Share This Post

Leave a Reply