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 <TheNameOfYourCMSServer> -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] >> $Error[0]"}
}
#Catch the error if the ping test fails
ELSE {Write-Output "Server Ping Error: $servername >> $Error[0]"}
}
