Formatted Grouping in PowerShell

In a previous article I guided you through using Group-Object to slice and dice data gathered from PowerShell. You can easily export or convert that output like you would any other PowerShell data. You can either process the grouped information as it is or process each group of related items. The choice is yours. But I thought I’d lend a hand and show you a few ways to take advantage of your grouped output in terms of reporting. I’ll start with HTML.

Using HTML with PowerShell Reporting

Normally when you convert something to HTML, you get a table view of the PowerShell objects. But when working with grouped data, I’m betting you want to break each group down. For example, I’m going to get the 100 most recent entries in my System eventlog and group them on the Source property.

$logs = Get-Eventlog System -Newest 100 | Group -property Source | Sort Count -Descending

I want to create an HTML report with a table for each group. I can accomplish this with a series of HTML fragments. I’ll initialize an array that will hold them all.

$fragments = @()

Next, I can process each item in $logs.

foreach ($item in $logs) {
 $fragments+="<h3>$($item.Name) - $($item.count)</h3>"
 $fragments+= $item.Group | Select TimeGenerated,EntryType,Message | ConvertTo-Html -Fragment
} 

I added a heading for each group that includes the name and the total number of entries for each. You can use as much HTML as you want. Then I am getting each entry from the group and only selecting the properties I’m interested in. The important thing is to use the –Fragment parameter because I don’t want a complete document.
It isn’t required, but to make the report pretty and portable, I am going to insert my style sheet into the header.

$head = @"
<Title>System Events</Title>
<Style>
Body {
font-family: "Tahoma", "Arial", "Helvetica", sans-serif;
background-color:#F0E68C;
}
table
{
border-collapse:collapse;
width:60%
}
td
{
font-size:12pt;
border:1px #0000FF solid;
padding:5px 5px 5px 5px;
}
th
{
font-size:14pt;
text-align:left;
padding-top:5px;
padding-bottom:4px;
background-color:#0000FF;
color:#FFFFFF;
}
name tr
{
color:#000000;
background-color:#0000FF;
}
</style>
"@

All that remains now is to create the final HTML document.

ConvertTo-Html -Body $fragments -Head $head | out-file c:\work\sys.htm -Encoding ascii

Note that if you want to include a title and you are inserting a header, put the Title in the header. Otherwise the –Title parameter will be ignored. My end result is an HTML report like this:

Example of HTML report. (Image Credit: Jeff Hicks)
Example of HTML report. (Image Credit: Jeff Hicks)

If you prefer, you can also create fragments as a list. Use the –AS parameter with a value of List.

Using the Format-List Cmdlet

Speaking of lists, perhaps that is all you need. You can use PowerShell’s formatting cmdlets to also prepare nicely formatted reports that you can send to a file or printer. I’m going to get all the processes on my computer that have a company property value and group them.

$p = Get-Process | Where {$_.company} | Group Company | Sort Name

You might think all you have to do is this after looking at help for Format-List.

$p | format-list -GroupBy Name

But that won’t give you the result you were probably expecting.

Formatting a group object is tricky and requires a little more tinkering to format correctly. (Image Credit: Jeff Hicks)
Formatting a group object is tricky and requires a little more tinkering to format correctly. (Image Credit: Jeff Hicks)

The problem is that I am trying to format a group object, which is a bit more complicated. I’ve actually made things more difficult by grouping. Instead I can let Format-List handle the grouping.

$p = Get-Process | Where {$_.company}
$p | format-List -GroupBy Company

This looks better.

Using Format-List to handle grouping. (Image Credit: Jeff Hicks)
Using Format-List to handle grouping. (Image Credit: Jeff Hicks)

If you try this and keep scrolling, then you’ll see company names repeated. This is because Format-List is processing items as they are gathered. The recommendation is to sort first.

$p | Sort Company | format-List -GroupBy Company

Because I’m using Format-List and there is a default list view for process objects, I’m getting those properties, but I can select anything that I want.

$p | Sort Company | format-List -GroupBy Company -Property Id,Name,WS,VM,Path

Selecting properties and formatting with Format-List. (Image Credit: Jeff Hicks)
Selecting properties and formatting with Format-List. (Image Credit: Jeff Hicks)

This would be easy enough to save to file.

$p | Sort Company | format-List -GroupBy Company -Property Id,Name,WS,VM,Path | out-file c:\work\proclist.txt

This works great when you are interested in a few properties and formats quite easily.

Format-Table

You can achieve similar results with Format-Table, which also has a –GroupBy parameter. Again, you should sort objects on the property you plan on grouping.

get-process | where {$_.company} | sort company | format-table -GroupBy Company

Here is some of the output:

Using Format-Table to group objects. (Image Credit: Jeff Hicks)
Using Format-Table to group objects. (Image Credit: Jeff Hicks)

Both formatting cmdlets let you use a custom groupby property as well, similar to what I showed you with Group-Object. The tricky part to remember is that you will also need to sort on the same custom property.

$grouped = {
Switch ($_.WS) {
{$_ -ge 300MB} {"Large" ;break}
{$_ -ge 100MB} {"Medium" ;break}
{$_ -le 2MB} {"Small" ; break}
Default { "Normal"}
} #close switch
}
get-process | Sort $Grouped | format-table -GroupBy @{Name="VMSize";Expression=$grouped}

I created a scriptblock to that assigns a value depending on the size of the Workingset property. I can use the same scriptblock for both the sort and grouping.

Using the script block to sort and group. (Image Credit: Jeff Hicks)
Using the script block to sort and group. (Image Credit: Jeff Hicks)

I encourage you to look at help and examples for Sort-Object and Format-Table to learn more about these customizations.
I should point that that there appears to be a formatting issue with some file types. If I try this command:

dir c:\scripts -file | Where {$_.extension} | sort Extension | format-table -GroupBy Extension

Output will be properly grouped although you don’t see the group heading.

Formatting issue with different file types. (Image Credit: Jeff Hicks)
Formatting issue with different file types. (Image Credit: Jeff Hicks)

However, if I specify properties, then it does work.

dir c:\scripts -file | Where {$_.extension} | sort Extension | format-table -GroupBy Extension -Property LastWriteTime,CreationTime,Length,Name

Specifying properties fixes the formatting issue with different file types. (Image Credit: Jeff Hicks)
Specifying properties fixes the formatting issue with different file types. (Image Credit: Jeff Hicks)

Not sure why this is the case but something to be aware of.

I think there is lot of value in seeing grouped output. And with PowerShell, you aren’t limited by an object’s property. It might take a little PowerShell kung fu but with practice you can get the output you need.