Use Powershell to insert data into multiple columns in SQL Server

Question

I'm trying to insert data collected from VMware vCenter into a database using Powershell and SQL Server.

This is what I have for now:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=Computer;Integrated Security=SSPI;"
$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn

$val = 0
$clusterinf = foreach($cluster in Get-Cluster){
$esx = $cluster | Get-VMHost
$ds = Get-Datastore -VMHost $esx | where {$_.Type -eq "VMFS"}

$cluster | Select @{N="VCname";E={$cluster.Uid.Split(':@')[1]}},
    @{N="DCname";E={(Get-Datacenter -Cluster $cluster).Name}},
    @{N="Clustername";E={$cluster.Name}},        
    @{N="TotalPhysicalMemory";E={[Math]::Round(($esx | Measure-Object -Property MemoryTotalMB -Sum).Sum /1KB)}},
    @{N="TotalUsedMemory";E={[Math]::Round(($esx | Measure-Object -Property MemoryUsageMB -Sum).Sum /1KB)}},
    @{N="AvailableMemroy";E={[Math]::Round(($esx | Measure-Object -InputObject {$_.MemoryTotalMB - $_.MemoryUsageMB} -Sum).Sum /1KB)}},
    @{N="TotalCPU";E={[Math]::Round(($esx | Measure-Object -Property CpuTotalMhz -Sum).Sum /1KB)}},
    @{N="TotalUsedCPU";E={[Math]::Round(($esx | Measure-Object -Property CpuUsageMhz -Sum).Sum /1KB)}},
    @{N="AvailableCPU";E={[Math]::Round(($esx | Measure-Object -InputObject {$_.CpuTotalMhz - $_.CpuUsageMhz} -Sum).Sum /1KB)}},
    @{N="TotalDiskSpace";E={[Math]::Round(($ds | where {$_.Type -eq "VMFS"} | Measure-Object -Property CapacityMB -Sum).Sum /1KB)}},
    @{N="ConfiguredDiskSpace";E={[Math]::Round(($ds | Measure-Object -InputObject {$_.CapacityMB - $_.FreeSpaceMB} -Sum).Sum /1KB)}},
    @{N="AvailableDiskSpace";E={[Math]::Round(($ds | Measure-Object -Property FreeSpaceMB -Sum).Sum /1KB)}},
    @{N="Total Configured Memory GB For Powered on VMs";E={[Math]::Round(($_ | Get-VM | Where-Object {$_.PowerState -eq "PoweredOn"} | %{$_.MemoryMB} | Measure-Object -Sum | Select -ExpandProperty Sum)/1KB) }}

$cmd.commandtext = "INSERT INTO Clusterinfo (VCname) VALUES('{$val}')" -f $clusterinf.VCname
$cmd.ExecuteNonQuery()   
$val++     

}
$conn.close()

And this is working as intended i believe. Now my question is, how do I continue with the next column? So that I can add DCname, Clustername, TotalPhysicalMemory and so on?

I have been trying with this insert statement:

$cmd.commandtext = "INSERT INTO Clusterinfo (VCname, DCname, CLustername) VALUES('$($clusterinf.VCname)','$($clusterinf.DCname)','$($clusterinf.Clustername)')"

But its not realy giving the wanted result, as it will insert every VCname, DCname and so on, into one row, and not into different rows.

I have been looking at this question: Powershell How to query multiple classes and write in to SQL Table

But I can't seem to get my head around a solution to my own question.

Anybody got an idea on how to solve this?

Frode F. · Accepted Answer · 2014-05-11 20:05:12Z

$clusterinf needs to be the object with the data of the current cluster, not the result of the entire foreach-loop.

I would try something like this:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=Computer;Integrated Security=SSPI;"
$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn

foreach($cluster in (Get-Cluster)){

    $esx = $cluster | Get-VMHost
    $ds = Get-Datastore -VMHost $esx | where {$_.Type -eq "VMFS"}

    $clusterinf = $cluster | Select @{N="VCname";E={$cluster.Uid.Split(':@')[1]}},
        @{N="DCname";E={(Get-Datacenter -Cluster $cluster).Name}},
        @{N="Clustername";E={$cluster.Name}},        
        @{N="TotalPhysicalMemory";E={[Math]::Round(($esx | Measure-Object -Property MemoryTotalMB -Sum).Sum /1KB)}},
        @{N="TotalUsedMemory";E={[Math]::Round(($esx | Measure-Object -Property MemoryUsageMB -Sum).Sum /1KB)}},
        @{N="AvailableMemroy";E={[Math]::Round(($esx | Measure-Object -InputObject {$_.MemoryTotalMB - $_.MemoryUsageMB} -Sum).Sum /1KB)}},
        @{N="TotalCPU";E={[Math]::Round(($esx | Measure-Object -Property CpuTotalMhz -Sum).Sum /1KB)}},
        @{N="TotalUsedCPU";E={[Math]::Round(($esx | Measure-Object -Property CpuUsageMhz -Sum).Sum /1KB)}},
        @{N="AvailableCPU";E={[Math]::Round(($esx | Measure-Object -InputObject {$_.CpuTotalMhz - $_.CpuUsageMhz} -Sum).Sum /1KB)}},
        @{N="TotalDiskSpace";E={[Math]::Round(($ds | where {$_.Type -eq "VMFS"} | Measure-Object -Property CapacityMB -Sum).Sum /1KB)}},
        @{N="ConfiguredDiskSpace";E={[Math]::Round(($ds | Measure-Object -InputObject {$_.CapacityMB - $_.FreeSpaceMB} -Sum).Sum /1KB)}},
        @{N="AvailableDiskSpace";E={[Math]::Round(($ds | Measure-Object -Property FreeSpaceMB -Sum).Sum /1KB)}},
        @{N="Total Configured Memory GB For Powered on VMs";E={[Math]::Round(($_ | Get-VM | Where-Object {$_.PowerState -eq "PoweredOn"} | %{$_.MemoryMB} | Measure-Object -Sum | Select -ExpandProperty Sum)/1KB) } }

    $cmd.commandtext = "INSERT INTO Clusterinfo (VCname, Clustername, TotalPhysicalMemory, TotalUsedMemory, AvailableMemroy, TotalCPU, TotalUsedCPU, AvailableCPU, TotalDiskSpace, ConfiguredDiskSpace, AvailableDiskSpace) VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')" -f $clusterinf.VCname, $clusterinf.Clustername, $clusterinf.TotalPhysicalMemory, $clusterinf.TotalUsedMemory, $clusterinf.AvailableMemroy, $clusterinf.TotalCPU, $clusterinf.TotalUsedCPU, $clusterinf.AvailableCPU, $clusterinf.TotalDiskSpace, $clusterinf.ConfiguredDiskSpace, $clusterinf.AvailableDiskSpace
    $cmd.ExecuteNonQuery()
}

$conn.close()

As a side note, you should replace commandtext with values with parameters. It's less prone to SQL injection and is alot cleaner in my opinon. See this link

Frode F, thank you very much. Its working perfect. I actually did try to do it that way, before with my script, but in the database I only got "System.Object[]" inserted, instead of the values. So I guess by moving $clusterinf inside the foreach loop, its working as intented. Guess I simply could not see that. Again, thanks a million for pointing that out to me. And also, thank you for the side note, i will look into it. Have a great evening.

Collectives™ on Stack Overflow

Use Powershell to insert data into multiple columns in SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related