Need help with a quick way to insert 2 columns but 1 - 3 row results into system.data.DataTable using Powershell

sqlagent007
sqlagent007 used Ask the Experts™
on
Need some powershell help adding what could be variable results into system.data.datatable. I have the following powershell command that will return 1 - 3 rows depending on the cluster. I have limited the results to only the "name" and "OwnerNode" columns.
$clusterName = "MyCluster-1"
get-clusterGroup -Cluster $clusterName | where-object `
    {$_.name -like "*DB*" -or $_.name -like "*SQL*" -and $_.name -notlike "*MSDTC*"} | `
    Select-Object -Property Name,OwnerNode

Open in new window


If the cluster has 3 SQL Instances installed, I will get results like this:
Name                       OwnerNode                                                                    
--------                    --------- 
SQL Instance 1       node1
SQL Instance 2       node2
SQL Instance 3       node1

Open in new window


If the cluster has only 1 SQL Instance I will bet results like this:
Name                       OwnerNode                                                                    
--------                    --------- 
SQL Instance 1       Server1

Open in new window


I want to insert the results into system.data.datatable or something where I can create one large 2 column list will all the SQL instance names (aka "Name") and all the OwnerNode names.

I want to then query this object for any and all "owner nodes" that own more than one cluster role ("name"). If I were using T-SQL to do this, I will simply create a temp table, then insert into the temp table using a cursor....But...I think I need to do this in powershell so i can connect to all the clusters in our domain. I think I can do the query of the data table on my on own, but I need help getting the results into a single data table from a list of clusters. I was able to create a list of all the clusters in my domain using this code:
$clusterList = get-Cluster -Domain mydom.local | where-object -like "*SQL*"

Open in new window


Then I was able to use
foreach ($names in $clusterList) 
{
  get-ClusterGroup -Cluster $names | where-object `
 {$_.name -like "*DB*" -or $_.name -like "*SQL*" -and $_.name -notlike "*MSDTC*"} | `
  Select-Object -Property Name,OwnerNode
}

Open in new window


Now I am just stuck on how to get each execution of "get-ClusterGroup into a dataTable. Please help = )
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
What do you want to do with the results once you know which node owns more than one node?
$clusterList = Get-Cluster -Domain mydom.local | Where-Object -like "*SQL*"
$allClusterGroups = $clusterList | ForEach-Object {
	Get-ClusterGroup -Cluster $_ |
		Where-Object {$_.Name -like "*DB*" -or $_.Name -like "*SQL*" -and $_.Name -notlike "*MSDTC*"} |
		Select-Object -Property Name, OwnerNode
}
$allClusterGroups | Group-Object -Property OwnerNode | Where-Object {$_.Count -gt 1} | Sort-Object -Property Count

Open in new window

sqlagent007Technology professional

Author

Commented:
What I am trying to produce output (a report) for our NOC so during patching windows they can "re-balance" the SQL clusters. For example during a patching window the NOC may install windows patches on our 2 SQL nodes, then reboot both servers and we will end up with "SQL1" and "SQL2" running on the "WidowsNode1". When in reality what we want is "SQL1" owned by "WindowsNode1" and "SQL2" owned by "WindowsNode2".

I will play with this and I think this is what I need. Thanks!
sqlagent007Technology professional

Author

Commented:
Thanks!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial