This is actually part of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts.
In case you are wondering if I am reinvesting the wheel here, you are right, in most cases. As a consultant and visiting DBA, I have good reasons to resort to this. Fortunately I already had the scripts so this is more about automating the whole process.
This is part 1 in the series. It finds new servers added to the AD. At this stage, we would not know if any of those servers are SQL Servers. That will be in my next blog!
It will display the results of discovery to the console as well as export to CSV. Please feel to comment/change anything you would like.
Before trying this script, please review and adjust the default values for the variables.
<#
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
You would need a normal AD account to be able to search AD. However, you don't need to be a domain admin or need any special permission in the AD.
#>
"Start: "+ (Get-Date)
try
{
# 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
$logging_level=1
$search_base = "OU=SERVERS," + $distinguished_name
$export_file_name="new_servers.csv"
$days_to_search =-7 # this value needs to be negative integer
if ($days_to_search -ge 0) {throw "Value for variable $days_to_search must be a negative integer in days."}
"Begin searching for new servers in the AD domain: "+ (Get-Date)
"-------------------------------------------------------"
$date_filter = (get-date).adddays($days_to_search)
"Date filter value: " + $date_filter
"Find new computers created in last " + $days_to_search + " days in domain " + $domain_name + " using domain controller " + $domain_controller+"....."
$computers=get-adcomputer -SearchBase $search_base -Properties * -Filter {Created -gt $date_filter -and operatingsystem -like "*windows*"} -server $domain_controller
"Total Number of Servers Found: " + $Computers.Count
# Display the results on the console
$computers | Select-Object Name, Created, createTimeStamp, Description,DNSHostName,DistinguishedName,IPv4Address,IPv6Address,OperatingSystem,OperatingSystemHotfix,OperatingSystemServicePack,OperatingSystemVersion | ft -AutoSize
# Exports results to a CSV file
$computers | Select-Object Name, Created, createTimeStamp, Description,DNSHostName,DistinguishedName,IPv4Address,IPv6Address,OperatingSystem,OperatingSystemHotfix,OperatingSystemServicePack,OperatingSystemVersion | Export-CSV $export_file_name -NoTypeInformation-EncodingUTF8
"End searching for new computers: "+ (Get-Date)
"------------------------------------------------------"
"Completed: "+ (Get-Date)
}
Catch
{
"Error occurred: " + (Get-Date)
throw
}
Caveats:
As you can guess, this script only works if in your organization all the servers must be in AD. If the new servers are setup as non-AD, stand alone servers in some private DMZs, this script won't find them.
Also, it assumes that all servers, including SQL Servers, are added to the SERVERS OU in AD. If your organization uses a different OU or you would like to search all OUs (including computers running non-server editions of windows), just update the $search_base variable.
Also, currently this script can only search one domain at a time, which is by default your current domain but you could also specify any domain in the AD forest as long you have access to that domain or if there is trust setup between your authentication domain and that domain. This script can be enhanced to search all domains in an AD forest.
