SCCM Report / SQL query – WSUS Server sync status

SCCM 2012 has a menu item in the console under the Monitoring tab that will display the Software Update Point Synchronization Status.  Use of the SQL query below to display the same information in a reporting format.

SELECT SiteCode, WSUSServerName, WSUSSourceServer, SyncCatalogVersion, LastSuccessfulSyncTime,
CASE [LastSyncState]
WHEN 6700 THEN ‘WSUS Sync Manager Error’
WHEN 6701 THEN ‘WSUS Synchronization Started’
WHEN 6702 THEN ‘WSUS Synchronization Done’
WHEN 6703 THEN ‘WSUS Synchronization Failed’
WHEN 6704 THEN ‘WSUS Synchronization In Progress Phase Synchronizing WSUS Server’
WHEN 6705 THEN ‘WSUS Synchronization In Progress Phase Synchronizing SMS Database’
WHEN 6706 THEN ‘WSUS Synchronization In Progress Phase Synchronizing Internet facing WSUS Server’
WHEN 6707 THEN ‘Content of WSUS Server is out of sync with upstream server’
WHEN 6709 THEN ‘SMS Legacy Update Synchronization started’
WHEN 6710 THEN ‘SMS Legacy Update Synchronization done’
WHEN 6711 THEN ‘SMS Legacy Update Synchronization failed’
END AS ‘Last Sync State’, LastSyncStateTime
FROM vSMS_SUPSyncStatus

The following query will work for SCCM 2007 systems.

SELECT uss.SiteCode, si.ServerName, si.SiteName,
uss.ContentVersion, uss.SyncTime
FROM update_syncstatus uss, v_Site si
WHERE uss.SiteCode=si.SiteCode
AND uss.contentversion < 999999
ORDER BY SyncTime

2 Comments

  1. Anton Potgieter

    I have added the difference between the last sync date and the current date, to easily identify or see how many days have passed between sync’s. Can see problems much faster.

    SELECT SiteCode, WSUSServerName, WSUSSourceServer, SyncCatalogVersion, LastSuccessfulSyncTime,
    CASE [LastSyncState]
    WHEN 6700 THEN ‘WSUS Sync Manager Error’
    WHEN 6701 THEN ‘WSUS Synchronization Started’
    WHEN 6702 THEN ‘WSUS Synchronization Done’
    WHEN 6703 THEN ‘WSUS Synchronization Failed’
    WHEN 6704 THEN ‘WSUS Synchronization In Progress Phase Synchronizing WSUS Server’
    WHEN 6705 THEN ‘WSUS Synchronization In Progress Phase Synchronizing SMS Database’
    WHEN 6706 THEN ‘WSUS Synchronization In Progress Phase Synchronizing Internet facing WSUS Server’
    WHEN 6707 THEN ‘Content of WSUS Server is out of sync with upstream server’
    WHEN 6709 THEN ‘SMS Legacy Update Synchronization started’
    WHEN 6710 THEN ‘SMS Legacy Update Synchronization done’
    WHEN 6711 THEN ‘SMS Legacy Update Synchronization failed’
    END AS ‘Last Sync State’, LastSyncStateTime, (Datediff(d,LastSyncStateTime,GETDATE())) as “DateDiff”
    FROM vSMS_SUPSyncStatus

    Reply
    1. Jacques (Post author)

      Nice addition. Thanks Anton.

      Reply

Leave a Comment

Your email address will not be published. Required fields are marked *