How to create a spreadsheet from the pipeline

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Odie Odie 1 year, 9 months ago.

  • Author
    Posts
  • #23209
    Profile photo of Odie
    Odie
    Participant

    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.

  • #23210
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #23211
    Profile photo of Don Jones
    Don Jones
    Keymaster

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

  • #23235
    Profile photo of Odie
    Odie
    Participant

    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.

You must be logged in to reply to this topic.