#192500 - 2009-02-25 05:35 AM
Ping Computer VB to KIX
|
Saleem
Hey THIS is FUN
Registered: 2001-04-11
Posts: 280
Loc: UAE
|
I am trying to convert this VB script to kiX, i am gettting this error message
ERROR : IDispatch pointers not allowed in expressions! Script: D:\kix\pingvb.KIX Line : 10
Please help!!!
$objExcel = CreateObject('Excel.Application')
$objExcel.Visible = True
$objExcel.Workbooks.Add
$intRow = 2
$objExcel.Cells(1, 1).Value = "Machine Name"
$objExcel.Cells(1, 2).Value = "Results"
$Fso = CreateObject('Scripting.FileSystemObject')
$InputFile = $fso.OpenTextFile('d:\kix\MachineList.Txt')
Do While NOT ($InputFile.atEndOfStream)
$HostName = $InputFile.ReadLine
$WshShell = WScript.CreateObject('WScript.Shell')
$Ping = $WshShell.Run("ping -n 1 " $HostName, 0, True)
$objExcel.Cells($intRow, 1).Value = $HostName
Select Case $Ping
Case 0 $objExcel.Cells($intRow, 2).Value = "On Line"
Case 1 $objExcel.Cells($intRow, 2).Value = "Off Line"
EndSelect
$intRow = $intRow + 1
Loop
$bjExcel.Range("A1:B1").Select
$objExcel.Selection.Interior.ColorIndex = 19
$objExcel.Selection.Font.ColorIndex = 12
$objExcel.Selection.Font.Bold = True
$objExcel.Cells.EntireColumn.AutoFit
_________________________
“I’ll not change you unless you don’t have intention to change yourself”
--H:Quran
|
Top
|
|
|
|
#192519 - 2009-02-25 02:01 PM
Re: Ping Computer VB to KIX
[Re: Saleem]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
Don't know why we're all reinventing the wheel, especially for something that's effectively pointless in the current concept (see my comments below on Ping). Maybe 15 years as a technology instructor makes me look at things from a different perspective, but found myself shaking my head this morning.
First rule of any project - define the goal and then outline the process. Write the process down, not in code, so you can read it and see if it makes sense.
Goal: We want to obtain a list of computers in the network at a given point in time and identify their operational status - Up or Down.
Process - We need to:
- Read a file to get a list of hostnames - ReadFile() would return them in an array, easy to enumerate with a "For Each" loop.
- Ping a host once and determine if it responds. Well, this is pointless for the desired goal.. A computer may not reply if it is busy, the routers may drop ICMP packets if they are busy, so there's a fair chance you'll not get a reply with just one ping - FALSE NEGATIVE. Then, most newer PC hardware will reply to a ping once the network hardware has been initialized by loading an O/S, even if the O/S hangs. So, you get a reply from the NIC from an effectively dead computer - FASLE POSITIVE.
If you must use Ping(), the functions that I use will ping a number of times to account for the low priority of ICMP, but will exit after the first succesful reply. The result is fast and accurate. I usually use that to determine if a computer can reply, and then make some other type of connection, such as reading the service list on a Windows computer (WMISvcMgr('list', ,,'HostName') or making some other type of socket connection to *nix systems to see if it's actually "alive". This requires more advanced capabilities than what Kix offers today, so I use a Socket Ping utility I wrote in Perl for the *nix systems. KixForms has socket support, but it's currently broken. The point is - you must actually COMMUNICATE to the host O/S to determine if it is "up" or "down" - something Ping can't really do.
A combination of Ping() and WMISvcMgr() will provide accurate results with minimal delay. - Determine the result and write to an Excel file - the xlLib UDF library exists with all the capabilities to read/write/format Excel files with minimal effort or understanding of COM.
I'm not picking on Saleem - asking questions is how we all learn, but gee-whillikers, guys! This can be done in a few lines of new code, encouraging the use of proven, tested UDFs. If you're a new coder, you can treat the UDF as a "black box", just as any other built-in function, and get your code operational quickly. Then, you can go back and examine the UDFs and really get an education in coding by picking apart that logic - slowly and methodically. These are luxuries you may not have when under pressure to get a job done, but are critical to expanding your knowledge. The logic, and even the coding style found in UDFs gives you a broader knowledge base than any book can provide.
I also keep referencing my Kix library. Most of the functions have been posted here on KORG, but I keep my library up to date, and the latest versions from my production library are formatted and pushed to the web site nightly. These functions meet our internal coding standards, so even if the function was written by someone else, it will often have many more comments explaining the what and why of the logic.
Here's some basic code to put this together with the UDFs I've mentioned. It's PSEUDO-CODE, meaning it's the concept, untested, and makes assumptions that you'll locate and load the needed UDFs and add some good practices like variable declarations and error checking. All but the ReadFile() UDF are on the resource pages of my web site, updated directly from my active development library, so much searching is eliminated.
Glenn
; make sure followint UDFs are either CALLed or pasted into the script:
; ReadFile
; Ping
; xlLib
; WMISvcMgr
; good coders declare their vars here... ;)
; Prepare Excel
$oXL = xlInit() ; initialize Excel
xlBookCreate(xlBookCreate($oXl,1) ; create a workbook
; verify function syntax
$Hosts = ReadFile('myListOfHosts.txt')
$Row = 2 ; row of first Excel data, after any headers
; enumerate hosts
For Each $Host in $Hosts
If Ping($Host) ; got a ping reply, so do effective test
$Junk = WMISvcMgr('list',,,$Host)
If Not @ERROR ; got a reply, so computer is alive *1
$Data = $Host, 'Responds'
xlRangeValue($oXl, 'A'+CStr($Row), $Data) ; write in Col A, starting in $Row
$Row = $Row + 1 ; increment row pointer
EndIf
Else ; no response at all
$Data = $Host, 'Fails'
xlRangeValue($oXl, 'A'+CStr($Row), $Data)
; might want to consider formatting the data - red text for fail?
$Row = $Row + 1 ; increment row pointer
EndIf
Next
; prolly want to write some header cells before you exit
xlFile($oXL, 1, 'log.xls') ; write the excel file
xlQuit($oXl) ; close the excel session
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#192522 - 2009-02-25 02:42 PM
Re: Ping Computer VB to KIX
[Re: Glenn Barnas]
|
Allen
KiX Supporter
Registered: 2003-04-19
Posts: 4545
Loc: USA
|
I feel like I just took a 50 year journey back to the land of "Leave it to Beaver". Glenn are you Ward or Eddie Haskel?
|
Top
|
|
|
|
#192558 - 2009-02-26 01:12 PM
Re: Ping Computer VB to KIX
[Re: Saleem]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
Saleem,
No apology needed!
The first thing would be to determine if you are actually getting data to write to the file. The best thing would be to write some diagnostic messages, such as:
Right after "If Ping($Host)", you could add " 'got Ping reply from ' $Host ? " - this would tell you if the computer replied.
After the "$Junk = WMISvcMgr('list',,,$Host)" line, you could add "UBound($Junk) + 1 ' services were found' ?" - this will tell you how many services were reported by the remote system. Zero would indicate a problem that may not generate an error.
If those prove that your logic related to querying the hosts is working, then I'd focus on the Excel write logic. Since this is a new set of functionality for you, I'd recommend that you write a separate small script just to test writing to Excel. Verify that the commands are correct without the complexity of your actual program.. for example
oXL = xlInit() ; initialize Excel
xlBookCreate(xlBookCreate($oXl,1) ; create a workbook
For $Row = 2 to 11 ; write 10 rows of test data
$Data = 'ThisHost', 'Responds'
xlRangeValue($oXl, 'A'+CStr($Row), $Data) ; write in Col A, starting in $Row
Next
xlFile($oXL, 1, 'log.xls') ; write the excel file
xlQuit($oXl) ; close the excel session
Did this create a spreadsheet file with ten lines of data across two columns? Is the data in the range A2:B11?
By creating this simple test, you can verify that the core logic used to update the spreadsheet is functioning. If it fails, you have a very small set of commands to troubleshoot. Once you find the error, you can correct it in your main script and verify the change.
I don't know your scripting proficiency level, but the biggest challenge most of my students have is trying to troubleshoot their entire script at once. I always break things down into small pieces based on (lack of) familiarity or logic complexity. My current project has half a dozen small script segments to test new concepts or verify logic outside of the main script.
I also like to use the Msg() library that I wrote.. the Dbg('message') function will only output messages when the global var $DEBUG is true. Makes it easy to use debugging messages and then turn them off when putting a script into production.
Glenn
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#192662 - 2009-03-01 01:30 PM
Re: Ping Computer VB to KIX
[Re: Glenn Barnas]
|
Glenn Barnas
KiX Supporter
Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
|
OK - missed something in my example.. The xlRangeValue can accept a simple value when writing ONE cell, but when writing a range, it requires an Array of Arrays. The outer array represents the rows, the inner array the columns in each row. Since we had only one row, my example was a simple array.
There was also a typo, probably intoduced when I answered the phone in the middle of my post and came back.. I had typed the name of the function twice.
Adding an "@SERROR ?" after the xlRangeValue() function displayed an error, which lead me to read the header info for that function in more detail. The solution is rather simple, but not obvious: Change the Dim statement from to Then, by adding the "[0]" array reference to the data assignment, we have an array of arrays, which makes the function happy. The file is now generated.
One key thing to point out - note that even though $Data was an array, and now it's a multi-dimensional array, we don't use any element references when we pass it to the functions (ie - we declare $Data[0], we put values into $Data[0], but we pass $Data, not $Data[0]). This syntax indicates that we want to pass the ENTIRE array to the function and not one element.
Here's the updated example code, for testing the xlLib and for your process.
Glenn
xlLib test script:Break On
Dim $Data[0] ; need to declare as an array
$oXL = xlInit() ; initialize Excel
$oWB = xlBookCreate($oXL, 1) ; create a workbook
For $Row = 2 to 11 ; write 10 rows of test data
$Data[0] = 'ThisHost', 'Responds' ; create an array in array element 0
$Pos = 'A'+CStr($Row) ; extracted this to be able to display value
'writing to ' $Pos ?
xlRangeValue($oXL, $Pos, $Data) ; write in Col A, starting in $Row
@SERROR ? ; display the result while debugging
Next
xlFile($oXL, 1, 'H:\log.xls') ; write the excel file
xlQuit($oXL) ; close the excel session Your project code; make sure following UDFs are either CALLed or pasted into the script:
; ReadFile
; Ping
; xlLib
; WMISvcMgr
; good coders declare their vars here... ;)
Dim $Data[0] ; need to declare as an array
; Prepare Excel
$oXL = xlInit() ; initialize Excel
$0WB = xlBookCreate($oXl,1) ; create a workbook
; verify function syntax
$Hosts = ReadFile('myListOfHosts.txt')
$Row = 2 ; row of first Excel data, after any headers
; enumerate hosts
For Each $Host in $Hosts
If Ping($Host) ; got a ping reply, so do effective test
$Junk = WMISvcMgr('list',,,$Host)
If Not @ERROR ; got a reply, so computer is alive *1
$Data[0] = $Host, 'Responds'
xlRangeValue($oXl, 'A'+CStr($Row), $Data) ; write in Col A, starting in $Row
$Row = $Row + 1 ; increment row pointer
EndIf
Else ; no response at all
$Data = $Host, 'Fails'
xlRangeValue($oXl, 'A'+CStr($Row), $Data)
; might want to consider formatting the data - red text for fail?
$Row = $Row + 1 ; increment row pointer
EndIf
Next
; might want to write some header cells before you exit
$ = xlFile($oXL, 1, 'log.xls') ; write the excel file
xlQuit($oXl) ; close the excel session
_________________________
Actually I am a Rocket Scientist!
|
Top
|
|
|
|
#192676 - 2009-03-02 08:20 AM
Re: Ping Computer VB to KIX
[Re: Glenn Barnas]
|
Saleem
Hey THIS is FUN
Registered: 2001-04-11
Posts: 280
Loc: UAE
|
Thanx Glenn Barnas
This time it did the Job !!
made some slight ammenmends
Break on
Call "d:\kix\udf\ReadFile.udf"
Call "d:\kix\udf\ping.udf"
Call "d:\kix\udf\xlLib.udf"
Dim $Data[0]
$oXL = xlInit()
$0WB = xlBookCreate($oXl,1)
$Hosts = ReadFile('d:\kix\MachineList.txt')
$Row = 2
$head = 1
$header1 = 'Computer Name'
$header2 = 'Responce'
xlRangeValue($oXl, 'A'+CStr($head), $header1)
xlRangeValue($oXl, 'B'+CStr($head), $header2)
For Each $Host In $Hosts
If Ping("$Host",0,5,1000)=1
$Data[0] = $Host, 'Responds'
xlRangeValue($oXl, 'A'+CStr($Row), $Data)
$Row = $Row + 1
Else
$Data[0] = $Host, 'Fails'
xlRangeValue($oXl, 'A'+CStr($Row), $Data)
$Row = $Row + 1
EndIf
Next
$ = xlFile($oXL, 1, 'd:\kix\log.xls')
xlQuit($oXl)
Thanx again Glenn
_________________________
“I’ll not change you unless you don’t have intention to change yourself”
--H:Quran
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 329 anonymous users online.
|
|
|