use Win32::OLE qw (in); use constant adCmdStoredProc => 4; use constant adUseClient => 3; use constant StringCompare => 1; #Requires the Outlook CDO components to be installed or some other application that installs the CDO.Message object. my $smtp_mail_from = "Some Friendly Name "; my $smtp_mail_to = "Recipient Name "; my $smtp_server = "somesmtpserver.somesite.org"; my $smtp_port = "25"; my $db = "SUSDB"; my $appname = "SUSDB Mailer"; my $db_server = "YOUR-DB-SERVER"; my $Conn = Win32::OLE->new("ADODB.Connection"); if (Win32::OLE->LastError() != 0) { print "Failed creating ADODB.Connection object -> ". Win32::OLE->LastError(); exit 0; } $Conn->{ConnectionTimeout} = 15; $Conn->{CursorLocation} = adUseClient; $Conn->{Open} = "DRIVER={SQL Server};SERVER=". $db_server .";APP=". $appname .";DATABASE=". $db .";Trusted_Connection=yes;" if (Win32::OLE->LastError() != 0) { print "Failed opening ADODB.Connection object with DB info-> ". Win32::OLE->LastError(); exit 0; } my $Cmd = Win32::OLE->new("ADODB.Command"); if (Win32::OLE->LastError() != 0) { print "Failed creating ADODB.Command object -> ". Win32::OLE->LastError(); exit 0; } $Cmd->{CommandText} = "spSRMCountComputersNeedingUpdates"; $Cmd->{CommandType} = adCmdStoredProc; $Cmd->{ActiveConnection} = $Conn; $Cmd->Prepared = 1; $Cmd->CommandTimeout = 15; my $RS = $Cmd->Execute(); if (Win32::OLE->LastError() != 0) { print "Failed opening ADODB.Recordset object for Command -> ". Win32::OLE->LastError(); exit 0; } $rs_count = $RS->{RecordCount} my $string = "\n"; if ($RS->Fields(0) > 0) { print "Count = ". $RS->Fields(0)->{Value)."\n"; $RSUpdates = $RS->NextRecordSet(); $RSData = $RS->NextRecordSet(); } else { print "No updates. Quitting successfully\n"; exit 1; } #Loop through all the computers that need updates ' Create the dictionary instances. my $Updates = Win32::OLE->new("Scripting.Dictionary"); #I could have used perl associative arrays for this but then it wouldn't have translated so nice to VB. $Updates->{CompareMode} = StringCompare; my $x = 0; while ($RSUpdates->EOF != 1) { if (!$Updates->Exists($RSUpdates->Fields("LocalUpdateID")->{Value}) { $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}); } $RSUpdates->MoveNext(); } while ($RSData->EOF != 1) { $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 . "\n". "\n". "\n". "\n". "\n". "\n". "

Type: ". $strUpdateType ."	KB Article: ". $strKBID ."	Bulletin: ". $strBulletinID ."
Title: ". $strUpdateTitle ."
Description: ". $strUpdateDesc ."
More Information: ". $strInfoURL ."
Server Name(s): ". $strSrv ."

\n"; $RSData->MoveNext(); } $string = $string . "\n"; $cdoMessage = Win32::OLE->new("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")->{Value} = 2; $cdomessage->{Configuration}->{Fields}->Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")->{Value} = $smtp_server; $cdomessage->{Configuration}->{Fields}->Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")->{Value} = $smtp_port; $cdomessage->{Configuration}->{Fields}->Update(); $cdomessage->Send(); if (Win32::OLE->LastError() == 0) { print "Success\n"; exit 1; } else { print "Error sending CDO Message: ". Win32::OLE->LastError(); exit 0; }