How to create a spreadsheet from the pipeline

This topic contains 3 replies, has 2 voices, and was last updated by  Odie 2 years, 8 months ago.

  • Author
    Posts
  • #23209

    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

    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

    Don Jones
    Keymaster

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

  • #23235

    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.