VI-DB capman.php v2.1

From vwiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
<html xmlns='http://www.w3.org/TR/REC-html40'>

<!--Version 2.1 -->

<html>
<head>
<title>vi-db (cap-man)</title>
<link rel='SHORTCUT ICON' href='favicon.ico'>
<link rel='stylesheet' type='text/css' href='alpha.css' />
<meta name='Author' content='Simon Strutt'>
<meta name='Description' content='VI Info'>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" >
<meta http-equiv="Content-Type" content="image/jpeg" >
<meta http-equiv='Pragma' content='no-cache'>
<meta http-equiv='Expires' content='900'>
</head>
<body>

<?php
$time_start = microtime(true);

//Local variables etc
require 'config.php';

// Thresholds (get from config.php where available)
$cpu_pc = $cpu_pc_alert;
$vcpu_pcpu = 4;				// To be derived from config.php
$mem_pc = $mem_pc_alert;
$clust_n = 1;				// To be derived from config.php
$disk_pc = $disk_pc_alert;
$disk_unit = 40;				// To be derived from config.php

// open connection and select database
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect to database!");
mysql_select_db($db) or die ("Unable to select database!");

//Set debug on if required
if (array_key_exists('debug', $_REQUEST) and ($_REQUEST["debug"] == 1)) {
	$debug = 1;
}

// show parameters and errors
if ($debug) {
	error_reporting(-1);
	print_r($_REQUEST);
	print "<br>";
}

// Update thresholds if defined...
if (array_key_exists('thres_vcpu', $_REQUEST)) {
	$vcpu_pcpu = $_REQUEST['thres_vcpu'];
}
if (array_key_exists('thres_mem', $_REQUEST)) {
	$mem_pc = $_REQUEST['thres_mem'];
}
if (array_key_exists('thres_n', $_REQUEST)) {
	$clust_n = $_REQUEST['thres_n'];
}	
if (array_key_exists('thres_disk', $_REQUEST)) {
	$disk_pc = $_REQUEST['thres_disk'];
}	
if (array_key_exists('thres_dunit', $_REQUEST)) {
	$disk_unit = $_REQUEST['thres_dunit'];
}	

//print "<div title='body'><div title='disclaimer'>";
print "DISCLAIMER: Please use all information gained from here with caution - I haven't performed exhaustive validation and testing of any of the information that may be presented. <br>\n";
print "If you're making changes or decisions, please double check with the reality of what is displayed by the vCentre servers.  You retain full responsibility for your actions...!\n ";
print "<!--[if IE]>\n";
print "<span style='color:red;'><b>Internet Explorer is not supported - the HTML form may not work properly due to a long standing IE bug, use any other browser</b></span>\n";
print "<![endif]-->\n";
//print "</div>\n";


// ------------------- Layout table cell ----------------------
print "<table align='center' cellspacing=10><tr><td valign='top' colspan=2>\n";
//print "<div title='cluster overview'>\n";

print "<h1>Cluster Capacity</h1>\n";

$query = "SELECT vc.vcid, vc.name AS vc, cluster.clid, cluster.name AS cluster, COUNT(esxid) AS esxs, SUM(cpu_core) AS cpu_core, SUM(cpu_pc) AS cpu_pc_sum, SUM(mem)/1024 AS mem, ";
$query .= "SUM(mem_pc) AS mem_pc_sum FROM esx JOIN esx_state USING (esxid) JOIN vc USING (vcid) JOIN cluster USING (clid) WHERE esx.exist=1 GROUP BY clid ORDER BY vc, cluster.name;";

$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
	die ("No clusters found in db!!! Unexpected error!");
}

print "<table class='BasicTable' align='center' valign='top'>";
print "<tr><th rowspan=2>vCentre<th rowspan=2>Cluster<th rowspan=2>ESXs<th colspan=3 class='section'>CPU<th colspan=2>Memory<th colspan=3>Disk<th colspan=4>Free Capacity<br><small>(using redundancy)</small><th colspan=4 class='black_text'>Spare Capacity<br><small>(retaining failover capacity)</small>\n";
// CPU
print "<tr><th>Total<br>Cores<th><small>vCPU/<br>pCPU</small><th>%<br><small>load</small>";
// Memory
print "<th>Total<br><small>GB</small><th>%<br><small>load</small>";
// Disk
print "<th><small>No of<small><br>LUNs<th>Total<br><small>TB</small><th>%<br><small>used</small>";
// Free Capacity
print "<th>CPU<br><small>cores</small><th>Mem<br><small>GB</small><th>Disk<br><small>GB</small><th>Disk<br><small>+VM off</small>";
// Spare Capacity
print "<th class='black_text'>CPU<br><small>cores</small><th class='black_text'>Mem<br><small>GB</small><th class='black_text'>Disk<br><small>GB</small><th class='black_text'>Disk<br><small>+VM off</small>\n";

