脚本的第一步从 SQL 表中提取 3 个字段。第 2 步抓取服务器名称并循环遍历服务器名称以获取更多信息。我的目标是将 SQL 表中的其他 2 列与输出中从 WMI 收集的信息结合起来。
#SQL Variables
$SQLServer = 'remoteservername'
$Database = 'Dbname'
#Create a SQL Query function
Function SQL_Query ($Query) {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$Database;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $Query
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$a = $SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
} #End SQL Query Function
$Servers = SQL_Query "SELECT
Servername, Status, Purpose
FROM tablename "
$Results = $Servers | Select-Object -ExpandProperty ServerName
#loop through servers
$Output = @()
$Output =Foreach ($Server in $Results)
{
$Servers # This variable stores Servername, Status, and Purpose information. I want the Status and Purpose fields to show up in the loop below. The idea is if the Servername from $servers matches $server,add the extra 2 fields from #Servers
$BootTime = GET-WmiObject win32_Operatingsystem -ComputerName $server -EA STOP | select @{n='ServerName';e={$_.csname}},@{n='LastBootUpTime';e={$_.ConverttoDateTime($_.lastbootuptime)}}`
,@{n='LocalTime';e={$_.ConverttoDateTime($_.LocalDateTime)}} ,@{n='UpTime';e={"Uptime:" + ((GET-DAte) - $_.ConverttoDateTime($_.lastbootuptime)).Days + " Days " +`
((GET-DAte) - $_.ConverttoDateTime($_.lastbootuptime)).Hours + " Hours " + ((GET-DAte) - $_.ConverttoDateTime($_.lastbootuptime)).Minutes + " Minutes "}},@{n='OS';e={$_.caption}}
}
#Display Out here
$Boottime | Select ServerName,LastBootUpTime,Uptime,Status,Purpose |Out-GridView
对于更简洁的代码，我不会在Select-Object这里使用with 计算属性，而是让循环PsCustomObject直接发出s。
#loop through servers
$result = foreach ($server in $Servers) {
try {
$os = Get-WmiObject Win32_Operatingsystem -ComputerName $server.ServerName -ErrorAction Stop
$lastBoot = $os.ConverttoDateTime($os.LastBootUpTime)
$localTime = $os.ConverttoDateTime($os.LocalDateTime)
$upTime = (Get-Date) - $lastBoot
# output a PsObject
[PsCustomObject]@{
'ServerName' = $os.CSName
'LastBootUpTime' = $lastBoot
'LocalTime' = $localTime
'UpTime' = '{0} Days {1} Hours {2} Minutes' -f $upTime.Days, $upTime.Hours, $upTime.Minutes
'OS' = $os.Caption
'Status' = $server.Status
'Purpose' = $server.Purpose
}
}
catch {
Write-Warning "WMI Query failed on computer '$($server.ServerName)'"
}
}
# Display the result here
$result | Out-GridView
如果您有 PowerShell 3.0 或更高版本，则可以更改要使用的代码，Get-CimInstance而不是Get-WmiObject为了提高性能。请参阅Get-CIMInstance 与 Get-WMIObject
这样做也意味着您不必转换LastBootUpTime和LocalDateTime属性，因为它们已经是真正的 DateTime 对象。
然后代码将显示为：
#loop through servers
$result = foreach ($server in $Servers) {
try {
$os = Get-CimInstance -ClassName Win32_Operatingsystem -ComputerName $server.ServerName -ErrorAction Stop
$upTime = (Get-Date) - $os.LastBootUpTime
# output a PsObject
[PsCustomObject]@{
'ServerName' = $os.CSName
'LastBootUpTime' = $os.LastBootUpTime
'LocalTime' = $os.LocalDateTime
'UpTime' = '{0} Days {1} Hours {2} Minutes' -f $upTime.Days, $upTime.Hours, $upTime.Minutes
'OS' = $os.Caption
'Status' = $server.Status
'Purpose' = $server.Purpose
}
}
catch {
Write-Warning "WMI Query failed on computer '$($server.ServerName)'"
}
}
# Display the result here
$result | Out-GridView
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句