This post is a writeup of a project for a master’s class in Decision Support Systems at Murray State. This is my first dive into VMware PowerCLI aside from some one shots. All feedback is welcome.
Our Problems
Problem 1: Servers are not being virtualized due to a decentralized procurement process
A decentralized server procurement process presents many problems to an organization. There are many gains with standardizing OS/hardware platforms.
Problem 2: Servers are not being virtualized because knowledge is required to make “Virtualize/Don’t Virtualze” decision
The benefits of server virtualization are easy to explain and are a part of our culture. However, the organization has not adopted a “virtualize first” mentality. There is still a lack of stakeholder understanding with regards to virtualization.
Due to lack of knowledge, ROI is not maximized. This knowledge exists in two places – the virtual infrastructure itself and as tacit knowledge with the VMware administrator.
PROBLEM ANALYSIS
Problem 1: Servers are not being virtualized due to a decentralized procurement process
This problem is outside of the scope of the CIS645 class. We’re working on it.
Problem 2: Servers are not being virtualized because knowledge is required to make “Virtualize/Don’t Virtualze” decision
Problem 2 has two major parts.
CAPACITY – CAN OUR VIRTUAL INFRASTRUCTURE SUPPORT THIS APPLICATION?
This question has historically been answered heuristically with ball park figures. Manually gathering current storage and RAM capacity data too time consuming.
CANDIDACY – BASED ON SYSTEM REQUIREMENTS AND INDUSTRY KNOWLEDGE, IS VIRTUALIZATION SUITABLE FOR THIS APPLICATION?
This is the harder question. Typically you’ll hear consultants say “it depends”. Answering this question usually involves a phone call with the VMware administrator. The conversation is series of questions from the administrator to the stakeholder.
RECOMMENDATION
When the two questions have been answered, a recommendation of Virtualize/Don’t Virtualize is made. If a Virtualize decision is made, the VMware administrator must find the optimal storage unit to deploy to and coordinate the deployment with the stakeholder.
SOLUTION DESIGN
USER INTERFACE
The users of this system are already familiar with Excel and would prefer to utilize Excel’s familiarity and What-If scenario planning.
What if we added another 2TB of storage?
What if we upgraded our RAM?
What if we didn’t have to have the license dongle?
Excel quickly enables these questions to be answered. A normal ‘GUI’ application would take more time to develop and would not invite queries of an ad-hoc nature.
CAPACITY
Capacity data resides at several levels: the virtual machine itself, the host, and the data store. The data is put into Excel using VMware’s PowerCLI. PowerCLI is a Windows PowerShell snap-in that integrates with any VMware Virtual Infrastructure. Windows PowerShell also integrates nicely with Excel.
Here are the steps to capacity gathering with the VMware Expert System:
- Open the Excel Spreadsheet
- Clear previously gathered data
- Connect to a vCenter Server
- Gather datastore information
- Gather host information
- Gather virtual machine information
- Write values to ‘Capacity’ Worksheet
- Write values to ‘New Virtual Machine’ Worksheet
- Save Excel Spreadsheet
- Clean up and quit Excel
CANDIDACY
The user of the VMware Expert System will answer a series of questions to determine system candidacy. Through knowledge capture, the conversation with the VMware Administrator does not need to take place. The knowledge is generally accepted by a community of VMware experts.
RECOMMENDATION
After answering the capacity and candidacy questions, the user receives a final recommendation. The recommendation is only “Virtualize” if capacity is available and candidacy is met.
The interface also displays reasons why a machine is not suitable for virtualization to enable What-If analysis.
DECISION TREE
Modified from VI:OPS P2V Decision Tree
RUNNING THE VMWARE EXPERT SYSTEM
PREREQUISITES
- VMware PowerCLI 4.0 or better
STEPS TO RUN THE VMWARE EXPERT SYSTEM
- Download and extract vmware-expert-system.zip
- Rename launch.tab to launch.bat
- Edit launch.bat, line 2
- Substitute your path to updatespreadsheet.ps1 where you see “C:\users\%username%\Documents\cis645\Project\vmware_expert_system\updatespreadsheet.ps1″, make sure the path is in quotation marks
- Edit updatespreadsheet.ps1, line 11
- Substitute your path to vmware_expert_system.xlsm where you see “C:\users\%username%\Documents\cis645\Project\vmware_expert_system\vwmare_expert_system.xlsm”, make sure the path is in quotation marks
- Run click ‘launch.bat’
- A screen similar to this will appear:
- Launch the spreadsheet “vmware_expert_system.xlsm” and enable macros
- Enter system requirements
- Press “Send Work Order”
EXAMPLE SYSTEM: NEW WEB SERVER
- Enter the hostname: newwebserver
- The hostname must not be already existing and must be a valid hostname (“The Internet Engineering Task Force (IETF)”)
- Enter a functional contact: Andy Hill
- Enter a staff contact: Andy Hill
- Select an Operating System: Windows Server 2003
- Enter a storage requirement: 20 GB
- The minimum storage requirement must be >8 GB and less than the size of a maximum single disk
- Enter a RAM requirement: 1024 MB
- The minimum RAM requirement is 256MB and must be less than one host and still tolerant of a host failure
- Number of Processors: 1
- Must be numeric, greater than or equal to 1, less than or equal to 4
- Number of NICs: 1
- Must be numeric, greater than or equal to 1, less than or equal to 4
- Average CPU utilization: 5%
- Must be numeric, between 0 and 1, if 4 processors are used average utilization cannot exceed 50%
- Average RAM utilization: 256 MB
- Average NIC utilization: 1 MBps
- Maximum Disk IO: 10 MBps
- Answer TRUE/FALSE to the following hardware components
- Modems: FALSE
- Fax Cards: FALSE
- License Dongles: FALSE
- Security Dongles: FALSE
- Hardware Encryption: FALSE
- Answer TRUE/FALSE to Vendor Support: TRUE
- Recommendation: Virtualize!
ADD NEW SUPPORTED GUEST OS
VMware’s Guest OS Compatibility Guide (“VMware, Inc.”) is exhaustive and does not line up with Murray State University’s environment. The drop-down list is populated from a hidden worksheet within Excel. For our environment, we limited this drop down to Guests OSes which have regularly maintained templates.
To add, delete, or change an entry in the operating system list follow these steps:
- Toward the bottom of Excel, right click the current worksheet
- From the context menu, select “Unhide…”
- From the Unhide Window, Select ‘Supported Guest Operating Systems’ and press OK
- Navigate to the ‘Supported Guest Operating Systems’ Worksheet. Make changes Column A. Only changes in Column A will be reflected in the spreadsheet. Save your changes.
Future Considerations
Future versions of this project will include:
- Support for advanced disk layouts
- Get-Template feeding the ‘Supported Guest OS’ worksheet
- 1 click ‘deploy from template’
- Support for tiered storage
- Graphs of compute resources by host and virtual machine
# VMware Expert System Capacity Gathering
# v0.2
# by Andy Hill
# http://virtualandy.wordpress.com
# gathering data for VMware capacity
$viserver = Read-Host "Enter a vCenter server";
Write-Host "Gathering Excel data...1/8"
$excel = new-object -comobject Excel.Application
# Edit this value to the location of your vmware_expert_system.xlsm
$excelfile = $excel.workbooks.open("C:\Users\andy.hill\Documents\cis645\Project\vmware_expert_system\vmware_expert_system.xlsm")
$worksheet = $excelfile.worksheets.item(3) # Select Capacity Worksheet
Write-Host "Clearing existing capacity data...2/8"
# Clear existing data
$worksheet.Range("A5:N65000").Clear() | out-null
$worksheet.cells.item(1,2) = $viserver
Write-Host "Connecting to $viserver, this may take a moment...3/8"
connect-viserver $viserver -erroraction stop -WarningAction SilentlyContinue | out-null
# datastore information
Write-Host "Gathering disk information...4/8"
$i = 5
$disks = get-datastore
foreach($disk in $disks) {
$worksheet.cells.item($i, 1) = $disk.name;
$worksheet.cells.item($i, 2) = $disk.freespaceMB;
$worksheet.cells.item($i, 3) = $disk.capacityMB;
$i++;
}
$disk_count = $i;
$i = 5
Write-Host "Gathering host information...5/8"
# host information
Get-VMHost | %{Get-View $_.ID} | %{
$esx = "" | select Name, NumCpuPackages, NumCpuCores, Hz, Memory
$esx.NumCpuPackages = $_.Hardware.CpuInfo.NumCpuPackages
$esx.NumCpuCores = $_.Hardware.CpuInfo.NumCpuCores
$esx.Hz = $_.Hardware.CpuInfo.Hz
$esx.Memory = $_.Hardware.MemorySize
$esx.Name = $_.Name
$worksheet.cells.item($i, 6) = $esx.Name
$worksheet.cells.item($i, 7) = $esx.NumCpuPackages
$worksheet.cells.item($i, 8) = $esx.NumCpuCores
$worksheet.cells.item($i, 9) = $esx.hz / 1000 / 1000
$worksheet.cells.item($i, 10) = $esx.memory / 1024 / 1024;
$i++;
}
$host_count = $i;
# vm information
$i = 5
Write-Host "Gathering virtual machine information...6/8"
get-vm | % {
$vm = "" | select name, MemoryMB
$worksheet.cells.item($i, 13) = $_.Name
$worksheet.cells.item($i, 14) = $_.MemoryMB
$i++;
}
# Create the totals and amount utilized
$worksheet.cells.item(($i+1),13) = "Total"
$worksheet.cells.item(($i+1),14) = "=sum(N6:N" + $i + ")"
$vm_count = $i;
Write-Host "Writing values to Excel Spreadsheet...7/8"
#add some formatting
$worksheet.cells.item(($disk_count + 2), 1) = "Datastore with most free space";
$worksheet.cells.item(($disk_count + 3), 1) = "Memory (MB) Available";
$worksheet.cells.item(($disk_count + 4), 1) = "Memory Utilization %";
$worksheet.cells.item(($disk_count + 5), 1) = "Storage Available (GB)";
$worksheet.cells.item(($disk_count + 6), 1) = "Storage Utilization %";
$worksheet.cells.item(($disk_count + 7), 1) = "Most Storage Available on a datastore (GB)";
# add the formulas
$worksheet.cells.item(($disk_count + 2), 2) = "=INDEX((A5:A" + $disk_count + "),MATCH(MAX(B5:B" + $disk_count + "),B5:B" + $disk_count + ",0))";
$worksheet.cells.item(($disk_count + 3), 2) = "=SUM(J5:J" + $host_count + ") - N" + ($vm_count+1);
$worksheet.cells.item(($disk_count + 4), 2) = "=N" + ($vm_count+1) + "/SUM(J5:J" + ($host_count-1) + ")"; # n-1 hosts for HA failover
$worksheet.cells.item(($disk_count + 5), 2) = "=SUM(B5:B" + $disk_count + ")/1024";
$worksheet.cells.item(($disk_count + 6), 2) = "=1-SUM(B5:B" + $disk_count + ")/SUM(C5:C" + $disk_count + ")";
$worksheet.cells.item(($disk_count + 7), 2) = "=INDEX((B5:B" + $disk_count + "),MATCH(MAX(B5:B" + $disk_count + "),B5:B" + $disk_count + ",0))/1024";
Write-Host "Saving Excel Spreadsheet...8/8";
# Select main worksheet
$worksheet = $excelfile.worksheets.item(1);
# Update the 'new virtual machine' worksheet with capacity data
$worksheet.cells.item(8,4) = "=Capacity!B" + ($disk_count + 5) + "-'New Virtual Machine'!B8";
$worksheet.cells.item(8,7) = "=MAX(Capacity!B5:" + "B" + ($disk_count - 1) + ")/1024";
$worksheet.cells.item(9,4) = "=(Capacity!B" + ($disk_count +3) + ")/1024";
$worksheet.cells.item(29,2) = "=Capacity!B" + ($disk_count + 2);
$excel.activeworkbook.save();
$excel.quit();
