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)

Component/Log
Log Line Text
sitecomp.log
Starting service SMS_SERVER_BOOTSTRAP_DEMO1 with command-line arguments "DM1 D:\Program Files\Microsoft Configuration Manager /install D:\Program Files\Microsoft Configuration Manager\bin\x64\rolesetup.exe SMSWSUS "...
SUPSetup.log
SMSWSUS Setup Started....
SUPSetup.log
Supported WSUS version found
SUPSetup.log
Installation was successful.
WSUSCtrl.log
Attempting connection to local WSUS server
WSUSCtrl.log
Successfully connected to local WSUS server
WCM.log
WSUS Server configuration has been updated. Updating Group Info.
WCM.log
Subscribed Update Categories <?xml version="1.0" ?>~~<Categories>~~
WCM.log
Configuration successful. Will wait for 1 minute for any subscription or proxy changes
WCM.log
Setting new configuration state to 2 (WSUS_CONFIG_SUCCESS)
Once the software update point installation is completed, a list of products/categories won't happen until the first successful SUP sync.
Incomplete WSUS catalog after initial setup
You can right-click All Software Updates and click Synchronize Software Updates to start the first sync.
Sync software update point in ConfigMgr
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

Component/Log
Log Line Text
wsyncmgr.log
Starting Sync
wsyncmgr.log
Synchronizing WSUS server DEMO1 ...
wsyncmgr.log
sync: Starting WSUS synchronization
wsyncmgr.log
sync: WSUS synchronizing categories
wsyncmgr.log
Synchronizing SMS database with WSUS, default server is DEMO1.CONTOSO.LOCAL
wsyncmgr.log
Synchronizing SMS database with WSUS server DEMO1 ...
wsyncmgr.log
sync: Starting SMS database synchronization
wsyncmgr.log
sync: SMS synchronizing categories
wsyncmgr.log
sync: SMS synchronizing categories, processed 0 out of 246 items (0%)
wsyncmgr.log
sync: SMS synchronizing categories, processed 246 out of 246 items (100%)
wsyncmgr.log
declare @refd xml = N'<?xml version="1.0" encoding="utf-16"?><Categories><Category Id="Product:7e903438-3690-4cf0-bc89-2fc34c26422b" /><Category Id="Product:e903c733-c905-4b1c-a5c4-3528b6bbc746" />......
wsyncmgr.log
Done synchronizing SMS with WSUS Server DEMO1
wsyncmgr.log
Set content version of update source {891B2E1B-4873-4092-B1FD-7EAADE75A3D3} for site DM1 to 10
Tip: During the first sync, the longest part will be WSUS pulling the catalog for Microsoft Update.
WSUS performing first sync from Microsoft update catalog

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.
Component/Log
Log Line Text
wsyncmgr.log
Wakeup by inbox drop
wsyncmgr.log
select cat.CategoryInstance_UniqueID, cat.CategoryInstanceName from fn_ListUpdateCategoryInstances(9) cat where cat.AllowSubscription=1 and cat.IsSubscribed=1 and cat.IsParentSubscribed=0 order by 1
wsyncmgr.log
Read SUPs from SCF for DEMO2.CONTOSO.LOCAL
wsyncmgr.log
Synchronizing SMS database with WSUS, default server is DEMO2.CONTOSO.LOCAL
wsyncmgr.log
Synchronizing SMS database with WSUS server DEMO2 ...
wsyncmgr.log
Syncing updates arrived after 03/05/2021 21:07:43
wsyncmgr.log
Requested categories: Company=Patch My PC, Product=Windows 10, version 1903 and later, UpdateClassification=Security Updates, UpdateClassification=Updates, UpdateClassification=Critical Updates
wsyncmgr.log
sync: SMS synchronizing categories, processed 378 out of 378 items (100%)
wsyncmgr.log
sync: SMS synchronizing updates
wsyncmgr.log
sync: SMS synchronizing updates, processed 0 out of 30 items (0%)
wsyncmgr.log
select CI_UniqueID, RevisionNumber, LastModifiedDate=convert(nvarchar, DateLastModified, 126)+N'Z', IsDeployed~from v_UpdateCIs~where CIType_ID in (1,8) and IsExpired=0 and UpdateSource_ID=16777217 and CI_UniqueID='6ec13d2d-306c-41fe-83bf-9789346721bf'
wsyncmgr.log
insert vCI_ConfigurationItems (CI_UniqueID, CIVersion, ModelID, CIType_ID, PolicyVersion, Precedence, DateCreated, DateLastModified, LastModifiedBy, CreatedBy, ContentSourcePath, PermittedUses, IsBundle, IsHidden, IsUserDefined, IsEnabled, IsExpired, SourceSite, ApplicabilityCondition, CI_CRC, IsTombstoned) values ('6ec13d2d-306c-41fe-83bf-9789346721bf', 200, 16780004, 8, 1, 0, '05/11/2021 18:15:49', '05/11/2021 18:15:49', N'', N'', N'', 0, 1, 0, 0, 1, 0, 'DM2', '<ApplicabilityRule ExpressionLanguage="Custom" Handler="{2DB70103-3DFB-4806-85B0-B27B561284BB}"><UpdateId>6ec13d2d-306c-41fe-83bf-9789346721bf</UpdateId><CISource><Id>{CA3F999B-7A81-43BF-912C-56E267C1E565}</Id><Version>9</Version></CISource><ProductId>b3c75dc1-155f-4be4-b015-3f1a91758e52</ProductId></ApplicabilityRule>', 'c02abc2', 0)
wsyncmgr.log
insert into CI_DocumentStore (DocumentIdentifier, Body, IsVersionLatest, DocumentType) values ('c344e7f6-f83b-4693-8c02-41b2e072127e', '', 0, 0)~;select SCOPE_IDENTITY()
wsyncmgr.log
sync: SMS synchronizing updates, processed 30 out of 30 items (100%)
wsyncmgr.log
sync: SMS performing cleanup
wsyncmgr.log
Done synchronizing SMS with WSUS Server DEMO2
wsyncmgr.log
Updated 60 items in SMS database, new update source content version is 9
Here's an example of us querying an update in the ConfigMgr database that was synchronized:
1
select * FROM CI_DocumentStore where DocumentIdentifier = 'c344e7f6-f83b-4693-8c02-41b2e072127e'
Copied!

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

