VBScript Help

Lankio · 21 Feb 2008 at 09:16

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

MrMagoo · 21 Feb 2008 at 15:18

Hi,

The issue looks as though you aren't looping through the recordset for it to match the server, all your lookups are done on the same result (1st one) from the services table, you could add a objRecordset.MoveFirst and loop, but even then it also has looped through all the services and appears to only add 1 service per machine as this add code is outside of the loop?

I would change the following:

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

To:

Code:

For Each objService in colprintServices
	strSQL = "SELECT ServerName FROM Services WHERE ServerName = '" & strComputer & "' AND ServiceName = '" & objService.DisplayName & "'"
	objRecordset.Open strSQL, objConnection
	If objRS.EOF Then
		strSQL = "INSERT INTO Services(ServerName, ServiceName, ServiceState, Date, Time) " _
		& "VALUES('" & strComputer & "', '" & objService.DisplayName & "', '" & objService.State & "', '" & FormatDateTime(Now(), 2) & "', '" & Time & "')"
	Else
		strSQL = "UPDATE Services SET ServiceState = '" & objService.State & ", " _
		& "Date = '" & FormatDateTime(Now(), 2) & ", " _
		& "Time = '" & Time & " " _
		& "WHERE ServerName = '" & strComputer & "' AND ServiceName = '" & objService.DisplayName & "'"
	End If
	objRecordSet.Close
	objConnection.Execute(strSQL)
Next

You would have to remove the objConnection.Open bit at the top to select from services, and the objRecordset.close at the very end as well though

.

If you are using access though replace the 'around any dates (if the field is set to a date field) with #, if text leave with the '.

Not sure how much help that is, got a headache and tired, just taking a break from doing work

Lankio · 21 Feb 2008 at 15:41

Great thanks I'll give it a try.

MrMagoo · 21 Feb 2008 at 18:39

Oops, meant the objRecordset from the top, not the connection! see was tired/headache

hope it worked (other than telling you to remove a bit thats needed).

Lankio · 22 Feb 2008 at 12:38

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?

MrMagoo · 22 Feb 2008 at 12:46

Hi,

How is this being run in a .vbs file? to debug it make it output the sql statements to a messagebox so you can see the queries being run and if a result is found, appears as if it isn't finding a match if it just adds entries but doesnt update any?

if you can get a list of the queries run (the SELECT one with na,e/service passed to it, then look for the corresponding entry in the db and see if they match?

Lankio · 22 Feb 2008 at 13:28

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 · 22 Feb 2008 at 13:35

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 · 22 Feb 2008 at 13:51

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

MrMagoo · 22 Feb 2008 at 14:08

Hi,

Glad it's working

, sorry for that, I use objRS for recordsets, just slipped it in by mistake instead of the objRecordset

, also sorry for missing off the ' closing bits, like I said, had a headache all day

Lankio · 22 Feb 2008 at 14:43

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

Thanks again