how to read a column from a DB in VBScript

[mvpuiu]

hi, im new in scripting and im trying to make my life easyer with a script...
i made a script that pings some computers in my network every 3 minutes and if the state is changed its sending an email to my email address. but the ip's are put manualy.
i want to create a DB that has 2 columns: ip and computername
the script will read the ip, check if the state is changed and if its changed i want to send me an email with the computername from that row with the ip verified...can somebody help me?

 

[mvpuiu]

its not a problem to read and write in the same file...the problem is that i dont know how to write in the txt file on the 3rd column

 

[markdmac]

You write to the file the same way as you write Up or Down earlier.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[mvpuiu]

please give me an ex how to write "up" in a txt file and txt file to look like this:

192.168.1.2 computer up

 

[PHV]

Have a look here:

http://msdn2.microsoft.com/en-us/library/z9ty6h50.aspx

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

 

[guitarzan]

mvpuiu:

I don't know of a way to overwrite only the third column in a text file. A database / recordset is more suited for this. One way of doing what you want is, when you are ready to save your values, recreate (overwrite) the entire text file with all the information you want. For example:

1. Read the data, perhaps creating three different arrays of the same size; one for IP, one for computer name, and one for status.
2. Do your pings, update "status" accordingly in the status array.
3. Loop through the array and recreate your text file.

 

[mvpuiu]

yes i thought of that too...if its working im gonna let u know

 

[mvpuiu]

i dont know how to recreate the text file...
im writeing in it but in the end i have only one line in it not 15 like i had at the begining

this is the script:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("d:\temp\hosts1.txt", ForReading)
Set objShell = WScript.CreateObject("WScript.Shell")

Const ForReading = 1
Const ForWriting = 2

Dim arrFileLines()
i = 0
Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop
objFile.Close

For Each ip in arrFileLines
a=split(ip," ")
c = 0
x = 0
for c = 0 to 3
If Ping(a(0)) = True then
x = 1
Else
x = 0
End if
x = x + 1
c = c + 1
next

if x <> 0 then
p = "up"
else
p = "down"
end if

dim objfso
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objTextStream
Set objTextStream = objFSO.OpenTextFile("d:\temp\hosts1.txt", ForWriting, True)
If p <> a(2) then
objExec4=objShell.run("D:\Temp\SMTPSend.exe -fvalentin.mihul@mozzartbet.com -tvalentin.mihul@mozzartbet.com -sHost_" & a(1) & "_is_" & p & " -hmail.mozzartbet.com",1,True)
objTextStream.Writeline "" & a(0) & " " & a(1) & " " & p & "" & vbcrlf
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing
Else
objTextStream.Write "" & a(0) & " " & a(1) & " " & p & "" & vbcrlf
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing
End if
next
objFile.Close

Function Ping(strHost)

dim objPing, objRetStatus

set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & strHost & "'")

for each objRetStatus in objPing
if IsNull(objRetStatus.StatusCode) or objRetStatus.StatusCode<>0 then
Ping = False
'WScript.Echo "Status code is " & objRetStatus.StatusCode
else
Ping = True
'Wscript.Echo "Bytes = " & vbTab & objRetStatus.BufferSize
'Wscript.Echo "Time (ms) = " & vbTab & objRetStatus.ResponseTime
'Wscript.Echo "TTL (s) = " & vbTab & objRetStatus.ResponseTimeToLive
end if
next
End Function

 

[guitarzan]

No, I meant something more like this. Read and store everything. Then update status as necessary. Then, write it all back into the same file.

Option Explicit
Const sDataFile = "d:\temp\hosts1.txt"
Const ForReading = 1
Const ForWriting = 2

Dim arrIP(), arrName(), arrStatus()
Dim x

ReadData

For x = LBound(arrIP) To UBound(arrIP)
   If Ping(arrIP(x)) = True Then
      arrStatus(x) = "UP"
   Else
      arrStatus(x) = "DOWN"
   End If
Next 

SaveData

