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

Question

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\<NAME>\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.

• Tony Hinkle: Hi Dennis--it seems that you are writing an example of how to do some, but not asking a question. If that is the case, you need to rewrite it as a question ("How do you run a SQL command on several servers in a list") and the post your script as an answer. Also, I think the first line needs to be $ServerInstance = get-content "C:\Users\Documents\InstanceList.txt"
• Dennis M.: Thanks alroc, the example I provided is in the making, but not quite working. I was looking for another approach, perhaps what I've used wasn't the most fitting. But yes, it should be a question.

Answer 1

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\<NAME>\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.

Answer 2

You should be able to use this script here to accomplish what you need: https://sql-box.com/2012/10/03/how-to-run-a-sql-query-on-list-of-servers-and-save-output-to-excel-spreadsheet-using-power-shell/

• Dennis M.: Thank you! I actually saw this post during my research, the difference is that my serverlist.txt contained the servers, e.g. server.domain.com, so I replaced it with the actual instance name e.g. server.domain.com\instance_name, and it worked.
• Dennis M.: The only thing I need to include now is the hostname from the instance itself to distinguish where the instance is coming from once it gets exported to csv or excel.
• Select 'DBA': Hi Dennis, an easy way to accomplish that without needing to adjust your PowerShell query is adding "CONVERT(sysname, SERVERPROPERTY('servername'))" to your select statement. That adds the server and instance from the SQL statement to your results.
• Dennis M.: Gotcha! it works like a charm, had to clean it up as certain columns are not needed to be a part of the group by