SQL Query for WSUS 3 Needed Updates

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

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

8 thoughts to “SQL Query for WSUS 3 Needed Updates”

  1. A better place to get the values for SummarizationState is the table-valued function PUBLIC_VIEWS.fnUpdateInstallationStateMap() as they don’t guarantee the numbers will stay the same between versions. (I think they’d be crazy to change them, but then again, I’m not the crazy one in this relationship… ;-) )

    On my box, “select * from PUBLIC_VIEWS.fnUpdateInstallationStateMap()” gives

    Id Name
    — ———————-
    0 Unknown
    1 NotApplicable
    2 NotInstalled
    3 Downloaded
    4 Installed
    5 Failed
    6 InstalledPendingReboot

  2. I did the same thing, but I wanted to know which updates was not installed. My problem is that updates that weren’t approved still shows up as “needed”

    my query:
    ———————————————————————————–
    SELECT DISTINCT
    KBArticle.KBArticleID,
    updateDesc.Title,
    computers.FullDomainName,
    DATEADD(hour, 2, computers.LastReportedStatusTime) AS LastReportedStatusTime,
    ComputerSummaryForMicrosoftUpdates.Installed,
    ComputerSummaryForMicrosoftUpdates.NotInstalled,
    ComputerSummaryForMicrosoftUpdates.Downloaded,
    ComputerSummaryForMicrosoftUpdates.Failed,
    ComputerSummaryForMicrosoftUpdates.NotInstalled
    + ComputerSummaryForMicrosoftUpdates.Downloaded
    + ComputerSummaryForMicrosoftUpdates.Failed AS OutStandingUpdates,
    ComputerSummaryForMicrosoftUpdates.InstalledPendingReboot,
    UpdateStatusPerComputer.SummarizationState,
    CASE UpdateStatusPerComputer.SummarizationState
    WHEN 1 THEN (SELECT Name from PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Id = 1)
    WHEN 2 THEN (SELECT Name from PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Id = 2)
    WHEN 3 THEN (SELECT Name from PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Id = 3)
    WHEN 4 THEN (SELECT Name from PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Id = 4)
    WHEN 5 THEN (SELECT Name from PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Id = 5)
    WHEN 6 THEN (SELECT Name from PUBLIC_VIEWS.fnUpdateInstallationStateMap() WHERE Id = 6)
    END AS Status
    FROM
    [SUSDB].dbo.tbComputerTarget AS computers
    INNER JOIN [SUSDB].dbo.tbComputerSummaryForMicrosoftUpdates AS ComputerSummaryForMicrosoftUpdates ON computers.TargetID = ComputerSummaryForMicrosoftUpdates.TargetID
    INNER JOIN [SUSDB].dbo.tbUpdateStatusPerComputer AS UpdateStatusPerComputer ON computers.TargetID = UpdateStatusPerComputer.TargetID
    INNER JOIN [SUSDB].dbo.tbPreComputedLocalizedProperty AS updateDesc
    INNER JOIN [SUSDB].dbo.tbUpdate AS updates
    INNER JOIN [SUSDB].dbo.tbRevision AS revision ON updates.LocalUpdateID = revision.LocalUpdateID
    INNER JOIN [SUSDB].dbo.tbKBArticleForRevision AS KBArticle ON revision.RevisionID = KBArticle.RevisionID
    ON updateDesc.UpdateID = updates.UpdateID
    ON UpdateStatusPerComputer.LocalUpdateID = updates.LocalUpdateID
    WHERE (updateDesc.ShortLanguage = ‘en’)
    AND (revision.IsLatestRevision = 1)
    AND (KBArticle.KBArticleID 000000)
    AND (UpdateStatusPerComputer.SummarizationState6)
    AND (UpdateStatusPerComputer.SummarizationState1)
    ORDER BY OutStandingUpdates desc,
    FullDomainName,
    UpdateStatusPerComputer.SummarizationState

    1. is this part of the query correct? “AND (revision.IsLatestRevision = 1)
      AND (KBArticle.KBArticleID 000000)
      AND (UpdateStatusPerComputer.SummarizationState6)
      AND (UpdateStatusPerComputer.SummarizationState1)”

  3. Here is what I did to make sure only approved patches were included in the list up updates required:

    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

  4. i can filter for the update name

    {SELECT C.FULLDOMAINNAME AS COMPUTADOR,VU.DEFAULTTITLE AS ATUALIZAÇÃO,
    ‘STATUS’ = CASE
    WHEN UP.SummarizationState = 1 THEN ‘NÃO INSTALADO’
    WHEN UP.SummarizationState = 2 THEN ‘NÃO INSTALADO’
    WHEN UP.SummarizationState = 3 THEN ‘INSTALANDO’
    WHEN UP.SummarizationState = 4 THEN ‘INSTALADO’
    WHEN UP.SummarizationState = 5 THEN ‘FALHOU’
    END

    FROM TBCOMPUTERTARGET C
    INNER JOIN tbUpdateStatusPerComputer UP ON C.TargetID = UP.TargetID
    INNER JOIN tbUpdate U ON UP.LocalUpdateID = U.LocalUpdateID
    INNER JOIN [PUBLIC_VIEWS].[vUpdate] VU ON U.UpdateID = VU.UpdateId
    WHERE VU.DefaultTitle LIKE ‘WINDOWS Internet Explorer 9 for Windows 7′
    OR VU.DefaultTitle LIKE’Update for Microsoft Office 2010%’}

  5. Guys,

    In Wsus client if I do a report status I get the following menu to filter through. I want a sql query with the following status below but only where the approval status is install. The report in wsus is showing both install and Not approved.

    Include updates in these classifications : security updates

    Include updates for these products: Any product

    Include updates that have a status of: Needed, Failed

    Any help much appreciated.

    Thanks

Leave a Reply