Search This Blog

Thursday, June 6, 2019

Powershell script to find new servers in an AD domain

Powershell script to find new servers in an AD domain
This post is part of a process I'm developing to automatically discover SQL Server instances within an Active Directory domain. Expect a series of several related posts.

You might wonder if I'm reinventing the wheel. In many cases, you'd be right. However, as a consultant and visiting DBA, I have valid reasons for this approach. Luckily, I already possess the necessary scripts, so this endeavor mainly revolves around automating the entire process.

This is part one of the series. It identifies new servers added to AD. At this point, we can't ascertain if any of these servers are SQL Servers. That topic will be addressed in the subsequent blog post at:


https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-sql-server.html


The script below, written in PowerShell, displays the results of the discovery on the console (for up to 100 servers). It also exports the findings to a CSV file named 'new_servers.csv'. Feel free to modify or comment out any part as you see fit.

Before executing this script, kindly review and modify the default values for the variables as necessary.

<#

You can use this to get list of all servers in an AD domain or
new servers added in last X days, or any other properties
you want to filter the results on.

You should not need to be a domain admin or 
need any special permission in the AD.
This might change in the future versions though.


You will need powershell active directory module installed 
on the computer where you are running this script from.

If you are using a Windows 10 machine like I am right now, 
here is a good resource to get the AD module installed.
https://gallery.technet.microsoft.com/Install-the-Active-fd32e541


#>
try
{

# filter by servers added in last n days
# or set this to 0 for all servers
$days_to_search = 30

# if searching in different domain than your current domain, 
# specifiy the domain name between the double quotes
$domain = ""                

if ($domain -eq "")
{
        $domain = Get-ADDomain 
}
else
{
        $domain = Get-ADDomain -Identity $domain
}


$domain_name = $domain.name
$distinguished_name = $domain.DistinguishedName
$domain_controller = (Get-ADDomainController -server $domain_name).HostName

$search_base = "OU=SERVERS," + $distinguished_name
$export_file_name = $env:USERPROFILE + "\Documents" + "\new_servers.csv"


# convert $days_to_search to a negative value
if($days_to_search -lt 0) {$days_to_search = -$days_to_search}


[String](Get-Date) + ": Begin searching for new servers in the AD domain"
"-------------------------------------------------------"

if($days_to_search -lt 0)
{
   $date_filter = (get-date).adddays($days_to_search)
   "Date filter value: " + $date_filter
   $search_filter = {Created -gt $date_filter -and operatingsystem -like "Windows Server*"}
   "Find new computers added in last " + $days_to_search + " days to AD domain (" + $domain_name + ")"

}
else
{
   $search_filter = {operatingsystem -like "Windows Server*"}
   "Find all servers in AD domain (" + $domain_name + ")"
}
"Search Base: $search_base"
"Domain controller: " + $domain_controller


$computers = @()
$computers += (get-adcomputer -SearchBase  $search_base -Properties * -Filter $search_filter -server $domain_controller)

[String](Get-Date) + ": Total Number of Servers Found: " + $Computers.Count

# Display the results on the console 
"Displaying first 100 results...."
$computers | Select-Object Name, 
                           Created, 
                           IPv4Address,
                           OperatingSystem,
                           OperatingSystemVersion  -First 100 | ft -AutoSize


# Exports results to a CSV file
[String](Get-Date) + ": Exporting results to ($export_file_name)...."
$computers | Select-Object Name, 
                           Created, 
                           DNSHostName,
                           IPv4Address,
                           OperatingSystem,
                           OperatingSystemHotfix,
                           OperatingSystemServicePack,
                           OperatingSystemVersion, 
                           IPv6Address,
                           DistinguishedName, 
                           createTimeStamp, 
                           Description | Export-CSV `
                                         $export_file_name -NoTypeInformation `
                                                           -Encoding UTF8


[String](Get-Date) + ": End searching for new computers"

}
Catch
{
    [String](Get-Date) + ": Error occurred"
    throw
   
}

Download this PowreShell script From GitHub

Caveats:

This script is specifically tailored for organizations where all servers are registered in Active Directory (AD). If there are new servers set up as non-AD, standalone units within private DMZs, this script will not detect them.

Additionally, the script operates under the assumption that all servers, SQL Servers included, are registered under the 'SERVERS' Organizational Unit (OU) in AD. Should your organization utilize a different OU, or if you wish to scan all OUs (which might include computers running non-server Windows editions), you'll need to adjust the $search_base variable.

At its current configuration, the script searches one domain at a time. By default, it's set to your current domain. However, it can be adjusted to target any domain within the AD forest, given you have access to it or if there's a trust relationship established between your authentication domain and the target domain. Further development can enhance this script to scan all domains in an AD forest.