Query WSUS server

On a regular basis we patch our Windows Servers and it would be nice to get a realtime view of just how patched our servers are – which servers are not yet patched and how many patches are about to get applied.

WSUS (Windows Server Update Services) stores it’s data in a SQL Server database which makes this a easy task.
The query I used is like this:

SELECT tbComputerTarget.FullDomainName as [ServerName], 
tbComputerTarget.IPAddress as [IPAddress],
count(tbComputerTarget.FullDomainName) as [MissingPatches],
tbComputerTarget.LastSyncTime as [LastSyncTime]
FROM tbUpdateStatusPerComputer (nolock) 
INNER JOIN tbComputerTarget (nolock) 
ON tbUpdateStatusPerComputer.TargetID = tbComputerTarget.TargetID
WHERE (NOT (tbUpdateStatusPerComputer.SummarizationState IN ('1', '4'))) 
GROUP BY tbComputerTarget.FullDomainName,
tbComputerTarget.IPAddress, tbComputerTarget.LastSyncTime
ORDER BY COUNT(*) DESC

After getting this into a webpage, my colleagues can now easily get a live view of the status in our serverpark.

This entry was posted in SQL Script, Tips, WSUS. Bookmark the permalink.

Leave a Reply

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