使用Powershell将数据插入Server中的多列

Question

我试图使用Powershell和Server将从VMware vCenter收集的数据插入数据库。

这就是我现在拥有的：

$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()

我相信这是按照我的计划进行的。现在我的问题是，我如何继续下一篇专栏文章？这样我就可以添加DCname，Clustername，TotalPhysicalMemory等等？

我一直在尝试使用以下插入语句：

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

但是它不真实地给出想要的结果，因为它会将每个VCname、DCname等插入到一行中，而不是插入到不同的行中。

我一直在研究这个问题：Powershell如何查询多个类并写入SQL表

但我似乎无法解决我自己的问题。

有人知道怎么解决这个问题吗？

Answer 1

$clusterinf需要是具有当前集群数据的对象，而不是整个foreach-循环的结果。

我会尝试这样的方法：

$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()

另外，您应该用参数替换命令文本。它不太容易使用SQL注入，而且在我的操作系统中非常干净。请参阅此链接