1 Powershell脚本2 SQL表
1 Powershell脚本2 SQL表
提问于 2015-07-07 13:25:31
在中填充第二个表时遇到了问题。我的第一个表ServerList包含下面屏幕截图中的所有信息。然后，第二个表ServerDrives应该包含有关硬盘规范的所有信息(空闲、使用、信件，以及连接到的硬盘驱动器)。
目前，我的powershell脚本从ServerList中拉出列ServerName中的所有值，并将信息推入powershell脚本，然后该脚本将填充OS、RAM等。
ServerID问题:我不知道如何让同一个powershell脚本使用表 ServerList 中相同的主键来填充 ServerDrives 表。因为我希望该表中的每一行对于中的每个服务器都是一个行。我目前在脚本中有一种收集硬盘信息的方法，但我不知道如何使用该信息填充我的空表*
目标：将ServerID (ServerList中的主键)和ServerDriveID (ServerDrives中的主键)用于同一服务器(QAGGAPP01、QAGGAPP03、QAGGAPP05等)。为了让变量$disks被推到数据库表ServerDrives中，使用适当的硬盘驱动器、字母和规格。每个服务器都有一个或两个硬盘驱动器。
代码：
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代码中才能更容易地查看被推送到服务器的内容，而不必每次都重新打开表：
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-07-15 01:38:58
根据另一条评论中的建议得到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()
}
}Stack Overflow用户
发布于 2015-07-07 14:12:31
当您询问服务器名称时，您不能直接获取主密钥吗？
$query = "SELECT ServerID, ServerName FROM ServerList"现在您可以使用WHERE ServerID = $CurrentServerID或somesuch。
您没有运行任何INSERT语句。这是一个选择，后面跟着更新。如果您需要主键，只需向服务器询问它。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31269860
复制相关文章
相似问题
腾讯云开发者
