get-stat for monthly trending of datastores

[kyliejudd]

Hi all,

Long time lurker,

I haven't been using PS for long and mainly just spit out one liners for things like "whats connected to X" etc

I am trying to output a file that I can put into excel and create some graphs used vs provisioned is the idea.

I would like to get this down so I can manipulate it for other entities.

I can wrap this around a foreach loop which I can figure out usually.

With get-stat I notice that it gets one value then the other so the MetricID is changing, so I cant just select the property.

I have a script already where I can show the last value and then put them all on the same graph but It would be cool to have a graph per datastore to see the trends.

We have vcops but I find the graphing a slow process as we have dozens of datastores globally.

this is my simple script I need to then cut and paste the values into additional columns but who wants to do it manually :smileysilly:

I can do something like this

$todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0)

$Startmonth = -1

$metrics = "disk.capacity.latest", "disk.provisioned.latest", "disk.used.latest"

$datastore = "vm_production01"

$arrds = Get-datastore | where-object {$_.Name -like $datastore}

$a = Get-Stat -Entity $arrds -Stat $metrics[0] -Start $todayMidnight.AddMonths($Startmonth) -Finish $todayMidnight | where {$_.instance -eq ""} | sort timestamp | select timestamp, value

$b = Get-Stat -Entity $arrds -Stat $metrics[1] -Start $todayMidnight.AddMonths($Startmonth) -Finish $todayMidnight | where {$_.instance -eq ""} | sort timestamp | select value

$c =Get-Stat -Entity $arrds -Stat $metrics[2] -Start $todayMidnight.AddMonths($Startmonth) -Finish $todayMidnight | where {$_.instance -eq ""} | sort timestamp | select value

$fin =  $a + $b + $c

$fin =  $a + $b + $c

$fin | ConvertTo-Csv | Out-File C:\support\scripts\development\datastoreusage.csv

See the attached file for the end goal.

I'm not sure if I am needing to create a custom object to accomplish it, If someone could help me out it would be great.

and any helpful articles where I can learn how to do it, I'm almost finished powershell in a month of lunches but I dont think this is covered :smileygrin:

thanks all

[LucD · Accepted solution]

To run this for all your datastores and then create a worksheet per datastore, you can use the Export-Xlsx function.

The script would look something like this

$todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0)

$Startmonth = -1

$metrics = "disk.capacity.latest", "disk.provisioned.latest", "disk.used.latest"

$ds = Get-Datastore

$xlsxPath = 'C:\ds-report.xlsx'

Get-Stat -Entity $ds -Stat $metrics -Start $todayMidnight.AddMonths($Startmonth) -Finish $todayMidnight |

Group-Object -Property {$_.Entity.Name} | %{

    $dsStats = $_.Group | Group-Object -Property Timestamp | %{

        New-Object PSObject -Property @{

            Timestamp = $_.Name

            "Capacity (GB)" = [Math]::Round(($_.Group |

                where {$_.MetricId -eq "disk.capacity.latest" -and $_.Instance -eq ""}).Value/1MB,2)

            "Allocated (GB)" = [Math]::Round(($_.Group |

                where {$_.MetricId -eq "disk.provisioned.latest" -and $_.Instance -eq ""}).Value/1MB,2)

            "Used (GB)" = [Math]::Round(($_.Group |

                  where {$_.MetricId -eq "disk.used.latest" -and $_.Instance -eq ""}).Value/1MB,2)

        }

    }

    $dsStats | Sort-Object -Property Timestamp |

    Select Timestamp,"Capacity (GB)","Allocated (GB)","Used (GB)" |

    Export-Xlsx -Path $xlsxPath -AppendWorksheet -WorksheetName $_.Name -SheetPosition end

}

[kyliejudd]

Hi LucD,

Thanks for the speedy reply, I've tried that out and it works great!

It interesting to see how the new-object cmdlet works I will have to finished up this book and move onto the tool making.

Thanks again,

Have a good weekend

[dzak64]

Odd - when I run the script, the name of the datastore is not listed.

Is there a change needed in the section of the script listed below?

____________________________________________________________

Group-Object -Property {$_.Entity.Name} | %{

$dsStats = $_.Group | Group-Object -Property Timestamp | %{

New-Object PSObject -Property @{

Timestamp = $_.Name

____________________________________________________________

My output looks like this ...

Timestamp Capacity (GB) Allocated (GB) Used (GB)
--------- ------------- -------------- ---------
3/15/2021 8:00:00 PM 4095.75 2368.68 1635.99
3/16/2021 8:00:00 PM 4095.75 2368.59 1655.16
3/17/2021 8:00:00 PM 4095.75 2368.63 1651.54

[LucD]

The name of the Worksheet is the name of the Datastore.

[dzak64]

if I type $_.Name at the prompt, it returns no value. The other variables contain data.

[LucD]

You can't access the pipeline variable ($_) from the prompt.

[dzak64]

OK - thank you.