List the CMS SQL Server Instances using Powershell I need to run a set of Powershell commands from my Central Management Server onto all of the SQL Server Instances that it contains. Can someone let me know how this could be achieved?

I need to loop each server instance from the central management and calculate/get the free space. I am using SQL Server 2012. I need to try out the following things:


Search particular extension files on Server Instances
Check the space occupied by them 
Check Free Space 
Store the above info. in a file on the current system


I am simply aiming for getting the size of backup files (.bak files) only. I have a solution already where we query Invoke-Command to find the files for us on remote machine. I wanted to know whether there exist a better solution to get the task done.
First, get your server list query returning what you want, bellow is an example query to run against your CMS.

SELECT DISTINCT [server_name] 
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]


Next the PowerShell, we use a loop that pings the server first to verify it is available then checks the SQL connection. If everything is good it will execute the requested code. The below executes a SQL query but anything could be put in that block. This script runs under the current windows user when connecting to the SQL Instances.

#Import the SQLPS module to use the Invoke-SQLCmd
Import-Module -Name SQLPS -DisableNameChecking -Force

#Create a variable to hold your SQL query for the server list
$ServerListQuery = @"
SELECT DISTINCT [server_name] 
FROM [msdb].[dbo].sysmanagement_shared_registered_servers_internal
ORDER BY [server_name]
"@

#Create a variable to hold the query or queries you want to execute against each SQL Server
$SQLServerVersionQuery=@"
SELECT GetDate() AS DateTimeStamp, 
CAST(SERVERPROPERTY('ServerName')AS sysname)  AS ServerName, 
@@version AS SQLServerVersion
"@

#Create your list and place it in a variable using Invoke-Sqlcmd
#Invoke-Sqlcmd -ServerInstance &lt;TheNameOfYourCMSServer&gt; -Database msdb (because that is where CMS is stored) -Query $ServerListQuery
$list = Invoke-Sqlcmd -ServerInstance MyCMSServer -Database msdb -Query $ServerListQuery

#Loop through the list and run your query
ForEach ($databaseinstance in $list){

#remove named instance if it exists for the ping test
$servername = $databaseinstance[0].split("\") | Select-Object -Index 0
IF(Test-Connection -ComputerName $servername -quiet) {
TRY{

#Run the query variable created at the top $SQLServerVersionQuery
Invoke-Sqlcmd -ServerInstance $databaseinstance[0] -Query $SQLServerVersionQuery -QueryTimeout 300 | 
Select-Object -Property DateTimeStamp ,ServerName, SQLServerVersion

} 
#Catch the error if SQL connectino doesn't work
CATCH {Write-Output "SQL Connect Error on: $databaseinstance[0] &gt;&gt; $Error[0]"}
} 
#Catch the error if the ping test fails
ELSE {Write-Output "Server Ping Error: $servername &gt;&gt; $Error[0]"}
}