Powershell script to find SQL Server instances on remote servers

This is regarding finding the SQL Server database services/instances on remote computers, not any other SQL Server components like SSRS, SSIS, Full-Text services etc. maybe installed there...

But, first, if you are just interested in looking up SQL Server services (i.e., database instances) on a single remote computer, you can simply use this PowerShell one-liner:

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" `
              -ComputerName "SQLSERVERVM1" |
              Format-Table -Property PSComputerName, Name, StartMode, State, Status

You can even specify multiple remote computers, each separated by a comma (e.g. "SQLSERVERVM1", "SQLSERVERVM2")

---

This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain, more specifically, new SQL Server  instances added to any existing server or on newly installed servers.  The process is supposed to be for organizations with very large number of computers where you don't want to scan the entire AD everyday to see if any new SQL Servers are installed.

The idea is to:

.

• Get list of all servers in the AD and store the results in to a SQL table
• Get servers with SQL Server installed and store the results into a SQL table
• Compare the list with the previous list and send a report to DBAs

You can find the part 1 of this blog series at the following link:

https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html

Please note that this part does not extend to connecting and discovering SQL Server-specific configuration details such as version, edition, or other instance-level parameters.

---

I will be using the CSV file (new_servers.csv) generated by the PowerShell script mentioned in the above post. You can also create your own text/CSV file with list of servers in it, with Name as the name of the first column, it could be the only column in it.

In below PowerShell script all I am doing is to check if the remote servers have sql server instance Winodws services setup and their current status. I am not checking yet whether I have access to them or what version of sql servers these instances are running. That will be in the next post in this series!

Additionally, in this post I am also inserting the collected information into a sql staging table.

---

What follows are PowerShell and SQL scripts that you can copy/paste, review, analyze and customize to fit your needs.

The PowerShell script to find SQL Server instances on remote servers:

<#


This powershell script uses WMI to connect to the each server and 
check windows services that matches %%sqlservr.exe%% pattern.
Therefore in order for this to work you would need to have access 
to the servers otherwise it will throw Access Denied errors. 
However since I am getting the list of servers to check from a CSV, 
it will continue on to the next server after the errors.


At the end it displays list of servers it successfully connected 
to and a separate list where it errored out.


It also exports the list of sql instances it discovered to a CSV file.

By default it uses the connected users credentials.
Though, there is option ($user variable) to specify a different 
credentials (Windows).  The password field is in plain text so 
I am not a big fan of it.

