Run a query against registered servers and insert results into table

  • mhorner 67968

    SSC Enthusiast

    Points: 196

    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: 323444

    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

    Thank you very much. Works well.

  • lello bello

    SSC Journeyman

    Points: 77

    Hello,

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

    Thx!

    Lello

  • a.spijker

    Grasshopper

    Points: 11

    How can it be done using powershell?

    1. create connection to registered server group? (How can i do this?)
    2. execute the query
    3. 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 2 weeks ago by  a.spijker.

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

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