# Manipulating csv with storage counters

**URL:** <https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803>\
**Category:** PowerShell Help\
**Created:** [July 10, 2014, 12:07am UTC](https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803 "2014-07-10T00:07:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sassan-fanai](https://avatars.discourse-cdn.com/v4/letter/s/c5a1d2/32.png) [@sassan-fanai](https://forums.powershell.org/u/sassan-fanai)\
**Post date:** [July 10, 2014, 12:07am UTC](https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803/1 "2014-07-10T00:07:01Z")

</div>

Hi

I was hoping to play and learn with a task in powershell that at least sounded pretty easy in my head, I guess it is pretty easy stuff but I’m obviously doing something very wrong. After a couple of hours of trial and error I’m pretty sure I’m approaching this the wrong way.

I recently installed a tool to get some statistics/values from our storage system.  
The tool produces a bunch of csv files (attached one of them)

I started with just trying to play with the first four columns: Timestamp, MDisk, Read operations (IO/s),Write operations (IO/s)

I’m trying to calculate the average read and write IOPS per MDisk.  
Wit the code I got now, it actually outputs a view I was kind of happy with, at least for a start.  
Problem is that the final $object only has one (the last) mdisk and its values in it.

I kind of understand that I’m doing something stupid with the foreach statement that’s causing this, and I’ve tried a couple of things like initializing an empty array to be able to use +=$object.

My hope is to be able to use these csv files to parse the data in a nice way in the future, but as I got stuck right at the beginning of playing and started to get disgruntled I was hoping someone could guide me in the right direction.

The code:

```
$csv = Import-Csv .\Reports\StorageV7000.csv

foreach ($mdisk in $csv | Select-Object -ExpandProperty MDisk -Unique)
{

        $avgRead = $csv | Where-Object MDisk -eq $mdisk | Measure-Object -Property "Read operations (IO/s)" -Average -Maximum -Minimum
        $avgWrite = $csv | Where-Object MDisk -eq $mdisk | Measure-Object -Property "Write operations (IO/s)" -Average -Maximum -Minimum

       $object = [pscustomobject]@{
      

                'MDisk' = $mdisk
                'Average Read IOPS' = [System.Math]::Round($avgRead.Average,2)
                'Average Write IOPS' = [System.Math]::Round($avgWrite.Average,2)
                'Average Total IOPS' = [System.Math]::Round($avgRead.Average+$avgWrite.Average,2)
        }

        $object

}
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/1X/385e4f9fe133561ad30a814262fe0e0ae6bc3ef0.png) [@system](https://forums.powershell.org/u/system)\
**Post date:** [July 10, 2014, 12:20am UTC](https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803/2 "2014-07-10T00:20:08Z")

</div>

Looks like you just need to gather up all of your objects into a collection. Try this:

```
$csv = Import-Csv .\Reports\StorageV7000.csv

$report = foreach ($mdisk in $csv | Select-Object -ExpandProperty MDisk -Unique)
{
 
        $avgRead = $csv | Where-Object MDisk -eq $mdisk | Measure-Object -Property "Read operations (IO/s)" -Average -Maximum -Minimum
        $avgWrite = $csv | Where-Object MDisk -eq $mdisk | Measure-Object -Property "Write operations (IO/s)" -Average -Maximum -Minimum
 
       [pscustomobject]@{
                'MDisk' = $mdisk
                'Average Read IOPS' = [System.Math]::Round($avgRead.Average,2)
                'Average Write IOPS' = [System.Math]::Round($avgWrite.Average,2)
                'Average Total IOPS' = [System.Math]::Round($avgRead.Average+$avgWrite.Average,2)
        } 
}

$report
```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/1X/385e4f9fe133561ad30a814262fe0e0ae6bc3ef0.png) [@system](https://forums.powershell.org/u/system)\
**Post date:** [July 10, 2014, 12:23am UTC](https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803/3 "2014-07-10T00:23:55Z")

</div>

On a side note, you may find this version slightly clearer:

```
$csv = Import-Csv .\Reports\StorageV7000.csv

$report = $csv |
Group-Object -Property MDisk |
ForEach-Object {
    $diskGroup = $_
    $avgRead = $diskGroup.Group | Measure-Object -Property "Read operations (IO/s)" -Average -Maximum -Minimum
    $avgWrite = $diskGroup.Group | Measure-Object -Property "Write operations (IO/s)" -Average -Maximum -Minimum
    
    [pscustomobject]@{
        'MDisk' = $diskGroup.Name
        'Average Read IOPS' = [System.Math]::Round($avgRead.Average,2)
        'Average Write IOPS' = [System.Math]::Round($avgWrite.Average,2)
        'Average Total IOPS' = [System.Math]::Round($avgRead.Average+$avgWrite.Average,2)
    } 
}
```

With your calls to Select-Object -Property MDisk -Unique, and later Where-Object MDisk -eq $mdisk, you were basically recreating the functionality of Group-Object in a less efficient way.

---

<div class="post-metadata">

**Author:** ![sassan-fanai](https://avatars.discourse-cdn.com/v4/letter/s/c5a1d2/32.png) [@sassan-fanai](https://forums.powershell.org/u/sassan-fanai)\
**Post date:** [July 10, 2014, 3:25am UTC](https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803/4 "2014-07-10T03:25:56Z")

</div>

Thanks Dave, I’m gonna go with your last suggestion, haven’t used Group-Object alot.  
I know there a lot of ways of doing this stuff in PS, but would you (or anyone else) say this is decent approach or are there any other (better) ways?

This is what I got right now with your help:

```
$csv = Import-Csv .\Reports\StorageV7000.csv
 
$report = $csv |
Group-Object -Property MDisk |
ForEach-Object {
        $diskGroup = $_
        $avgReadIO = $diskGroup.Group | Measure-Object -Property "Read operations (IO/s)" -Average -Maximum -Minimum
        $avgWriteIO = $diskGroup.Group | Measure-Object -Property "Write operations (IO/s)" -Average -Maximum -Minimum
        $avgReadB = $diskGroup.Group | Measure-Object -Property "Read blocks (Byte/s)" -Average -Maximum -Minimum
        $avgWriteB = $diskGroup.Group | Measure-Object -Property "Write blocks (Byte/s)" -Average -Maximum -Minimum
        $avgReadUSEC = $diskGroup.Group | Measure-Object -Property "Read external response time (usec)" -Average -Maximum -Minimum
        $avgWriteUSEC = $diskGroup.Group | Measure-Object -Property "Write external response time (usec)" -Average -Maximum -Minimum

        [decimal]$avgReadMB = $avgReadB.Average /1MB
        [decimal]$avgWriteMB = $avgWriteB.Average /1MB
        [decimal]$avgReadMS = $avgReadUSEC.Average /1000
        [decimal]$avgWriteMS = $avgWriteUSEC.Average /1000
    
 
    [pscustomobject]@{
        'MDisk' = $diskGroup.Name
        'Read IOPS' = [System.Math]::Round($avgReadIO.Average,2)
        'Write IOPS' = [System.Math]::Round($avgWriteIO.Average,2)
        'Total IOPS' = [System.Math]::Round($avgReadIO.Average+$avgWriteIO.Average,2)
        'Read MB/s' = [System.Math]::Round($avgReadMB,2)
        'Write MB/s' = [System.Math]::Round($avgWriteMB,2)
        'Read MS' = [System.Math]::Round($avgReadMS,2)
        'Write MS' = [System.Math]::Round($avgWriteMS,2)

    } 
}
```

Thinking that next step somehow should be to include the Storage Pools.  
For example mdisk0-5 are in one Pool, so maybe create another object that just sums some of the values of those mdisks, that should be easy, let’s see how that goes 🙂  
I’ll keep this post updated if that’s okay.

Also, any feedback/suggestions on how to go forward with this, other angles or code cleanup are always appreciated.

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:47pm UTC](https://forums.powershell.org/t/manipulating-csv-with-storage-counters/2803/5 "2024-05-16T20:47:51Z")

</div>

Closed on May 16, 2024, 8:47pm UTC


