Sorting out -GroupBy

Sorting out -GroupBy

  • Comments 5

Did you realize that Format-Table had a parameter -GroupBy?  This tells Format-Table to generate a series of tables instead of a single table.  The best way to get this in focus is to show an example. 

For the purposes of this blog entry, I'm going to use the aliases GSV for Get-Service and FT for Format-Table.  Also, I don't want too much data for the examples so I'm going to leverage the fact that GSV supports wildcards and specify s[p-z]*  which says give me all the services that start with "s" and whose second character is between "p" and "z" inclusive.  There is nothing special about this, I just did some quick experimentation and that seemed to produce a reasonable dataset for this blog (and it allows me to show off wildcarding in GSV :-) ).

First let's look at the output of GSV and then explore what additional information is available:

PS> gsv s[p-z]* |ft

Status   Name               DisplayName
------   ----               -----------
Running  Spooler            Print Spooler
Stopped  SQLBrowser         SQL Server Browser
Stopped  SQLWriter          SQL Server VSS Writer
Running  SRUserService      IT Connection Manager
Running  SSDPSRV            SSDP Discovery
Running  stisvc             Windows Image Acquisition (WIA)
Stopped  swprv              Microsoft Software Shadow Copy Prov...
Running  SysMain            Superfetch


PS> gsv s[p-z]* |Get-Member -MemberType Property


   TypeName: System.ServiceProcess.ServiceController

Name                MemberType Definition
----                ---------- ----------
CanPauseAndContinue Property   System.Boolean CanPauseAndContinue {get;}
CanShutdown         Property   System.Boolean CanShutdown {get;}
CanStop             Property   System.Boolean CanStop {get;}
Container           Property   System.ComponentModel.IContainer Containe...
DependentServices   Property   System.ServiceProcess.ServiceController[]...
DisplayName         Property   System.String DisplayName {get;set;}
MachineName         Property   System.String MachineName {get;set;}
ServiceHandle       Property   System.Runtime.InteropServices.SafeHandle...
ServiceName         Property   System.String ServiceName {get;set;}
ServicesDependedOn  Property   System.ServiceProcess.ServiceController[]...
ServiceType         Property   System.ServiceProcess.ServiceType Service...
Site                Property   System.ComponentModel.ISite Site {get;set;}
Status              Property   System.ServiceProcess.ServiceControllerSt...

 

Notice that the ServiceController objects emitted by Get-Service (GSV) contain the field ServiceType .  Let's do this again but this time have Format-Table Group the services by ServiceType:


PS> gsv s[p-z]* |ft -GroupBy ServiceType


   ServiceType: Win32OwnProcess, InteractiveProcess

Status   Name               DisplayName
------   ----               -----------
Running  Spooler            Print Spooler


   ServiceType: Win32OwnProcess

Status   Name               DisplayName
------   ----               -----------
Stopped  SQLBrowser         SQL Server Browser
Stopped  SQLWriter          SQL Server VSS Writer
Running  SRUserService      IT Connection Manager


   ServiceType: Win32ShareProcess

Status   Name               DisplayName
------   ----               -----------
Running  SSDPSRV            SSDP Discovery


   ServiceType: Win32OwnProcess

Status   Name               DisplayName
------   ----               -----------
Running  stisvc             Windows Image Acquisition (WIA)
Stopped  swprv              Microsoft Software Shadow Copy Prov...


   ServiceType: Win32ShareProcess

Status   Name               DisplayName
------   ----               -----------
Running  SysMain            Superfetch

Now you get a series of tables. 

But wait.  Take a close look at those tables.  Notice that you have 2 tables for both Win32ShareProcess and Win32OwnProcess.  The reason for that is that is that Format-Table (ft) is STREAM-ORIENTED.  By that I mean that it gets an object and processes it and then moves on to the next.  It does that so that it doesn't have to keep all the objects in memory.  When it is done with the object, it is done with the object. 

The only exception to this is when you specify -AutoSize . This tells Format-Table to collect all the objects and calculate the optimal column widths.