#>
(Get-Date).ToString() + ": Begin" 
try
{

        $user = ""           # Should be in Domain\UserName format
        $pass = ""
        

        if ($user -eq "") { $user = $Null}


        # If user/pass pair is provided, authenticate it against the domain
        if ($user-ne $Null)
        {
            "Authenticating user $user against AD domain"
            $domain = $user.Split("{\}")[0] 
            $domainObj = "LDAP://" + (Get-ADDomain $domain).DNSRoot 
            $domainObj
            
            $domainBind = New-Object System.DirectoryServices.DirectoryEntry($domainObj,$user,$pass)
            $domainDN = $domainBind.distinguishedName 
            "domain DN: " + $domainDN
            
            # Abort completely if the user authentication failed for some reason
            If ($domainDN -eq $Null) 
               {
                       "Please check the password and ensure the user exists and is enabled in domain: $domain"
                       throw "Error authenticating the user: $user"
                       exit
               }
            else {"The account $user successfully authenticated against the domain: $domain"}

            $passWord = ConvertTo-SecureString -String $pass -AsPlainText -Force
            $credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $passWord
        }

        $csv_file_name = "new_servers.csv"
        $CSVData = Import-CSV $csv_file_name
        $export_file_name = "sql_server_instances.csv"
        $csv_row_count = $CSVData.Count
        (Get-Date).ToString() + ": Total rows in the CSV file: " + $csv_row_count
        $servers = $CSVData.DNSHostName

        $SqlInstancesList = @()
        $ErrorServers = @()
        ""
        $servers
        ""
        
        # iterate through each server and search for sql services on them

        foreach($server in $servers) 

        { 

            ""
            "Searching for SQL Server DB services on: $server"
        try
        {

                If (-Not (Test-Connection -ComputerName $server -Count 2 -Quiet))
                    {Throw "Invalid Computer Name: $server"}

                If ($user-ne $Null)
                   {$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -credential $credentials  -ComputerName $server -ErrorAction Continue}
                Else
                   {$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $server -ErrorAction Continue}
                
                $SqlInstancesList += $SqlServices
                
                "Number of SQL instances found on $server : " + $SqlInstancesList.Count | Write-Host -ForegroundColor Green
        }
        catch
        {
                # even though error occured, it will continue to the next server
                $em = $_.Exception.Message
                "Skipping $server because an error encountered ($em):" | Write-Host -ForegroundColor Yellow
                $ErrorServers += $server + " (" + $em + ")"
               
        }
        } 

        # if there were any errors with any of the servers, print off names of those servers along with the error message/reason
        if ($ErrorServers.Count -gt 0)
        {
                ""
                "Error when looking up SQL Instances on following servers:"  | Write-Host -ForegroundColor Red
                "--------------------------------------------------------"
                $ErrorServers
        }

        ""
        "EXPORTING TO FILE: $export_file_name"
        $SqlInstancesList | select-object -Property PSComputerName, @{n="SqlInstance";e={$_.Name -replace "MSSQL\$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | Export-CSV $export_file_name -NoTypeInformation -Encoding UTF8

        ""
        "SQL Instances Found:" | Write-Host -ForegroundColor Green
        "--------------------"

        Import-Csv -Encoding UTF8 -Path $export_file_name | ft -AutoSize
        (Get-Date).ToString() + ": Complete" 
}
 
Catch
{
    (Get-Date).ToString() + ": Error Occurred" 
     throw  
}

PowerShell script to load the collected information into sql table:

<#


This too uses the connected users credentials to connect to 
sql server instance.


Since I am loading data into a staging table, this first 
truncates that table then loads the data into it.

#>


<# Since the following script doesn't do error checking
   Lets make sure it stops if an error occurs with any
   of the commands. #>

$ErrorActionPreference = 'Stop'
(Get-Date).ToString() + ": Begin Loading data into sql staging table" 

$sql_instance_name = 'SQLVM01\SQL2016AG01' 
$db_name = 'AdminDBA'
$destination_schema_name = 'dbo'
$destination_table_name = 'sql_server_instances_stage'
$export_file_name = 'sql_server_instances.csv'

# Create the destination SQL table if doesn't already exist
$sql_create_table = "
USE [$db_name]
GO
If object_id('$destination_schema_name.$destination_table_name', 'U') is null

CREATE TABLE [$destination_schema_name].[$destination_table_name](
 [id] [int] IDENTITY(1,1) PRIMARY KEY,
 [PSComputerName] [varchar](500) NULL,
 [ServiceName] [varchar](500) NULL,
 [InstanceName] [varchar](500) NULL,
 [PathName] [varchar](1500) NULL,
 [ExitCode] [int] NULL,
 [ProcessID] [int] NULL,
 [StartMode] [varchar](500) NULL,
 [State] [varchar](500) NULL,
 [Status] [varchar](500) NULL,
 [InsertedDate] [datetime] NULL DEFAULT GETDATE()
)
"

"Create destination table:"
"========================="
$sql_create_table
"========================="
""

invoke-sqlcmd -Database $db_name -Query $sql_create_table -serverinstance $sql_instance_name

$truncate_table_command = "truncate table [$destination_schema_name].[$destination_table_name]"
"Truncate table command: " + $truncate_table_command
invoke-sqlcmd -Database $db_name -Query $truncate_table_command -serverinstance $sql_instance_name

$SqlServices = Import-Csv -Encoding UTF8 -Path $export_file_name
foreach ($sqlservice in $SqlServices)
{
                    $PSComputerName      = $SqlService.PSComputerName
                    $Name                = $SqlService.Name
                    $SqlInstance         = $SqlService.SqlInstance
                    $PathName            = $SqlService.PathName
                    $ExitCode            = $SqlService.ExitCode
                    $ProcessID           = $SqlService.ProcessID
                    $StartMode           = $SqlService.StartMode
                    $State               = $SqlService.State
                    $Status              = $SqlService.Status

$insert_query = "INSERT INTO [$destination_schema_name].[$destination_table_name]" + " (PSComputerName,ServiceName, InstanceName,PathName,ExitCode,ProcessID,StartMode,State,Status)
          VALUES ('$PSComputerName','$Name','$SqlInstance','$PathName','$ExitCode','$ProcessID','$StartMode','$State','$Status')" 

"SQL Insert Statement: $insert_query"
$execute_query = invoke-sqlcmd -Database $db_name -Query $insert_query  -serverinstance $sql_instance_name
}

$select_query = "select count(*) rcount from " + $destination_table_name 
$rcount = invoke-sqlcmd -Database $db_name -Query $select_query -serverinstance $sql_instance_name -As DataTables
""
"Number of records inserted into sql table: " + $rcount[0].rcount
(Get-Date).ToString() + ": Complete Loading data into sql staging table" 

SQL Script to create the dbo.sql_server_instances_stage table:

USE [AdminDBA]
GO
if object_id('sql_server_instances_stage', 'U') is not null
drop table [sql_server_instances_stage]
GO

CREATE TABLE [sql_server_instances_stage](
 [id] [int] IDENTITY(1,1) PRIMARY KEY,
 [PSComputerName] [varchar](500) NULL,
 [ServiceName] [varchar](500) NULL,
 [InstanceName] [varchar](500) NULL,
 [PathName] [varchar](1500) NULL,
 [ExitCode] [int] NULL,
 [ProcessID] [int] NULL,
 [StartMode] [varchar](500) NULL,
 [State] [varchar](500) NULL,
 [Status] [varchar](500) NULL,
 [InsertedDate] [datetime] NULL DEFAULT GETDATE()
)

Sample Reports Queries:

-- List All Discovered SQL Server Services by Computer
SELECT 
    PSComputerName,
    ServiceName,
    InstanceName,
    PathName,
    State,
    Status,
    StartMode,
    InsertedDate
FROM sql_server_instances_stage
ORDER BY PSComputerName, ServiceName;

-- Count of SQL Services by State
SELECT 
    State,
    COUNT(*) AS ServiceCount
FROM sql_server_instances_stage
GROUP BY State
ORDER BY ServiceCount DESC;

-- List Services Not Running (State Not 'Running')
SELECT 
    PSComputerName,
    ServiceName,
    InstanceName,
    State,
    Status,
    InsertedDate
FROM sql_server_instances_stage
WHERE State <> 'Running'
ORDER BY PSComputerName, ServiceName;

-- Show Services with Recent Discovery (Last 7 Days)
SELECT 
    PSComputerName,
    ServiceName,
    InstanceName,
    State,
    Status,
    InsertedDate
FROM sql_server_instances_stage
WHERE InsertedDate >= DATEADD(DAY, -7, GETDATE())
ORDER BY InsertedDate DESC;

Find clustered index on non primary key columns

By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index.

That works best in most cases and is the recommended best practice.

And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to.

Scenario:

So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time.

But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now that index may not be optimal. Of course that could be true for any index but the consequences are more severe if that's the case for a clustered index.

Now you are supporting that database in production mode. Users report that the query performance has gotten extremely slow and you also notice that the index optimization job is taking much longer to complete.

As part of your research and troubleshooting this issue, one of things you decide to check is index strategy already in place and you check 1) Are there any missing indexes 2) are the indexes of correct type (unique, clustered, non-clustered etc.), fill factor etc. 3) whether the clustered index is created on right columns etc....