Sub ReadData()
   Dim objFSO, objFile
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFile = objFSO.OpenTextFile(sDataFile, ForReading)
   Dim i, sThisLine, arrThisLine
	
   i = 0	
   Do Until objFile.AtEndOfStream
      sThisLine = objFile.ReadLine 'Get the next line
      'split the array, so we can extract the 3 fields
      arrThisLine = Split(sThisLine, " ")
		
      'redim the arrays that will hold the different fields
      ReDim Preserve arrIP(i), arrName(i), arrStatus(i)
		
      'store the 3 fields for record "i"
      arrIP(i) = arrThisLine(0)
      arrName(i) = arrThisLine(1)
      arrStatus(i) = arrThisLine(2)
	
      i = i + 1
   Loop
   objFile.Close
End Sub


Sub SaveData()
   Dim objFSO, objFile
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Dim sOutputFile, x

   For x = LBound(arrIP) To UBound(arrIP)
      sOutputFile = sOutputFile & arrIP(x) & " " & _
      arrName(x) & " " & _
      arrStatus(x) & vbCrLf
   Next

   Set objFile = objFSO.OpenTextFile(sDataFile, ForWriting, True)
   objFile.Write sOutputFile
   objFile.Close

End Sub

 

[mvpuiu]

sorry guitarzan but im to noob to understand what u wrote can u give me an yahoo messenger id to tell u what i want to do, maybe u can help me with that

 

[guitarzan]

No, that is against forum rules. The posts here can help others with similar questions / problems. Taking the thread offline deprives everyone else of that opportunity.

If you ask specific questions about what you don't understand, I (and others here) would be glad to help.

 

[mvpuiu]

i dont understand how to use the script u send me...i want to ping a location but i want to ping it 4 times/1 script run. each time i have a counter witch is increasing if the ping its true or 0 if the ping is false. after that, is the counter is biger than 0 then the ping is up, if not is down and after that its being write in the array
but i dont know where to implement this in the script u gave me.

 

[guitarzan]

Since your question is general, here's a general explanation of how my code works.

You defined the data file as having 3 fields (ip, name, and status) separated by spaces:
192.168.1.2 computer1 down
192.168.1.3 computer2 up
192.168.1.4 computer3 up

The first thing my code does after declaring some variables is call the "ReadData" function. This function reads the data file line-by-line, splits out the 3 fields, and stores them in three different arrays, (arrIP, arrName, and arrStatus). The end result of ReadData is that you end up with the following array values:

arrIP(0)="102.168.1.2"  arrName(0)="computer1"  arrStatus(0)="down"
arrIP(1)="102.168.1.3"  arrName(1)="computer2"  arrStatus(1)="up"
arrIP(2)="102.168.1.4"  arrName(2)="computer3"  arrStatus(2)="up"

Then, the script uses a For/Next loop to process each record one at a time, and ping the IP addresses one at a time.

For x = LBound(arrIP) To UBound(arrIP)

The loop goes from the lower bound of the array (zero), to the upper bound (two). Same as "for x = 0 to 2". Inside the loop, arrIP(x) refers to the IP address of the record we are at, and Ping(arrIP(x)) will ping that address. The value in arrStatus(x) is updated accordingly. Hint: This is where you will have to add your logic to check for four pings, etc.

Finally, the SaveData function is called, which again loops through the arrays, this time building (one line at a time), a variable called sOutputFile. When the loop is finished, sOutputFile will be exactly what you want your NEW data file to look like. The last few lines write it, overwriting the contents of what is there.

Hope this helps.

 

[mvpuiu]

i understood this part...but i want to send 4 pings in each ip every time i run the script...if ping is 50 % true then arrStatus = up
if ping is under 50 % then arrstatus = down
after that i want to check if the current stus matches the one from the previous script runing. if the status dont match the script send me an email if it does only overwrite the line.

in this moment, my script is runing but its not using a single file(hosts1.txt). for each ip a have created a txt file in witch i have the status...i want to have only a file thats why i want to use ur method

P.S. i tried to add the 4 time pinging to ur script but now i dont know how to compare it with the status of the previous script run

 

[guitarzan]

