PowerShell Pivot Tables

I was working on a question in the Ask Don and Jeff forum about multidimensional arrays. The ultimate goal of sorts was to create an HTML report that contained in essence a pivot table. This device allows you to slice and dice data so that you can identify trends or patterns. In PowerShell, there aren’t too many tools that allow you to “re-organize” an object. For example, say you have a variable that contains the results of querying a few computers for some critical services. But you want a table that shows you the computername as the Y-Axis and each service name as the header on the X axis. For each computer, the intersection should be the status of said property. In other words, a result like this:

Computername Lanmanserver Wuauserv    Bits Spooler Audiosrv
------------ ------------ --------    ---- ------- --------
serenity          Running  Running Running Running  Running
quark                      Stopped Running Running  Running
jdhit-dc01        Running  Running Running Stopped

At a glance I can see where there might be problems. Blanks indicate the service doesn’t exist on that computer. In this case, you might be able to get similar results with some grouping and Format-Table tricks. But more than likely you wouldn’t have something you could export to XML or convert to HTML.
Or say you have a directory listing for a handful of file extensions and you want to see a count of each extension for each folder, perhaps something like this:

Directory                                        .ZIP .BAT .PS1 .TXT
---------                                        ---- ---- ---- ----
C:\scripts\AD-Old\New                               0    0    1    1
C:\scripts\AD-Old                                   1    0   82    1
C:\scripts\ADTFM-Scripts\LocalUsersGroups           0    0    8    0
C:\scripts\ADTFM-Scripts                            0    0   55    3
C:\scripts\en-US                                    0    0    1    0
C:\scripts\GPAE                                     0    0    8    3
C:\scripts\modhelp                                  1    0    0    0
C:\scripts\PowerShellBingo                          0    0    4    0
C:\scripts\PS-TFM                                   1    0   69    2
C:\scripts\PSVirtualBox                             0    0    0    1
C:\scripts\quark                                    0    0    0    1
C:\scripts\TechEd2012                               1    0   11    3
C:\scripts\Toolmaking\old                           0    0   10    0
C:\scripts\Toolmaking                               0    0   48    0
C:\scripts                                         55   13 1133  305

That last row is not a sum, but rather the count of each of those file types in the root of C:\Scripts. I did all of this with a function I call New-PSPivotTable.

