VM Resources allocation over time

[mfiedler]

I've taken on trying to graph out capacity planning over time for our clusters.

Allocation only for now, utilization maybe later. Attempting to trend how we allocate vms (spikes).

Elements I care about are:

Physical: Amount of CPUs in cluster, amount of physical RAM in cluster - Total (at a given time? don't think that's possible, but it'd be nice)

This part is less problematic, as I have something that works for this.

Virtual: Amount of Virtual CPUs, RAM allocated - Total for each month

Some code was modified off this topic (thanks, LucD!), and more from various other segemnts.

This gets me a nice referential listing of created times to work with:

$SQLSERVER="MYSQLSERVER"
$Database="VCDB"
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 
$SqlCmd.CommandText = "select distinct lower(vm_name) AS vm_name, max(create_time) as create_time " +
					"from VPX_EVENT " +
					"where	(event_type like '%.VmCreatedEvent' or " +
					"		event_type like '%.VMClonedEvent' or " +
					"		event_type like '%.VMRegisteredEvent') " +
					"group by vm_name"
$SqlCmd.Connection = $SqlConnection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Export-Csv c:\vm_create-list.csv -NoTypeInformation

Now that there's a file with dates, I then use that to join the date aspect to a given resource allocation:

$creationtimes = Import-Csv 'c:\vm_create-list.csv'

$realvms = Get-View -ViewType "VirtualMachine"

$myCol = @()

foreach ($item in $realvms) {
	
	$Row = "" | Select-Object CreationTime, NumCPU, MemoryMB
	
	$Row.NumCpu = $item.Config.Hardware.NumCPU
	$Row.MemoryMB = $item.Config.Hardware.MemoryMB
	
	$createtime = $creationtimes | ? {$_.vm_name -match $item.Name} | Select-Object create_time
	if ( $createtime.create_time -eq $null ) {
		# for VMs that exists but db has pruned data, lump into single date
		$Row.CreationTime = [datetime]'12/01/2007 00:00:00 AM'
	} else {
		$Row.CreationTime = [datetime]$createtime.create_time
	}
	
	$myCol += $Row
	}

$myCol = $myCol | Sort CreationTime

This now leaves me with a nice collection array of dates, cpu counts and ram size.

I'm stuck trying to now sum this by calendar month - output something like:

month,numcpu,memorymb,numOfVMs
2009-10,2,8192,1
2009-11,3,12228,2
2009-12,1,4096,1

For the ultimate purpose of dumping into charts, and graph with ceilings determined in the first

I realize that there's probably a way to tie the SQL Query method in as well, and bundle this all as one script. No idea how to transform the DataSet returned to a usable Collection object.

Unless someone out there has a great way of charting VM Allocation growth over time?

[LucD]

If I understood your question correctly, you're stuck trying to summarise the gathered data per month.

You can do this with the Group-Object cmdlet.

Something like this (I assume your data is in the array $myCol

$mReport = @()
$groups = $myCol | Group-Object -Property {$_.Date.Month}
$groups | %{
	$row = "" | Select Month, TotalCpu, TotalMemMb, TotalVm
	$_.Group | %{
		$row.TotalCpu += $_.NumCpu
		$row.TotalMemMb += $_.MemoryMb
	}
	$row.Month = $_.Name
	$row.TotalVm = $_.Group.Count
	$mReport += $row
}
$mReport

[mfiedler]

This is definitely the right direction, thanks.

I see one problem from the get-go - this looks fine for a 12-month span of a calendar month, but how does it handle multiple years?

[RParker]

Well I do this in either Access or Excel, as they both have access to external data.

Excel is nice because it has built in functions, but its much slower...

[LucD · Accepted solution]

The Group-Object cmdlet can take more than 1 property for grouping

Something like this

$mReport = @()
$groups = $myCol | Group-Object -Property {$_.Date.Year, $_.Date.Month}
...

[mfiedler]

Perfect!

I had to modify the Group-Object statement to get the date from the correct property of the myCol item:

$groups = $myCol | Group-Object -Property {$_.CreationTime.Date.Year, $_.CreationTime.Date.Month}

But other than that, looks just fine. Thanks!

Now if I could only get rid of intermediate CSV files by taking the SQL query tableset results and making them an input for $creationtimes...

[LucD]

I was in fact wondering why you use the SQL Select in the first place.

Is it because of performance ?

You could get the same data with the Get-VIEvent cmdlet.

Have a look at some of my event-related blog entries.

[mfiedler]

Indeed I reviewed them, however in order to reach back two years, I'd have to load a significant amount of samples and search through them - something that managed to kill my vCenter process.

I don't know how many maxsamples I should use to ensure I get all the data.

[LucD]

If you use the SDK method you can define a window and scroll back- or forward over the events.

See Events – Part 4 : Who started that VM ? for a sample script that uses the eventcollector.

Notice that it reads the events 1000 at a time.

The start date can be defined in the $efilter object in the time.beginTime property.

In the switch statement you could handle the 3 events you're after.