Virtual Center Performance Reporting

While Virtual Center provides a great performance monitoring interface to gain some insight into VM resource consumption, I wanted a way to automatically get a run down of my VM's memory and CPU utilization e-mailed to me on a weekly basis. This is especially useful when setting resource pool reservations. If you would like to do the same for your VC database, you'll need to set up database mail on your SQL server and create a SQL Server Agent job to run each of the following scripts. Set up a schedule and you're on your way!

Script 1: Memory Utilization

EXEC msdb.dbo.sp_send_dbmail@recipients = 'yourname@yourdomain.com', @subject = 'Virtual Center Memory Utilization Report',@body_format = 'HTML',@body = 'Here are some memory utilization metrics for the last month.',@query = ' USE VirtualCenter select vms.NAME as ''VIRTUAL_MACHINE'' , (AVG(STAT_VALUE)/10000) as ''MEMORY_PERCENT''
into #STAT_REPORT_TEMPfrom dbo.VPXV_HIST_STAT_YEARLY
JOIN VPXV_VMS vmsON SUBSTRING([ENTITY] ,4 , 4) = vms.VMID
where stat_group = ''mem''and entity like ''vm%''and sample_time > (getdate() - 30)
group by vms.NAME
go
select VIRTUAL_MACHINE as ''Virtual Machine'' , MEMORY_PERCENT * vm.MEM_SIZE_MB as ''Memory Usage in MB''
from #STAT_REPORT_TEMP stat
JOIN VPXV_VMS vmsON VIRTUAL_MACHINE = vms.NAME
JOIN VPX_VM vmON vms.VMID = vm.ID
ORDER BY VIRTUAL_MACHINE
go
drop table #STAT_REPORT_TEMP',@attach_query_result_as_file = 1,@query_attachment_filename = 'Virtual Machine Memory Stats.xls'

Script 2: CPU Utilization

EXEC msdb.dbo.sp_send_dbmail@recipients = 'yourname@yourdomain.com', @subject = 'Virtual Center CPU Utilization Report',@body_format = 'HTML',@body = 'Here are some CPU utilization metrics for the last month.',@query = ' USE VirtualCenter select vms.NAME as ''VIRTUAL_MACHINE'' , (AVG(STAT_VALUE)) as ''CPU_USAGE''
from dbo.VPXV_HIST_STAT_YEARLY
JOIN VPXV_VMS vmsON SUBSTRING([ENTITY] ,4 , 4) = vms.VMID
where STAT_NAME = ''usagemhz''and entity like ''vm%''and sample_time > (getdate() - 30)
group by vms.NAMEorder by vms.NAME',@attach_query_result_as_file = 1,@query_attachment_filename = 'Virtual Machine CPU Stats.xls'

Each of these scripts takes an average for each metric over the last month but if you prefer, you could edit these to use a different time frame.