Ping Test Your XenDesktop Resources with PowerShell

One of my current adventures is a migration of some of my XenServers to Hyper-v in the home lab. As I was dividing up some NFS storage to a iSCSI server, I found myself bringing up and updating a lot of my infrastructure components and started to think that maybe it was a good time to update an old vb script I wrote years ago to test for availability of several infrastructure components.

In the script below there are a few lines where the formatting gets messed up because of the blogging software. Script is attached but ignore the Pink barred “Unknown Macro” parts. Remove Unknown Macro and the {} and I think you will be set. (or download the script)

So here is the script: PingHostList.ps1

I. Set up the workspace in Excel
II. Set up workbook and worksheet in the spreadsheet.
III. If the workbook has three sheets, remove extra sheets.
IV. Title Row in worksheet
V. Fonts, Bold Colors in title row.
VI. Set the first working row for data at row 2.
VII. Create a working directory in the %appdata% path
VIII. Create the working file PingHostText.txt in working directory.
IX. Open the PingHostText.txt file if it did not exist for population.
X. Parse the file for a list of hosts to ping test.
XI. Perform the ping test.
XII. Change the output based on success and format output.
XIII. Auto fit columns to compensate for hostname or FQDN width.
XIV. Create Totals Row.
XV. Add some documentation to the bottom to show the location of the data file from section VIII

‘# I. Set up the workspace in Excel
$erroractionpreference = “SilentlyContinue”
$objExcelApp = New-Object -comobject Excel.Application
$objExcelApp.visible = $True

‘# II. Set up workbook and worksheet in the spreadsheet.
$objWorkbook = $objExcelApp.Workbooks.Add()
$varSheet1 = $objWorkbook.Worksheets.Item(1)
$varSheet1 = $objWorkbook.Worksheets.Item(1)
‘# III. If the workbook has three sheets, remove extra
$varSheet2 = $objWorkbook.sheets | where {$_.name -eq “Sheet2”}
$varSheet3 = $objWorkbook.sheets | where {$_.name -eq “Sheet3”}
$varSheet3.delete()
$varSheet2.delete()

‘# IV. Title Row in worksheet
$varSheet1.Cells.Item(1,1) = “Machine Name”
$varSheet1.Cells.Item(1,2) = “Ping Status Up”
$varSheet1.Cells.Item(1,3) = “Ping Status Down”
$varSheet1.EntireColumn.AutoFit()

‘# V. Fonts, Bold Colors in title row.
$varSheet1 = $varSheet1.UsedRange
$varSheet1.Interior.ColorIndex = 19
$varSheet1.Font.ColorIndex = 11
$varSheet1.Font.Bold = $True
$varSheet1.EntireColumn.AutoFit($True)

‘# VI. Set the first working row for data at row 2.
$intRow = 2

‘# VII. Create a working directory in the %appdata% path
$strWorkingPath = “$env:appdata\Tokeshi”
if (!(Test-Path -path “$strWorkingPath”))

Unknown macro: {
[IO.Directory]}

‘# VIII. Create the working file PingHostText.txt in working directory.
if (!(Test-Path -Path “$strWorkingPath\PingHostList.txt”))

Unknown macro: {
New-Item -ItemType file "$strWorkingPathPingHostList.txt"
'# IX. Open the PingHostText.txt file if it did not exist for population.
notepad.exe "$strWorkingPathPingHostList.txt"
}

‘# X. Parse the file for a list of hosts to ping test.
$varSheet1olComputers = Get-Content “$strWorkingPath\PingHostList.txt”
foreach ($strComputer in $varSheet1olComputers)
{
$varSheet1.Cells.Item($intRow, 1) = $strComputer.ToUpper()

‘# XI. Perform the ping test.
$objPingTest = new-object System.Net.NetworkInformation.Ping
$varReply = $objPingTest.send($strComputer)
‘# XII. Change the output based on success and format output.
if ($varReply.status -eq “Success”)

Unknown macro: {
$varSheet1.Cells.Item($intRow, 2) = "Online"
$varSheet1.Cells.Item($intRow, 2).Interior.ColorIndex = 4
$varSheet1.Cells.Item($intRow, 2).HorizontalAlignment = -4108
}

else

Unknown macro: {
$varSheet1.Cells.Item($intRow, 3) = "Offline"
$varSheet1.Cells.Item($intRow, 3).Interior.ColorIndex = 3
$varSheet1.Cells.Item($intRow, 3).HorizontalAlignment = -4108
}

$varReply = “”

$intRow = $intRow + 1
‘# XIII. Auto fit columns to compensate for hostname or FQDN width.
$varSheet1.EntireColumn.AutoFit()
}
$varSheet1.EntireColumn.AutoFit()

‘# XIV. Create Totals Row.
$intRow = $intRow – 1
$strCountOnline = “=COUNTIF(B2:B” + $intRow + “, `”Online`”)”
$strCountOffline = “=COUNTIF(C2:C” + $intRow + “, `”Offline`”)”
$intRow = $intRow + 1
$varSheet1.Cells.Item($intRow, 1) = “Totals”
$varSheet1.Cells.Item($intRow, 1).HorizontalAlignment = -4152
$varSheet1.Cells.Item($intRow, 2) = $strCountOnline
$varSheet1.Cells.Item($intRow, 2).Interior.ColorIndex = 4
$varSheet1.Cells.Item($intRow, 2).HorizontalAlignment = -4108
$varSheet1.Cells.Item($intRow, 3) = $strCountOffline
$varSheet1.Cells.Item($intRow, 3).Interior.ColorIndex = 3
$varSheet1.Cells.Item($intRow, 3).HorizontalAlignment = -4108
$varSheet1.Cells.Item($intRow, 4) = “Tested”
$varSheet1.Cells.Item($intRow, 4).HorizontalAlignment = -4152
$varSheet1.Cells.Item($intRow, 5) = “=SUM(B” + $intRow + “:C” + $intRow + “)”
$varSheet1.Cells.Item($intRow, 5).HorizontalAlignment = -4131
$rangeTotal =
$intRow = $intRow + 2

‘# XV. Add some documentation to the bottom to show the location of the data file from section VIII
$varSheet1.Cells.Item($intRow, 1) = “Put host names of ping test targets in PingHostText.txt.”
$intRow = $intRow + 1
$varSheet1.Cells.Item($intRow, 1) = “This file is in $strWorkingPath folder.”
$intRow = $intRow + 1
$varSheet1.Cells.Item($intRow, 1) = “If the file did not exist, then notepad opened it in another window.”

‘# XVI. Show script completion via text output.
Write-Host “RAT says … $env:username Rocks! / … $env:username is the bomb! / … $env:username is `$env:CurrentColloquialismPositive”