1 Powershell脚本2 SQL表

我无法填充我的SQL Server Management Studio中的第二个表。我的第一个表ServerList包含下面屏幕截图中的所有信息。第二个表ServerDrives应该包含所有关于硬盘规格的信息（免费的，使用过的，字母的，以及连接到硬盘的服务器）。

目前，我有我的PowerShell脚本从ServerList所有值，并将信息推入PowerShell脚本，然后将填充操作系统，RAM等等

问题：我不知道如何使用相同的ServerID脚本从表ServerList使用相同的主键ServerID填充我的ServerDrives表。 因为我希望该表中的每一行都是ServerList每个服务器的一行。 我在脚本目前有一种方法来收集硬盘驱动器的信息，但我不知道如何使用该信息来填充我的空表

目标：使ServerID（ ServerList主键）和ServerDriveID（ServerDrives中的主键）适用于同一服务器（QAGGAPP01，QAGGAPP03，QAGGAPP05等）。要使用合适的硬盘驱动器字母和规格将variables$disks推入数据库表ServerDrives 。每台服务器都有1个或2个硬盘。

码：

  Write-Output " `n Start of Hal0 `n"; #Start of Server Connection $connectionString = "Server=QAUTILITYDB01;Database=Hal0Test;Integrated Security=True;" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $connection.Open() $command = $connection.CreateCommand() $ServerArray = [System.Collections.ArrayList]@() $query = "SELECT ServerName FROM ServerList" $command.CommandText = $query $ServerNames = $command.ExecuteReader() $table = new-object “System.Data.DataTable” $table.Load($ServerNames) $ServerArray = $table | select -Expand ServerName $ServerArray | ForEach-Object { #$ServerArray returns each server name #Operating System $SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername $_ $os = Get-WmiObject -Class Win32_OperatingSystem -Computer $_ #Server's Memory (RAM) Usage Average $TotalRAM = [math]::round($SystemInfo.TotalVisibleMemorySize/1MB,4) $FreeRAM = [math]::round($SystemInfo.FreePhysicalMemory/1MB,3) $UsedRAM = $TotalRAM - $FreeRAM $RAMPercentFree = [math]::round(($FreeRAM / $TotalRAM) * 100,3) $RAMPercentUsed = [math]::round(($UsedRAM / $TotalRAM) * 100,3) #Server's CPU (Proccess) Usage Average $cpuAVG = Get-WmiObject -computername $_ win32_processor | Measure-Object -property LoadPercentage -Average | Select Average #Server's Hard Drives (MB) Free/Used $disks = Get-WmiObject -Class Win32_LogicalDisk -Computer $_ | Where-Object {$_.DriveType -eq 3} | ForEach-Object { '{0} {1:D} MB Free/{2:D} MB Used' -f $_.DeviceID, [int]($_.FreeSpace/1MB), [int]($_.Size/1MB) } #Value Types being pushed to Server $command.CommandText = "UPDATE ServerList SET FQDN = '$_', TotalRAM= '$TotalRAM' , FreeRAM= '$FreeRAM' , UsedRAM= '$UsedRAM' , RAMPercentFree = '$RAMPercentFree' , RAMPercentUsed= '$RAMPercentUsed' , OS = '$($os.Caption)' WHERE ServerName LIKE '$($os.PSComputerName)%';" $result = $command.ExecuteNonQuery() } Write-Output "`n End of Hal0";

ServerDrives：

在这里输入图像说明

HTML预览，我只有在我的PowerShell代码只是为了更容易看到什么被推送到服务器，而不必每次重新打开表格：

在这里输入图像说明

根据另一个评论中的建议获取serverID 。您可以更改您的代码，如下所示：

使用“SELECT serverID，serverName FROM serverList”获取名称和ID
管道结果表并分别读取$ serverName和$ serverID
当您迭代磁盘以插入/更新$ serverID时使用此信息

执行：

 #Start of server Connection $ConnectionString = "<CONNECTION>" $Connection = New-Object System.Data.SqlClient.SqlConnection $Connection.ConnectionString = $ConnectionString $Connection.Open() $Command = $Connection.CreateCommand() # Selects server id and name and puts it into the table $serverInfoQuery = "SELECT serverID, serverName FROM serverList" $Command.CommandText = $serverInfoQuery $serverInfoReader = $Command.ExecuteReader() $serverInfo = New-Object System.Data.DataTable $serverInfo.Load($serverInfoReader) $serverInfo | ForEach-Object { # Read Name and ID from table $serverName = $_.serverName $serverID = $_.serverID # Operating System $SystemInfo = Get-WmiObject -Class Win32_OperatingSystem -computername $serverName $os = Get-WmiObject -Class Win32_OperatingSystem -Computer $serverName $TotalRAM = [math]::round($SystemInfo.TotalVisibleMemorySize/1MB,4) $FreeRAM = [math]::round($SystemInfo.FreePhysicalMemory/1MB,3) $UsedRAM = $TotalRAM - $FreeRAM $RAMPercentFree = [math]::round(($FreeRAM / $TotalRAM) * 100,3) $RAMPercentUsed = [math]::round(($UsedRAM / $TotalRAM) * 100,3) # server's CPU (Proccess) Usage Average $cpuAVG = Get-WmiObject -computername $serverName win32_processor | Measure-Object -property LoadPercentage -Average | Select Average # Value Types being pushed to server $Command.CommandText = " UPDATE serverList SET FQDN = '$serverName', TotalRAM= '$TotalRAM', FreeRAM= '$FreeRAM', UsedRAM= '$UsedRAM', RAMPercentFree = '$RAMPercentFree', RAMPercentUsed= '$RAMPercentUsed', OS = '$($os.Caption)' WHERE serverID = $serverID" $result = $Command.ExecuteNonQuery() # server's Hard Drives (MB) Free/Used $disks = Get-WmiObject -Class Win32_LogicalDisk -Computer $serverName | Where-Object {$_.DriveType -eq 3} | ForEach-Object { $DriveLetter = $_.DeviceID $DiskFree = [Math]::Round($_.FreeSpace/1MB, 0) $DiskUsed = [Math]::Round($_.Size/1MB, 0) $Command.CommandText = " IF EXISTS (SELECT * FROM serverDrives WHERE serverID = '$serverID' AND DriveLetter = '$DriveLetter') BEGIN UPDATE serverDrives SET DriveLetter = '$DriveLetter', DiskFree = '$DiskFree', DiskUsed = '$DiskUsed', serverID = '$serverID' WHERE serverID = '$serverID' AND DriveLetter = '$DriveLetter' END ELSE BEGIN INSERT INTO serverDrives (DriveLetter, DiskFree, DiskUsed, serverID) VALUES ('$DriveLetter','$DiskFree','$DiskUsed', '$serverID') END" $command.ExecuteNonQuery() } }

当你要求服务器名称时，你不能直接获取主键吗？

 $query = "SELECT serverID, serverName FROM serverList"

现在你可以使用WHERE serverID = $CurrentserverID或者somesuch。

您没有运行任何INSERT语句。这是一个SELECT，然后是UPDATE。如果你需要主键，只需要问服务器。