PowerShell: Get SQL Servers From AD

Today I’d like to share a quick script that is useful when working with a new domain of SQL servers. It’s also good for anyone who doesn’t know their environment very well or suspects there might be some hidden or rarely used servers. There are probably many ways to do this scattered around the internet, but this method is easy and as long as you have a properly designed Active Directory, you’ll be good to go.

The script isn’t anything special, but it will require the Active Directory PowerShell module. You’ll need to run the script on an existing terminal server or at least be able to successfully import the module. Importing a module is very straightforward and this one should already be available on a terminal server.

Import-Module ActiveDirectory

Once you have the module loaded, you can run the following script.

Get-ADComputer -Filter { Description -LIKE "*sql*" } -Properties Description | SELECT Name, Description

Let’s break this down since this is a short post. First of all, we are running the Get-ADComputer cmdlet. That’s going to search through your Active Directory for a list of computer names. The -Filter parameter is looking for any Description that contains the word SQL. If you want to instead search for a name prefix, you can change the filter to { Name -LIKE "Prefix*" }. The -Properties Description parameter will populate the description of the computers from AD. Without that piece, your descriptions will be blank even though we are specifically selecting them. Finally, we pipe the results to a SELECT since I’m only interested in the name of the computers and their descriptions.

This script relies on your Active Directory having useful descriptions. If your coworkers have been lazy and left those blank or put in less than useful information…it won’t be very effective.


PowerShell: What Computer is that IP?

Oftentimes I receive an alert about a server, but the only information I have to work with is an IP address. Luckily, with the help of PowerShell and Active Directory, I was able to write a script to pull the computer name and some information on what that computer does.

The script is short, but a bit complicated. The first variable acquires the computer name via the IP address.  That alone is enough if you have few servers, but with the number I deal with, oftentimes I need a bit more information.

The second part of the script splits the hostname up, removing any extra information and leaving just the computer name.  Afterwards that value is piped to Get-AdComputer and then formatted as an easy to read table with minimal information. It returns what Organizational Units the computer is in, which should tell you what purpose your mystery IP serves. If you are lucky enough to have a description listed in AD, it will return that as well.

$IP = ""
$HostName = ([System.Net.DNS]::GetHostByAddress($IP)).HostName
$Hostname.Split('.')[0] | 
  Get-AdComputer | 
    FT Name,Description,DistinguishedName -AutoSize