Help with VM report (Name, MemoryMB, NumCPU, Datastore, Total Disk Size)

[munizaba]

I currently have a script (listed below) to query VC for each VM and their allocated disks. The problem with the script currently is that when it encounters a VM with disks on multiple datastores only the first datastore is listed in the report. The total disk size allocated is correct however.

Any ideas to resolve this?

$datastoreExp = @{N="Datastore"; E={ ($_ | get-datastore | select-object -first 1).Name }}

$diskSizeExp = @{N="Total Disk"; E={ ($_ | get-harddisk | measure-object -property CapacityKB -sum).Sum }}

get-vm | select Name,MemoryMB,NUmCPU, $datastoreExp, $diskSizeExp | sort -property datastore,"Total Disk" | Export-Csv C:\Scripts\Reports\chargeback.csv

[LucD]

That's because you only ask for the first object (Select-Object -First 1) returned from the Get-Datastore cmdlet.

This will list all the datastores

$datastoreExp = @{N="Datastore"; E={ ($_ | get-datastore | where {$_.Type -eq "VMFS"} | select Name) }}
$diskSizeExp = @{N="Total Disk"; E={ ($_ | get-harddisk | measure-object -property CapacityKB -sum).Sum }}
get-vm | select Name,MemoryMB,NUmCPU, $datastoreExp, $diskSizeExp | sort -property datastore,"Total Disk" | ft

The problem is that you can't export an array to a column with Export-Csv.

To solve this I have placed all datastores in 1 string.

$datastoreExp = @{N="Datastore"; E={ [string]::join(" ",($_ | get-datastore | where {$_.Type -eq "VMFS"} | %{$_.Name})) }}
$diskSizeExp = @{N="Total Disk"; E={ ($_ | get-harddisk | measure-object -property CapacityKB -sum).Sum }}
get-vm | select Name,MemoryMB,NUmCPU, $datastoreExp, $diskSizeExp | `
   sort -property datastore,"Total Disk" |`
   Export-Csv C:\chargeback.csv

If you later need to read the CSV file and get at the individual datastore names you could use the Split method on the Datastore property.

[munizaba]

Hi LucD

We are using this report for cost tracking and chargeback; we have datastores in different tiers of storage.The name of datastore contain what Tiers is on (t1,t2 and t3)

• VM_T1

• VM_T2

• VM_T3

Some Virtual Machine will have vmdk files on different datastores and as per your reply now I can identify them and report looks like listed below:

Name	MemoryMB	NumCpu	Datastore	Total Disk
VM01	4096	1	VM_T1 VM_T2	1094713344
VM02	1024	1	VM_T1	20971520
VM03	2048	1	VM_T3	20971520
VM04	2048	1	VM_T2 VM_T3	20971520

What I need is report ,that I can identify how much each VM is using from each tiers (datastore). something as listed below:

Name			Datastore	Total Disk
VM01			VM_T1	547356672
VM01			VM_T2	547356672
VM02			VM_T1	20971520
VM03			VM_T3	20971520
VM04			VM_T2	10485760
VM04			VM_T3	10485760

Any help would be highly appreciated

[LucD]

Sure, we just need to loop through the datastores and create each row at the inner-most loop.

$report = @()
get-vm | % {
	$vm = $_
	$disksize = ($_ | get-harddisk | measure-object -property CapacityKB -sum).Sum
	$_ | Get-Datastore | Where-Object {$_.Type -eq "VMFS"} | % {
		$row = "" | Select Name,MemoryMB,NUmCPU, Datastore, "Total Disk"
		$row.Name = $vm.Name
		$row.MemoryMb = $vm.MemoryMb
		$row.NumCpu = $vm.NumCpu
		$row.Datastore = $_.Name
		$row.{Total Disk} = $disksize
		$report += $row
	} 
}
$report | sort -property Name, datastore,"Total Disk" |Export-Csv "C:\chargeback.csv" -noTypeInformation

[munizaba]

Hi LucD,

Thank you for your help.

Report is not calculating total disk size per datastore correctly (is showing same size). Do you know if there is the way to do calculation so if VM1 have 20 GB in datastorT1 and 40 GB in datastoreT2 report will produce something like below:

Name	Datastore	Disk size
VM1	datastoreT1	20GB
VM1	datastoreT2	40GB

[LucD · Accepted solution]

I thought that was what you wanted judging from your examples.

In any case, no problem, the following should do the trick.

$dsRegEx = [regex]"\[(\w+)\]"
$report = @()
get-vm | % {
	$vm = $_
    $vm.HardDisks | %{
		$row = "" | Select Name,MemoryMB,NUmCPU, Datastore, "Total Disk (Gb)"
		$row.Name = $vm.Name
		$row.MemoryMb = $vm.MemoryMb
		$row.NumCpu = $vm.NumCpu
		$row.Datastore = $dsRegEx.Match($_.Filename).Groups[1].Value
		$row.{Total Disk (Gb)} = "{0:N}" -f ($_.CapacityKB / 1Mb)
		$report += $row
	} 
}
$report | sort -property Name, datastore,"Total Disk" |Export-Csv "C:\chargeback.csv" -noTypeInformation

