Storage Report Help

[pamiller21]

Hello all,

I am fairly new to using PowerCLI and I am attempting to make a report that would show a datastore and all the VMs on it, the host they are on and the space used in GB and percent.  Maybe in the future include Snapshot size and percent.  This is what I have:

Get-VM | Select-Object @{N=”Datastore”;E={Get-Datastore -VM $_}},Name,VMHost,UsedSpaceGB | Export-CSV "\\DIR\test.csv" -NoTypeInformation

This outputs the data but all the datastores in just random order, then VMs, host and space.  I would like to have the datastores listed together if possible.  Overall any advice would be excellent!

[LucD]

If you are looking for the VMs on a specific datastore, you could do something like this.

Note the use of the Sort-Object cmdlet to get the VMs sorted by name.

$dsName = "MyDatastore"
Get-VM -Datastore $dsName | 
Select-Object @{N=”Datastore”;E={$dsName}},Name,VMHost,UsedSpaceGB | 
Sort-Object -Property Name |
Export-CSV "\\DIR\test.csv" -NoTypeInformation -UseCulture

[pamiller21]

I was hoping to not have to define the datastore if at all possible so that I do not need to update the script everytime we create a new one.  Any ideas if this is possible?

[LucD]

So you want the output from all datastores in the same CSV file ?

[pamiller21]

Either all in one or possibly separate CSVs.

[LucD · Accepted solution]

Try like this, it should produce a CSV file per datastore

foreach($ds in Get-Datastore){
    Get-VM -Datastore $ds |
    Select-Object @{N=”Datastore”;E={$ds.Name}},Name,VMHost,UsedSpaceGB |
    Sort-Object -Property Name |
    Export-CSV "\\DIR\$($ds.Name)-VM.csv" -NoTypeInformation -UseCulture
}

[pamiller21]

This worked excellent!!! Unfortunatly my manager now wants to make the csv one sheet and when I tried to re-work this it failed.  Any advice how to make it one sheet?

[LucD]

No problem, try something like this

Get-Datastore |
Get-VM |
Select-Object @{N="Datastore";E={$_.ExtensionData.Config.Files.VmPathName.Split(']')[0].TrimStart('[')}},Name,VMHost,UsedSpaceGB |
Sort-Object -Property Datastore,Name |
Export-CSV "C:\report.csv" -NoTypeInformation -UseCulture