'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 " smtp_mail_to = "Recipient Name " 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 = "" & 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 & "" & vbCrlf & _ "" & vbCrlf & _ "" & vbCrlf & _ "" & vbCrlf & _ "" & vbCrlf & _ "
Type: " & strUpdateType & "KB Article: " & strKBID & "Bulletin: " & strBulletinID & "
Title: " & strUpdateTitle & "
Description: " & strUpdateDesc & "
More Information: " & strInfoURL & "
Server Name(s): " & strSrv & "
" & vbCrlf RSData.MoveNext Wend string = string & "" 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