Windows Server Update Services add-ons — by Steven Manross

Steven Manross has created Windows Server Update Services add-ons in the form of an SQL stored procedure and .vbs / Perl scripts to determine if computers currently show as needing updates.


The SQL stored procedure (spSRMCountComputersNeedingUpdates.sql) is used in conjunction with the WSUSReport.vbs or (WSUSReport.pl) scripts to automatically notify an admin via email that there are computers needing Windows Security-related updates.


In step 1, let’s add the sql stored procedure on WSUS Database Server and in step 2 we will run the .vbs script scripts to automatically notify WSUS Administrator via email that there are computers needing updates.


SAMPLE OUTPUT AS SEEN IN EMAIL:


Subject: WSUS: There are computers needing updates


Type: Software KB Article: 816093 Bulletin: MS03-011
Title: 816093: Security Update Microsoft Virtual Machine (Microsoft VM)
Description: This update helps resolve a vulnerability in the Microsoft virtual machine. After you install this item, you may have to restart your computer. Once you have installed this item, it cannot be removed.
More Information: http://go.microsoft.com/fwlink/?LinkId=14964
Server Name(s): computer1.domain.com,computer2.domain.com,computer3.domain.com


PRE-REQUISITES:


The .vbs code below requires Outlook CDO components to be installed or some other application that installs the CDO.Message object from the computer running WSUSReport.vbs.


STEP 1:


Let’s start by adding the following code as a stored procedure (spSRMCountComputersNeedingUpdates.sql);

  • In SQL Enterprise Manager under “instancename\Databases\SUSDB\Stored Procedures”.
  • Right click on the Stored Procedure – click on New Stored Procedure.
  • Paste the code below – click on Check Syntax and make sure it is successful.

spSRMCountComputersNeedingUpdates.sql:-




CREATE PROCEDURE [dbo].[spSRMCountComputersNeedingUpdates]  AS


declare @computersNeedingUpdates int
declare @updatesNeededByComputers int
  SELECT @computersNeedingUpdates = COUNT(DISTINCT(C.TargetID)),
         @updatesNeededByComputers = COUNT(DISTINCT(U.LocalUpdateID))
      FROM tbUpdate AS U
    INNER JOIN dbo.tbUpdateStatusPerComputer AS S WITH (INDEX (nc3UpdateStatusPerComputer)) ON U.UpdateID=S.UpdateID
    INNER JOIN dbo.tbComputerTarget AS C ON C.TargetID = S.TargetID
    WHERE S.SummarizationState IN (2,3,6)  
        AND EXISTS (SELECT * FROM dbo.tbDeployment AS D
                             INNER JOIN dbo.tbRevision AS Re ON Re.RevisionID=D.RevisionID
                             INNER JOIN dbo.tbTargetGroup AS tg ON tg.TargetGroupID = D.TargetGroupID
                             WHERE Re.LocalUpdateID=U.LocalUpdateID AND
                                   D.ActionID IN (0,2) AND
                                   tg.Name <> ‘All Computers’
                   )



select @computersNeedingUpdates as computersNeedingUpdates,@updatesNeededByComputers as updatesNeededByComputers