The query that I have below is to find out if clustered index is on non-pk columns. I have consciously decided to exclude tables that have either no clustered index, no primary key or there is clustered as well as non-clustered index created on primary key columns.

-- CREATE A TEST TABLE
IF OBJECT_ID('dbo.tbl_test_ci_on_non_pk', 'U') IS NOT NULL
   DROP TABLE tbl_test_ci_on_non_pk
GO

-- ADD A CLUSTERED INDEX ON A NON-PK COLUMN
CREATE TABLE [dbo].[tbl_test_ci_on_non_pk](
	[id] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
	[name] [varchar](50) NULL)
GO
CREATE CLUSTERED INDEX [idx_ci_tbl_test_ci_on_non_pk_name] 
ON [dbo].[tbl_test_ci_on_non_pk]
([name] ASC)
GO

;WITH cte_indexes

     AS (SELECT db_name()                         db_name, 
                schema_name(o.schema_id)          schema_name, 
                object_name(i.object_id)          object_name, 
                o.type_desc                       object_type, 
                i.NAME                            index_name, 
                i.type_desc                       index_type, 
                i.is_primary_key, 
                o.object_id                       object_id, 

                pk_index_id   = (SELECT index_id FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                pk_index_name = (SELECT name FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                clustered_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ic.index_id = i.index_id) 
        FOR xml path ('')), 1, 1, '') ), ''),

               pk_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ii.is_primary_key = 1) 
        FOR xml path ('')), 1, 1, '') ), '')

         FROM   sys.objects o 
                INNER JOIN sys.indexes i ON o.object_id = i.object_id 
         WHERE  Objectproperty(o.object_id, 'ismsshipped') = 0) 

 
SELECT db_name, 
       schema_name, 
       object_type, 
       object_name,
       index_name  non_pk_clustered_index_name,
       pk_index_name,
       clustered_index_columns,
       pk_index_columns

FROM   cte_indexes 
WHERE  1 = 1 
       AND index_type = 'CLUSTERED' 
       AND pk_index_id ! = 1 
       AND clustered_index_columns != pk_index_columns
