im trying to take a list of computers and pull information from them and export it into an excel sheet where i have all information of the systems per row. im trying to get information like:

• Computername
• OS
• BIOS
• Last boot
• Etc

the code i wrote:

$computers = Get-Content "C:\example\Computers.txt)"


$OSinfo = Get-WmiObject Win32_OperatingSystem -ComputerName $computers | Select PSComputerName, Caption, OSArchitecture, Version, BuildNumber
$BIOSinfo = Get-WmiObject -Class Win32_BIOS -ComputerName $computers | Select-Object PSComputerName, Manufacturer, SerialNumber, SMBIOSBIOSVersion
$lastboot = Get-CimInstance win32_operatingsystem -ComputerName $computers | select csname, lastbootuptime

$objects  =$OSinfo  =$BIOSinfo  =$lastboot

$objects | Export-csv -Path "C:\example\output.csv"

however, i cant figure out how to put all this information into one spreadsheet tab. i also cant figure out how to tell the script if it cant ping or find it, to just say "offline"

CodePudding user response：

This should work for you:

$computers = Get-Content "C:\example\Computers.txt"

# Define the properties we want to select from each query
$osColumns = 'PSComputerName', 'Caption', 'OSArchitecture', 'Version', 'BuildNumber'
$biosColumns = 'PSComputerName', 'Manufacturer', 'SerialNumber', 'SMBIOSBIOSVersion'
$lastbootColumns = 'csname', 'lastbootuptime'

# Obtain the desired information from Wmi/Cim
$OSinfo = Get-WmiObject Win32_OperatingSystem -ComputerName $computers | Select-Object $osColumns
$BIOSinfo = Get-WmiObject -Class Win32_BIOS -ComputerName $computers | Select-Object $biosColumns
$lastboot = Get-CimInstance win32_operatingsystem -ComputerName $computers | Select-Object $lastbootColumns

# Iterate over the computers and collect the computer-specific information
$computerInfo = foreach( $computer in $computers ) {
  $thisOSInfo = $OSInfo | Where-Object { $_.PSComputerName -eq $computer }
  $thisBIOSInfo = $BIOSinfo | Where-Object { $_.PSComputerName -eq $computer }
  $thisLastboot = $lastboot | Where-Object { $_.csname -eq $computer }

  # This row object will be returned as a PSCustomObject for export
  $row = @{}
  foreach( $prop in @( $thisOSInfo.psobject.Properties.Name ) ) {
    if( $prop -in $osColumns ) {
      $row[$prop] = $thisOSInfo.$prop
    }
  }

  foreach( $prop in @( $thisBIOSInfo.psobject.Properties.Name ) ) {
    if( $prop -in $biosColumns ) {
      $row[$prop] = $thisBIOSInfo.$prop
    }
  }

  foreach( $prop in @( $thisLastboot.psobject.Properties.Name ) ) {
    if( $prop -in $lastbootColumns ) {
      $row[$prop] = $thisLastboot.$prop
    }
  }
  
  [PSCustomObject]$row
}

$computerInfo | Export-Csv -NoTypeInformation \path\to\report.csv

The way this works:

• Well-define the columns we want from each query. We'll use those later to make sure we only grab properties we want from the resulting query responses. Note that Get-CimInstance will have the csname property instead of PSComputerName.
• Query WMI classes for information on known computers, store each response in its own variable.
• Iterate over each computer queried, building that row of information from each query response.
  • Only add the query property if it exists in the corresponding *Columns variable. This is less necessary for your code but becomes necessary when exporting some other, non-WMI/Cim objects, or if you don't use Select-Object earlier, to reduce noise.
• Each row is defined as a hastable for ease of modification, then converted to a PSCustomObject to it behaves more like an immutable object upon return. This is necessary for the output of Export-Csv to output the way you want.
• Export the computer information with Export-Csv to a CSV file, which is now an array of PSCustomObjects where each row should be a single computer you've queried.