VBScript Help

[Lankio]

I have written a VBScript that queries a list of servers from a text file and produces a list of the services and there status and stores it in database. I need the script to update the tables everytime it runs and to add any new server or services to the database. I cant seem to get the update part of the script to work. If anyone has any ideas I appricate it.

The script is below

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=script;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Services" , objConnection, adOpenStatic, adLockOptimistic

On Error Resume Next
Const ForReading = 1
Set objDictionary = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile _
("servers.txt", ForReading)
i = 0
Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline
objDictionary.Add i, strNextLine
i = i + 1
Loop
For Each objItem in objDictionary
StrComputer = objDictionary.Item(objItem)
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colPrintServices = objWMIService.ExecQuery ("Select * from Win32_Service")

bFoundService = false
For Each objService in colprintServices
if (objRecordSet("ServerName") = strComputer & objRecordSet("ServiceName") = objService.DisplayName) then
bFoundService = true
objRecordSet("ServiceState") = objService.State
objRecordSet("Date") = FormatDateTime(Now(), 2)
objRecordSet("Time") = Time
objRecordSet.Update
end if
Next

if bFoundService = false then
objRecordSet.AddNew
objRecordSet("ServerName") = strComputer
objRecordSet("ServiceName") = objService.DisplayName
objRecordSet("ServiceState") = objService.State
objRecordSet("Date") = FormatDateTime(Now(), 2)
objRecordSet("Time") = Time
objRecordSet.Update
end if
Next
objRecordset.Close
objConnection.Close

Cheers

 

[Lankio]

Great thanks I'll give it a try.

 

[Lankio]

Thanks for your help, it's getting a bit closer. It's now adding a record onto the end everytime and not updating any records. Do you have any ideas?

 

[Lankio]

Hi,

It is being run from a VBS file. I have checked the queries being made and they seem to be correct. I have run the queries on the SQL database and it does return results. Here is an example for the strSQL

"SELECT ServerName FROM Services WHERE ServerName = 'localhost' AND ServiceName = 'Alerter'"

I'm not sure how to check for a positive result but the ObjRS.EOF is empty and I'd assume that should be true or false.

 

[Lankio]

I've changed the objRS.EOF to objRecordSet.EOF and this seems to now trigger the true \false flags. This is still doesnt seem to update the record, the StrSQL is

"UPDATE Services SET ServiceState = 'Stopped, Date = '22/02/2008, Time = '13:35:27 WHERE ServerName = 'localhost' AND ServiceName = 'Alerter'"

 

[Lankio]

I think I've fixed it I added the missing ' to the update line and it now seems work

strSQL = "UPDATE Services SET ServiceState = '" & objService.State & "', " _
& "Date = '" & FormatDateTime(Now(), 2) & "', " _
& "Time = '" & Time & "' " _
& "WHERE ServerName = '" & strComputer & "' AND ServiceName = '" & objService.DisplayName & "'"

Thanks again for your help MrMagoo

 

[Lankio]

No apology required, I was going nowhere fast with it. I hope you feel better now.

Thanks again