Script to get VM list and count on datastore

[RajuVCP]

Hi All,

I have one liner script which gives me number of VMs on datastore, i was looking to get both number of VMs plus VM names also.

Get-Datastore | Select Name, @{N="NumVM";E={@($_ | Get-VM).Count}} | Sort Name | export-csv -path "C:\Users\userA\Desktop\Cluster_Host_Report\datastorevmcount.csv"

the above script gives only no. or count of VMs on datastore in VC, can we get which all VMs are there on datastore in VC.

Thanks a ton in advance.

[LucD]

How do you want the result ?

One line per VM in the CSV file, and repeat the count on each line ?

[RajuVCP]

one liner per VM will be better, so that i can sort it in csv or excel and get exact number of VMs and VM names.

[LucD]

Try something like this

foreach($ds in Get-Datastore){

    Get-VM -Datastore $ds |

    Select Name,

        @{N='Datastore';E={$ds.Name}},

        @{N='VM on datastore';E={$ds.ExtensionData.Vm.Count}}

}

[RajuVCP]

Thanks LucD,

I tried your command it was working Good. But i added below line to export it in excel or csv.. getting many errors.

.| export-csv -path "C:\Users\userA\Desktop\Cluster_Host_Report\datastorevmcountNew.csv

can you put the remaining command if i want to export it in excel or csv.

Thanks.

[LucD · Accepted solution]

The ForEach doesn't place anything on the pipeline, you can fix that with the call operator (&)

&{foreach($ds in Get-Datastore){

    Get-VM -Datastore $ds |

    Select Name,

        @{N='Datastore';E={$ds.Name}},

        @{N='VM on datastore';E={$ds.ExtensionData.Vm.Count}}

}} | Export-Csv report.csv -NoTypeInformation -UseCulture

[Shahnawaz26]

Dear Lucd, lovely script..but why am i getting the result twice for each VM.

[LucD]

That can have multiple reasons, but the most common one I see is that there are multiple connections open to vSphere servers (vCenter & ESXi).

This causes the same VM to be returned multiple times.

Check the content of $global:defaultviservers

[Shahnawaz26]

Dear LucD, You are such a Star :smileycool:

Finally, How can i get rid of the following in the output CSV file: 

#TYPE Selected.VMware.VimAutomation.ViCore.Impl.V1.VM.UniversalVirtualMachineImpl

[LucD]

Normally the NoTypeInformation switch on the Export-Csv cmdlet does that.

Did you use that switch?

[Shahnawaz26]

Works like a charm, Thanks a Ton !!!

[ronm1969]

Love the script, any way to make a count collection for datastores above 11 vm's? Im building a task to alert me when I have over 11 vms per datastore to reduce latencies on my storage.

Im using this method at the moment.

Get-Datastore | Select Name, @{N="NumVM";E={@($_ | Get-VM).Count}} | Sort Name | export-csv -path "C:\vmreports\datastorevmcount.csv"