IF @computersNeedingUpdates > 0
  BEGIN


    SELECT U.LocalUpdateID,
      C.FullDomainName as FullDomainName
      FROM tbUpdate AS U
      INNER JOIN dbo.tbPreComputedLocalizedProperty AS PCLP  ON PCLP.UpdateID=U.UpdateID
      INNER JOIN dbo.tbLanguage as L on L.ShortLanguage = PCLP.ShortLanguage
      INNER JOIN dbo.tbLanguageInSubscription as LIS on LIS.LanguageID = L.LanguageID
      INNER JOIN dbo.tbUpdateType AS UT  ON UT.UpdateTypeID=U.UpdateTypeID
      INNER JOIN dbo.tbUpdateStatusPerComputer AS S ON U.UpdateID=S.UpdateID
      INNER JOIN dbo.tbComputerTarget AS C ON C.TargetID = S.TargetID
      INNER JOIN dbo.tbTargetInTargetGroup AS TITG ON TITG.TargetID = C.TargetID
      INNER JOIN dbo.tbTargetGroup AS TG ON TG.TargetGroupID = TITG.TargetGroupID
      INNER JOIN dbo.tbRevision AS Re ON Re.LocalUpdateID = U.LocalUpdateID
      LEFT JOIN dbo.tbKBArticleForRevision AS KB ON KB.RevisionID = RE.RevisionID
      LEFT JOIN dbo.tbSecurityBulletinForRevision AS SB ON SB.RevisionID = RE.RevisionID
      INNER JOIN dbo.tbMoreInfoURLForRevision AS MI ON MI.RevisionID = RE.RevisionID and MI.ShortLanguage = L.ShortLanguage
      WHERE S.SummarizationState IN (2,3,6)  AND
            EXISTS (SELECT * FROM dbo.tbDeployment AS D
                             INNER JOIN dbo.tbRevision AS Re ON Re.RevisionID=D.RevisionID
                             INNER JOIN dbo.tbTargetGroup AS tg ON tg.TargetGroupID = D.TargetGroupID
                             WHERE Re.LocalUpdateID=U.LocalUpdateID AND
                                   D.ActionID IN (0,2) AND
                                   tg.Name <> ‘All Computers’
                    )


    SELECT U.LocalUpdateID,
      UT.Name as UpdateTypeName,
      KB.KBArticleID,
      case when SB.SecurityBulletinID IS NULL Then ‘None’ Else convert(varchar(15),SB.SecurityBulletinID) End as SecurityBulletinID,
      MI.MoreInfoURL as MoreInfoURL,
      PCLP.Title as UpdateTitle,
      PCLP.Description as UpdateDescription
      FROM tbUpdate AS U
      INNER JOIN dbo.tbPreComputedLocalizedProperty AS PCLP  ON PCLP.UpdateID=U.UpdateID
      INNER JOIN dbo.tbLanguage as L on L.ShortLanguage = PCLP.ShortLanguage
      INNER JOIN dbo.tbLanguageInSubscription as LIS on LIS.LanguageID = L.LanguageID
      INNER JOIN dbo.tbUpdateType AS UT  ON UT.UpdateTypeID=U.UpdateTypeID
      INNER JOIN dbo.tbUpdateStatusPerComputer AS S ON U.UpdateID=S.UpdateID
      INNER JOIN dbo.tbComputerTarget AS C ON C.TargetID = S.TargetID
      INNER JOIN dbo.tbTargetInTargetGroup AS TITG ON TITG.TargetID = C.TargetID
      INNER JOIN dbo.tbTargetGroup AS TG ON TG.TargetGroupID = TITG.TargetGroupID
      INNER JOIN dbo.tbRevision AS Re ON Re.LocalUpdateID = U.LocalUpdateID
      LEFT JOIN dbo.tbKBArticleForRevision AS KB ON KB.RevisionID = RE.RevisionID
      LEFT JOIN dbo.tbSecurityBulletinForRevision AS SB ON SB.RevisionID = RE.RevisionID
      INNER JOIN dbo.tbMoreInfoURLForRevision AS MI ON MI.RevisionID = RE.RevisionID and MI.ShortLanguage = L.ShortLanguage
      WHERE S.SummarizationState IN (2,3,6)  AND
            EXISTS (SELECT * FROM dbo.tbDeployment AS D
                             INNER JOIN dbo.tbRevision AS Re ON Re.RevisionID=D.RevisionID
                             INNER JOIN dbo.tbTargetGroup AS tg ON tg.TargetGroupID = D.TargetGroupID
                             WHERE Re.LocalUpdateID=U.LocalUpdateID AND
                                   D.ActionID IN (0,2) AND
                                   tg.Name <> ‘All Computers’
                    )
    GROUP BY U.LocalUpdateID,UT.Name,KB.KBArticleID,SB.SecurityBulletinID,MI.MoreInfoURL,PCLP.Title,PCLP.Description



  END
–ENDIF
RETURN 1
GO




STEP 2:


Now save the following .vbs code as WSUSReport.vbs for computers needing updates using the stored procedure above. The following code requires Outlook CDO components to be installed or some other application that installs the CDO.Message object from the computer running WSUSReport.vbs.


WSUSReport.vbs:-




‘On Error Resume Next
Const adCmdStoredProc = 4
Const adUseClient = 3


‘Requires the Outlook CDO components to be installed or some other application that installs the CDO.Message object.


smtp_mail_from = “Some Friendly Name <someaddress@somesite.org>”
smtp_mail_to = “Recipient Name <
recipient@somesite.org>”
smtp_server = “somesmtpserver.somesite.org”
smtp_port = “25”


db = “SUSDB”
appname = “SUSDB Mailer”
db_server = “YOUR-DB-SERVER”


Set Conn = CreateObject(“ADODB.Connection”)
if Err.Number <> 0 Then
  WScript.Echo “Failed creating ADODB.Connection object -> ” & Err.Description
  WScript.Quit(0)
End If


Conn.ConnectionTimeout = 15
Conn.CursorLocation = adUseClient
Conn.Open = “DRIVER={SQL Server};SERVER=” & db_server & “;APP=” & appname & “;DATABASE=” & db & “;Trusted_Connection=yes;”


if Err.Number <> 0 Then
  WScript.Echo “Failed opening ADODB.Connection object with DB info-> ” & Err.Description
  WScript.Quit(0)
End If


Set Cmd = CreateObject(“ADODB.Command”)


if Err.Number <> 0 Then
  WScript.Echo “Failed creating ADODB.Command object -> ” & Err.Description
  WScript.Quit(0)
