Find Disconnected Servers Using PowerShell

Often we find ourselves troubleshooting outages which may have been caused by the physical server or virtual machine being unreachable rather than the database service itself being unavailable.

End-users don’t know this. They simply call and say that the application or database is not working. We may try to access the database via SSMS or SQL Developer. If that fails, we may try to remote into the physical server to investigate, only to be met the following connection error.

At this point, we may be wondering what other servers are down. Multiple servers being down may point towards a networking issue. We then hastily try to ping or remote desktop into other servers to verify connectivity. All of this takes time.

This PowerShell script pings a list of IP addresses. Add all the servers, physical or VM, that you are required to check. A text file is created with the results.
Save the following script with the .ps1 extension e.g server_ping.ps1

$folder_path = 'E:\connection_logs\'
$file_name = 'server_ping'
$currentTime = get-date
$extension = '.txt'
$log_file = $file_name + "_" + $currentTime + $extension
$log_file = $log_file.replace(':', '_')
$log_file = $log_file.replace(' ', '_')
$log_file = $log_file.replace('/', '_')

$log_file = $folder_path + $log_file;

$serversIPs = @(
    '192.168.100.129'
    '192.168.100.136'
    '192.168.100.140'
)

foreach($ip in $serversIPs){
   "ping for " + $ip  | out-file $log_file -append
   test-connection $ip -Quiet | out-file $log_file -append
   "" | out-file $log_file -append
}

You can see in the results that 2 of the servers are unreachable:

The script can be setup as a job to run in the following situations:

• Before the start of the working day
• At intervals. You can even run it every minute of the day because the text file is timestamped which prevents it from being overridden. Just remember to setup a job to cleanup files which are older than a day to avoid cluttering the drive with text files and filling the space.
  
• On demand. If you get a call that a particular service is down, run the script to do a check of all other servers

A good thing about this script is that it runs on your own machine rather than the server. No permission to install the script should be required.

How to I Use the Script

I like to have this report running at 7:30 AM every morning before everyone logs in at 8:00 AM. I created it as a task using Windows Task Scheduler. The job also gets run on-demand if an outage is reported after work starts.

Safraz Hosein
0