Function New-PSPivotTable {
<#
comment based help omitted here
#>

[cmdletbinding(DefaultParameterSetName="Property")]

Param(
[Parameter(Position=0,Mandatory=$True)]
[object]$Inputobject,
[Parameter()]
[String]$yLabel,
[Parameter(Mandatory=$True)]
[String]$yProperty,
[Parameter(Mandatory=$True)]
[string]$xLabel,
[Parameter(ParameterSetName="Property")]
[string]$xProperty,
[Parameter(ParameterSetName="Count")]
[switch]$Count,
[Parameter(ParameterSetName="Sum")]
[string]$Sum,
[Parameter(ParameterSetName="Sum")]
[ValidateSet("None","KB","MB","GB","TB")]
[string]$Format="None",
[Parameter(ParameterSetName="Sum")]
[ValidateScript({$_ -gt 0})]
[int]$Round
)

Begin {
    Write-Verbose "Starting $($myinvocation.mycommand)"
    $Activity="PS Pivot Table"
    $status="Creating new table"
    Write-Progress -Activity $Activity -Status $Status
    #initialize an array to hold results
    $result=@()
    #if no yLabel then use yProperty name
    if (-Not $yLabel) {
        $yLabel=$yProperty
    }
    Write-Verbose "Vertical axis label is $ylabel"
}
Process {    
    Write-Progress -Activity $Activity -status "Pre-Processing"
    if ($Count -or $Sum) {
        #create an array of all unique property names so that if one isn't
        #found we can set a value of 0
        Write-Verbose "Creating a unique list based on $xLabel"
        <#
          Filter out blanks. Uniqueness is case sensitive so we first do a
          quick filtering with Select-Object, then turn each of them to upper
          case and finally get unique uppercase items.
        #>

        $unique=$inputobject | Where {$_.$xlabel} |
         Select-Object -ExpandProperty $xLabel -unique | foreach {
           $_.ToUpper()} | Select-Object -unique
         
        Write-Verbose ($unique -join  ',' | out-String).Trim()
     
    }
    else {
     Write-Verbose "Processing $xLabel for $xProperty"    
    }
   
    Write-Verbose "Grouping objects on $yProperty"
    Write-Progress -Activity $Activity -status "Pre-Processing" -CurrentOperation "Grouping by $yProperty"
    $grouped=$Inputobject | Group -Property $yProperty
    $status="Analyzing data"  
    $i=0
    $groupcount=($grouped | measure).count
    foreach ($item in $grouped ) {
      Write-Verbose "Item $($item.name)"
      $i++
      #calculate what percentage is complete for Write-Progress
      $percent=($i/$groupcount)*100
      Write-Progress -Activity $Activity -Status $Status -CurrentOperation $($item.Name) -PercentComplete $percent
      $obj=new-object psobject -property @{$yLabel=$item.name}  
      #process each group
        #Calculate value depending on parameter set
        Switch ($pscmdlet.parametersetname) {
       
        "Property" {
                    <#
                      take each property name from the horizontal axis and make
                      it a property name. Use the grouped property value as the
                      new value
                    #>

                     $item.group | foreach {
                        $obj | Add-member Noteproperty -name "$($_.$xLabel)" -value $_.$xProperty
                      } #foreach
                    }
        "Count"  {
                    Write-Verbose "Calculating count based on $xLabel"
                     $labelGroup=$item.group | Group-Object -Property $xLabel
                     #find non-matching labels and set count to 0
                     Write-Verbose "Finding 0 count entries"
                     #make each name upper case
                     $diff=$labelGroup | Select-Object -ExpandProperty Name -unique |
                     Foreach { $_.ToUpper()} |Select-Object -unique
                     
                     #compare the master list of unique labels with what is in this group
                     Compare-Object -ReferenceObject $Unique -DifferenceObject $diff |
                     Select-Object -ExpandProperty inputobject | foreach {
                        #add each item and set the value to 0
                        Write-Verbose "Setting $_ to 0"
                        $obj | Add-member Noteproperty -name $_ -value 0
                     }
                     
                     Write-Verbose "Counting entries"
                     $labelGroup | foreach {
                        $n=($_.name).ToUpper()
                        write-verbose $n
                        $obj | Add-member Noteproperty -name $n -value $_.Count -force
                    } #foreach
                 }
         "Sum"  {
                    Write-Verbose "Calculating sum based on $xLabel using $sum"
                    $labelGroup=$item.group | Group-Object -Property $xLabel
                 
                     #find non-matching labels and set count to 0
                     Write-Verbose "Finding 0 count entries"
                     #make each name upper case
                     $diff=$labelGroup | Select-Object -ExpandProperty Name -unique |
                     Foreach { $_.ToUpper()} |Select-Object -unique
                     
                     #compare the master list of unique labels with what is in this group
                     Compare-Object -ReferenceObject $Unique -DifferenceObject $diff |
                     Select-Object -ExpandProperty inputobject | foreach {
                        #add each item and set the value to 0
                        Write-Verbose "Setting $_ sum to 0"
                        $obj | Add-member Noteproperty -name $_ -value 0
                     }
                     
                     Write-Verbose "Measuring entries"
                     $labelGroup | foreach {
                        $n=($_.name).ToUpper()
                        write-verbose "Measuring $n"
                       
                        $measure= $_.Group | Measure-Object -Property $Sum -sum
                        if ($Format -eq "None") {
                            $value=$measure.sum
                        }
                        else {
                            Write-Verbose "Formatting to $Format"
                             $value=$measure.sum/"1$Format"
                            }
                        if ($Round) {
                            Write-Verbose "Rounding to $Round places"
                            $Value=[math]::Round($value,$round)
                        }
                        $obj | Add-member Noteproperty -name $n -value $value -force
                    } #foreach
                }        
        } #switch

        #add each object to the results array
      $result+=$obj
    } #foreach item
} #process
End {
    Write-Verbose "Writing results to the pipeline"
    $result
    Write-Verbose "Ending $($myinvocation.mycommand)"
    Write-Progress -Completed -Activity $Activity -Status "Ending"
}
} #end function

There is a lot going on here but I’ve tried to use comments and Write-Verbose statements to make it easier to track. In short, the function takes a collection of data and builds a table based on the property names and values you specify. You can build a table on a property like I did with the services. You can create a table based on count or on size. For the latter you need to also specify what property name to measure to calculate the size.

The function is writing an object to the pipeline which means you can transform the output however you need it.

PS C:\> $files=dir c:\scripts -include *.ps1,*.txt,*.zip,*.bat -recurse
PS C:\> $pivot=New-PSPivotTable $files -yProperty Directory -xlabel Extension -sum length -round 2 -format kb
PS C:\> $pivot[0] | format-table -auto

Directory             .ZIP .BAT .PS1 .TXT
---------             ---- ---- ---- ----
C:\scripts\AD-Old\New    0    0 2.32 1.49
PS C:\> $pivot | convertto-html -Title "Extension Pivot" | out-file extpivot.htm

Honestly, I think the best thing is for you to download New-PSPivotTable and try it out. The download has full help and examples which you should read. I really hope you find it useful and if you run into some scenarios where this doesn’t work the way you think it should, please let me know.

Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to FriendFeed Post to Google Buzz Post to Ping.fm Post to Reddit Post to Slashdot Post to StumbleUpon Post to Technorati


About the Author

PowerShell.org Announcer

This is the official account for PowerShell.org and sponsor announcements.