$odd_row = true;
$cl_list = array();
while ($cluster = mysql_fetch_assoc($result)) {
	$cl_list[$cluster['clid']]=$cluster['cluster'];
	$odd_row = !$odd_row;
	if ($odd_row) {
		print "<tr class='odd'>";
	} else {
		print "<tr>";
	}
	$vm = mysql_fetch_assoc(mysql_query("SELECT COUNT(vmid) AS vms, SUM(cpu) AS cpus, SUM(mem)/1024 AS mem FROM vm JOIN vm_ext USING (vmid) WHERE clid=".$cluster['clid']." AND is_on=1 AND exist=1;"));
	$esx_core = mysql_result(mysql_query("SELECT MAX(cpu_core) FROM esx WHERE clid=". $cluster['clid'] .";"), 0);
	$esx_mem = mysql_result(mysql_query("SELECT MAX(mem)/1024 FROM esx WHERE clid=". $cluster['clid'] .";"), 0);
	$ds_space_vm_off = mysql_result(mysql_query("SELECT SUM(vmdk.size)/1024 AS size FROM vm JOIN vc USING (vcid) JOIN cluster USING (clid) JOIN vmdk USING (vmid) WHERE vm.exist=1 AND is_on=0 AND clid=". $cluster['clid'] .";"), 0);
	$ds = mysql_fetch_assoc(mysql_query("SELECT COUNT(lun) AS luns, SUM(size)/1024 AS size, SUM(used)/1024 AS used FROM (SELECT DISTINCT lun AS lun, size AS size, used AS used FROM ds JOIN esx2ds USING (dsid) JOIN esx USING (esxid) WHERE ds.exist=1 AND esx.exist=1 AND clid=".$cluster['clid']." AND ds.name NOT LIKE('%Local%') AND ds.name NOT LIKE('%iSCSI%')) AS ds;"));
	print "<td><a href='vc-detail.php?vcid=".$cluster['vcid']."&clid=".$cluster['clid']."'>".$cluster['vc']."</a><td><a href='cluster-detail.php?clid=".$cluster['clid']."'><b>".$cluster['cluster']."</b></a><td class='num'><a href='esxs.php?clid=".$cluster['clid']."'>".$cluster['esxs']."</a>";
	// CPU
	print "<td class='num'>".$cluster['cpu_core']."<td class='num'>".round(($vm['cpus']/$cluster['cpu_core']),1)."<td class='num'>".round($cluster['cpu_pc_sum']/$cluster['esxs']);
	// Memory
	print "<td class='num'>".number_format(round($cluster['mem']))."<td class='num'>".round($cluster['mem_pc_sum']/$cluster['esxs']);
	// Disk
	print "<td class='num'>".$ds['luns']."<td class='num'>".number_format(round($ds['size']/1024),1)."<td class='num'>".round($ds['used']/$ds['size']*100);
	// Free Capacity (CPU + Mem)
	print "<td class='num'>".($cluster['cpu_core']*$vcpu_pcpu-$vm['cpus'])."<td class='num'>".number_format(round(($cluster['mem']-$vm['mem'])));
	// Free Capacity (Disk)
	print "<td class='num'>".number_format((round($ds['size']-$ds['used'])))."<td class='num'>".number_format((round($ds['size']-$ds['used']+$ds_space_vm_off)));
	// Spare Capacity (CPU + Mem)
	$cpu_spare = ($cluster['cpu_core']-$esx_core*$clust_n)*$vcpu_pcpu-$vm['cpus'];
	if ($cpu_spare < 1) {
		print "<td class='num'><b><span class='warn'>$cpu_spare</span></b>";
	} else {
		print "<td class='num'><b>$cpu_spare</b>";
	}
	$mem_spare = ($cluster['mem']-$esx_mem*$clust_n)-$vm['mem'];
	if ($mem_spare < 1) {
		print "<td class='num'><b><span class='warn'>".number_format(round($mem_spare))."</span></b>";
	} else {
		print "<td class='num'><b>".number_format(round($mem_spare))."</b>";
	}
	//Calculate useable storage space free
	$luns_result = mysql_query("SELECT lun, size/1024 AS size, used/1024 AS used FROM ds JOIN esx2ds USING (dsid) JOIN esx USING (esxid) WHERE ds.exist=1 AND esx.exist=1 AND clid=".$cluster['clid']." AND ds.name NOT LIKE('%Local%') AND ds.name NOT LIKE('%iSCSI%') GROUP BY lun;");
	$ds_space = 0;
	$title = "";
	while ($ds = mysql_fetch_assoc($luns_result)) {
		$space = $ds['size']*($disk_pc/100)-$ds['used'];
		$ds_space += floor($space/$disk_unit)*$disk_unit;
		$title .= "LUN ".$ds['lun'].": $space ($ds_space) | ";
	}
	if ($ds_space < 1) {
		print "<td class='num' title='$title'><b><span class='warn'>".number_format($ds_space)."</span></b>";
	} else {
		print "<td class='num' title='$title'><b>".number_format($ds_space)."</b>";
	}
	if (($ds_space+$ds_space_vm_off) < 1) {
		print "<td class='num'><b><span class='warn'>".number_format(round($ds_space+$ds_space_vm_off,-1))."</span></b>\n";
	} else {
		print "<td class='num'><b>".number_format(round($ds_space+$ds_space_vm_off,-1))."</b>\n";
	}
	
	if (array_key_exists('req', $_REQUEST) and $_REQUEST["req"]=="do" and $_REQUEST["req_clust"]==$cluster['clid']) {
		$cl_test = array();
		$cl_test['vcpu'] = $cpu_spare;
		$cl_test['mem'] = $mem_spare;
		$cl_test['disk'] = $ds_space;
		$cl_test['disk_off'] = $ds_space+$ds_space_vm_off;
	}
}
print "</table>\n";

