我在 SQL Server Management Studio 中填充第二个表时遇到问题。 我的第一个表 ServerList 包含下面屏幕截图中的所有信息。第二个表 ServerDrives 应该包含有关硬盘规范的所有信息(免费、二手、盘符，以及硬盘连接到哪个服务器)。

目前，我的 powershell 脚本从 ServerList 中提取列 ServerName 中的所有值，并将信息推送到 powershell 脚本中，然后填充操作系统、RAM、等等

问题:我不确定如何让相同的 powershell 脚本使用来自表 ServerList 的相同主键 ServerID 来填充我的 ServerDrives表。因为我希望该表中的每一行都对应于 ServerList 中的每个服务器。我目前在脚本中有一种收集硬盘驱动器信息的方法，但我不确定如何使用该信息来填充我的空表

目标:让 ServerID(ServerList 中的主键)和 ServerDriveID(ServerDrives 中的主键)用于同一台服务器 (QAGGAPP01 、QAGGAPP03、QAGGAPP05 等)。将变量 $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";

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

关于sql-server - 1 Powershell 脚本 2 SQL 表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31269860/