End If
Cmd.CommandText = “spSRMCountComputersNeedingUpdates”
Cmd.CommandType = adCmdStoredProc
Cmd.ActiveConnection = Conn


Cmd.Prepared = 1
Cmd.CommandTimeout = 15


Set RS = Cmd.Execute


if Err.Number <> 0 Then
  WScript.Echo “Failed opening ADODB.Recordset object for Command -> ” & Err.Description
  WScript.Quit(0)
End If


rs_count = RS.RecordCount


Dim string


string = “<HTML><BODY>” & vbCrlf


if RS.Fields(0) > 0 Then
  WScript.Echo “Count = ” & RS.Fields(0).Value
  Set RSUpdates = RS.NextRecordSet
  Set RSData = RS.NextRecordSet
Else
  WScript.Echo “No updates.  Quitting successfully”
  WScript.Quit(1)
End If


‘Loop through all the computers that need updates


  Dim Updates
  Dim Computers
 
  Dim vContainer
  ‘ Create the dictionary instances.
  Set Updates = CreateObject (“Scripting.Dictionary”)
  Updates.CompareMode = StringCompare


x = 0
while (RSUpdates.EOF <> True)
  if Not Updates.Exists(RSUpdates.Fields(“LocalUpdateID”).Value) Then
    Updates.Add RSUpdates.Fields(“LocalUpdateID”).Value, RSUpdates.Fields(“FullDomainName”).Value
  Else
    Updates.Item(RSUpdates.Fields(“LocalUpdateID”).Value) = Updates.Item(RSUpdates.Fields(“LocalUpdateID”).Value) & “,” & RSUpdates.Fields(“FullDomainName”).Value
  End If
 
  RSUpdates.MoveNext
Wend


while (RSData.EOF <> True)
  strUpdateID = RSData.Fields(“LocalUpdateID”).Value
  strSrv = Updates.Item(strUpdateID)
  strUpdateType = RSData.Fields(“UpdateTypeName”).Value
  strKBID = RSData.Fields(“KBArticleID”).Value
  strBulletinID = RSData.Fields(“SecurityBulletinID”).Value
  strInfoURL = RSData.Fields(“MoreInfoURL”).Value
  strUpdateTitle = RSData.Fields(“UpdateTitle”).Value
  strUpdateDesc = RSData.Fields(“UpdateDescription”).Value
  string = string & “<TABLE border = 1>” & vbCrlf & _
           “<TR><TD><b>Type:</B> ” & strUpdateType & “</TD><TD><B>KB Article:</B> ” & strKBID & “</TD><TD><B>Bulletin:</B> ” & strBulletinID & “</TD></TR>” & vbCrlf & _
           “<TR><TD colspan = 3><B>Title:</B> ” & strUpdateTitle & “</TD></TR>” & vbCrlf & _
           “<TR><TD colspan = 3><B>Description:</B> ” & strUpdateDesc & “</TD></TR>” & vbCrlf & _
           “<TR><TD colspan = 3><B>More Information:</B> <A href=” & strInfoURL & “>” & strInfoURL & “</A></TD></TR>” & vbCrlf & _
           “<TR><TD colspan = 3><B>Server Name(s):</B> ” & strSrv & “</TD></TR></TABLE>” & vbCrlf
  RSData.MoveNext
Wend
string = string & “</BODY></HTML>”


Set cdoMessage = CreateObject(“CDO.Message”)
cdoMessage.Subject = “WSUS: There are computers needing updates”
cdoMessage.From = smtp_mail_from
cdoMessage.To = smtp_mail_to
cdoMessage.HTMLBody = string


cdoMessage.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing“) = 2
cdoMessage.Configuration.Fields.Item(“
http://schemas.microsoft.com/cdo/configuration/smtpserver“) = smtp_server
cdoMessage.Configuration.Fields.Item(“
http://schemas.microsoft.com/cdo/configuration/smtpserverport“) = smtp_port
cdoMessage.Configuration.Fields.Update


cdoMessage.Send
If Err.Number = 0 Then
  WScript.Echo “Success”
  WScript.Quit(1)
Else
  WScript.Echo “Error sending CDO Message: ” & Err.Description
  WScript.Quit(0)
End If


MORE INFORMATION


Kudos to Steven – http://www.manross.net/links.html

3 thoughts on “Windows Server Update Services add-ons — by Steven Manross”

  1. I noticed the proceedure above is not the same as the proceedure at the link…. neither of which work for me.
    The proceedure above gives me a ddl error, and the link give me non declared errors.

  2. The stored procedure does not work on MSDE.

    The error message is as follows

    Msg 2782, Level 16, State 1, Server ***, Procedure spSRMCountComputersNeedingUpdates, Line 82
    DDL statement is not allowed.

    Any ideas? thanks

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>