P.S. i tried to add the 4 time pinging to ur script but now i dont know how to compare it with the status of the previous script run

Check the ping status of arrIP(x). This will include your logic of doing 4 pings, 50% true means up, <50% means down, etc. You indicate that you have already done this.

Then, compare that status (which is the current status) with arrStatus(x) (which is the previously saved status). If they are different, send an email.

Finally, update arrStatus(x) with the current status, so it will get saved in the SaveData routine.

 

[mvpuiu]

something like this?

Option Explicit
Const sDataFile = "d:\temp\hosts1.txt"
Const ForReading = 1
Const ForWriting = 2

Dim arrIP(), arrName(), arrStatus()
Dim x, c, p, s

ReadData

For x = LBound(arrIP) To UBound(arrIP)
c = 0
p = 0
for c = 0 to 3
If Ping(arrIP(x)) = True Then
p = 1
Else
p = 0
End If
p = p + 1
c = c + 1
next
if p > 2 then
arrStatus(x) = "UP"
else
arrStatus(x) = "DOWN"
end if
Next

SaveData

Sub ReadData()
Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(sDataFile, ForReading)
Dim i, sThisLine, arrThisLine

i = 0
Do Until objFile.AtEndOfStream
sThisLine = objFile.ReadLine 'Get the next line
'split the array, so we can extract the 3 fields
arrThisLine = Split(sThisLine, " ")

'redim the arrays that will hold the different fields
ReDim Preserve arrIP(i), arrName(i), arrStatus(i)

if arrstatus(i) <> arrStatus(x) then
objExec4=objShell.run("D:\Temp\SMTPSend.exe -fvalentin.mihul@mozzartbet.com -tvalentin.mihul@mozzartbet.com -sHost_" & arrName(i) & "_is_" & arrStatus(x)& " -hmail.mozzartbet.com",1,True)
end if


'store the 3 fields for record "i"
arrIP(i) = arrThisLine(0)
arrName(i) = arrThisLine(1)
arrStatus(i) = arrThisLine(2)

i = i + 1
Loop
objFile.Close
End Sub

Sub SaveData()
Dim objFSO, objFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim sOutputFile, x

For x = LBound(arrIP) To UBound(arrIP)
sOutputFile = sOutputFile & arrIP(x) & " " & _
arrName(x) & " " & _
arrStatus(x) & vbCrLf
Next

Set objFile = objFSO.OpenTextFile(sDataFile, ForWriting, True)
objFile.Write sOutputFile
objFile.Close

End Sub

Function Ping(strHost)

dim objPing, objRetStatus

set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & strHost & "'")

for each objRetStatus in objPing
if IsNull(objRetStatus.StatusCode) or objRetStatus.StatusCode<>0 then
Ping = False
'WScript.Echo "Status code is " & objRetStatus.StatusCode
else
Ping = True
'Wscript.Echo "Bytes = " & vbTab & objRetStatus.BufferSize
'Wscript.Echo "Time (ms) = " & vbTab & objRetStatus.ResponseTime
'Wscript.Echo "TTL (s) = " & vbTab & objRetStatus.ResponseTimeToLive
end if
next
End Function

 

[guitarzan]

Replace the outer for/next loop with this

Dim sThisStatus 
For x = LBound(arrIP) To UBound(arrIP)
   [green]'Check the ping status of arrIP(x)[/green]
   p = 0
   For c = 0 to 3
      If Ping(arrIP(x)) = True Then
         p = p + 1
      End If
   Next
   If p >= 2 then
      sThisStatus = "UP"
   Else
      sThisStatus = "DOWN"
   End If

   [green]'Then, compare that status (which is the current status) 
   ' with arrStatus(x) (which is the previously saved status).
   [/green]'If they are different, send an email.
   If sThisStatus <> arrStatus(x) Then
      [green]'[i]send email code[/i][/green]
   End If

   arrStatus(x) = sThisStatus

Next

 

[mvpuiu]

i send email with the following :

