WSUS SQL Queryt to get update stats

Question

Hello
I need to write SQL Queryt to get missing needed security updates from WSUS for all my domain server’s and workstations.

Can anyone help me ?

Thanks in advance

Answer 1

Hello,

here is the most common query used:

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′))) AND
tbUpdateStatusPerComputer.LocalUpdateID IN (SELECT LocalUpdateID FROM dbo.tbUpdate WHERE UpdateID IN
(SELECT UpdateID FROM PUBLIC_VIEWS.vUpdateApproval WHERE Action=’Install’))
GROUP BY tbComputerTarget.FullDomainName, tbComputerTarget.LastSyncTime
ORDER BY COUNT(*) DESC

1 = Not Installed
2 = Needed
3 = Downloaded
4 = Installed
5 = Failed

I hope this will help you further!

Regards,
ALPD