Query Multiple SQL Server Instances using Powershell referencing the list of instances in a text file

Rather than issuing a query per instance via SQL Server Management Studio, we are trying to create a powershell script to query multiple instances by going through a loop referencing a text file where it has let's say 100 SQL Server instances. Go through each SQL Server instance, issue a query, and export to CSV.

The below is the powershell script we currently have:

$ServerInstance = "C:\Users\\Documents\InstanceList.txt"

foreach ($i in $ServerInstance)
{

$sql = "SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    RTRIM(loginame) as LoginName,
       RTRIM(Hostname) As HostName, Login_Time,Program_name
FROM
    sys.sysprocesses
WHERE --DB_NAME(dbid) = 'genesys_wfm' and 
    dbid > 5
       --and HostName = 'xxxx'
       and loginame not in ('NT AUTHORITY\SYSTEM','ACE','domain\xxxx')
GROUP BY 
    dbid, loginame,Hostname, Login_Time,Program_name
       order by Login_Time desc;"

Invoke-Sqlcmd -ServerInstance $i -Query $sql -ConnectionTimeout 60 -QueryTimeout 99999

The below is the InstanceList.txt:

servername\instance name1
servername2\instance name2

and so forth.

The easiest way to do this (read: least amount of additional code you need to write) is to use Invoke-DbaQuery from the dbatools PowerShell module.

Remove the foreach loop - this function will handle it for you. Pass the list of instances into Invoke-DbaQuery and include the -AppendServerInstance switch, and all the instances will be queried and the name of each included in the output - just like when you do a multi-instance query in SSMS. You'll get one object back which is a collection of the results of the query against each instance.

$InstanceList = get-content "C:\Users\\Documents\InstanceList.txt";
Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -QueryTimeout 99999 -AppendServerInstance

Since you don't mention script performance/elapsed time being an issue, I intentionally did not mention running multiple queries in parallel and then merging the results.

StackExchange Database Administrators Q#260490, answer score: 7