thaxor

We’ve been having lots of complaints about sketchy internet on the first floor. It’s always hard to quantify internet problems, is it bandwidth, poor wireless reception, bad AP/Wireless Card, someone complaining for fun, or an actual outage?

In an effort to diagnose it myself with the least amount of work possible I stumbled upon this VB Script which pings a host defined in a text document and outputs it to excel.

I hacked on it a little bit and came up with this:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("list.txt")

count=0

Do While Not (InputFile.atEndOfStream)
	objExcel.Cells(1, 1+count).Value = "Machine Name"
	objExcel.Cells(1, 2+count).Value = "Results"
	count=count+2
	InputFile.ReadLine
Loop
objExcel.Cells(1, 1+count).Value = "Time"
objExcel.Cells(1, 2+count).Value = "Date"

Do While 1
	Set Fso = CreateObject("Scripting.FileSystemObject")
	Set InputFile = fso.OpenTextFile("list.txt")
	count=0

	Do While Not (InputFile.atEndOfStream)
		HostName = InputFile.ReadLine
		Set WshShell = WScript.CreateObject("WScript.Shell")
		Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
		objExcel.Cells(intRow, 1+count).Value = HostName
		Select Case Ping
		Case 0
			objExcel.Cells(intRow, 2+count).Value = "On Line"
			objExcel.Cells(intRow, 2+count).Interior.ColorIndex = 4
		Case 1
			objExcel.Cells(intRow, 2+count).Value = "Off Line"
			objExcel.Cells(intRow, 2+count).Interior.ColorIndex = 3
		End Select
		count=count+2
	Loop

	objExcel.Cells(intRow, 1+count).Value = TimeValue(Now())
	objExcel.Cells(intRow, 2+count).Value = date

	intRow = intRow + 1

	WScript.sleep 30000

	objExcel.Rows(1).Select
	objExcel.Selection.Interior.ColorIndex = 19
	objExcel.Selection.Font.ColorIndex = 11
	objExcel.Selection.Font.Bold = True
	objExcel.Cells.EntireColumn.AutoFit
Loop

All you have to do is create a text document “list.txt” in the same directory as this vb script and it will layout the excel document for you without any modifications. Only thing you may want to change is the delay/sleep I put in on this line “WScript.sleep 30000″ which tells it to wait 30 seconds before continuing.

The best part is that I could feasibly give this to a non-technical tenant use it to troubleshoot their connection problems.
The host list would include something like their AP, the Gateway, google.com and another host thats known to be reliable to ping like 4.2.2.2