Understanding How ConfigMgr Interacts with WSUS

This deep dive covers how Configuration Manager interacts with WSUS to set up a software update point and synchronize data between WSUS and ConfigMgr.

Video Guide

Tip: To get the most from this guide, we recommend watching the video guide and then using this doc as a reference throughout the video.

Scenario 1: Installing a New Software Update Point

When you install a new software update point, the following will take place!

The Install Flow of new Software Update Point (SUP)

Once the software update point installation is completed, a list of products/categories won't happen until the first successful SUP sync.

You can right-click All Software Updates and click Synchronize Software Updates to start the first sync.

The first synchronization can take a while to complete!

The table below lists the flow to verify the first synchronization and population of the WSUS catalog/categories.

Note: The log lines below are with debug and verbose logging enabled. You log lines may not contain this level of data.

First SUP Synchronization Flow

Tip: During the first sync, the longest part will be WSUS pulling the catalog for Microsoft Update.

Scenario 2: How ConfigMgr Database Sync from WSUS Database

The ConfigMgr database pulls the update catalog from the WSUS database. Below, you can find more details about how this happens.

Sync Flow for ConfigMgr Sync from WSUS

In our example below, we manually triggered a sync using the step above.

Here's an example of us querying an update in the ConfigMgr database that was synchronized:

select * FROM CI_DocumentStore where DocumentIdentifier = 'c344e7f6-f83b-4693-8c02-41b2e072127e'

Scenario 3: WSUS Cleanup in ConfigMgr

Below are some key points mentioned in the video related to the cleanup.

Key Point for WSUS Maintenance in ConfigMgr

The setting in the Supersedence Rules tab determines how long an update needs to be superseded before it will be expired.

This setting in the WSUS Maintenance tab will determine if expired updates should be declined (improves WSUS performance and health)

Log Files for ConfigMgr Cleanup Task for WSUS

Scenario 4: View Update Views in the Database in Relationships

Determining Compliance

Quick dive into getting compliance data. The below query is an example of how you can retrieve the compliance status of updates for all machines in SQL. This view pulls from a few different points of interest.

SELECT CASE
			WHEN v_Update_ComplianceStatus.Status = '0' THEN 'UNKNOWN'
			WHEN v_Update_ComplianceStatus.Status = '1' THEN 'NOT REQUIRED'
			WHEN v_Update_ComplianceStatus.Status = '2' THEN 'NON COMPLIANT'
			WHEN v_Update_ComplianceStatus.Status = '3' THEN 'COMPLIANT'
			ELSE 'NA'
		END AS 'PatchStatus'
	, v_Update_ComplianceStatus.Status
    , v_Update_ComplianceStatus.CI_ID
    , v_Update_ComplianceStatus.resourceID
	, v_Update_complianceStatus.LastStatusCheckTime
    , v_StateNames.StateName
    , v_stateNames.StateDescription
FROM v_Update_ComplianceStatus
LEFT OUTER JOIN v_UpdateInfo on v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
LEFT OUTER JOIN v_StateNames on v_Update_ComplianceStatus.Status = v_StateNames.StateID
WHERE v_UpdateInfo.CIType_ID = '8' and v_StateNames.TopicType = '500'

The other side of the coin when you get ALL the data.

Note the query below can return a massive data set when run in production.

SELECT CASE
			WHEN v_Update_ComplianceStatusAll.Status = '0' THEN 'UNKNOWN'
			WHEN v_Update_ComplianceStatusAll.Status = '1' THEN 'NOT REQUIRED'
			WHEN v_Update_ComplianceStatusAll.Status = '2' THEN 'NON COMPLIANT'
			WHEN v_Update_ComplianceStatusAll.Status = '3' THEN 'COMPLIANT'
			ELSE 'NA'
		END AS 'PatchStatus'
	, v_Update_ComplianceStatusAll.Status
    , v_Update_ComplianceStatusAll.CI_ID
    , v_Update_ComplianceStatusAll.resourceID
	, v_Update_ComplianceStatusAll.LastStatusCheckTime
    , v_StateNames.StateName
    , v_stateNames.StateDescription
FROM v_Update_ComplianceStatusAll
LEFT OUTER JOIN v_UpdateInfo on v_Update_ComplianceStatusAll.CI_ID = v_UpdateInfo.CI_ID
LEFT OUTER JOIN v_StateNames on v_Update_ComplianceStatusAll.Status = v_StateNames.StateID
WHERE v_UpdateInfo.CIType_ID = '8' and v_StateNames.TopicType = '500'

Software Update Group Relationships

Software update groups are typically what we filter against as we typically have a group that we are targetting for a specific month. Get Software Update Group Names, and their CI's.

SELECT v_UpdateInfo.CI_ID
	, v_UpdateInfo.Title
FROM v_UpdateInfo
WHERE v_UpdateInfo.CIType_ID = '9'

Mapping the relationship of all updates in a software update group.

WITH SUGInfo AS
(
	SELECT v_UpdateInfo.CI_ID
	FROM v_UpdateInfo
	WHERE v_UpdateInfo.CIType_ID = '9'
)
SELECT v_CIRelation.FromCIID
	, v_CIRelation.ToCIID
FROM SUGInfo
LEFT OUTER JOIN v_CIRelation ON SUGInfo.CI_ID = v_CIRelation.FromCIID

Stringing it all together.

PreviousContent Troubleshooting NextUnderstanding the Software Update Chain

Last updated 3 years ago