1

I have got one script which actually checks all servers are able to ping or not, it creates excel file with the UP and Down status of servers and later it sends email if any server is down. I have modified script according to my needs.

e2a2128c-9f91-482f-a7d6-dc6d4be692bf-ps2.PNG
e2a2128c-9f91-482f-a7d6-dc6d4be692bf-ps2.PNG800×350 36.1 KB

I have used task scheduler to run this every 5 mins.

Now problem with this script is, It sending the email every 5 mins even there is no server down.

a8350a6a-8e59-4069-accc-b00b0571baf6-ps1.PNG
a8350a6a-8e59-4069-accc-b00b0571baf6-ps1.PNG779×240 11 KB

But my requirement is it should send email whenever any server is down otherwise mail sending should be skip.

#Below is the script
#$credential = Get-Credential
$credential.Password | ConvertFrom-SecureString | Set-Content “E:\Reports\Ping\Ppassword.txt”

$emailusername = “myemail@domain.com”
$encrypted = Get-Content “E:\Reports\Ping\Ppassword.txt” | ConvertTo-SecureString
$credentials = New-Object System.Management.Automation.PsCredential($emailusername, $encrypted)

$PingResultPath = “E:\Reports\Ping\PingResults.xls”

$ResultExcel = new-object -comobject excel.application

if (Test-Path $PingResultPath)
{
$ResultWorkbook = $ResultExcel.WorkBooks.Open($PingResultPath)
$ExcelWorksheet = $ResultWorkbook.Worksheets.Item(1)
}

else {
$ResultWorkbook = $ResultExcel.Workbooks.Add()
$ExcelWorksheet = $ResultWorkbook.Worksheets.Item(1)
}

$ResultExcel.Visible = $True

$downServerArray = [System.Collections.ArrayList]@()

$ExcelWorksheet.Cells.Item(1, 1) = “MachineName”
$ExcelWorksheet.Cells.Item(1, 2) = “Ping Result”

$Servers = gc “E:\Reports\Ping\ServerList.txt”
$count = $Servers.count

$ExcelWorksheet.Cells.Item(1,1).Interior.ColorIndex = 6
$ExcelWorksheet.Cells.Item(1,2).Interior.ColorIndex = 6

$row=2

$Servers | foreach-object{
$pingResult=$null
$Server = $_
$pingResult = Test-Connection $Server -Count 1 -ea silentlycontinue

if($pingResult){

$ExcelWorksheet.Cells.Item($row,1) = $Server
$ExcelWorksheet.Cells.Item($row,2) = “UP”
$ExcelWorksheet.Cells.Item($row,1).Interior.ColorIndex = 17
$ExcelWorksheet.Cells.Item($row,2).Interior.ColorIndex = 4

$row++}
else {

$ExcelWorksheet.Cells.Item($row,1) = $Server
$ExcelWorksheet.Cells.Item($row,2) = “DOWN”
$ExcelWorksheet.Cells.Item($row,1).Interior.ColorIndex = 17
$ExcelWorksheet.Cells.Item($row,2).Interior.ColorIndex = 3

$arrayID = $downServerArray.Add($Server)

$row++}
}

Format the excel

To wrap the text

$d = $ExcelWorksheet.UsedRange
$null = $d.EntireColumn.AutoFit()

##to set column width and cell alignment
$ExcelWorksheet.Columns.Item(1).columnWidth = 24
$ExcelWorksheet.Columns.Item(2).columnWidth = 24
$ExcelWorksheet.Columns.HorizontalAlignment = -4131
$ExcelWorksheet.rows.item(1).Font.Bold = $True

##to apply filter
$headerRange = $ExcelWorksheet.Range(“a1”,“n1”)
$headerRange.AutoFilter() | Out-Null

$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$enddate = (get-date -f yyyy-MM-dd).tostring()
$timeStamp= get-date -f MM-dd-yyyy_HH_mm_ss
$ResultExcel.ActiveWorkbook.SaveAs( (get-location).tostring() +“" +$timeStamp +”.xlx", $xlFixedFormat)

$ResultExcel.Workbooks.Close()
$ResultExcel.Quit()

Write-Host ’ Number of Servers that are down at the moment : ’ $downServerArray.Count

