Run a query against registered servers and insert results into table

Question

Run a query against registered servers and insert results into table

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

mhorner 67968 SSC Enthusiast Points: 196 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

mhorner 67968 SSC Enthusiast Points: 196 More actions · Answer 3

mhorner 67968

SSC Enthusiast

Points: 196

November 12, 2015 at 1:30 pm

#1839497

Thank you very much. Works well.

lello bello SSC Journeyman Points: 77 More actions · Answer 4

Hello,

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

Thx!

Lello

a.spijker Grasshopper Points: 11 More actions · Answer 5

How can it be done using powershell?

create connection to registered server group? (How can i do this?)
execute the query
mail the results

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "integrated security=SSPI; data source=?????; initial catalog=??????;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = "dba.dbo.stp_get_version"

$SqlCmd.Connection = $SqlConnection

$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure

$p1=$SqlCmd.Parameters.Add("@htmloutput", "")

$p1.Direction = [system.Data.ParameterDirection]::Output

$p1.Size=8000

$SqlConnection.Open()

$SqlCmd.ExecuteNonQuery()

$p1.SqlValue.Value

$MsgBody = $p1.SqlValue.Value

$SMTPSvr = 'my_exchange_server'

$from = 'me@mail.com'

$recipients = "you@mail.com, you2@mail.com"

[string[]]$to = $recipients.Split(',')




Send-MailMessage –From $from –To $to –Subject “server versions” -Body $MsgBody -SmtpServer $SMTPSvr -BodyAsHTML

This reply was modified 4 years, 10 months ago by a.spijker.