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
  • This can't be done in the public UI because it has no ability to specifs the lengths of time an updates thanks

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

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

  • 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

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

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

  • http://www.battery-export.com/hp/hp-pavilion-dv8100-series.html  HP Pavilion dv8100 Series  

    http://www.battery-export.com/hp/hp-pavilion-dv8200-series.html  HP Pavilion dv8200 Series  

    http://www.battery-export.com/hp/hp-pavilion-dv8300-series.html  HP Pavilion dv8300 Series

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

  • 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

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

    sites no spam :D

  • http://www.ircask.com

    bolca sohbet

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

  • 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

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

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

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