Since there are sometimes problems with displaying square brackets I have attached the script.

[munizaba]

LucD is legend

[munizaba]

Hi Lucd,

I have modified your script to list different Tiers of storage and report total size for each tear.

Script is working fine. Could you please let me know if there is better way of producing this report?

$report = @()

get-vm | % {

$vm = $_

$T2 = ($_ | get-harddisk | Where-Object {$_.Filename -match "T2"} | measure-object -property CapacityKB -sum).Sum

$T3 = ($_ | get-harddisk | Where-Object {$_.Filename -match "T3"} | measure-object -property CapacityKB -sum).Sum

$T4 = ($_ | get-harddisk | Where-Object {$_.Filename -match "T4"} | measure-object -property CapacityKB -sum).Sum

$_ | Get-Datastore | Where-Object {$_.Type -eq "VMFS"} | % {

$row = "" | Select Name,MemoryMb,NumCPU,"T2 (GB)","T3 (GB)","T4 (GB)"

$row.Name = $vm.Name

$row.MemoryMb = $vm.MemoryMb

$row.NumCpu = $vm.NumCpu

$row.{T2 (GB)} = ($T2 / 1Mb)

$row.{T3 (GB)} = ($T3 / 1Mb)

$row.{T4 (GB)} = ($T4 / 1Mb)

$report += $row

}

}

$report | sort -property Name |Export-Csv "C:\chargeback.csv" -noTypeInformation

[LucD]

No, the way you did it is how I would do it as well.

One suggestion, you could format the numbers to, let's say, only show 2 decimal places.

Something like this

...
		$row.{T2 (GB)} = "{0:f2}" -f ($T2 / 1Mb)
		$row.{T3 (GB)} = "{0:f2}" -f ($T3 / 1Mb)
		$row.{T4 (GB)} = "{0:f2}" -f ($T4 / 1Mb)
...

[Gfederizzi]

Hi,

I'm trying run this script and occurs the error message bellow:

Property '$host.SetShouldExit(392);Total Disk (Gb)' cannot be found on this object; make sure it exists and is settable.

At line 7, position 67

$vm.HardDisks | %{

Waiting for help,

Gabriel

[LucD]

How did you copy this script ?

The line "Property '$host.SetShouldExit(392);Total Disk (Gb)' cannot be found on this object; make sure it exists and is settable." seems to indicate something went wrong during the copy.

From where are you running this script ? From the PowerCLI prompt ?

[Gfederizzi]

Hi,

I'm trying run from PowerGUI Script Editor.

This is the complete script:

-

---

Connect-VIServer -Server VCServer1 -User $args[0] -Password $args[1]

$dsRegEx = "\[(\w+)\]"

$report = @()

get-vm | % {

$vm = $_

$vm.HardDisks | %{

$row = "" | Select Name,MemoryMB,NUmCPU, Datastore, "Total Disk (Gb)"

$row.Name = $vm.Name

$row.MemoryMb = $vm.MemoryMb

$row.NumCpu = $vm.NumCpu

$row.Datastore = $dsRegEx.Match($_.Filename).Groups[1].Value

$row.{Total Disk (Gb)} = "" -f ($_.CapacityKB / 1Mb)

$report += $row

}

}

$report | sort -property Name, datastore,"Total Disk" |Export-Csv "C:\chargeback.csv" -noTypeInformation

-

---

[LucD]

Did you copy the script from the browser ? Or did you use the attached script ?

With some browser there are copy problems when there are square brackets in the script.

[Gfederizzi]

I downloaded your script through WEB (VM-chargeback.PS1), open the PowerGUI Script Editor and add the commands to connect in Virtual Center

My PowerGUI version is 1.5.1.512

I have a lot of virtual machines (+900) but i'm beginner in PowerShell !

Tanks for your help,

Gabriel

[LucD]

I ran the script in PowerGui v1.8.0.815 without a problem.

Perhaps you should first upgrade to the latest PowerGui release.

[Gfederizzi]

Thanks, now works fine.

I have another question: How I include the Virtual Center FolderName information per VM? I use FolderName like my "Service Name".

--Gabriel

[LucD]

It's quit easy to get to the blue folder that contains the guest.

Only small problem, when a guest is located directly under the datacenter, the name shown is that of a hidden folder called "vm".

This is the script

$dsRegEx = [regex]"\[(\w+)\]"
$report = @()
get-vm | % {
	$vm = $_
    $vm.HardDisks | %{
		$row = "" | Select Name,Folder, MemoryMB,NUmCPU, Datastore, "Total Disk (Gb)"
		$row.Name = $vm.Name
		$row.Folder = (Get-Folder -Id $vm.FolderId).Name
		$row.MemoryMb = $vm.MemoryMb
		$row.NumCpu = $vm.NumCpu
		$row.Datastore = $dsRegEx.Match($_.Filename).Groups[1].Value
		$row.{Total Disk (Gb)} = "{0:N}" -f ($_.CapacityKB / 1Mb)
		$report += $row
	} 
}
$report | sort -property Name, datastore,"Total Disk" |Export-Csv "C:\chargeback.csv" -noTypeInformation