ORDER  BY object_name, 
          index_name 

Caveat:  I only considered the traditional index types (clustered, non-clustered, unique, non-unique etc.).

I have tested this on SQL Server versions 2008 R2 and above.

What about orphaned windows users?

SQL Server 2012+ only/: If you're on older versions, this won't work.

Almost every DBA and some developers know that a database user becomes orphaned when its SID doesn't match anything in sys.server_principals. No big deal if you're using contained databases with database-level authentication. But for traditional server logins, those users are locked out of the instance entirely, even though they still "exist" in the database with permissions.

This usually happens when:

• You restore a database to a new server, but the instance-level LOGINS don't match
• SIDs get out of sync between database users and server logins
• Someone deletes a login from sys.server_principals (or Active Directory cleans house)

Microsoft's been dealing with this forever and gave us sp_change_users_login to find/fix it:

How To Troubleshoot Orphaned Users in SQL Server

But sp_change_users_login only works for SQL Server logins, it completely ignores Windows users.

Windows users get even trickier because they often authenticate through Windows group membership. A simple SID comparison between sys.database_principals and sys.server_principals won't catch those. That's where the extended stored procedure xp_logininfo saves the day, it validates actual Windows account existence.

That's why I wrote the T-SQL script below. It finds orphaned Windows users and optionally fixes them by leveraging xp_logininfo to bridge the gap that traditional methods miss.

-- See blog post: https://sqlpal.blogspot.com/2019/06/what-about-orphaned-windows-users.html
/*==========================================================================================
  Script Name : Find-Fix-Orphaned-Windows-Users.sql

  Purpose:
      Identifies and optionally recreates orphaned Windows users in the current database.
      Unlike sp_change_users_login (SQL logins only), this handles Windows users/groups
      using xp_logininfo to validate Windows account existence.

  How it works:
      1. Finds database users (WINDOWS_USER type) with no matching server login SID
      2. Uses xp_logininfo @option='all' to verify if Windows account still exists
      3. Reports orphaned users OR auto-creates missing server logins

  Configuration:
      @fix_orphaned_user (BIT) 
          0 = Report-only mode (default, safe)
          1 = Auto-fix by creating missing server logins

  Prerequisites:
      - Run in target database (orphaned users are DB-scoped)
      - sysadmin or equivalent to create logins (when fixing)
      - Windows Authentication environment

  Safety Notes:
      - xp_logininfo queries Active Directory/local SAM - network dependent
      - Only creates logins, doesn't modify existing database users/permissions
      - Test in non-prod first when @fix_orphaned_user=1
      - Won't fix OS-level account issues (disabled/locked/deleted accounts)

  Typical Scenarios:
      - Database restore to new server (login SIDs don't match)
      - AD cleanup removed accounts referenced by databases
      - Failover cluster with domain trust issues
==========================================================================================*/

DECLARE @username NVARCHAR(500),
        @privilege NVARCHAR(500), 
        @sql NVARCHAR(4000),
        @fix_orphaned_user BIT,
        @cnt INT = 0;

SET @fix_orphaned_user = 0;  -- 0 = Report only (SAFE), 1 = Auto-fix logins

DECLARE c1 CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
    SELECT dp.NAME 
    FROM sys.database_principals dp 
    LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid 
    WHERE dp.type_desc = 'WINDOWS_USER' 
      AND dp.authentication_type_desc = 'WINDOWS'
      AND dp.principal_id != 1  -- Exclude dbo
      AND sp.sid IS NULL;       -- No matching server login

OPEN c1;
FETCH c1 INTO @username;

WHILE @@FETCH_STATUS = 0 
BEGIN
    -- Count potential orphans before validation
    SET @cnt = @cnt + 1;

    /*
        xp_logininfo validates Windows account existence in AD/local SAM.
        @option='all' returns privilege level (user/group details).
        NULL result = account doesn't exist = TRUE orphan.
    */
    EXEC xp_logininfo 
        @acctname = @username, 
        @option = 'all', 
        @privilege = @privilege OUTPUT;

    -- Orphan confirmed (no Windows account found)
    IF @privilege IS NULL 
    BEGIN
        RAISERROR('Orphaned Windows user: %s', 10, 1, @username) WITH NOWAIT;
        
        -- AUTO-FIX: Create missing server login
        IF @fix_orphaned_user = 1 
        BEGIN 
            SET @sql = 'CREATE LOGIN [' + @username + 
                       '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + DB_NAME() + ']';
            
            PRINT 'Creating login: ' + @sql;
            EXEC(@sql);
        END
    END;

    FETCH c1 INTO @username;
END;

