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?

 

[markdmac]

You have not given much info here. What kind of a database are you talking about? SQL, Access, Excel, txt file, CSV?

Do a quick search on using ADO to get quick examples on how to use VBScript to read info from a database.

If you want to just read from a text file, refer to my FAQ: faq329-4871

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]

i want something simple like a txt file and ill like to look like this:

ip computername
192.168.1.4 computer1
192.168.1.10 computer2
192.168.1.100 computer3

and i want to ping the ip's and if 192.168.1.10 is not responding to send me an email with the text: "computer2 is not responding"

in this moment is 192.168.1.10 is not responding its sending me "192.168.1.10 is not responding" and for 20 computers its a little bit hard to remember all the ip addresses

 

[markdmac]

Did you review the FAQ I linked to?

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]

yes...but i didnt understand much...

 

[markdmac]

Well, it it I give a function to perform a ping on computers. I also show how to read a text file.

You need to do a little research on your own to understand the basics of VBScript. I've given you samples of all the elements that you need. You need to take the step to learn how to use the technology though.

I recommend the MSPress book Windows Scripting Self Paced Learning Guide by Ed Wilson as a starting point.

http://www.amazon.com/Microsoft®-Wi...=sr_1_1?ie=UTF8&s=books&qid=1247937936&sr=8-1

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]

i know how to read from a txt file...i know how to write into one..i know how to ping...i dont know how to read a variable and from a row in a txt file and to return a variable from the same row but another column

 

[markdmac]

Read the text line. Then use a split command.

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]

can u tell me where im wrong?
this is the script:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("d:\temp\hosts.txt", ForReading)

Const ForReading = 1

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)

Set objShell = WScript.CreateObject("WScript.Shell")

Dim strHost

strHost = Wscript.Arguments(0)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("d:\temp\" & a(0) & ".txt", ForReading)

strLinenew = objFile.Readline


if Ping(strHost) = True then
p = "up"

else
p = "down"

end if

const fsoForWriting = 2

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim objTextStream
Set objTextStream = objFSO.OpenTextFile("d:\temp\" & a(0) & ".txt", fsoForWriting, True)

if p <> strLine then

objExec4=objShell.run("D:\Temp\SMTPSend.exe -fmonitorizare@mozzartbet.com -tmvpuiu@yahoo.com -sHost_" & a(1) & "_is_" & p & " -hmail.mozzartbet.com",1,True)

objTextStream.Write "" & p & ""
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing

Else

objTextStream.Write "" & p & ""
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing

end if

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

Next

in hosts.txt i have this:
192.168.1.20 computer1

in 192.168.1.20.txt i have:
up

when i try to execute its returning a syntax error in line 68 char 1 but i dont find the error...

 

[markdmac]

I don't follow all of your logic because the script is kind of a mess with redeclaring the same objects over and over but I think this is what you were shooting for.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("d:\temp\hosts.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," ")
	If Ping(a(0)) = True then
        p = "up"
	Else
        p = "down"
	End if

	Dim objTextStream
	Set objTextStream = objFSO.OpenTextFile("d:\temp\" & a(0) & ".txt", ForWriting, True)
	If p <> "up" then
		objExec4=objShell.run("D:\Temp\SMTPSend.exe -fmonitorizare@mozzartbet.com -tmvpuiu@yahoo.com -sHost_" & a(1) & "_is_" & p & " -hmail.mozzartbet.com",1,True)
		objTextStream.Write "" & p & ""
		objTextStream.Close
		Set objTextStream = Nothing
		Set objFSO = Nothing
	Else
		objTextStream.Write "" & p & ""
		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

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]

for all the ips i made a txt file were the script put the last state... because if im checkig if its up then the script will send me hundreds of emails until the connection is up again...if its checking last state will send me a message when its down and a message when its up again...thats why i need to read from another file

 

[markdmac]

That doesn't make sense to me. How often are you running the script?

I'd use a dictionary object to loop through your text file once and then store the state. You could eliminate the need for writing a file for each server that way.

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]

script runs every 5 minutes

 

[mvpuiu]

now everything its working just fine but can u tell me something...how can i create a txt file witch is not empty and has the string "down" in it ?because i didnt find in any tutorial this...

 

[mvpuiu]

i found a way to write something in the new txt file but now i dont know what to do
i have a txt file with ip addresses and names. i want to put in it another column with the state of connection witch is up or down. i know how to read it thanks to u but i dont know how to write on the 3rd column in the txt file... can u help me?

 

[markdmac]

Does the computer you are running the script from have excel on it? If so why not use Excel which is intended for this kind of data?

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]

because i dont want to depend on ms office...a txt file is read by any windows workstation without installing any more programs

 

[wvdba]

you probably need two files: one that you read ip and name from it. the second, would be a file that you write to, ip, name, and state (up/down). work on that.

 

[mvpuiu]

the program its supose to do this:

i have in a file 3 variables: ip, name and state

the script reads an ip from a file, send a ping in that ip, check if the state of the ping is the same with the state in the file and if not sends an email and after that writes in the file the ping state for being verified next time the script runs.

thats way i need to write on the 3rd column in tha txt file.

 

[MasterRacker]

It might still be easier to use 2 files. Start with input.txt - scan through it, pinging for status, writing the results to output.txt. At the very end, delete input.txt and rename output.txt to input.txt.

This way you don't have to keep track of your position for reads and writes in the same file.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]