sqlagent007
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.
If the cluster has 3 SQL Instances installed, I will get results like this:
If the cluster has only 1 SQL Instance I will bet results like this:
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:
Then I was able to use
Now I am just stuck on how to get each execution of "get-ClusterGroup into a dataTable. Please help = )
$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
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
If the cluster has only 1 SQL Instance I will bet results like this:
Name OwnerNode
-------- ---------
SQL Instance 1 Server1
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*"
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
}
Now I am just stuck on how to get each execution of "get-ClusterGroup into a dataTable. Please help = )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!!!
ASKER
I will play with this and I think this is what I need. Thanks!