May 31, 2019 at 6:48 pm
Hi Everyone!
Does anyone have a good link with instructions to Query a list of servers in a table and put the results in a table?
I created a table for the server connection strings.
I created a table to store the results.
I have the SQL query to get the information.
I just don't know how to make it work with PS. This is new to me. i can get other information. But i've tried a few queries i found online and it is not working for me.
Any advice would be greatly appreciated!
SELECTAGC.name as AvailabilityGroup, RCS.replica_server_name ServerName, ARS.role_desc as Role, AGL.dns_name as ListenerNameFROM sys.availability_groups_cluster AS AGCINNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_idINNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_idINNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id
¤ §unshine ¤
May 31, 2019 at 10:06 pm
I created a table for the server connection strings.
Does that table contain login names and passwords?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2019 at 12:35 am
here is an older example i used to use, that assumes you have a list of server sin Central Management Servers
for each server, i may run a query per server, or a query per database.
see if this makes any sense for you:
updated, remov
###################################################################################################
## Purpose: run one Query, potentially on each database and produce combined results
###################################################################################################
###################################################################################################
## CMS setup
$CMSSource = "CentralManagementServer\InstanceName"
$cmsQuery = @"
SELECT TOP 20
srvz.server_name AS ServerName,[grpz].[name] As GroupName
FROM msdb.dbo.sysmanagement_shared_registered_servers srvz
INNER JOIN msdb.dbo.[sysmanagement_shared_server_groups] grpz
ON srvz.[server_group_id] = [grpz].[server_group_id]
WHERE [grpz].[name] LIKE'[_]%[_]Apps'
"@
###################################################################################################
## Query Setup make sure you actually include @@SERVERNAME and db_name() in your results.
###################################################################################################
$ExecuteOnEachDB = $true;
$SQLQuery = @"
; SELECT
AGC.name as AvailabilityGroup,
RCS.replica_server_name ServerName,
ARS.role_desc as Role,
AGL.dns_name as ListenerName
FROM sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS
ON RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
ON ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL
ON AGL.group_id = ARS.group_id
"@
###################################################################################################
## The Work
###################################################################################################
$AllServers = Invoke-SQLCMD -Query $cmsQuery -ServerInstance $CMSSource
$FinalResults = New-Object System.Data.DataTable 'Results'
$sb = [System.Text.StringBuilder]::new();
foreach ($Server in $AllServers)
{
$Results = (Invoke-SQLCMD -Query $SQLQuery -ServerInstance $Server.ServerName -OutputAs DataTables)
foreach($DataRow in $Results)
{
$sb.Append($Server.ServerName)|Out-Null;$sb.Append('|')|Out-Null;
$sb.Append($DataRow.AvailabilityGroup)|Out-Null;$sb.Append('|')|Out-Null;
$sb.Append($DataRow.ServerName)|Out-Null;$sb.Append('|')|Out-Null;
$sb.Append($DataRow.Role)|Out-Null;$sb.Append('|')|Out-Null;
$sb.Append($DataRow.ListenerName)|Out-Null;$sb.Append('|')|Out-Null;
$sb.AppendLine('')|Out-Null
}
} #foreach
###################################################################################################
## The Results
###################################################################################################
$sb.ToString() | Out-GridView
ed the logic with DataTable.Merge, as it depends on some libraries and PowerShell Version
Lowell
June 1, 2019 at 12:36 am
holy crap the new format of the website plays holy hell with code i took a lot of time to comment well and make sharp looking.
Lowell
June 1, 2019 at 6:27 am
Heh... that's what you get for posting PowerShell on an SQL Forum. 😀 😀 😀
The problem is very likely the use of both carriage return and line feed (newline) from whatever you did the copy and past to. My recommendation would be to copy it to Word and replace and double paragraph marks with single marks and then replace those (^p) with single line terminators (^l that's a lower-case "L").
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2019 at 1:48 pm
No login codes or passwords.
¤ §unshine ¤
June 3, 2019 at 1:50 pm
Thank you! I will try this out today!
¤ §unshine ¤
June 17, 2019 at 6:12 pm
Hi!
I'm no really sure I am doing this correctly. I am not running anything by database and already have an existing table. The first one attached is what I tried to run but it is not working.I'm not sure where to define the existing table. The 2nd attachment works but only enters 1 row and some have more than 1 row. I also tried to add your cms query to run on all servers to the 2nd one, but no cigar on that one either. 🙁
¤ §unshine ¤
June 18, 2019 at 4:32 am
Hi! I'm no really sure I am doing this correctly. I am not running anything by database and already have an existing table. The first one attached is what I tried to run but it is not working.I'm not sure where to define the existing table. The 2nd attachment works but only enters 1 row and some have more than 1 row. I also tried to add your cms query to run on all servers to the 2nd one, but no cigar on that one either. 🙁
I'm not seeing the attachments you speak of.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2019 at 12:09 pm
I updated my original code sample with one I have just tested. it outputs data the way i would expect now.
I am using a stringBuilder instead of a DataTable to contain the results.
your latest post did not have enough information, remember we don't sit at the desk next to you, so we need everything you can provide to help debug:
for example, providing exact error messages, instead of saying it did not work, etc. goes a long way to getting great help here.
Lowell
June 18, 2019 at 3:42 pm
My apologies. I will send as soon as i can. i did come a bit closer with different parts i've found here and online. I'll post what i have soon.
¤ §unshine ¤
June 19, 2019 at 6:00 pm
Good afternoon!
I was able to get the attached PS somewhat working. It does insert data into my table, however, gives me the below error messages.
Would you happen to know how to resolve? Thank you so much for your assistance.
Get-Member : You must specify an object for the Get-Member cmdlet.
At D:\FinalWorksWithErrors.ps1:51 char:29
+ $Properties = $SelectRows | Get-Member -MemberType Property | Select- ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-Member], InvalidOperationException
+ FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand
Invoke-Sqlcmd : Incorrect syntax near ')'.
At D:\WithErrors.ps1:64 char:1
+ Invoke-Sqlcmd -ServerInstance $CMSServer -Database $CMSDatabase -Quer ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
¤ §unshine ¤
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply