How to create a spreadsheet from the pipeline

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.

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 {
    $bios = Get-WmiObject -Class Win32_BIOS -Computer $
    $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

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.

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

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.