다음을 통해 공유

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

  • Anonymous
    January 01, 2003
    Microsoft's Travis Plunk posted a modified version of Marc Shepard's WSUS SQL script for producing

  • Anonymous
    January 01, 2003
    Categories: Business Process Management , Desktop/Mobile , Dynamics , General , Microsoft Press Pass

  • Anonymous
    January 01, 2003
    Do you get the occasional OutOfMemoryException error when running reports in WSUS?  Are you looking

  • Anonymous
    January 01, 2003
    Great post, explained really well and I could really understand. Thank you.

  • Anonymous
    August 03, 2008
    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).

  • Anonymous
    August 04, 2008
    The comment has been removed

  • Anonymous
    September 03, 2008
    Internet explorer has finnaly the perfect [url=http://www.lookanddiscover.com]homepage[/url] ! see  the [url=http://www.lookanddiscover.com]homepage[/url] for yourself

  • Anonymous
    September 04, 2008
    Internet explorer has finnaly the perfect [url=http://www.lookanddiscover.com]homepage[/url] ! see  the [url=http://www.lookanddiscover.com]homepage[/url] for yourself

  • Anonymous
    September 14, 2008
    Hello! My name is Alex. Who Else Wants a Higher Paying Job? contact me: olgas32@gmail.com

  • Anonymous
    October 18, 2008
    thanks very nice good site beatiful

  • Anonymous
    October 27, 2008
    Dumb question... where would you run this script in?

  • Anonymous
    December 11, 2008
    http://www.battery-export.com/hp/hp-pavilion-dv6300-series.html  HP Pavilion dv6300 Series   http://www.battery-export.com/hp/hp-pavilion-dv6600-series.html  HP Pavilion dv6600 Series   http://www.battery-export.com/hp/hp-pavilion-dv8000-series.html  HP Pavilion dv8000 Series

  • Anonymous
    January 01, 2009
    thansk <a href="http://www.trstar.net" title="chat sohbet, sohbet odalari">sohbet chat</a> sites mcxno spam :S

  • Anonymous
    February 15, 2009
    Hello! My name is Alex. Who Else Wants a Higher Paying Job? contact me: olgas32@gmail.com

  • Anonymous
    February 15, 2009
    Great post, explained really well and I could really understand. Thank you

  • Anonymous
    July 28, 2009
    thanks very nice good site beatiful

  • Anonymous
    July 31, 2009
    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

  • Anonymous
    June 05, 2012
    Hi! Can you help me if i need to see which patches are not install on specific machine? Thanks