Whoa WUAU! What the heck is with the circular references (0x8024000F)?

Context

Using SCCM for patch deployment, all of sudden no status updates were getting posted by the clients.

Discovery

Reviewing WUAHandler.log showed the following error:

OnSearchComplete - Failed to end search job. Error = 0x8024000f. WUAHandler 08/23/2014 1:53:10 PM 1104 (0x0450)
Scan failed with error = 0x8024000f. WUAHandler 08/23/2014 1:53:10 PM 1104 (0x0450)

Now the question became why is the search job not ending?  Since SCCM uses WUAU to determine which updates are applicable, the next step was to see if there were any errors in the WindowsUpdate.log.  Reviewing the WindowsUpdate.log provided the following insight (trimmed for brevity):

2014-08-24 05:42:18:502 584 1a38 Agent * WARNING: Exit code = 0x8024000F
2014-08-24 05:42:18:502 584 1a38 Agent WARNING: WU client failed Searching for update with error 0x8024000f
2014-08-24 05:42:18:502 584 14e8 AU # WARNING: Search callback failed, result = 0x8024000F
2014-08-24 05:42:18:502 584 14e8 AU # WARNING: Failed to find updates with error code 8024000f

A quick review of Appendix G: Windows Update Agent Result Codes translated this to WU_E_CYCLE_DETECTED, "Circular update relationships were detected in the metadata."

Ahh, now I understand…  NOT!  Next step, as usual, is a quick internet search to see if someone figured this out for me.  Alas, no useful information came up, which is also why this makes it worthwhile to post (and so I'll be able to remember what I did the next time this happens).

Having spent some time in the WSUS database before I know there are a couple of tables building relationships between the various updates and/or update bundles.  For those who haven't spent a lot of time in here, the table that seems to be at the center of everything is [dbo].[tbRevision] which is linked to [dbo].[tbUpdate] via the [LocalUpdateID] column (did not design this, so just sharing what I've learned).  [RevisionId] from [dbo].[tbRevision] and [UpdateId] from [dbo].[tbUpdate] are used pervasively throughout the database and form the relationship between the abstract concept of the Update and the individual components that constitute said update.