//print "</div>\n";
// ------------------- Layout table cell ----------------------
print "\n<tr><td valign='top'>\n";
//print "<div title='thresh' style='float:left;'>\n";

print "<h2>Thresholds</h2>\n";

print "<table class='BasicTable' align='left' valign='top'>\n";
print "<tr><th>Mertic<th>Value<th>Description\n";
//print "<tr><th>CPU %<td>".$cpu_pc."<td>Maximum CPU usage for an ESX in a cluster (suggested - $cpu_pc_alert %)\n";
print "<tr><form><th>vCPU/pCPU<td><input type='text' name='thres_vcpu' size=2 maxlength=2 style='text-align: right' value=$vcpu_pcpu><td>Maximum no of VM CPUs per ESX CPU core (suggested - 4)\n";
print "<tr><th>Mem %<td><input type='text' name='thres_mem' size=2 maxlength=3 style='text-align: right' value=$mem_pc><td>Maximum memory usage for an ESX in a cluster (suggested - $mem_pc_alert %)\n";
print "<tr><th>N +<td><input type='text' name='thres_n' size=2 maxlength=1 style='text-align: right' value=$clust_n><td>Minimum ESX redundancy for a cluster (suggested - 1)\n";
print "<tr><th>Disk %<td><input type='text' name='thres_disk' size=2 maxlength=3 style='text-align: right' value=$disk_pc><td>Maximum space usage for a datastore (suggested - $disk_pc_alert %)\n";
print "<tr><th>Disk Unit<td><input type='text' name='thres_dunit' size=2 maxlength=3 style='text-align: right' value=$disk_unit><td>Unit size for a VM's disk free space calculation (suggested - 40)\n";
print "<tr><td colspan=3 align='center'><button type='submit' name='thres' value='update'>Update</button>\n";
print "</table>\n";
//print "</div>";


// ------------------- Layout table cell ----------------------
print "\n<td align='left' valign='top'>\n";
//print "<div style='width:1200px;'>\n";
//print "<div title='reqs' style='float:right; width:600px'>\n";

print "<h2>Requirements</h2>\n";
print "<table align='left' valign='top'><tr><td>\n";
print "<table class='ReqTable' align='left' valign='top'>\n";
print "<tr><td>vCPUs<td>Mem (GB)<td>Disk (GB)\n";
print "<tr><td><input type='text' name='req_vcpu' size=3 maxlength=3 style='text-align: right'";
if (array_key_exists('req_vcpu', $_REQUEST)) {
	print " value='".$_REQUEST["req_vcpu"]."'";
}
print ">\n<td><input type='text' name='req_mem' size=3 maxlength=4 style='text-align: right'";
if (array_key_exists('req_mem', $_REQUEST)) {
	print " value='".$_REQUEST["req_mem"]."'";
}
print ">\n<td><input type='text' name='req_disk' size=3 maxlength=4 style='text-align: right'";
if (array_key_exists('req_disk', $_REQUEST)) {
	print " value='".$_REQUEST["req_disk"]."'";
}
print ">\n<tr><td colspan=3>Destination Cluster<tr><td colspan=3><select name='req_clust'>";
foreach ($cl_list as $clid => $cl_name) {
	print "<option value=$clid";
	if (array_key_exists('req_clust', $_REQUEST) and $_REQUEST["req_clust"]==$clid) {
		print " selected='selected'";
	}
	print ">$cl_name</option>\n";
}
print "</select>";

