How to create a spreadsheet from the pipeline

PowerShell Help
1

I have a task that I need to run and pull information from several places into a single spreadsheet. I believe I can get everything using PowerShell but I need help on trying to get everything into a single spreadsheet if its possible.

To make it easy and to see the concept I will just start with a couple items. I need to pull together all Servers with the OS and then query the machine for the Serial number of the machine. I know how to do each independently but don’t know if there is a way to have a spreadsheet with 3 columns with servername, OS, serial number. Here is the script I am thinking of using:

For servername and OS:

Get-ADComputer -Filter * -Properties operatingsystem | Where operatingsystem -match ‘server’ | Select Name, OperatingSystem

For Serial Number:

gwmi win32_bios

I can piple the first script into the second but I don’t think all the properties from the first come over but I may be wrong.

2

I would write a section function called Add-WmiBiosInfo. I’d pipe input to it (the ADComputer objects), let it query each one, and add the BIOS information as a property of the ADComputer object (use Add-Member to do so). Then, output the new object to the pipeline.

Untested, non-Mother-approved:

function Add-WmiBiosInfo {
  [CmdletBinding()]
  Param(
    [Parameter(ValueFromPipeline=$True)][object]$inputObject
  )
  PROCESS {
    $bios = Get-WmiObject -Class Win32_BIOS -Computer $inputobject.name
    $inputobject | Add-Member -Member NoteProperty -Name BIOSSerial -Value ($bios.serialnumber) -Pass
  }
}

Get-ADComputer -filter * -Properties operatingsystem | where-object { $_.operatingsystem -match “server” } | Add-WmiBiosInfo | Select Name,BIOSSerial,OperatingSystem | Export-CSV results.cv

That’s the super-quick and dirty version of what I’m describing. The ultimate idea is that Export-CSV can only take one kind of object, and it’ll give you “spreadsheet” columns for that object’s properties. So you need to munge all the properties you want onto a single object.

3

BTW, spreadsheets as a database, suck. Any way I can convince you to use maybe SQL Express?

4

the main reason for the spreadsheet is that it is required by corporate audit to verify licensing with Microsoft on all servers. So they have a predefined spreadsheet and I want to query for the data which includes physical vs. vm, # of procs, machine sn, OS, among other things.