wake up PC's then pass data to an SQL table, then send email notice

[KALASHNICOV]

*** full solution****


It has taken ages for me to finish this, so i thought i would share what i have done. Please use this as a rough guide...

When i created this script, the objective was to apply a wake up script to a select group of networked client PC's at x time in the morning, then record which PC has been started within an SQL table, then notify the systems debt of any failures via email.


I used windows scheduler on one of our servers to achieve this first part:

***script 1 of 3

On Error Resume Next

Set CON = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
Set wObj = CreateObject("ActiveXperts.WOL")
Dim cn, strsql, rsr, myteam, mystate
c = 0

CON.Mode = adModeReadWrite
CON.Open "DRIVER={SQL Server};@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
strsql = "SELECT name, location, replace(mac,':', '-') mac FROM OPENQUERY(ADSI, 'SELECT name, location FROM ''LDAP://OU=?????????,OU=?????,OU=????,OU=????,DC=???,DC=???,DC=???'' where objectClass = ''computer'''), networkadapters n where n.hostname = name ORDER BY name"

RS.Open strsql, CON
RS.Movefirst

Do Until rst.EOF

mac = Trim(RS("mac"))

wObj.WakeUp mac

RS.Movenext
Loop

RS.Close


Set wObj = Nothing
Set CON = Nothing
Set RS = Nothing

****script 2 of 3

the next stage, i needed this script to only pass data to my SQL table based on a time variable. This way, i would know that the PC's had been switched on automatically, and not manually. Which is why i applied this at 5:00 am, when our facility is closed. I applied this script to an existing client log on script in group policy.

Dim timex
Set objNetwork = CreateObject("Wscript.Network")

Timex = Now

TIMEx = Right(TIMEx,8)

'If TIMEx > "04:55:00" And timex < "05:10:00" Then

set cn = CreateObject("ADODB.Connection")
strComputerName = trim(lcase(OBJNETWORK.ComputerName))
cst = "DRIVER={SQL Server}@@@@@@@@@@@@@@@@@@@@@@@@@@@"
cn.ConnectionString = cst
cn.open
set cmd = CreateObject ("Adodb.command")
set cmd.ActiveConnection = cn

cs = "INSERT INTO CLIENTPC_LOG_TEST (COMPUTER, time, turnedon) VALUES ('" & strcomputername & "','" & Now & "', '" & "1" & "')"
cmd.CommandText = cs
cmd.Execute
cn.close
Set cmd = Nothing
Set cn = nothing


'End If
'Tidy Up
Set objnetwork = Nothing


***** script 3 0f 3

This script then checks the "startup" data from SQL, and creates an array. To do this i created a view based on the CLIENTPC_LOG_TEST table i created, which excludes any machines that have turned on. Then passed to an email function, that notifies failure.


Dim cn, rst, c, strsql, Comp()
Set cn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
c = 0
ReDim Preserve Comp(0)
cn.Mode = adModeReadWrite
cn.Open "DRIVER={SQL Server};SERVER=@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@"
strsql = "SELECT name, mac from computer_not_turnon"
rst.Open strsql, cn
rst.MoveFirst
Do Until rst.EOF
'MsgBox rst("Name")
ReDim Preserve Comp(c)
Comp(c) = rst("Name") & " - " & rst("mac")
c = c + 1
rst.MoveNext
Loop
rst.Close
cn.Close
'outstr = "Job Machines that have not turned on "


For i = 0 to UBound(Comp)

OutStr = outstr & chr(13) & Comp(i) & chr(13)


Next
'MsgBox outstr

email "administrator@","user@","WOL Failure notification","Job Machines that have not started" & OutStr

Function email(personfrom, personto, subject,body)
Set objMessage = CreateObject("CDO.Message")
objMessage.From = personfrom
objMessage.To = personto
objMessage.Subject = Subject
objMessage.Textbody = body
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "email IP"
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function

 

[BrooksR]

When I try the code below, I get the error:
"the transport failed to connect to the server"

I'm running this from my PC. Does this require Exchange Server?

Brooks

email "administrator@","user@","WOL Failure notification","Job Machines that have not started" & OutStr

Function email(personfrom, personto, subject,body)
Set objMessage = CreateObject("CDO.Message")
objMessage.From = personfrom
objMessage.To = personto
objMessage.Subject = Subject
objMessage.Textbody = body
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/sendusing")

= 2
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserver")

= "email IP"
objMessage.Configuration.Fields.Item("

http://schemas.microsoft.com/cdo/configuration/smtpserverport")

= 25
objMessage.Configuration.Fields.Update
objMessage.Send
End Function

 

[PHV]

You should replace 'email IP' with the IP address of your smtp server.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886