print "<tr><td colspan=3 align='center'><button type='submit' name='req' value='do'>Calculate</button></form></table>\n";

if (array_key_exists('req', $_REQUEST) and $_REQUEST["req"]=="do") {
	print "<tr><td>";
	// Sanity check params
	if (!is_numeric($_REQUEST["req_vcpu"])) {
		print "<p><span class='req_alert'>vCPUs requirement is not an integer?</p></table>\n";
	} elseif (!is_numeric($_REQUEST["req_mem"])) {
		print "<p><span class='req_alert'>Memory requirement is not an integer?</p></table>\n";
	} elseif (!is_numeric($_REQUEST["req_disk"])) {
		print "<<p><span class='req_alert'>Disk Space requirement is not an integer?</p></table>\n";
	} else {
	
		// Sanity check passed - do the biz...
		if ($_REQUEST["req_vcpu"] > $cl_test['vcpu'] or $_REQUEST["req_mem"] > $cl_test['mem']) {
			// Don't fit
			print "<p><span class='req_alert'>&nbsp; Insufficient CPU/Memory capacity !! &nbsp;</p>\n";
		} elseif ($_REQUEST["req_disk"] > $cl_test['disk']) {
			if ($_REQUEST["req_disk"] < $cl_test['disk_off']) {
				// Only fits if powered off VM;s deleted
				print "<p><span class='req_warn'>&nbsp; Disk capacity short - powered off VM's need to be deleted ! &nbsp;</p>\n";
			} else {
				// Just don't fit
				print "<p><span class='req_alert'>&nbsp; No disk capacity !! &nbsp;</p>\n";
			}
		} else {
			// Fits
			print "<p><span class='req_good'>&nbsp; Sufficient capacity! &nbsp;</span></p>\n";
		}
		
		print "<p>Residual capacity post-deployment...</p><ul>\n";
		print "<li>".($cl_test['vcpu']-$_REQUEST['req_vcpu'])." vCPUs</li>\n";
		print "<li>".(number_format(round($cl_test['mem']-$_REQUEST['req_mem'])))." GB Memory</li>\n";
		print "<li>".($cl_test['disk']-$_REQUEST['req_disk'])." GB Disk Space (".number_format(round(($cl_test['disk_off']-$_REQUEST['req_disk']),-1))." GB if <a href='ds-waste.php?clid=".$_REQUEST['req_clust']."'>powered off VM's</a> deleted)</li></ul></table>\n";
	}
} else {
	print "</table>";
}

//print "</div>\n";

// ------------------- Layout table cell ----------------------
print "\n<tr><td valign='top' colspan=2>\n";
//print "<div id='centered'>\n";
//print "<div title='notes'>\n";

print "<h2>Notes</h2>\n";
print "<p><ul><li>Where clusters have heterogeneous ESX's, the worst case is assumed for <i>N+</i> calculations.</li>";
print "<li>Negative values are shown where clusters do not have a spare ESX’s worth of capacity, or storage with less than $disk_pc% free space.</li>";
print "<li>Spare disk is calculated in $disk_unit GB blocks so that small, unusable bits of space don’t contribute to the end disk capacity value.</li>\n";
print "<li>iSCSI and local disk datastores are excluded.</li>\n";
print "<li>No account is made for datastores that are attached to multiple clusters - the capacity will appear in all clusters!</li>\n";
print "<li>For a list of powered off VM's that could be deleted see <a href='ds-waste.php'>here</a></li>\n";
print "</ul></p>";

//print "</div>\n</div>";
// ------------------- Layout table cell ----------------------
print "\n</table>\n";

// ------------------- Footer---------------------------------
//print "<div title='footer'>\n";
print "<br><table class='NavTable' align='center' valign='top'><th>Main Pages:<td><a href='index.php'>Search page</a><td><a href='status.php'>Status page</a></table><br>\n";
	
$time = round((microtime(true) - $time_start)*1000);
print "Page took $time msecs to execute on server\n";
//print "</div>\n</div>\n";

print "</body><html>";
?>