Extract output from central management servers into a table

Question

Post reply

Extract output from central management servers into a table

PHXHoward

SSCrazy Eights

Points: 8388
More actions
May 26, 2016 at 7:02 pm

#308003

This one has my head spinning. I would like to run a query on all of the servers registered in the central management server and then write the output to a table on our management server. I can get the output and also load it into the database but what is loaded is not right. Server Name and Node Name don't go into a table correctly. I've been reading a bunch of examples from Google but really don't understand them. Somehow I need to split the output into two parts so one column can go into ServerName and one into NodeName.
Any help would be greatly appreciated.
#Get list of servers in CMS
$instanceNameList = invoke-Sqlcmd -query "
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSRSI.server_group_id = '11'
" -serverinstance "TESTSRV2"
$results = @()
#Collect server name and node node from each into a variable
foreach($instanceName in $instanceNameList)
{$results += Invoke-Sqlcmd -Query "
SELECT @@SERVERNAME as ServerName, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as NodeName
" -ServerInstance $instanceName.Name}
#Insert the results into the STG_Results table in the DBA database
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='TESTSRV2';database='DBA';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
foreach($node in $results)
{
$sql = "insert into STG_Results (ServerName,NodeName)
select '$node.ServerName','$node.NodeName'"
$Command.CommandText = $sql
$Command.ExecuteNonQuery()
}
$Connection.Close()

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

Did you get this working?

Are you still trying to resolve this?

If you still need help the in what way does it fail?

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

PHXHoward SSCrazy Eights Points: 8388 More actions · Answer 2

Hi Gary. I never got it working.

Instead of inserting the ServerName and NodeName in the database it just inserts this for every line:

System.Data.DataRow.ServerNameSystem.Data.DataRow.NodeName

Howard

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

I am in a caravan on the Dorset coast during a windy week on my mobile so cannot check but I suggest outputting the types of things like $node e.g. $node.GetType() and output them.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 4

Gary Varga (8/2/2016)
I am in a caravan on the Dorset coast during a windy week on my mobile so cannot check but I suggest outputting the types of things like $node e.g. $node.GetType() and output them.

Gary is wise.

use this instead:

$node.ServerName.ToString()

Thomas Rushton
blog: https://thelonedba.wordpress.com

PHXHoward SSCrazy Eights Points: 8388 More actions · Answer 5

$results.GetType() returns this:

IsPublic IsSerial Name BaseType

-------- -------- ---- --------

True True Object[] System.Array

If I do the SQL insert like this:

foreach($node in $results)

{

$sql = "insert into STG_Results (ServerName,NodeName)

select '$node.ServerName.ToString()','$node.NodeName.ToString()'"

...

}

It comes out like this:

ServerNameNodeName

System.Data.DataRow.ServerName.ToString()System.Data.DataRow.NodeName.ToString()

There are multiple columns and rows in $results because it is loaded by looping through multiple servers. I'd like to pluck the values of each row out of $results and insert them in the database table. Certainly I'm missing some fundamental concept of how it should be written.

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 6

Hmm. There's something fundamentally wrong with my PoSh setup on this computer - it doesn't like that "+=" constructor you have when building up $results...

Will have to think again about this. Sorry.

Thomas Rushton
blog: https://thelonedba.wordpress.com

PHXHoward SSCrazy Eights Points: 8388 More actions · Answer 7

I'm sorry Thomas, I see now that I missed a line in the original script.

I had to define the variable as an array first like this

$results = @()

PHXHoward SSCrazy Eights Points: 8388 More actions · Answer 8

Hi everyone. jrv found the solution to this one.

Changing the query to insert the values solved the problem.

$sql = "insert into STG_Results(ServerName,NodeName) VALUES('{0}','{1}')" -f $node.ServerName, $node.NodeName

Thanks again for your input.

Howard