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:
ManageEngine ADManager Plus - Download Free Trial
Exclusive offer on ADManager Plus for US and UK regions. Claim now!
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.
Liked it…keep it up the Good Work..
thanks
aman
Thank you for the function.
I noticed it produced uncorrect results in my tests.
I first noticed it didn’t report on my top-level folder, then missing result values (0.00).
The problem was it needs a sort on fullname to produce the currect results.
$files = dir c:\temp -include *.ps1,*.txt,*.zip,*.csv -recurse | sort fullname
I need to see how you used New-PSPivotTable so see if I can duplicate your problem.