Powershell: Combining columns from SQL query with WMI results

Question

The First step of the script pulls 3 fields from SQL table. The 2nd steps grabs the server name and loops through the servername to grab more information. My goal is to combine the other 2 columns from SQL table with the information gathers from the WMI in the output.

  #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

Theo · Accepted Answer · 2020-02-23 16:00:40Z

For cleaner code, I would not use the Select-Object with calculated properties here, but instead have the loop emit PsCustomObjects directly.

#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

If you have PowerShell version 3.0 or better, you can change the code to use Get-CimInstance instead of Get-WmiObject for better performance. See Get-CIMInstance Vs Get-WMIObject

Doing that, also means you do not have to convert the LastBootUpTime and LocalDateTime properties, because they will already be true DateTime objects.

The code would then read:

#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

Removing the usage of Select-object and corrected the variable usage solved my problem "foreach ($server in $Servers)". I actually use psCustomObject but i shortened my codes when i posted my question here. Thank you for intoducing Get-CimInstance, I tried using it but unfortunately i have some 2008 OS.

Desinternauta · Accepted Answer · 2020-02-22 02:17:53Z

You can use $Servers instead of $Results for the loop and add $server data to the select

@{n = 'Status'; e = { $Server.Status } }, @{n = 'Purpose'; e = { $Server.Purpose } }

The result code will be like this:

#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
= SQL_Query "SELECT 
                               Servername, Status, Purpose
                        FROM tablename "

#dont need this
#$Results = $Servers | Select-Object -ExpandProperty ServerName

#loop through servers
$Output = @()
$Output = Foreach ($Server in $Servers) {
    #set this variable to be used in the ComputerName param
    $serverName = $Server.Servername

    #dont need this
    #$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
    GET-WmiObject win32_Operatingsystem -ComputerName $serverName -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 } }, @{n = 'Status'; e = { $Server.Status } }, @{n = 'Purpose'; e = { $Server.Purpose } }
    #see the added columns above Status and Purpose
}
#Display Out here
$Output | Select ServerName, LastBootUpTime, Uptime, Status, Purpose | Out-GridView

That is right, simply switching to use $servers resolve my problem!

Collectives™ on Stack Overflow

Powershell: Combining columns from SQL query with WMI results

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related