VI-DB ds-info.php v2.1

From vwiki
Revision as of 10:33, 19 December 2011 by Sstrutt (talk | contribs) (Initial creation)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search
<html xmlns='http://www.w3.org/TR/REC-html40'>

<!--Version 2.1 -->

<html>
<head>
<title>vi-db (ds)</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';
$self = $_SERVER['PHP_SELF'];

// 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>";
}

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 ";

if (array_key_exists('dsid', $_REQUEST)) {
	show_ds_detail($_REQUEST['dsid']);
} else {
	show_ds_summary();
}

print "<br><a href='index.php'>Search page</a> &nbsp <a href='status.php'>Status page</a><br>\n";
	
$time = round((microtime(true) - $time_start)*1000);
print "Page took $time msecs to execute on server";

// ------------------- End of HTML ---------------------------------
print "</body><html>";

// ================ FUNCTIONS ============================

function show_ds_summary () {
	global $debug, $self, $disk_pc_warn, $disk_pc_alert;

	print "<h1>Datastores</h1>\n";

	// Initial query
	$query = "SELECT vc.name AS vc, ds.name AS ds, dsid, size, used, san_model, lun FROM ds JOIN vc USING (vcid) WHERE exist=1 ORDER BY vc.name, ds.name;";

	if ($debug == 1) {print ($query);}
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0) {
		die ("No powered off machines found...!");
	}
	print "<table class='BasicTable' align='center'>\n<tr><th>vCenter<th>Name<th>LUN<th>SAN<br>Model<th width=300>Overview<br><small>Usage % | Total TB</small>\n";

	$dss = mysql_fetch_all($result, MYSQL_ASSOC);
	//Find largest datastore size to scale to
	$max_vmfs = 0;
	foreach ($dss as $vmfs) {
		$max_vmfs = max($max_vmfs, $vmfs['size']);
	}
	foreach ($dss as $vmfs) {
		print "<tr><td>".$vmfs['vc']."<td><a href='$self?dsid=".$vmfs['dsid']."'>".$vmfs['ds']."</a><td class='num'>".$vmfs['lun']."<td>".$vmfs['san_model'];
		print bar_graph_both ($vmfs['used'], $vmfs['size'], $max_vmfs, $disk_pc_warn, $disk_pc_alert);
	}
	print "</table>";
}


