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.