Querying multiple servers via CMS: When does [Server Name] become ‘real’?

Maybe a silly question. I set up an CMS earlier this year, which has improved my work day immeasurably. Servers are split into appropriate groups, but occasionally I only need to target some servers within a group, or I’d like to set up a condition based on the server name. Aside from creating a temp table to insert a CMS query to and working from there, can I ever use the [Server Name] column to filter on or order by?

Thanks

If you’re talking about the option in SSMS to add in the Server Name and Login Name columns, those are added to the dataset through SSMS and is not part of the query to the server and can’t be accessed from the query. It becomes “real” when the result set is returned from the server and SSMS adds the column.

The workaround would be to check the Server Name in the query, or as you’ve already stated, pull the results into an intermediate table and process more from there. Using order by would only be able to be accomplished through an intermediate table as each query doesn’t know of any other, as SSMS makes a connection per instance behind the scene to do CMS multi-server queries.