So the results we got are not quite what we wanted.  The solution is to sort the object stream prior to sending it to Format-Table (Yes yes yes - let me preempt the question - given these semantics we could have chosen to do the sort in Format-Table.  The reason we didn't is that it would be wasteful if the stream was already properly sorted).  So lets see what that looks like:

PS> gsv s[p-z]* |Sort ServiceType |ft -GroupBy ServiceType


   ServiceType: Win32OwnProcess

Status   Name               DisplayName
------   ----               -----------
Stopped  SRUserService      IT Connection Manager
Running  stisvc             Windows Image Acquisition (WIA)
Stopped  swprv              Microsoft Software Shadow Copy Prov...
Stopped  SQLBrowser         SQL Server Browser
Stopped  SQLWriter          SQL Server VSS Writer


   ServiceType: Win32ShareProcess

Status   Name               DisplayName
------   ----               -----------
Running  SysMain            Superfetch
Running  SSDPSRV            SSDP Discovery


   ServiceType: Win32OwnProcess, InteractiveProcess

Status   Name               DisplayName
------   ----               -----------
Running  Spooler            Print Spooler

Now that is much better but notice that within a table, things are not sorted by Status.  No problem.  Did you realize that sort can do a multi-key sort?  After we sort by Status, we'll figure out that we also want it sorted by Name as well so let's do it all at once shall we?

PS> gsv s[p-z]* |Sort ServiceType,Status,Name |ft -GroupBy ServiceType


   ServiceType: Win32OwnProcess

Status   Name               DisplayName
------   ----               -----------
Stopped  SQLBrowser         SQL Server Browser
Stopped  SQLWriter          SQL Server VSS Writer
Stopped  SRUserService      IT Connection Manager
Stopped  swprv              Microsoft Software Shadow Copy Prov...
Running  stisvc             Windows Image Acquisition (WIA)


   ServiceType: Win32ShareProcess

Status   Name               DisplayName
------   ----               -----------
Running  SSDPSRV            SSDP Discovery
Running  SysMain            Superfetch


   ServiceType: Win32OwnProcess, InteractiveProcess

Status   Name               DisplayName
------   ----               -----------
Running  Spooler            Print Spooler

Cheers! 

Jeffrey Snover [MSFT]
Windows PowerShell/MMC Architect
Visit the Windows PowerShell Team blog at:    http://blogs.msdn.com/PowerShell
Visit the Windows PowerShell ScriptCenter at:  http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
  • Interesting to see from the list of services displayed that you're running this on Vista:-)  How did you install yours?  Any chance we could have a copy to try?

    Thanks!

    Chris

  • > Interesting to see from the list of services displayed that you're running this on Vista:-)  How did you install yours?  Any chance we could have a copy to try?

    It's coming.  If the world didn't speak so many languages, it would be out by now.  :-)

    Again - apologizes for the delay in getting you Vista.  The team feels horrible about the situation.

    Jeffrey Snover [MSFT]

    Windows PowerShell/MMC Architect

    Visit the Windows PowerShell Team blog at:    http://blogs.msdn.com/PowerShell

    Visit the Windows PowerShell ScriptCenter at:  http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

  • Good demo.

    It's the little things that really make PS a powerful tool.

    It seems to me that PS is so rich and deep that it is goiong to take many, many bloggers to uncover most of it's capabilities.

  • I think it would be even better to have aggregate functions here. Count (as in group-object) and also sum, max, min, avg, etc.

    Are you planning to add something like that in future versions of format-table or group-object or is there another simple way to get these aggregate values in PowerShell?

    Cheers

     Klaus

  • > I think it would be even better to have aggregate functions here. Count (as in group-object) and also sum, max, min, avg, etc

    We've talked about doing this a number of times but it is not currently on the list of things to do.  Please request it.

    Jeffrey Snover [MSFT]

    Windows PowerShell/MMC Architect

    Visit the Windows PowerShell Team blog at:    http://blogs.msdn.com/PowerShell

    Visit the Windows PowerShell ScriptCenter at:  http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

Page 1 of 1 (5 items)