if($downServerArray.Count -gt 0)
{
$Body =‘body{background-color:lightgrey;}’
$Body = $Body + " Hi Team,

The below mentioned servers are down at the moment. Please check on these servers.

"
$Body = $Body + "

"
foreach($item in $downServerArray)
{
Write-Host $item -ForegroundColor red -BackgroundColor white
$Body = $Body + " "
}
$Body = $Body + “
Server IPs
$item


Regards,
IT Team”

$SmtpServer = ‘smtp.office365.com’
$MailtTo = ‘myemail@domain.com’
$MailFrom = ‘myemail@domain.com’
$Mailcc = ‘myemail@domain.com’
$MailSubject = “Ping Status Result”
Send-MailMessage -To “$MailtTo” -cc “$Mailcc” -from “$MailFrom” -Subject $MailSubject -Body “$Body” -BodyAsHtml -SmtpServer $SmtpServer -UseSsl -Credential $Credentials -port 587

Let me know anyone have any suggestions.

Regards,

Ram

6 Spice ups
2

This whole bit:

if($downServerArray.Count -gt 0)
{
  $Body ='<style>body{background-color:lightgrey;}</style>'
$Body = $Body + " <body> Hi Team, <br /><br /> The below mentioned servers are down at the moment. Please check on these servers. <br /><br /> "
  $Body = $Body + " <table style='border-width: 1px;padding: 0px;border-style: solid;border-color: black;'><th style='background-color:black;color : white; font-weight:bolder;'> Server IPs </th> "
foreach($item in $downServerArray)
{
    Write-Host $item -ForegroundColor red -BackgroundColor white
    $Body = $Body + " <tr style='background-color:red;color:white'><td> $item </td></tr>"
}
$Body = $Body + "</table></br></br> Regards,</br>IT Team</body>"

$SmtpServer = 'smtp.office365.com'
$MailtTo = 'myemail@domain.com'
$MailFrom = 'myemail@domain.com'
$Mailcc = 'myemail@domain.com'
$MailSubject = "Ping Status Result"
Send-MailMessage -To "$MailtTo" -cc "$Mailcc" -from "$MailFrom" -Subject $MailSubject -Body "$Body" -BodyAsHtml -SmtpServer $SmtpServer -UseSsl -Credential $Credentials -port 587

Needs to be wrapped in your " if($downServerArray.Count -gt 0)" code block like this

if($downServerArray.Count -gt 0)
{
  $Body ='<style>body{background-color:lightgrey;}</style>'
  $Body = $Body + " <body> Hi Team, <br /><br /> The below mentioned servers are down at the moment. Please check on these servers. <br /><br /> "
  $Body = $Body + " <table style='border-width: 1px;padding: 0px;border-style: solid;border-color: black;'><th style='background-color:black;color : white; font-weight:bolder;'> Server IPs </th> "
  foreach($item in $downServerArray)
  {
    Write-Host $item -ForegroundColor red -BackgroundColor white
    $Body = $Body + " <tr style='background-color:red;color:white'><td> $item </td></tr>"
  }
  $Body = $Body + "</table></br></br> Regards,</br>IT 
  Team</body>"

  $SmtpServer = 'smtp.office365.com'
  $MailtTo = 'myemail@domain.com'
  $MailFrom = 'myemail@domain.com'
  $Mailcc = 'myemail@domain.com'
  $MailSubject = "Ping Status Result"
  Send-MailMessage -To "$MailtTo" -cc "$Mailcc" -from "$MailFrom" -Subject $MailSubject -Body "$Body" -BodyAsHtml -SmtpServer $SmtpServer -UseSsl -Credential $Credentials -port 587
}

(Actually, it looks like you are just missing the closing bracket for the If block.)

3

First things first, you may find pasting your code in a code formatted block will help visibility for those reading it.

I assume there’s a missing } at the end there to close the if($downServerArray.Count -gt 0) ?
Anyway, assuming that is the case, what’s the output of the script when run manually? As that email section should only be run if the array has more than 0 items, we should inspect to see what’s in that array.

If the missing } is actually in your code, then that end section may well be running regardless, which may be the cause in that case.

1 Spice up
4

Thanks Guys,

Its working now perfect after adding "}’ at the end of my script.

@paul-wilde ​ : Sure, I will use formatted block from next time. Thank you.

5

Just a small addenda to this thread and addressing the missing ‘}’

The latest versions of VS Code with the PowerShell extension does a great job of giving you colour coding - and provides colour coding of opening and closing parens and braces. This makes it really easy to see simple syntax errors. When you move your cursor to an opening parenthesis or brace, you can see highlighted what VS Code thinks is closing one. And you can see it in colour!

A small plea for using a great Interactive Development Environment.

3 Spice ups
6

+1 for VSCode. I’ve taken to using it for a lot more than just PowerShell and it’s completely replaced all of my other text editors at this point (sans Nano, VIM, and very occasionally Visual Studio proper)