Hyper-v capacity report (Powershell). Part 1

I am going to write about capacity. Some days ago one of our managers asked me about the ISO 20.000 and the capacity plan. He was looking for a simple dashboard to display our hypervisors capacity every month. So, I thought that using Powershell and SSRS could be a good idea to display and access this information.
So in this post I am going write about the draft version of the system that will generate this capacity dashboard.



There are four main steps to generate this report:

First of all, it's necessary to set up a SQL server database. Let's start with the simplest one: two tables and a few basic fields.

In the second step, we need to extract the information from hypervisors. The easiest way to do it in a small environment is with scheduled task and Powershell. In Windows 2012 we have a lot of cmdlets to extract this info.

Next, we have to insert our data extracted from Powershell to SQL server too.

Finally, we have to create our report with VS and deploy it into the SSRS server.

Let's start with the first step, the SQL database tables:

 USE [VM-STATS]  
 GO  
 /****** Object: Table [dbo].[Recursos]  Script Date: 10/4/2014 10:59:30 AM ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 CREATE TABLE [dbo].[Recursos](  
      [id] [int] IDENTITY(1,1) NOT NULL,  
      [date] [date] NULL,  
      [nombre] [nvarchar](50) NULL,  
      [hypervisor] [nvarchar](50) NULL,  
      [procs] [int] NULL,  
      [memoria] [numeric](18, 0) NULL,  
      [VHDsize] [numeric](18, 0) NULL,  
      [VHDsizeReal] [numeric](18, 0) NULL  
 ) ON [PRIMARY]  
 GO  

This first table has some fields which store VM characteristics, but basically we are looking for the id, date, vm name, hypervisor name, processors number, memory, VHD set size and VHD size in disk.

 USE [VM-STATS]  
 GO  
 /****** Object: Table [dbo].[Virtualizadores]  Script Date: 10/4/2014 10:59:46 AM ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 CREATE TABLE [dbo].[Virtualizadores](  
      [Virtualizador] [nvarchar](50) NULL,  
      [VRAM] [numeric](18, 0) NULL,  
      [VProc] [int] NOT NULL  
 ) ON [PRIMARY]  
 GO  

The second table has hypervisor characteristics. "Virtualizador" is the name os hypervisor, "VRAM" is the maximum VRAM available in the hypervisor and "VPROC" are the maximum numbers.

In the second step we have to harvest the data from hypervisors and insert it into the database with  the following script:

 $hypervisor = gc env:computername  
 $totalprocs=0  
 $totalmemory=0  
 $VHDtotalfilesize=0  
 $date=Get-Date -format "dd-MM-yyyy"  
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection   
 $SqlConnection.ConnectionString = "Server = SERVER\INSTANCE; Database = VM-STATS; User Id=vm-reports-database-user;Password=PASWORD1234;"  
 get-vm | foreach-object {$procs=(Get-VMProcessor $_.name).count  
 $VHDsize=(get-VMHardDiskDrive $_.name | foreach-object {Get-VHD $_.path }).size  
 $VHDsizeReal=(get-VMHardDiskDrive $_.name | foreach-object {Get-VHD $_.path }).filesize  
 $nombre=($_.name)  
 $memoria=($_.MemoryAssigned)  
 $memoria=$memoria/1024/1024/1024  
 $totalprocs=$totalprocs+$procs   
 $totalmemory=$totalmemory+$_.MemoryAssigned  
 $VHDtotalfilesize=$VHDsizeReal+$VHDsizeReal  
 write-output "$date,$nombre,$hypervisor,$procs,$memoria,$VHDsize,$VHDsizeReal"  
 #SQL INSERT  
 $SqlQuery = "INSERT INTO dbo.Recursos (date,nombre,hypervisor,procs,memoria,VHDsize,VHDsizeReal) VALUES ('$date','$nombre','$hypervisor','$procs','$memoria','$VHDsize','$VHDsizeReal');"  
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
 $SqlCmd.CommandText = $SqlQuery  
 $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]  
 }  
 $totalmemory=$totalmemory/1024/1024/1024  
 $VHDtotalfilesize=$VHDtotalfilesize/1024/1024/1024  
 write-output "TOTAL VCPUS $totalprocs , VRAM $totalmemory, STORAGE $VHDtotalfilesize GB "  

This script is a quick draft with some variables in Spanish, keep in mind it's just an approximation to the final script to give you an idea of how to write your own custom script.

Ok, now we have harvested the data and inserted it into the database, it is time to create the report. For this we need access to SSRS instance and Visual Studio. If you are working with Visual Studio 2013 you need an extra piece of software called Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 .

We will explain this step in Part 2.