Metadata Only Updates in SCCM 2012 R2 Console

Been a while since i blogged – but this i felt needed to come up.

If you work with Software Updates in SCCM then you are used to see Normal, Expired & Superseeded Updates from Microsoft… Not Metadata Only Updates! (Unless you work with SCUP or other 3rd party patching) – https://technet.microsoft.com/da-dk/library/hh848254.aspx

But i just had a case where the WSUS had totally broken down, the SUSDB was gone and the SUP was gone. So the only option left was to start over with building a new WSUS and setting a new SUP to connect to this new WSUS.

Note: The quick way to install a WSUS on Windows Server 2012 & 2012 R2 via PowerShell:

Install-WindowsFeature -Name UpdateServices-DB, UpdateServices-UI
& "C:\Program Files\Update Services\Tools\WsusUtil.exe" postinstall CONTENT_DIR=F:\WSUS SQL_INSTANCE_NAME="CM01"

(Remember to configure the WSUS IIS website SSL settings if you have a PKI solution implemented.)

But back to the Metadata Only Updates – all logs was not giving me any errors, warnings or information about why and where. I tried all the tricks in the book – removing SUP classifications, products and adding them again. Opened the WSUS console to see if there was some setting available to change updates from Metadata to Normal, even did a cleanup from WSUS. (Be carefull when working in the WSUS console, any configuration or change made in there can break the connection between WSUS and the SCCM SUP)

Further more i saw that going forward, the updates that where released or revised after the time of the incident was coming in as Normal updates, so this was not a problem going forward – it was just isolated to a bunch of updates that for some historical reason had been deemed as Metadata Only in the SCCM database.

Only thing left was to go into the database, and i cannot tell you how important it is to be carefull when venturing into the SCCM database! Test in a lab first, have a backup of your database ready and be miniscule in everything you do.

I looked everywhere for a solution to just change the update from Metadata Only to Normal but i didnt find anything that could reassure me that would work, so only option was to delete the Update CI’s in the SCCM database and then do a full sync of WSUS.

First of all go into the SCCM console, and in the All Software Updates view add the column Unique Update ID. This will give you the ID of the updates you need to work with.

Take the Unique Update ID and do a select to verify the object in the database.

select * from CI_ConfigurationItems where CI_UniqueID = 'id_value'

After having verified the object in the database then insert the Unique Update ID with the id_value in the actions below and execute one line at the time.

update CI_ConfigurationItems set IsExpired=1 where CI_UniqueID = 'id_value'
delete CI_ConfigurationItemRelations where ToCI_ID in (select CI_ID from CI_ConfigurationItems where CI_UniqueID = 'id_value')
delete CI_AssignmentTargetedCIs where CI_ID in (select CI_ID from CI_ConfigurationItems where CI_UniqueID = 'id_value')
delete CI_ConfigurationItems where CI_UniqueID = 'id_value'
delete CI_CIDocuments where CI_ID not in (select CI_ID from CI_ConfigurationItems)
delete CI_DocumentStore where Document_ID not in (select Document_ID from CI_CIDocuments)

When all is done then run a full sync of the WSUS from a PowerShell console started from the SCCM console (this way PowerShell automatically connects to SCCM).

Sync-CMSoftwareUpdate -FullSync $true

Hope this helps someone.


Comments (2):

  1. Hi Henrik,

    I just have to say, thank you for posting this. This actually got me going down the path I was looking for in an issue I was working on where I had about 851 updates all expired due to a database restore, so to this with the unique ID would have been painful. I made some adjustments to take care of a mass of updates and share the query for others to find helpful:

    I replaced CI_UniqueID with CIType_ID and left everything else the same and this worked very well with me. Here is the resulting code:

    select * from CI_ConfigurationItems where CIType_ID = ‘8’

    update CI_ConfigurationItems set IsExpired=1 where CIType_ID = ‘8’
    delete CI_ConfigurationItemRelations where ToCI_ID in (select CI_ID from CI_ConfigurationItems where CIType_ID = ‘8’)
    delete CI_AssignmentTargetedCIs where CI_ID in (select CI_ID from CI_ConfigurationItems where CIType_ID = ‘8’)
    delete CI_ConfigurationItems where CIType_ID = ‘8’
    delete CI_CIDocuments where CI_ID not in (select CI_ID from CI_ConfigurationItems)
    delete CI_DocumentStore where Document_ID not in (select Document_ID from CI_CIDocuments)

    I hope this helps someone in their turmoil :)

  2. Bobby Singh says:

    Henrik’s methodology would have been very painful indeed. Thanks, Zach for this update, it saved me a lot of grief.

Leave a Reply