WSUS Product Team Blog

WSUS Product Team thoughts, information, tips and tricks and beyond :-)

Baseline compliance report, using public WSUS views

Baseline compliance report, using public WSUS views

  • Comments 39
  • Likes

Customers at TechEd asked how to generate a compliance report that shows computers that are out of compliance against updates that have been approved for install to them for N days. This can't be done in the public UI because it has no ability to specify the length of time an update has been approved, or to scope to just updates approved-for-install to that computer. However it can be done in WSUS 3 and later via our public SQL views.

Information on how to use our public DB views can be found here: http://msdn.microsoft.com/en-us/library/bb410149(VS.85).aspx. As described in the article, to do this with the Windows Internal Database with WSUS, one first downloads SQL Studio Express Edition, and then connects to the DB using Windows Auth and the connection string "\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query".

Anyways, here's a query that accomplishes this. This query has not been tried on large DBs yet and may have  performance challenges on such DBs. We will probably update this blog next week with a tweaked version of this query that performs better on large systems, and that lists the particular updates that are needed by the computer (and also says if they are needed just because a reboot is needed). But so many folks asked about how to use the public views to do this type of query last week that we wanted to show how it is done.

-Marc Shepard

Lead Program Manager, WSUS

 

-- Find computers within a target group that need updates

-- which have been approved for install for at least N days

USE SUSDB

DECLARE @TargetGroup nvarchar(30)

DECLARE @Days int

SELECT @TargetGroup = 'Test Machines'

SELECT @Days = 7

 

-- Find all computers in the given @TargetGroup

SELECT vComputerTarget.Name

FROM PUBLIC_VIEWS.vComputerGroupMembership

INNER JOIN PUBLIC_VIEWS.vComputerTarget on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId

INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId

WHERE

vComputerTargetGroup.Name = @TargetGroup

-- And only select those for which an update is approved for install, the

-- computer status for that update is either 2 (not installed), 3 (downloaded),

-- 5 (failed), or 6 (installed pending reboot), and

-- the update has been approved for install for at least @Days

AND EXISTS

(

select * from

PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer

INNER JOIN PUBLIC_VIEWS.vUpdateApproval on vUpdateApproval.UpdateApprovalId = vUpdateEffectiveApprovalPerComputer.UpdateApprovalId

INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId

WHERE

vUpdateEffectiveApprovalPerComputer.ComputerTargetId = vComputerTarget.ComputerTargetId

AND vUpdateApproval.Action = 'Install'

AND vUpdateInstallationInfoBasic.UpdateId = vUpdateApproval.UpdateId

AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)

AND DATEDIFF (day, vUpdateApproval.CreationDate, CURRENT_TIMESTAMP) > @Days

)

Comments
  • Hooray!!! finally some useful compliance reporting from WUS.

    Why anyone thought that reporting on 'Needed' updates for machines that an update hadn't been approved was more useful than.....

  • Yet another set of foreign language updates synched with my WSUS server today (6/24) - with it set to only download English updates!

    Why??? If I wanted Bulgarian or Estonian updates, I would have selected them!

  • I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.

  • Microsoft's Travis Plunk posted a modified version of Marc Shepard's WSUS SQL script for producing

  • The list is a little longer today because of not posting last week. Enjoy! Microsoft Advanced Windows

  • Hi all, We have released a fix for the synchronization issues that Cecilia described in http://blogs.technet.com/wsus/archive/2008/06/20/baseline-compliance-report-using-public-wsus-views.aspx

  • Need help with auto up dates on home computer

    please contact me or e-mail with tel #for support

    thanks

    jim macpherson

    jmacphe103@aol.com

  • What are the chances that these SQL queries appearing on this blog will be incorporated into the reports module of the next WSUS version?

    Please remember that WSUS admins hail from all walsk of life: the gamut from full-bore IT administrators to power desktop users who get nominated as the IT person for their site/company. Using SQL to find this information out is really not the best way of doing it. Please provide more canned reports or more ad hoc report functionality in WSUS itself!

  • I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.

  • gtq Een plaatje zegt alles, toch ? uiz  Het volledige rapport is hier te vinden. Lees natuurlijk j  de blogposting. j n

    Thanks for interesting post! xoz

    [url=http://skuper.ru]паркет[/url] 9i

  • 132 Microsoft Team blogs searched, 105 blogs have new articles in the past 30 days. 641 new articles

  • Do you get the occasional OutOfMemoryException error when running reports in WSUS?  Are you looking

  • This type of query really should be in a report in the GUI

    If a patch or update is installed by some other means than WSUS then  WSUS should not return a ‘Not Applicable’ Status. It can say already installed or something else but to me Not applicable on a machine I know should  have something like SP 3 makes it look like an error

  • Using SQL to find this information out is really not the best way of doing it. Please provide more canned reports or more ad hoc report functionality in WSUS itself!

    thanks!

  • Not Applicable’ Status. It can say already installed or something else but to me Not applicable on a machine I know should  have something like SP 3 makes it look like an error

    thanks!

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment