I wanted an SQL query to retrieve the number of updates “required” by our clients / computers managed by WSUS. Most of the advice online seemed to be applicable only to WSUS 2. With a few tweaks to an existing script I managed to get a working SQL query (key: ComputerID now seems to be TargetID).
This is executed from within SQL Server Management Studio on the WSUS server itself (I migrated the database to the full version of SQL Server).
SQL version: 2008 R2
WSUS version: 3.2.7600.226
Windows version: Server 2008 x64 SP2
SELECT left(tbComputerTarget.FullDomainName,30) as [Machine Name]
,count(tbComputerTarget.FullDomainName) as [# of Missing patches]
,tbComputerTarget.LastSyncTime as [Last Sync Time]
FROM tbUpdateStatusPerComputer INNER JOIN tbComputerTarget ON tbUpdateStatusPerComputer.TargetID = tbComputerTarget.TargetID
WHERE (NOT (tbUpdateStatusPerComputer.SummarizationState IN ('1', '4'))) GROUP BY tbComputerTarget.FullDomainName, tbComputerTarget.LastSyncTime
ORDER BY COUNT(*) DESC
1 = Not Installed
2 = Needed
3 = Downloaded
4 = Installed
5 = Failed