SQL Query for WSUS 3 Needed Updates

9 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

   Reply

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

   Reply

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

      Reply

   2. PUFF, I like this querry. Only thing to improve – filter out deprecated updates like cummulative, when you install newer version cummulative (B), and cummulative before (B) is on failed state and windows does not let install B, as you’re already have newer A update

      Reply

3. Sorry, not “needed”, but rather “NotInstalled”

   Reply

4. 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

   Reply

5. 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%’}

   Reply

6. 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

   Reply
7. Pingback: Abfrage WSUS-database für erforderliche Updates pro server SQL Server