Link to home
Start Free TrialLog in
Avatar of sqlagent007
sqlagent007Flag for United States of America

asked on

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

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 = )
ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sqlagent007

ASKER

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!
Thanks!!!