Finding machines not compliant with a specific security bulletin


I read Marc’s post about Compliance Reporting and it was similar to a problem I deal with in my job.  Part of my job is to run Update Management on one of the domains consisting of around 12,000 managed computers at Microsoft using WSUS.  We do this in order to validate WSUS (and similar products) in an environment with real users.  Another group at Microsoft audits my compliance, and often request a list of non-compliant machines for specific security bulletins.  I have adapted Marc’s SQL script to do just that.


 


I ran into one issue, Marc’s SQL script will blocks clients from scanning while it runs.  Since the script can take a long time to execute on larger data sets, I decided to allow SQL to read dirty data and unblock my clients (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.).


 


I hope you find this useful.


 


Travis Plunk


Software Design Engineer in Test II, WSUS


 


— Find computers within a target group that need a security bulletin


 


USE


SUSDB


go


 


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


GO


 


DECLARE


@TargetGroup nvarchar(30)


DECLARE


@Bulletin nvarchar(9)


 


SELECT


 


@TargetGroup = ‘All Computers’


SELECT


@Bulletin = ‘MS08-030’


 


 


— Find the computers not compliant for each security bulletin in the given @TargetGroup


— where the approved occured between @Days and @DaysEnd days ago


 


SELECT              ct.Name,@Bulletin as Bulletin,ct.LastReportedStatusTime


FROM         PUBLIC_VIEWS.vComputerGroupMembership as cgm INNER JOIN


                      PUBLIC_VIEWS.vComputerTarget as ct ON


                      cgm.ComputerTargetId = ct.ComputerTargetId INNER JOIN


                      PUBLIC_VIEWS.vComputerTargetGroup as ctg ON


                      cgm.ComputerTargetGroupId = ctg.ComputerTargetGroupId


                     


WHERE     (ctg.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 bulletin is the one provided.


                              AND EXISTS


                          (SELECT     1


                            FROM          PUBLIC_VIEWS.vUpdateEffectiveApprovalPerComputer as ueapc INNER JOIN


                                                   PUBLIC_VIEWS.vUpdateApproval as ua ON


                                                   ua.UpdateApprovalId = ueapc.UpdateApprovalId INNER JOIN


                                                   PUBLIC_VIEWS.vUpdateInstallationInfoBasic uiib ON


                                                   uiib.ComputerTargetId = ct.ComputerTargetId AND


                                                   ua.UpdateId = uiib.UpdateId


                                                   inner join PUBLIC_VIEWS.vUpdate as u on ua.updateid=u.updateId 


                            WHERE      (ueapc.ComputerTargetId = ct.ComputerTargetId) AND


                                                   (ua.Action = ‘Install’) AND (uiib.State IN (2, 3, 5, 6)) AND u.securityBulletin is not null and u.securityBulletin=@Bulletin )


 


Travis Plunk


OMPS – Customer Readiness Test


Comments (18)

  1. Anonymous says:

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

  2. Anonymous says:

    Categories: Business Process Management , Desktop/Mobile , Dynamics , General , Microsoft Press Pass

  3. Anonymous says:

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

  4. Anonymous says:

    Great post, explained really well and I could really understand. Thank you.

  5. Tommy Doan says:

    Forefront Client Security is a special case since it is not detected as needed until a policy change is implemented on the computer. There is also no MSRC bulletin associated with the installer.

    To query for computers where the FCS client is not installed, use AND NOT EXISTS between the select statements and set uiib.State = 4 (update installed).

    Also change securityBulletin to KnowledgebaseArticle and @Bulletin to @KBArticle and set it equal to 952265 (as of now).

  6. Björn Axéll says:

    Hi,

    I was wondering if you might have any information on how to cretae reports by utilizing the SQL report engine. Is there any rdl (report definition files) for WSUS db?

    Thanks

    Björn

  7. lookanddiscover says:

    Internet explorer has finnaly the perfect [url=http://www.lookanddiscover.com]homepage[/url] ! see  the [url=http://www.lookanddiscover.com]homepage[/url] for yourself

  8. lookanddiscover says:

    Internet explorer has finnaly the perfect [url=http://www.lookanddiscover.com]homepage[/url] ! see  the [url=http://www.lookanddiscover.com]homepage[/url] for yourself

  9. Crineeftveill says:

    Hello!

    My name is Alex.

    Who Else Wants a Higher Paying Job?

    contact me: olgas32@gmail.com

  10. muzik dinle says:

    thanks

    very nice good site

    beatiful

  11. Wings1 says:

    Dumb question… where would you run this script in?

  12. sohbet odalari says:

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

  13. karot says:

    Hello!

    My name is Alex.

    Who Else Wants a Higher Paying Job?

    contact me: olgas32@gmail.com

  14. kartvizit says:

    Great post, explained really well and I could really understand. Thank you

  15. medyum says:

    thanks

    very nice good site

    beatiful

  16. Rap says:

    50 binlira varmi abi pp

    http://www.kodes.com Hiphop, Rap, Ceza, sagopa, Kolera

    http://www.gekkog.com Hiphop, Rap, Gekko G

    http://www.maskanimasyon.com Animasyon

  17. Capricorn says:

    Hi!

    Can you help me if i need to see which patches are not install on specific machine?

    Thanks