function show_ds_detail ($dsid) {
	global $debug, $disk_pc_warn, $disk_pc_alert;
	
	// Initial query
	$result = mysql_query("SELECT name, size, used, san_model, lun, block, canonical FROM ds WHERE dsid = $dsid;");
	if ($debug) {
		print $query;
	}
	if (mysql_num_rows($result) == 0) {
		die ("<br>Datastore not found....");
	}
	$ds = mysql_fetch_assoc($result);
	
	print "<h1>".$ds['name']."</h1>\n";
	
	// ----------------- Layout table (top) ----------------------------
	print "<table align='center' cellspacing=10><tr><td valign='top' colspan=2>\n";
	
	// ----------------- Summary data --------------------------------------
	print "<table class='BasicTable' align='center'>\n<tr><th>SAN:<td>". $ds['san_model']."\n";
	print "<tr><th>LUN ID:<td>".$ds['lun']."\n";
	print "<tr><th>Sizes:<td><b>".$ds['size']/1024 ." GB</b> in ". $ds['block']." MB blocks (".$ds['block']*256 ." GB max file size)\n";
	print "<tr><th>Usage:";
	bar_graph_both ($ds['used'], $ds['size'], $ds['size'], $disk_pc_warn, $disk_pc_alert, 1);
	print "<tr><th>Canonical:<td><span style='font-family:courier;'>".$ds['canonical']."</span></table>\n";
	
	// ----------------- Layout table (bottom left) ----------------------------
	print "<tr><td valign='top'>\n";
	
	// ----------------- ESX list --------------------------------------------
	print "<h2>ESXs</h2>\n";
	$query = "SELECT esx.name AS esx, vc.name AS vc, cluster.name AS cluster, mpath FROM ds JOIN esx2ds USING (dsid) JOIN esx USING (esxid) JOIN cluster USING (clid) JOIN vc ON esx.vcid=vc.vcid ";
	$query .= "WHERE dsid=$dsid AND esx.exist=1 ORDER BY cluster, esx;";
	if ($debug) {print ($query);}
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0) {
		print ("No connected ESX's found....");
	} else {
		print "<table class='BasicTable' align='center'>\n<tr><th>vCentre<th>Cluster<th>ESX<th>Multipathing\n";
		$odd_row = true;
		while ($esx = mysql_fetch_assoc($result)) {
			$odd_row = !$odd_row;
			if ($odd_row) {
				print "<tr class='odd'>";
			} else {
				print "<tr>";
			}
			print "<td>".$esx['vc']."<td>".$esx['cluster']."<td>".$esx['esx']."<td>".$esx['mpath']."\n";
		}
		print "</table>\n";
	}
	
	// ----------------- Layout table (bottom right) ----------------------------
	print "<td valign='top'>\n";
	
	// ----------------- VM list --------------------------------------------
	print "<h2>VMs</h2>\n";
	$query = "SELECT cluster.name AS cluster, vm.name AS vm, vm.is_on, COUNT(num) AS no, SUM(size) AS size, esx.name AS esx, mpath ";
	$query .= "FROM vmdk JOIN vm USING (vmid) JOIN cluster USING (clid) JOIN esx ON vm.esxid=esx.esxid JOIN esx2ds ON esx.esxid=esx2ds.esxid ";
	$query .= "WHERE vmdk.dsid=$dsid AND esx2ds.dsid=$dsid AND vm.exist=1 GROUP BY vmid ORDER BY cluster, vm;";
	if ($debug) {print ($query);}
	$result = mysql_query($query);
	if (mysql_num_rows($result) == 0) {
		print ("No VM's found on datastore....");
	} else {
		print "<table class='BasicTable' align='center'>\n<tr><th>Cluster<th>VM<th>Power<th>Disks<th>Size<br><small>GB</small><th>ESX<th>Multipathing\n";
		$odd_row = true;
		while ($vm = mysql_fetch_assoc($result)) {
			$odd_row = !$odd_row;
			if ($odd_row) {
				print "<tr class='odd'>";
			} else {
				print "<tr>";
			}
			print "<td>".$vm['cluster']."<td>".$vm['vm']."<td>";
			if ($vm['is_on']) {
				print "ON";
			} else {
				print "OFF";
			}
			print "<td class='num'>".$vm['no']."<td class='num'>".round($vm['size']/1024)."<td>".$vm['esx']."<td>".$vm['mpath']."\n";
		}
		print "</table>\n";
	}
	// ----------------- Layout table (end) ----------------------------
	print "</table>\n";
}

function search_char ($text) {
	
	return (str_replace(array("*", "?"), array("%", "_"), trim($text)));
}

function bar_graph_both ($data1, $data2, $max, $warn_threshold, $alert_threshold, $plain=0) {
	// data1 MUST BE LESS THAN data2 !!!
	if ($data2 == 0) {
		$data2 = 1;
	}
	if ($plain) {
		$bar_space_percent = 100;
	} else {
		$bar_space_percent = 95;
	}
	$data1_percent = round($data1/$max*$bar_space_percent);
	$data2_percent = round($data2/$max*$bar_space_percent);
	$usage_percent = round($data1/$data2*100);
	$sizeGB = round($data2/1024);
	
	print "<td class='bar' title='$usage_percent % used of $sizeGB GB'><div style='width: $data1_percent%' ";
	if ($data1/$data2*100 >= $alert_threshold) {
		print "class='bar_alert'>";
	} elseif ($data1/$data2*100 >= $warn_threshold) {
		print "class='bar_warn'>";
	} else {
		print "class='bar_good'>";
	}
	print $usage_percent." %</div>";
	print "<div style='width: ". ($data2_percent - $data1_percent) ."%'";
	print "class='bar_norm'> </div>";
	if (!$plain) {
		print round_half($sizeGB/1024)."\n";
	} else {
		print "\n";
	}
}


function mysql_fetch_all($result) {
   while($row=mysql_fetch_array($result, MYSQL_ASSOC)) {
       $return[] = $row;
   }
   return $return;
}

function round_half($num) {
	return round($num / 0.5) * 0.5;
}
	
?>