Get SCCM WSUS Obsolete Updates Deletion Alerts using Status Message Queries

Let’s check how to get SCCM WSUS Obsolete Updates Deletion Alerts using Status Message Query. I have shared a custom SCCM status message query to get details of WSUS cleanup tasks. The status message alerts can help to trigger the automation process as well.

WSUS cleanup was one of the tedious tasks for me as an SCCM admin until Microsoft introduced some out of the box options to delete the obsolete updates in the SCCM console itself. Until that time, WSUS cleanup was a very complex process.

Many community scripts were used to perform the cleanup activities on SUSDB and reindex the SQL DB, etc. SCCM Scan Timeout Errors were very common because of the lack of scheduled maintenance of the WSUS database.

There are 43 useful SCCM status message queries (default queries). You can also create custom status message queries for specific alerts and monitoring scenarios. I have created and shared one for monitoring Task Sequence deployment status.

More Details – SCCM WSUS Maintenance Task – SUSDB Cleanup

• Who deleted the SCCM collection?
• SCCM Audit Reports Who Initiated CMPivot Query | ConfigMgr
• Who Deleted ConfigMgr Task Sequence | Modified |Created | SCCM

WSUS Cleanup Activity to Delete Obsolete Updates

WSUS cleanup tasks configured in the console help cleanup Obsolete Updates from SCCM and WSUS. The following are the three (3) tasks that SCCM can run automatically after each Software Update Point synchronization.

These three tasks help maintain WSUS, and it’s Database (SUSDB). The SUS was the previous version of WSUS, and that is why the WSUS DB is still using SUSDB as the name. SCCM WSUS obsolete updates will get deleted based on WSUS Cleanup SuperSedence Rules.

• Decline expired updates in WSUS according to supersedence rules
• Add non-Clustered indexes to the WSUS database
• Remove Obsolete updates from the WSUS database

NOTE! – More details on the WSUS cleanup options are available at SCCM WSUS Cleanup Tasks & FIX to SCCM Scan Timeout Errors HTMD.

This post will help you get SCCM WSUS Obsolete Updates Deletion Alerts using Status Message Queries. Let’s check the sections below to get more details.

Get SCCM WSUS Obsolete Updates Deletion Alerts via SMS_WSUS_SYNC_MANAGER Component

The WSUS Sync Manager component provides status messages that can help troubleshoot WSUS are patching issues within SCCM. I decided to create a custom SCCM status message query based on Message ID 6718 and component SMS_WSUS_SYNC_MANAGER.

You can get delete Obsolete Updates Alerts via the SMS_WSUS_SYNC_MANAGER component using the default SCCM status message queries. I used the All Status Messages query, Status messages reported after a specific date and time, to find out specific message ID to understand the deletion alerts obsolete updates.

The following is one example of the deletion of the Obsolete Updates status message. Based on this data, we will make a custom status message query for the deletion of obsolete updates.

Severity Type Site code Date / Time System Component Message ID Description
Information Milestone MEM 4/17/2022 12:04:38 AM CMMEMCM.MEMCM.COM SMS_WSUS_SYNC_MANAGER 6718 10 Obselete Updates were deleted.

WSUS SYNC Completion Alert

You can also get WSUS Synchronization completed alert from the SCCM status message query. The following example of a WSUS sync completion alert helps track the successful WSUS sync.

NOTE!- You can create a custom status message query for successful WSUS sync completion using Message ID 6703 and component SMS_WSUS_SYNC_MANAGER.

Severity	Type	Site Code	Date	Time	System	Component	Message ID	Description
Information	Milestone	MEM	4/17/2022	12:09:40	CMMEMCM.MEMCM.COM	SMS_WSUS_SYNC_MANAGER	6702	WSUS Synchronization done

Create Custom Status Message Query for SCCM WSUS Obsolete Updates Deletion Alerts

Let’s see how to create a custom status message query to get SCCM WSUS Obsolete Updates Deletion Alerts. The message ID and component name are used in the WQL query below to create a custom status message.

Navigate to \Monitoring\Overview\System Status\Status Message Queries from the SCCM admin console to create the custom status message query.

• Click on Create Status Message Query button from the ribbon menu.

• Enter the Name of the Status Message Query – SCCM WSUS Obsolete Updates Deletion Alerts.
• Enter the Comment for the SCCM Status Message Query -> Create Custom Status Message Query for SCCM WSUS Obsolete Updates Deletion Alerts.
• Click on the Edit Query Statement button.

Click on the Show Query Language button from the General tab in the new window.

Let’s copy the following WQL query to Create Custom SCCM Status Message Query for SCCM WSUS Cleanup Obsolete Updates alerts. This query prompts you to select the time frame you want to cover.

select stat.*, ins.*, att1.*, stat.Time from  SMS_StatusMessage as stat left join SMS_StatMsgInsStrings as ins on ins.RecordID = stat.RecordID left join SMS_StatMsgAttributes as att1 on att1.RecordID = stat.RecordID where stat.Component = "SMS_WSUS_SYNC_MANAGER" and stat.MessageID = 6718 and stat.Time >= ##PRM:SMS_StatusMessage.Time## order by stat.Time DESC

Click on the OK button to continue. Click on the Next button from the summary page and the Close button to finish the custom SCCM Status Message Query for SCCM WSUS Cleanup Obsolete Updates.

Results of the custom SCCM Status message query for SCCM WSUS Obsolete Updates deletion alerts provide very useful information concerning WSUS cleanup scenarios.

Custom WQL Query for WSUS Sync Completion Alerts

Let’s check the options to create Custom WQL Query for WSUS Sync Completion Alerts for SCCM admins. This can be part of daily checks for the admins.

You can follow the same steps (as explained in the above section) to create the custom status message query under \Monitoring\Overview\System Status\Status Message Queries.

Use the following WQL query instead of the above mentioned query and provide the appropriate name (WSUS Sync Completion Alerts) and comment for the custom status message query.

select stat.*, ins.*, att1.*, stat.Time from  SMS_StatusMessage as stat left join SMS_StatMsgInsStrings as ins on ins.RecordID = stat.RecordID left join SMS_StatMsgAttributes as att1 on att1.RecordID = stat.RecordID where stat.Component = "SMS_WSUS_SYNC_MANAGER" and stat.MessageID = 6702 and stat.Time >= ##PRM:SMS_StatusMessage.Time## order by stat.Time DESC

You can complete the custom SCCM status message query creation process as explained above.

• Click on the Close button from the completion page, as shown in the below screenshot.

How to check alerts from SCCM Status Message Query

Let’s see how to check alerts from SCCM Status Message Query. You can select the time frame from the drop-down menu. Let’s follow the steps below to check the SCCM status message alerts.

• Navigate to \Monitoring\Overview\System Status\Status Message Queries
• Select and Right-click on the Status Message Query that you want to run
• Select the option called Show Messages.

As you can see from the WSUS sync Completion Alerts window screenshot, there is only one prompted value, which is Time. Although, you can create WQL queries with multiple prompted values.

You need to select the date and time from the drop-down option of the Value section of the window. This helps to choose the time frame of the alerts you want to look into or analyze.

• Click on the OK button after selecting the time frame 1 hour ago, 2 weeks ago, 6 months ago, 1 year ago, etc.

You can check the SCCM status message alerts, as you can see in the below screenshot. You can use the FUNEL type symbol from the query message window to filter out the results further.

Author