Component/Log
Log Line Text
wsyncmgr.log
sync: SMS performing cleanup
wsyncmgr.log
Removed 54 unreferenced updates
wsyncmgr.log
Starting cleanup on WSUS, default server DEMO2.CONTOSO.LOCAL
wsyncmgr.log
Cleaning up WSUS server DEMO2 ...
wsyncmgr.log
nclLocalizedPropertyID Index Already exists in column LocalizedPropertyID on table tbLocalizedPropertyForRevision. Did Not Create Index.Server: DEMO2, DataBase: SUSDB
wsyncmgr.log
nclSupercededUpdateID Index Already exists in column SupersededUpdateID on table tbRevisionSupersedesUpdate. Did Not Create Index.Server: DEMO2, DataBase: SUSDB
wsyncmgr.log
Done Indexing SUSDB. Custom indexes were created if they didn't exist previously. DEMO2
wsyncmgr.log
sync: SMS performing cleanup
wsyncmgr.log
Cleanup processed 57 total updates and declined 27
wsyncmgr.log
Done Declining updates in WSUS Server DEMO2
wsyncmgr.log
Starting Deletion of ObseleteUpdates
wsyncmgr.log
0 update(s) were deleted from SUSDB in Server: DEMO2 Database: SUSDB
wsyncmgr.log
Deletion Completed
wsyncmgr.log
Updated 54 items in SMS database, new update source content version is 11

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.
View Name
Purpose
v_Update_ComplianceStatus
Get compliance state for a CI_ID and a ResourceID (filtered)
v_UpdateInfo
Get real world Update Info
v_StateNames
Get the human meatning of state values
1
SELECT CASE
2
WHEN v_Update_ComplianceStatus.Status = '0' THEN 'UNKNOWN'
3
WHEN v_Update_ComplianceStatus.Status = '1' THEN 'NOT REQUIRED'
4
WHEN v_Update_ComplianceStatus.Status = '2' THEN 'NON COMPLIANT'
5
WHEN v_Update_ComplianceStatus.Status = '3' THEN 'COMPLIANT'
6
ELSE 'NA'
7
END AS 'PatchStatus'
8
, v_Update_ComplianceStatus.Status
9
, v_Update_ComplianceStatus.CI_ID
10
, v_Update_ComplianceStatus.resourceID
11
, v_Update_complianceStatus.LastStatusCheckTime
12
, v_StateNames.StateName
13
, v_stateNames.StateDescription
14
FROM v_Update_ComplianceStatus
15
LEFT OUTER JOIN v_UpdateInfo on v_Update_ComplianceStatus.CI_ID = v_UpdateInfo.CI_ID
16
LEFT OUTER JOIN v_StateNames on v_Update_ComplianceStatus.Status = v_StateNames.StateID
17
WHERE v_UpdateInfo.CIType_ID = '8' and v_StateNames.TopicType = '500'
Copied!
The other side of the coin when you get ALL the data.
View Name
Purpose
v_Update_ComplianceStatusAll
Get compliance stat for a CI_ID and a ResourceID (ALL)
Note the query below can return a massive data set when run in production.
1
SELECT CASE
2
WHEN v_Update_ComplianceStatusAll.Status = '0' THEN 'UNKNOWN'
3
WHEN v_Update_ComplianceStatusAll.Status = '1' THEN 'NOT REQUIRED'
4
WHEN v_Update_ComplianceStatusAll.Status = '2' THEN 'NON COMPLIANT'
5
WHEN v_Update_ComplianceStatusAll.Status = '3' THEN 'COMPLIANT'
6
ELSE 'NA'
7
END AS 'PatchStatus'
8
, v_Update_ComplianceStatusAll.Status
9
, v_Update_ComplianceStatusAll.CI_ID
10
, v_Update_ComplianceStatusAll.resourceID
11
, v_Update_ComplianceStatusAll.LastStatusCheckTime
12
, v_StateNames.StateName
13
, v_stateNames.StateDescription
14
FROM v_Update_ComplianceStatusAll
15
LEFT OUTER JOIN v_UpdateInfo on v_Update_ComplianceStatusAll.CI_ID = v_UpdateInfo.CI_ID
16
LEFT OUTER JOIN v_StateNames on v_Update_ComplianceStatusAll.Status = v_StateNames.StateID
17
WHERE v_UpdateInfo.CIType_ID = '8' and v_StateNames.TopicType = '500'
18
​
Copied!

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.
1
SELECT v_UpdateInfo.CI_ID
2
, v_UpdateInfo.Title
3
FROM v_UpdateInfo
4
WHERE v_UpdateInfo.CIType_ID = '9'
Copied!
Mapping the relationship of all updates in a software update group.
1
WITH SUGInfo AS
2
(
3
SELECT v_UpdateInfo.CI_ID
4
FROM v_UpdateInfo
5
WHERE v_UpdateInfo.CIType_ID = '9'
6
)
7
SELECT v_CIRelation.FromCIID
8
, v_CIRelation.ToCIID
9
FROM SUGInfo
10
LEFT OUTER JOIN v_CIRelation ON SUGInfo.CI_ID = v_CIRelation.FromCIID
Copied!

Stringing it all together.

​
Last modified 5mo ago