Grouping Data in Powershell

I needed to create a report based on data from two different databases and servers. My options were to create a SQL datawarehouse which drags in the data from the different databases – or utilize Powershell.

I decided to use Powershell, as I found it easier to estimate how long it would take me to produce the report. My data is in this format :

FullName MachineName IPAddress PackageName Cost State Requested Completed Installed
Fake Fred PCFGH78BH 10.0.0.1 Adobe Acrobat 90 Approved 15/04/2015 22:28 15/04/2015 22:28 15/04/2015 22:36

I wanted to create a table using Powershell grouping the costs per application with count. (As a summary) I thought there may be a builtin cmdlet to do it, but unfortunately i could not find one.

Google led me to this helpful Gist which i used as inspiration for the grouping. I understand that this may not be the most computationally efficient way of doing this, but my data will never be more than a few hundred rows.

This method involves creating a new object with the grouped data, and then adding 2 properties to that object, one for Cost and one for Count.

$dataGrouped = $data | Group-Object -Property PackageName

$test = @()
$test += foreach($item in $dataGrouped)
{
    $item.Group | select -Unique PackageName,
    @{Name='Count';Expression = {(($item.Group) | measure -Property PackageName).Count}}
    @{Name='Cost';Expression = {(($item.Group) | measure -Property Cost -sum).Sum}}
}

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s