Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Run a query against registered servers and insert results into table Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:01 PM
Points: 1, Visits: 26
I am trying to do the exact same thing. I don't see that any of these suggestions will result in the solution you are after. If you come up with something, I would be interested in knowing the solution. I'm currently stuck with running the queries through the Registered Servers option and then saving the results and importing them to tables in a database. Very awkward, but feasible.
Post #1496442
Posted Thursday, September 19, 2013 9:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, March 8, 2016 3:07 PM
Points: 825, Visits: 1,212
Just a thought but if you have to do more work to avoid creating an SSIS as a "on off" then it is still worth creating the SSIS even if you have no intention of ever running it again.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Post #1496454
Posted Friday, February 20, 2015 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 12, 2015 12:01 AM
Points: 1, Visits: 132
I wonder if you found a solution. I'm also very interested.
Post #1662019
Posted Wednesday, November 11, 2015 8:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 19, 2016 12:52 PM
Points: 4, Visits: 51
The only way I see to do this is to create a linked server and write the individual results into the table using a linked server.
Post #1735871
Posted Thursday, November 12, 2015 12:28 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 26, 2016 1:48 AM
Points: 392, Visits: 1,119
Hi,

So many years later, im not sure what i did back then, but now i just use PowerShell. load SQLPS module connect to the CMS Server query the msdb to get the list of servers and then loop through saving the results to a table or excel or what ever i need.

I can supply a example script if anyone wants something to start with.

Post #1736148
Posted Thursday, November 12, 2015 12:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 19, 2016 12:52 PM
Points: 4, Visits: 51
I'm using PowerShell as well but not with the CMS. How does that work? I'd be appreciative of that information and a copy of your script if you don't mind. Thanks, Marcus.
Post #1736149
Posted Thursday, November 12, 2015 12:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 14,549, Visits: 38,417
mhorner 67968 (11/12/2015)
I'm using PowerShell as well but not with the CMS. How does that work? I'd be appreciative of that information and a copy of your script if you don't mind. Thanks, Marcus.


you can query the msdb database, of the server that serves up the Central Management Server list.
with that list, you can use powershell to connect to each one;

select * from msdb.dbo.sysmanagement_shared_server_groups_internal
select * from msdb.dbo.sysmanagement_shared_registered_servers_internal

select * from msdb.dbo.sysmanagement_shared_registered_servers
select * from msdb.dbo.sysmanagement_shared_server_groups

here's an example where i pull stuff together:
SELECT ROW_NUMBER()
OVER (
PARTITION BY TheServer.NAME
ORDER BY TheServer.NAME) AS rw,
TheGroup.NAME AS [servergroup],
TheGroup.[Description] AS [groupdescription],
TheServer.NAME,
TheServer.server_name AS [servername],
TheServer.[description] AS [description]
FROM [HOL-WKS-444].msdb.dbo.sysmanagement_shared_server_groups_internal TheGroup
LEFT JOIN [HOL-WKS-444].msdb.dbo.sysmanagement_shared_registered_servers_internal TheServer
ON TheGroup.server_group_id = TheServer.server_group_id
WHERE TheGroup.server_type = 0 --only the Database Engine Server Group
AND server_name IS NOT NULL



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1736154
Posted Thursday, November 12, 2015 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 19, 2016 12:52 PM
Points: 4, Visits: 51
Thank you very much. Works well.
Post #1736164
Posted Thursday, September 15, 2016 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 11, Visits: 141
Hello,

Can you send my your example script (Powershell) per private message?

Thx!

Lello
Post #1818023
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse