Baseline compliance report, using public WSUS views


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 (42)

  1. Anonymous says:

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

  2. Anonymous says:

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

  3. Anonymous says:

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

  4. Anonymous says:

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

  5. Anonymous says:

    technet very good. thanks microsoft.. i love you windows

  6. Anonymous says:

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

  7. Anonymous says:

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

  8. Anonymous says:

    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

  9. Andrew says:

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

  10. StartupAdmin says:

    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!

  11. James MacPherson says:

    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

  12. Charlie says:

    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!

  13. ламинат says:

    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

  14. Bruce says:

    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

  15. klip izle says:

    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!

  16. teoman dinle says:

    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!

  17. autocad kursu says:

    This can’t be done in the public UI because it has no ability to specifs the lengths of time an updates thanks

  18. belgesel izle says:

    That computer. Howevers it can be done in WSUS 3 and laters via our public SQL view very good

  19. çizgi film says:

    Using SQL to find this informations out is really not the best way of doing it thanks you

  20. film izle says:

    But so many folks asked abouts how to use the public view to do this type of query last weeks that we wanteds to shows how it is done thanks

  21. gaziosmanpaşa says:

    I know should  have somethings like SP 3 makes it looks like an errors wonderful very nice

  22. BAttERY says:

     http://www.batteryfast.co.uk/apple/a1185.php NEW Hi-Cap 5500mAh Battery for MacBook 13 A1185 WHITE laptop battery,

  23. söve says:

    I know should  have somethings like SP 3 makes it looks like an errors wonderful very nice

  24. film izle says:

    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.

    Regards

  25. sohbet odaları says:

    thansk <a href="http://www.trstar.net&quot; title="chat sohbet, sohbet odalari">sohbet chat</a>

    sites no spam 😀

  26. karot says:

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

  27. kartvizit says:

    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.

    Regards

  28. Belgesel Tv says:

    Yes If a patch or update is installed by some other means than WSUS then  WSUS should not return a ‘Not Applicable’ Status

  29. chat says:

    thanks very good informations.. thanks thanks. nice sites.

  30. sohbet says:

    technet very good. thanks microsoft.. i love you windows

  31. compliance says:

    I need a sql statement that includes the date (or number of days) of the least recent update on non-compliant servers generated from Marc’s sql statement?

  32. Andy Helsby says:

    Come on Microsoft – shouldn’t you be checking your blog comments for spam – it’s rather worrying that there are so many spam comments on this particular posting (as an example).

    AS to this post – looks good, it would be nicer if it was easier to create these reports – this looks pretty complicated.

  33. medyum says:

    That computer. Howevers it can be done in WSUS 3 and laters via our public SQL view very good

  34. David says:

    In WSUS reports, Bulletin Number does not exist, would you please help on how to run a query to generate a report for "all computers’ patch status with Bulletin number"? thanks.

  35. araç sorgulama says:

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

  36. Andrew says:

    This seems like some good code. I’m a rookie when it comes to the WSUS/SUSDB. For some reason, there are no values in the my vComputerTarget and was wondering how does the database tables and views get poplulated.

  37. Herr Wuff says:

    How about a report on a selected group of patches, say weekly approved of 10-20 and their rollout to a site, showing machines with patches installed and requiring those patches?

  38. JimF says:

    Has anyone got this to work on a SQL 2014 server with the SUSDB running as a SQL 2008 db, reporting on a WSUS 3.0 installation? I have it generating an e-mail, but no output at all. No errors either. I am not really a SQL guy or a scripter, but I hold
    my on at times. It used to work on the SQL 2008 server with a WSUS 2.0 db. I feel that this might just be a case of it looking in the wrong location, or wrong table, for the WSUS 3.0 server.

  39. ByDesign1977 says:

    Hi

    Please excuse my ignorance but I am a newbie at all of this. I am running the above query against our WSUS DB and it is returning results but these results are simply the computer names.

    How would I go about getting more information such as what installation state each computer is in with an update etc?

    Again sorry if this is a really basic question but I am learning.

    Many thanks

  40. ByDesign1977 says:

    Just to extend on my question. Just for example it would be great to be able to show all computers pending a reboot..

    Thanks