Thanks to all for the replies.
In the end I used MS SQL Server Management Studio Express and it works
perfectly.
Here are 2 scripts that i wrote, the email code, debug code belong to Steven
Gill's Auto approve as does all but one AND statement in the Notify Script.
If you look at his script there are alot of similarities between mine and
his, this is because i used his script as a template and then butchered the
code. :D
(^^ bit of over kill there but dnt want to be accused of stealing code and
not crediting the orig author)
-----------------------
Update Notify Script
-----------------------
-----------------------
''WSUS-Notify based on Auto Approve WSUS Eulas
''
''Usage and more info at: http://gatefold.co.uk/wsus
''
''changelog
''V0.1a 19/06/05 - Some bugs fixed
''V0.1 11/06/05 - Steven Gill (gillsr at iee dot org)
''
''To use:
''Set up a scheduled job to run "cscript c:\autoapproveupdates.vbs" and run
with admin rights
''
''
'' Script rewritten by Ryan McLean (ryan1_00 at hotmail d0t com) 15/08/2006
in order to notify about update instead of approve
'' and to add ability to log.
''
'' -------------------------
'' Configuration
'' -------------------------
''Can use localhost if you have an smtp server locally
EmailDstName = "<Recipient-Address>"
EmailReplyToName = "<Sender-Address>"
EmailSrvName = "<smtp-server>"
serveradminurl = "http://<WsusServer>/WSUSAdmin"
WSUS_Con_STRING = "Provider=SQLOLEDB;Initial Catalog=SUSDB;Data
Source=<SERVER>\WSUS;Integrated Security=SSPI;Persist Security Info=False"
logging = true
Const LOG_FILE = "Script.log"
debugflag = false
strMsgBody = ""
strTitleList = ""
'' -------------------------
'' Constants
'' -------------------------
' Constants for File opening modes
Const forREAD = 1
Const forWRITE = 2
Const forAPPEND = 8
'' -------------------------
'' Identify if updates need to be approved
'' -------------------------
QueryString = "SELECT vwMinimalUpdate.UpdateID, tbProperty.RevisionID,
vwMinimalUpdate.RevisionNumber, tbProperty.EulaExplicitlyAccepted,
tbProperty.RequiresReacceptanceOfEula, vwMinimalUpdate.State,
vwUpdateLocalizedProperties.Title, vwMinimalUpdate.IsSuperseded FROM
vwMinimalUpdate INNER JOIN tbProperty ON vwMinimalUpdate.RevisionID =
tbProperty.RevisionID INNER JOIN vwUpdateLocalizedProperties ON
tbProperty.RevisionID = vwUpdateLocalizedProperties.RevisionID WHERE
(vwMinimalUpdate.EulaID IS NOT NULL) AND
(vwUpdateLocalizedProperties.ShortLanguage = 'en') AND (vwMinimalUpdate.State
<> 5)"
set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
NumApproved = 0
While (not rs.eof)
Title = rs.fields.item("Title").value
RevisionID = rs.fields.item("RevisionID").value
UpdateID = rs.fields.item("UpdateID").value
RevisionNumber = rs.fields.item("RevisionNumber").value
EulaExplicitlyAccepted = rs.fields.item("EulaExplicitlyAccepted").value
State = rs.fields.item("State").value
strTitleList = strTitleList & Title & vbNewline
NumApproved = NumApproved +1
rs.MoveNext()
Wend
'' -------------------------
'' Send email if new patches are available
'' -------------------------
if (NumApproved > 0) then
if (NumApproved > 1) then strPlural="s"
strMsgBody = strMsgBody & NumApproved & " Update" & strPlural & " need to
be approved for Installation" & vbcrlf
strMsgBody = strMsgBody & vbcrlf & serveradminurl & vbcrlf
strMsgBody = strMsgBody & vbcrlf & strTitleList
' Create the JMail message Object
set msg = CreateOBject("JMail.Message")
msg.Logging = true
msg.silent = true
msg.From = EmailReplyToName
msg.AddRecipient(EmailDstName)
msg.Subject = "[WSUS Server] " & NumApproved & " Update" & strPlural & "
need to be approved for Installation"
msg.Body = strMsgBody
if (msg.Send(EmailSrvName)) then
LogIt("Email Successfully sent, " & NumApproved & " Update" & strPlural &
" need to be approved")
else
LogIt("Error sending email!")
Logit(msg.log)
end if
else
LogIt("No updates need to be approved for Installation")
End if
rs.close()
sub LogIt(logtxt)
strMsgBody = strMsgBody & now() & ": " & logtxt & vbcrlf & vbcrlf
if (logging) then
Set objFSO = CreateObject("Scripting.FileSystemObject")
IF objFSO.FileExists(LOG_FILE) Then
Set objFile = objFSO.OpenTextFile(LOG_FILE, forAPPEND)
Else
Set objFile = objFSO.CreateTextFile(LOG_FILE)
objFile.Close
Set objFile = objFSO.OpenTextFile(LOG_FILE, forWRITE)
End If
objFile.Writeline "---------------------"
objFile.Writeline now() & ": " & logtxt
objFile.Writeline "---------------------"
objFile.close
end if
if (debugflag) then
wscript.echo(now() & ": " & logtxt)
end if
end sub
------------------------------
------------------------------
Unassigned computers Script
------------------------------
''WSUS-Unassigned
''
'' by Ryan McLean 11/09/2006
''
''
'' Written in order to notify about unassigned computers
'' instead of approve updates and to add ability to log.
''
'' Inspired by autoapproveupdates.vbs script written by Steven Gill
(http://gatefold.co.uk/wsus)
'' A lot of the code from the above script was used for this one
''
''
''To use:
''Set up a scheduled job to run "cscript c:\WSUS-Unassigned.vbs" and run
with admin rights
''
''
'' -------------------------
'' Configuration
'' -------------------------
''Can use localhost if you have an smtp server locally
EmailDstName = "***@accelrys.com"
EmailReplyToName = "***@accelrys.com"
EmailSrvName = "camdmail01.accelrys.net"
serveradminurl = "http://wsus-cam/WSUSAdmin"
WSUS_Con_STRING = "Provider=SQLOLEDB;Initial Catalog=SUSDB;Data
Source=IT4-CAM\WSUS;Integrated Security=SSPI;Persist Security Info=False"
logging = true
Const LOG_FILE = "test.log"
debugflag = false
strMsgBody = ""
strTitleList = ""
'' -------------------------
'' Constants
'' -------------------------
' Constants for File opening modes
Const forREAD = 1
Const forWRITE = 2
Const forAPPEND = 8
'' -------------------------
'' Identify if updates need to be approved
'' -------------------------
QueryString = "SELECT tbComputerTarget.FullDomainName FROM tbComputerTarget
LEFT JOIN tbTargetInTargetGroup ON tbComputerTarget.TargetID =
tbTargetInTargetGroup.TargetID WHERE tbTargetInTargetGroup.TargetID IS NULL;"
set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
unassigned = 0
While (not rs.eof)
FullDomainName = rs.fields.item("FullDomainName").value
strTitleList = strTitleList & FullDomainName & vbNewline
unassigned = unassigned+1
rs.MoveNext()
Wend
'' -------------------------
'' Send email if new patches are available
'' -------------------------
if (unassigned > 0) then
if (unassigned > 1) then strPlural="s"
strMsgBody = strMsgBody & unassigned & " Computer" & strPlural & " need to
be assigned groups" & vbcrlf
strMsgBody = strMsgBody & vbcrlf & serveradminurl & vbcrlf
strMsgBody = strMsgBody & vbcrlf & strTitleList
' Create the JMail message Object
set msg = CreateOBject("JMail.Message")
msg.Logging = true
msg.silent = true
msg.From = EmailReplyToName
msg.AddRecipient(EmailDstName)
msg.Subject = "[WSUS Server] " & unassigned & " Computer" & strPlural & "
need to be assigned a group"
msg.Body = strMsgBody
if (msg.Send(EmailSrvName)) then
LogIt("Email Successfully sent, " & unassigned & " Computer" & strPlural &
" need to be assigned a group")
else
LogIt("Error sending email!")
Logit(msg.log)
end if
else
LogIt("No unassigned Computers found")
End if
rs.close()
sub LogIt(logtxt)
strMsgBody = strMsgBody & now() & ": " & logtxt & vbcrlf & vbcrlf
if (logging) then
Set objFSO = CreateObject("Scripting.FileSystemObject")
IF objFSO.FileExists(LOG_FILE) Then
Set objFile = objFSO.OpenTextFile(LOG_FILE, forAPPEND)
Else
Set objFile = objFSO.CreateTextFile(LOG_FILE)
objFile.Close
Set objFile = objFSO.OpenTextFile(LOG_FILE, forWRITE)
End If
objFile.Writeline "---------------------"
objFile.Writeline now() & ": " & logtxt
objFile.Writeline "---------------------"
objFile.close
end if
if (debugflag) then
wscript.echo(now() & ": " & logtxt)
end if
end sub
-------------------
END
-------------------