Program/script to ping IPs from excel sheet

[comp110]

Can anybody help me with situation?
I have a spreadsheet that has a list of IP addresses, and would like to test the network connectivity via ping to each IP address. If this device is up, ping the next IP address. If the device is down, ping the next IP on that subnet. For example, if the IP is 192.168.0.2, ping 192.168.0.1. If this second device is down, go to the next IP in the spreadsheet. If the second device is up, send an alarm. I'm not sure how this alarm could or should be done. To make it simple, maybe we can put it into a log file. I'd prefer an email somehow though.

So, if the device in the spreadsheet is down, AND the device with this IP+1 is up, I want an alarm.

 

[HDSYSTEMS]

me too..i would like how that can be done

TIA

 

[dm4ever]

What have you tried or have so far?

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[comp110]

I have not tried anything yet, since I am not sure where to start.

 

[GrimR]

here's a start

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\backup\Ping.xls")
 
'******************************************************************
 
strComputer = "."
Set objWMIService = GetObject(_
    "winmgmts:\\" & strComputer & "\root\cimv2")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
 
Set colPings = objWMIService.ExecQuery _
   ("Select * From Win32_PingStatus where Address = '" & _
   objExcel.Cells(intRow,1).Value & "'") 

For Each objStatus in colPings
    If IsNull(objStatus.StatusCode) _
        or objStatus.StatusCode<>0 Then
        WScript.Echo "Computer did not respond." & objExcel.Cells(intRow,1).Value
    Else
        Wscript.Echo "Computer responded." & objExcel.Cells(intRow,1).Value
    End If
Next
intRow = intRow + 1
loop

 

[gurner]

i had a similar idea a while ago and managed to cobble this together

i know a couple of lines are unnecessary but haven't looked at it for ages. plus if you want to see what it is doing, as this one hides until it is finished, changed the first 'objExcel2.Visible = False' to 'objExcel2.Visible = True'

------------------------------------------------------
Const ForAppending = 8

Dim HOST
Dim TARGET
Dim TARGET2
Dim PingResults
Dim PingResult
Dim PingResults2
Dim PingResult2

Set objExcel2 = CreateObject("Excel.Application")
objExcel2.Visible = False
Set objWorkbook2 = objExcel2.Workbooks.Add()
Set objWorksheet2 = objWorkbook2.Worksheets(1)


HOST = InputBox("Enter the computer that will initiate the ping" & VbCrLf & "The Host needs to be Windows 2003 or Windows XP" & VbCrLf & "" & VbCrLf & "if you are pinging a machine/server/website from this machine" & VbCrLf & "you are running this program from, type" & VbCrLf & "" & VbCrLf & "LOCALHOST or 127.0.0.1", "ping test")
TARGET = InputBox("Enter the computer name or IP address that you want to ping", "ping test")
TARGET2 = InputBox("Enter the computer name or IP address that you want to ping", "ping test")

r = 2
s = 2

Do While z<100
Set PingResults = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
HOST & "/root/cimv2"). ExecQuery("SELECT * FROM Win32_PingStatus " & _
"WHERE Address = '" + TARGET + "'")

objWorksheet2.Cells(1,1) = "" + TARGET + ""


For Each PingResult In PingResults
objWorksheet2.Cells(r,1) = PingResult.ResponseTime

Next

Set PingResults2 = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _
HOST & "/root/cimv2"). ExecQuery("SELECT * FROM Win32_PingStatus " & _
"WHERE Address = '" + TARGET2 + "'")

objWorksheet2.Cells(1,2) = "" + TARGET2 + ""

For Each PingResult In PingResults2
objWorksheet2.Cells(r,2) = PingResult.ResponseTime
r = r + 1
s = s + 1
Next

z=z+1
If z>100 Then Exit Do
loop


Set objRange = objWorksheet2.UsedRange
objRange.Select

Set colCharts = objExcel2.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.ChartType = 65
objExcel2.Visible = True

-----------------------------------------------


Gurner