Rather than issuing a request 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 contains, say, 100 SQL Server instances. Browse each instance of SQL Server, issue a query, and export to CSV.
Here 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
OERE –DB_NAME (dbid) = & # 39; genesys_wfm & # 39; and
– and HostName = & # 39; xxxx & # 39;
and the login name is not in (& # 39; NT AUTHORITY SYSTEM & # 39 ;, & # 39; ACE & # 39 ;, & # 39; domain xxxx & # 39;)
dbid, loginame, Hostname, Login_Time, Program_name
order by Login_Time desc; "
Invoke-Sqlcmd -ServerInstance $ i -Query $ sql -ConnectionTimeout 60 -QueryTimeout 99999
The following is the InstanceList.txt:
server_name instance name1
servername2 instance name2
And so on.
Thank you, I hope my explanation helps.