''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
''
''
'' -------------------------
'' Configuration
'' -------------------------

''Can use localhost if you have an smtp server locally

usejmail 		= true '' If false will use CDonts

EmailDstName 		= "susadmin@company.com"
EmailReplyToName 	= "susserver@company.com"
EmailSrvName 		= "smtp.company.com"

WSUS_Con_STRING = "Provider=SQLOLEDB;Initial Catalog=SUSDB;Data Source=<YOURSERVERNAME>\WSUS;Integrated Security=SSPI;Persist Security Info=False"

debugflag = true
strMsgBody = ""

'' -------------------------
'' Clear EulaAcceptance table
'' -------------------------

QueryString = "DELETE FROM tbEulaAcceptance"

set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()


LogIt("Eulas Cleared")

'' -------------------------
'' Add in Eulas from updates
'' -------------------------

QueryString = "SELECT EulaID FROM tbProperty WHERE (EulaID IS NOT NULL) GROUP BY EulaID"

set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

While (not rs.eof)
	
	EulaId = rs.fields.item("EulaID").value
	
	Querystring = "INSERT INTO dbo.tbEulaAcceptance(EulaID, AcceptedDate, AdminName) VALUES ('"& EulaID &"', getutcdate(), 'Administrator')"

	set rs_this = CreateObject("ADODB.Recordset")
	rs_this.ActiveConnection = WSUS_Con_STRING
	rs_this.Source = QueryString
	rs_this.CursorType = 0
	rs_this.CursorLocation = 2
	rs_this.LockType = 3
	rs_this.Open()

	LogIt("Added Eula with ID: " & EulaID)

	rs.MoveNext()
Wend

rs.close()


'' -------------------------
'' Accept Eulas and Approve updates
'' -------------------------

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.IsSuperseded = 0)"

set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = WSUS_Con_STRING
rs.Source = QueryString
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()

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

	if (not EulaExplicitlyAccepted) then
	
		'' -------------------------
		'' Accept Eula on update
		'' -------------------------

		Querystring = "UPDATE dbo.tbProperty SET EulaExplicitlyAccepted=1, RequiresReacceptanceOfEula=0 WHERE (RevisionID = '"& RevisionID &"')"

		set rs_this = CreateObject("ADODB.Recordset")
		rs_this.ActiveConnection = WSUS_Con_STRING
		rs_this.Source = QueryString
		rs_this.CursorType = 0
		rs_this.CursorLocation = 2
		rs_this.LockType = 3
		rs_this.Open()
		
		
		LogIt("Eula Accepted for: " & Title )
		
	end if

	
	if (State <> 5) then

		'' -------------------------
		'' Approve update for installation (for unassigned computers group)
		'' -------------------------
	
		Querystring = "exec spDeployUpdate @updateID = '"& UpdateID &"', @revisionNumber = "& RevisionNumber &", @actionID = 0, @targetGroupID = 'A0A08746-4DBE-4A37-9ADF-9E7652C0B421', @adminName = N'Administrator', @isAssigned = 1, @downloadPriority = 1"

		set rs_this = CreateObject("ADODB.Recordset")
		rs_this.ActiveConnection = WSUS_Con_STRING
		rs_this.Source = QueryString
		rs_this.CursorType = 0
		rs_this.CursorLocation = 2
		rs_this.LockType = 3
		rs_this.Open()

		rs_this.close()
		
		LogIt("Approved for Installation: " & Title)

		NumApproved = NumApproved +1
		
	end if
	
	rs.MoveNext()
Wend

rs.close()

	
'' -------------------------
'' Send email if new patches are approved
'' -------------------------

if (NumApproved > 0) then

	if (NumApproved > 1) then strPlural="s"

	strMsgBody = strMsgBody & "Auto Approved by the WSUSAutoApprover http://gatefold.co.uk/wsus" & vbcrlf

	if (usejmail) then

		' 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 & " New Update" & strPlural & " with EULA Approved for Installation for Unassigned Computers group"
		msg.Body = strMsgBody

		if (msg.Send(EmailSrvName)) then
			LogIt("Success sent:" & vbNewline & strMsgBody)
			Logit(msg.log)
		else
			LogIt("Error sending email!")
			Logit(msg.log)
		end if
	
	else
	
		Set iMsg = CreateObject("CDO.Message")
		
		iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = EmailSrvName
		iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
		iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

		'' Uncomment these and set for authenticated SMTP etc
		
		''iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = "My Name"
		''iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = """MySelf"" <myself@example.com>"
		''iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/senduserreplyemailaddress") = """Another"" <another@example.com>"
		''iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
		''iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "domain\username"
		''iMsg.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"

		iMsg.from = EmailReplyToName
		iMsg.to = EmailDstName
		iMsg.subject = "[SUS Server] " & NumApproved & " New Patch" & strPlural & " Approved" 
		iMsg.textbody = strMsgBody
		iMsg.send

		LogIt("Sent:" & vbNewline & strMsgBody)
	
	end if

else
	LogIt("No new patches to approve ") 
End if

sub LogIt(logtxt)

	strMsgBody = strMsgBody & now() & ": " & logtxt & vbcrlf & vbcrlf

	if (debugflag) then
		wscript.echo(now() & ": " & logtxt)
	end if
end sub