Tracking SQL Server Instances across the network


This script is pretty simple but with great power to the DBA´s. How many times did you need to search for SQL Server Instances across the network ? I believe every DBA already had to do this at least once.

With PowerShell this process is , as always, pretty simple to do .

The script :

$verbosepreference= 'SilentlyContinue'

1..254 | foreach { 

     if ($_ -ne 4) { #put the last number of the IP of the machine that is running the script to exclude this machine

         $Ip =  "xxx.xxx.xxx.$_" 

         Write-Verbose "testing ip $($ip)"

         if ((Get-WmiObject Win32_PingStatus -Filter "Address= '$Ip'").Statuscode -eq 0) { 

             $SQLService = Get-service -ComputerName $Ip -Name "MSSQL*" -ErrorAction SilentlyContinue 

             if ($SQLService) { 

                 Write-Output "IP $($IP) Found $($SQLService.count) SQL Server Service(s) Instance(s) `n"  

             } else {

                 Write-Verbose "IP $($IP) does not found any SQL Server Service `n"

             }

         } else {

             Write-Verbose "IP $($IP) does not replied to ping `n"

         }

     }

 }

In the first line I define the range  or  1 to 254.

In the second line I am excluding the IP that is running the script, in my cases is the last number 4 , or xxx.xxx.xxx.4

In the third line I am defining the variable IP to serach in that specific IP. Of course yoou need to change the xxx.xxx.xxx to your network.

In the fourth line I am pinging the IP and in the fifth line searching for any SQL Server Service.

running this script :

image

You can also see the progress , just change the verbose preference to Continue :

$verbosepreference= 'Continue'

 

image

and if you want you can just see the progress and output the Ip that had SQL Server Service to a txt change the line :

Write-Output "IP $($IP) Found $($SQLService.count) SQL Server Service(s) Instance(s) `n" | Out-File c:\temp\Ips.txt -Append

 

Just remembering that you need to run this script in PowerShell as administrator Alegre

Simple, Clean and Fast. Classic Powershell Alegre

#PowerShellLIfeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s