What does "abstract concept of the update" mean?  For certain updates it there are multiple revisions of the  update in the database.  (again, don't ask me why, I didn't design this, just sharing what I've learned)  Note:  Digging deeper, both use the same files, so I suspect this may have something to do with metadata,  supersedence, expiration, etc.

Expanding on these two base tables via the Foreign Keys, and a little bit of intelligent discrimination (eliminating localized descriptions, etc. that I guessed a client wouldn't care about), I scoped this to the following potential loops:

  • [tbRevision] –> [tbBundleAtLeastOne] –> [tbBundleAll] –> [tbRevision] – multiple revisions roll up into a parent revision (but don't necessarily need to).  Think ITIL concept of Configuration Item (CI), where a parent CI can contain multiple child CIs.  Brief review looks like this accommodates multiple binaries for scenarios where there are different OS languages that need to be patched.
  • [tbRevision] –> [tbPrerequisite] –> [tbInstalledUpdateSufficientForPrerequsite] –> [tbUpdate] –> [tbRevision] – Self-explanatory, are the pre-requisites on the system?  I.e. for an update which covers multiple Service Pack, this would make sure the appropriate update is applied depending on which Service Pack revision is deployed.
  • [tbRevision –> [tbRevisionSupersedesUpdate] –> [tbUpdate] –> [tbRevision] –  Save some time in by not installing the updates for which a later update covers the (security) fix and addresses the new issue.

Note that the above loops can theoretically also be combined, i.e. [tbRevision] –> [tbRevisionSupersedesUpdate] –> [tbUpdate] –> [tbRevsion] (different RevisionID –> [tbBundleAtLeastOne] –> [tbBundleAll] –> [tbRevsion] (pointing back to original RevisionID).

This allowed me to start putting together some SQL queries to figure out what might be going on.

SELECT *
FROM [dbo].[tbBundleAtLeastOne] balo
INNER JOIN [dbo].[tbBundleAll] ba ON balo.BundledID = ba.BundledID
WHERE balo.RevisionID = ba.RevisionID

No results…

SELECT *
FROM [dbo].[tbPrerequisite] p
INNER JOIN [dbo].[tbInstalledUpdateSufficientForPrerequisite] iusfp ON p.PrerequisiteID = iusfp.PrerequisiteID
INNER JOIN [dbo].[tbRevision] r ON iusfp.LocalUpdateID = r.LocalUpdateID
WHERE p.RevisionID = r.RevisionID

Still No…

SELECT u.UpdateID
FROM [dbo].[tbRevisionSupersedesUpdate] rsu
INNER JOIN [dbo].[tbUpdate] u ON rsu.SupersededUpdateID = u.UpdateID
INNER JOIN [dbo].[tbRevision] r ON u.LocalUpdateID = r.LocalUpdateID
WHERE rsu.RevisionID = r.RevisionID

Bingo!!!

   

A little more SQL assuages my curiosity of where this came from:

SELECT r.RevisionID, vu.UpdateId, r.RevisionNumber, CAST(CASE WHEN rsu.revisionID IS NULL THEN 0 ELSE 1 END AS BIT) AS IsSuperseded
, lp.Title AS 'Company', vu.DefaultTitle, vu.DefaultDescription
, vu.ArrivalDate, vu.CreationDate, vu.IsDeclined, vu.PublicationState, vu.UpdateType
, vu.UpdateSource, vu.KnowledgebaseArticle, vu.SecurityBulletin
FROM [dbo].[tbUpdate] u
INNER JOIN [PUBLIC_VIEWS].[vUpdate] vu ON u.UpdateId = vu.UpdateID
INNER JOIN [dbo].[tbRevision] r ON u.LocalUpdateID = r.LocalUpdateID
INNER JOIN [dbo].[tbFlattenedRevisionInCategory] fric ON r.RevisionID = fric.RevisionID
INNER JOIN [dbo].[tbRevision] rCompany ON fric.CategoryID = rCompany.LocalUpdateID AND rCompany.IsLatestRevision = 1
INNER JOIN [dbo].[tbCategory] c ON fric.CategoryID = c.CategoryID AND c.CategoryType = 'Company'
INNER JOIN [dbo].[tbProperty] p ON rCompany.RevisionID = p.RevisionID
INNER JOIN [dbo].[tbLocalizedPropertyForRevision] lpr ON rCompany.RevisionID = lpr.RevisionID AND lpr.LanguageID = p.DefaultPropertiesLanguageID
INNER JOIN [dbo].[tbLocalizedProperty] lp ON lpr.LocalizedPropertyID = lp.LocalizedPropertyID
LEFT JOIN [dbo].[tbRevisionSupersedesUpdate] rsu ON r.RevisionId = rsu.RevisionID
WHERE vu.UpdateId IN
(
SELECT u.UpdateID
FROM [SUSDB].[dbo].[tbRevisionSupersedesUpdate] rsu
INNER JOIN dbo.tbUpdate u on rsu.SupersededUpdateID = u.UpdateID
INNER JOIN dbo.tbRevision r ON u.LocalUpdateID = r.LocalUpdateID
WHERE rsu.RevisionID = r.RevisionID
)

Resolution

Which now explains why this started right after I pushed some updates from SCUP. Note that SCUP was not the problem, it was the vendor who put the circular reference in the updated metadata.

  1. Find the update in SCUP (search on the UpdateID).
      
    The two UpdateIDs I found:
  • 06366964-77F9-4A48-9BAF-DBF9851BBAFF
  • 9F4B59BC-E73C-4E02-A5E5-2B3B40A23D73
  • Select the "Supersedes" Tab and confirm that it is Superseding itself.
  • Select the update (under the "Superseded Updates" item) and select "Remove Update".
      
  • Confirm that it is deleted.
      
  • Re-Publish the update.
    This will “retire” the old revision and replace it with the new revision that is not superseded by itself.  Run the “big” query above to see how this happens.