Set objShell = WScript.CreateObject("WScript.Shell")
objExec4=objShell.run("D:\Temp\SMTPSend.exe -fvalentin.mihul@mozzartbet.com -tvalentin.mihul@mozzartbet.com -sHost_" & arrName(i) & "_is_" & arrStatus(x) & " -hmail.mozzartbet.com",1,True)

but now its returning me an error 800A01F4 variable is undefined: 'objExec4'
can u tell me why? or how to put it in order to work?

 

[guitarzan]

That's probably because I used "Option Explicit" as the first line, which forces you to declare all your variables with a Dim statement, such as:
Dim objExec4

http://msdn.microsoft.com/en-us/library/bw9t3484(VS.85).aspx

Also, arrName(i)??? I think u probably meant arrName(x)...

 

[mvpuiu]

thanks

 

[guitarzan]

Oh, I see where the arrName(i) came from before... in one routine, I use i as a counter, and in another I used x. A little confusing, sorry.

The status check is best done right after the ping. I also made the comparison non-case-sensitive (by using LCase, which forces a string to lower case). This code below should do what you want.

Option Explicit

dim objshell, objExec4
Set objShell = WScript.CreateObject("WScript.Shell")
Const sDataFile = "x:\tt\hosts1.txt"
Const ForReading = 1
Const ForWriting = 2

Dim arrIP(), arrName(), arrStatus()
Dim x, c, p, s

ReadData

Dim sThisStatus
For x = LBound(arrIP) To UBound(arrIP)
   'Check the ping status of arrIP(x)
   p = 0
   For c = 0 to 3
      If Ping(arrIP(x)) = True Then
         p = p + 1
      End If
   Next
   If p >= 2 then
      sThisStatus = "UP"
   Else
      sThisStatus = "DOWN"
   End If

	If LCase(sThisStatus) <> LCase(arrStatus(x)) Then
      objExec4=objShell.run("D:\Temp\SMTPSend.exe -fvalentin.mihul@mozzartbet.com -tvalentin.mihul@mozzartbet.com -sHost_" & arrName(x) & "_is_" & arrStatus(x) & " -hmail.mozzartbet.com",1,True)
   End If
   
   arrStatus(x) = sThisStatus
   
Next

SaveData

Sub ReadData()
   Dim objFSO, objFile
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFile = objFSO.OpenTextFile(sDataFile, ForReading)
   Dim i, sThisLine, arrThisLine
    
   i = 0    
   Do Until objFile.AtEndOfStream
      sThisLine = objFile.ReadLine 'Get the next line
      'split the array, so we can extract the 3 fields
      arrThisLine = Split(sThisLine, " ")
        
      'redim the arrays that will hold the different fields
      ReDim Preserve arrIP(i), arrName(i), arrStatus(i)

     'store the 3 fields for record "i"
      arrIP(i) = arrThisLine(0)
      arrName(i) = arrThisLine(1)
      arrStatus(i) = arrThisLine(2)

      i = i + 1
   Loop
   objFile.Close
End Sub

Sub SaveData()
   Dim objFSO, objFile
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Dim sOutputFile, x

   For x = LBound(arrIP) To UBound(arrIP)
      sOutputFile = sOutputFile & arrIP(x) & " " & arrName(x) & " " & arrStatus(x) & vbCrLf
   Next

   Set objFile = objFSO.OpenTextFile(sDataFile, ForWriting, True)
   objFile.Write sOutputFile
   objFile.Close

End Sub

Function Ping(strHost)

    dim objPing, objRetStatus

    set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
      ("select * from Win32_PingStatus where address = '" & strHost & "'")

    for each objRetStatus in objPing
        if IsNull(objRetStatus.StatusCode) or objRetStatus.StatusCode<>0 then
    Ping = False
            'WScript.Echo "Status code is " & objRetStatus.StatusCode
        else
            Ping = True
            'Wscript.Echo "Bytes = " & vbTab & objRetStatus.BufferSize
            'Wscript.Echo "Time (ms) = " & vbTab & objRetStatus.ResponseTime
            'Wscript.Echo "TTL (s) = " & vbTab & objRetStatus.ResponseTimeToLive
        end if
    next
End Function