CLOSE c1;
DEALLOCATE c1;

-- Final status message
IF @cnt = 0 
    RAISERROR('No potential orphaned Windows users found.', 10, 1) WITH NOWAIT;
ELSE IF @cnt > 0 AND @fix_orphaned_user = 0
    RAISERROR('%d potential orphaned Windows users (run with @fix_orphaned_user=1 to auto-fix).', 
              10, 1, @cnt) WITH NOWAIT;

Report only:

Report and fix:

Download Script: Find-Orphan-Windows-Users-In-SQLServer

Caveat: The script won't fix OS-level account issues (deleted/disabled/locked Windows users).

Linux: Untested but should work, let me know!

Query machine name of the sql server instance - the hard or the harder way

I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was partly because of legacy reasons and partly because it supports clustering across all major hardware, operating systems, and applications, including SQL Server. SQL Server instances are set up in either 2 or 3 nodes, active/passive, active/active, etc., configurations. There are approximately 30 physical servers hosting SQL Server instances. Yes, the client is going to move all the SQL workloads to Always On Clusters, but the process has been slow because all the databases are used for COTS/third-party applications.

A virtual name is used to make a connection to a SQL Server instance. Often, I would need to know the actual physical node name where a particular SQL instance is active, and I needed to find it out programmatically.

You may have different reasons for connecting to SQL Server using a virtual name but need to know the underlying machine name.

So I first tried this query:

SELECT   

  @@SERVERNAME ServerName_Global_Variable
 ,SERVERPROPERTY('ServerName') ServerName
 ,SERVERPROPERTY('InstanceName') InstanceName
 ,SERVERPROPERTY('MachineName') MachineName
 ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') NetBIOS

That would be the easier way if it worked in this situation. Alas, it still kept giving me virtual server name.

Then I tried using a DOS command, assuming you have or are able to temporarily turn on XP_CMDSHELL.

EXEC master..XP_CMDSHELL 'Hostname'

And I still got the virtual server name.

Then I tried the following which does display the actual server name in one of the messages:

EXEC master..xp_cmdshell 'net user'

Mission accomplished, great! I could write additional code around it to trim out every other text from it, extracting only the computer name, and then store it in a variable or in my own metadata table for further processing, reporting, etc.

Then I thought, is there another way, perhaps a better way? Well, I wouldn't necessarily call my next approach better, but it's certainly another way.

If you're looking for a one-liner to remotely look up a computer name, simply run the following command from the command prompt or PowerShell:

wmic /NODE:sqlservernode1 computersystem get Name

Here is a bit lengthy TSQL code to do this while connected to a SQL instance. Note that if the XP_CMDSHELL is disabled,  it temporarily turns it on then off when done.

SET NOCOUNT ON

DECLARE @computer_name VARCHAR(500)
DECLARE @xp_cmdshell_status BIT

SELECT @xp_cmdshell_status = cast(value_in_use AS BIT)
FROM sys.configurations
WHERE name = 'xp_cmdshell'

IF @xp_cmdshell_status = 0
BEGIN
 PRINT 'XP_CMDSHELL is disabled on this server, temporarily enabling it...'

 EXEC sp_configure 'show advanced options','1'

 RECONFIGURE

 EXEC sp_configure 'xp_cmdshell','1'

 RECONFIGURE
END

IF object_id('tempdb..#t1_xp_cmdshell_output') IS NOT NULL
 DROP TABLE #t1_xp_cmdshell_output

CREATE TABLE #t1_xp_cmdshell_output (
 id INT identity
 ,txt NVARCHAR(2000)
 )

INSERT INTO #t1_xp_cmdshell_output
EXEC master..xp_cmdshell 'wmic computersystem get Name'

SELECT @computer_name = txt FROM #t1_xp_cmdshell_output WHERE id = 2

SELECT @computer_name computer_name

IF @xp_cmdshell_status = 0
BEGIN
 PRINT 'Changing XP_CMDSHELL back to disabled state...'

 EXEC sp_configure 'show advanced options','1'

 RECONFIGURE

 EXEC sp_configure 'xp_cmdshell','0'

 EXEC sp_configure 'show advanced options','0'

 RECONFIGURE
END

Quite a lengthy code!

Generally, when I'm using a DOS or PowerShell command, I prefer using PowerShell to populate the data in the SQL table, avoiding all the additional code I've used in the above T-SQL block.

And finally, there is one more way, sort of! If you execute a multi-server query with the "Add server name to the results" option set to true (default), it will display the physical server name in the results pane. However, I'm not aware of a way to capture it dynamically in a variable at this point.

If you think I missed something, please don't hesitate to provide feedback!

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.