A colleague of mine – Dusan from DMTECH has created two powershell scripts that allow charge-back accounting on an ESX farm that is used to host customer data:
Assuming a CSV file like this:
Name,Business Owner,Chargeable,Commence Date,Cost (Upfront),Cost (Yearly)
Dept1PWAP003,Bob Marley,Y,,”$2,722.41″,$815.00
Dept1PWAP004,Bob Marley,Y,,”$1,707.96″,$476.85
Dept1PWAP005,Bob Marley,Y,,”$2,722.41″,$815.00
Dept1PWAP006,Bob Marley,Y,,”$2,722.41″,$815.00
Dept1PWAP007,Bob Marley,Y,,”$2,722.41″,$815.00
Dept1PWAP008,Bob Marley,Y,,”$5,199.25″,”$1,310.37″
Dept1PWAP009,Bob Marley,Y,,”$2,722.41″,$815.00
Dept1TWAA010,Bob Marley,Y,,”$1,231.27″,$381.51
Dept1TWAA011,Bob Marley,Y,,”$1,231.27″,$381.51
Dept1TWAA012,Bob Marley,Y,,”$1,231.27″,$381.51
Dept1TWAA013,Bob Marley,Y,,”$2,245.72″,$719.66
Dept1TWAA014,Bob Marley,Y,,”$1,231.27″,$381.51
The following script will import the data above by matching VM names and attaching data to the annotations/custom attributes you’ve created (as per the headings in the CSV file – “Name”, “Business Owner”, “Chargeable”, “Commence Date”, “Cost (Upfront)”, “Cost (Yearly)”):
$fields = "Business Owner","Chargeable","Commence Date","Cost (Upfront)","Cost (Yearly)" (Upfront)"
$vms = Get-VM
foreach ($f in (import-csv C:\temp\CustumField.csv)) {
$vm = $vms | Where { $_.Name -eq $f.Name }
foreach ($cf in $fields) {
$vm | Set-CustomField -name $cf -value $f.$cf
}
}
Now, assuming you have tiered storage, you can charge customers for storage on the basis of Tier – for this your datastore names have to end in _T2/_T3/_T4 according to this script:
$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, MemoryGb,NumCPU,"T2 (GB)","T3 (GB)","T4 (GB)","Business Owner","Chargeable","Commence Date","Cost (Upfront)","Cost (Yearly)"
$row.Name = $vm.Name
$row.MemoryGB = "{0:f0}" -f ($vm.MemoryMb / 1Kb)
$row.NumCpu = $vm.NumCpu
$row.{T2 (GB)} = "{0:f0}" -f ($T2 / 1Mb)
$row.{T3 (GB)} = "{0:f0}" -f ($T3 / 1Mb)
$row.{T4 (GB)} = "{0:f0}" -f ($T4 / 1Mb)
$row.{Business Owner} = $vm.CustomFields.Values[2]
$row.Chargeable = $vm.CustomFields.Values[3]
$row.{Commence Date} = $vm.CustomFields.Values[4]
$row.{Cost (Upfront)} = $vm.CustomFields.Values[5]
$row.{Cost (Yearly)} = $vm.CustomFields.Values[6]
$report += $row
}
}
$report | select -unique Name, MemoryGb,NumCPU,"T2 (GB)","T3 (GB)","T4 (GB)","Business Owner","Chargeable","Commence
Date","Cost (Upfront)","Cost (Yearly)" | sort -property Name |Export-Csv "C:\chargeback.csv" -noTypeInformation
This produces the following output which specifies the total amount of storage per VM per tier:
| Name |
MemoryGb |
NumCPU |
T2 (GB) |
T3 (GB) |
T4 (GB) |
Business Owner |
Chargeable |
Commence Date |
Cost (Upfront) |
Cost (Yearly) |
| Dept1PWAP003 |
4 |
2 |
0 |
70 |
0 |
Bob Marley |
Y |
|
$2,722.41 |
$815.00 |
| Dept1PWAP004 |
4 |
1 |
0 |
0 |
70 |
Bob Marley |
Y |
|
$1,707.96 |
$476.85 |
| Dept1PWAP005 |
4 |
2 |
0 |
70 |
0 |
Bob Marley |
Y |
|
$2,722.41 |
$815.00 |
| Dept1PWAP006 |
4 |
2 |
0 |
70 |
0 |
Bob Marley |
Y |
|
$2,722.41 |
$815.00 |
| Dept1PWAP007 |
4 |
2 |
0 |
70 |
0 |
Bob Marley |
Y |
|
$2,722.41 |
$815.00 |
| Dept1PWAP008 |
4 |
2 |
0 |
320 |
0 |
Bob Marley |
Y |
|
$5,199.25 |
$1,310.37 |
| Dept1PWAP009 |
4 |
2 |
0 |
70 |
0 |
Bob Marley |
Y |
|
$2,722.41 |
$815.00 |
| Dept1TWAA010 |
4 |
1 |
0 |
0 |
70 |
Bob Marley |
Y |
|
$1,231.27 |
$381.51 |
| Dept1TWAA011 |
4 |
1 |
0 |
0 |
70 |
Bob Marley |
Y |
|
$1,231.27 |
$381.51 |
| Dept1TWAA012 |
4 |
1 |
0 |
0 |
70 |
Bob Marley |
Y |
|
$1,231.27 |
$381.51 |
| Dept1TWAA013 |
4 |
2 |
0 |
0 |
70 |
Bob Marley |
Y |
|
$2,245.72 |
$719.66 |
| Dept1TWAA014 |
4 |
1 |
0 |
0 |
70 |
Bob Marley |
Y |
|
$1,231.27 |
$381.51 |
Cheers,
Leo
