# Understanding How ConfigMgr Interacts with WSUS ## Video Guide {% embed url="" %} {% hint style="success" %} 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. {% endhint %} ## 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 \\~\~\\~\~ | | 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](https://609257483-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZONaLPXBEqwgqId5si%2F-M_QnPELKpYdTTbk0xEM%2F-M_QpXppbEoPJDH0AU6L%2Fimage.png?alt=media\&token=33c398bd-a835-424e-93d8-2c9c6410c13a) You can **right-click All Software Updates** and click **Synchronize Software Updates** to start the first sync. ![Sync software update point in ConfigMgr](https://609257483-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZONaLPXBEqwgqId5si%2F-M_QnPELKpYdTTbk0xEM%2F-M_Qok1q0v6EzEOBlCBU%2Fimage.png?alt=media\&token=b2da05e3-6599-4dbb-96aa-fdf253f8b242) {% hint style="warning" %} The **first synchronization** can take a while to complete! {% endhint %} 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'\\\\...... | | 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 | {% hint style="success" %} **Tip**: During the first sync, the longest part will be WSUS pulling the catalog for Microsoft Update. {% endhint %} ![WSUS performing first sync from Microsoft update catalog](https://609257483-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZONaLPXBEqwgqId5si%2F-M_QnPELKpYdTTbk0xEM%2F-M_Qqd51I8g8SR7G48uE%2Fimage.png?alt=media\&token=94a9e4b5-fde4-4bf3-ad38-ae9e31fae58d) ## 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', '\\6ec13d2d-306c-41fe-83bf-9789346721bf\\\{CA3F999B-7A81-43BF-912C-56E267C1E565}\\9\\\b3c75dc1-155f-4be4-b015-3f1a91758e52\\', '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: ```sql 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**. ![](https://609257483-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZONaLPXBEqwgqId5si%2F-M_XeoKbFPIsQHwDJjWz%2F-M_XfPtC3w0yWTLZb9kG%2Fimage.png?alt=media\&token=a116a06d-a937-4e54-af86-f40047f6e5a9) This setting in the **WSUS Maintenance** tab will determine if expired updates should be declined (improves WSUS performance and health) ![](https://609257483-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZONaLPXBEqwgqId5si%2F-M_XeoKbFPIsQHwDJjWz%2F-M_Xfg6uy1aRQNQEC-GW%2Fimage.png?alt=media\&token=db628683-953d-4fda-a6f7-b2d890edb538) ### 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 | ```sql 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. | View Name | Purpose | | ------------------------------ | -------------------------------------------------------- | | v\_Update\_ComplianceStatusAll | Get compliance stat for a CI\_ID and a ResourceID (ALL) | {% hint style="info" %} Note the query below can return a **massive** data set when run in production. {% endhint %} ```sql 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. ```sql 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. ```sql 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. ![](https://609257483-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MZONaLPXBEqwgqId5si%2F-M_RyrLbBGABrFRINazr%2F-M_Rz-NsOhOoGAqh2V8O%2Fimage.png?alt=media\&token=79742e67-bfcd-4b27-ac1a-8a927099517d)