VI-DB esxs.php v2

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 1.0 -->

<html>
<head>
<title>vi-db (esx-hware)</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 ";

print "<h1>ESX Hardware</h1>\n";

if (array_key_exists('clid', $_REQUEST)) {
	$cl_link = "&clid=".$_REQUEST["clid"];
	$clid=$_REQUEST["clid"];
} else {
	$cl_link = "";
	$clid=0;
}

if (array_key_exists('hw', $_REQUEST)) {
	$hw_link = "&hw=".$_REQUEST["hw"];
	$hw=$_REQUEST["hw"];
} else {
	$hw_link = "";
	$hw=0;
}

if (!array_key_exists('view', $_REQUEST)) {
	$view = "mobo";
} else {
	if ($_REQUEST["view"] == "nic") {
		$view = "nic";
	} elseif ($_REQUEST["view"] == "hba") {
		$view = "hba";
	} elseif ($_REQUEST["view"] == "mobo") {
		$view = "mobo";
	} else {
		die ("Error - view parameter error!");
	}
}

//Initial summary query
$query = "SELECT vc.name AS vc, cluster.clid, cluster.name AS cluster, COUNT(esxid) AS esxs, SUM(cpu_sock) AS cpu_sock, SUM(cpu_core) AS cpu_core, SUM(mem) AS mem FROM esx ";
$query .= "JOIN vc USING (vcid) JOIN cluster USING (clid) WHERE esx.exist=1 GROUP BY clid ORDER BY vc, cluster.name;";

$result = mysql_query($query);
//print $query;
if (mysql_num_rows($result) == 0) {
	die ("No vCentre's found...!");
}

// ----------------- Layout table (left) ----------------------------
print "<table align='center' cellspacing=10><tr><td valign='top'>\n";


print "<table class='BasicTable' align='center'>\n<tr><th>vCentre<th width=200>Cluster<th>ESXs<th>CPU<br>Socks<th>CPU<br>Cores<th>Memory<br><small>GB</small>\n";
$esxs = 0;
$cpu_sock_tot = 0;
$cpu_core_tot = 0;
$mem_tot = 0;
while ($row = mysql_fetch_assoc($result)) {
	print "<tr><td>".$row['vc'];
	if ($clid == $row['clid']) {
		print "<td><b>".$row['cluster']."</b>";
	} else {
		print "<td><a href='$self?clid=".$row['clid']."&view=$view$hw_link'>".$row['cluster']."</a>";
	}
	print "<td class='num'>".$row['esxs']."<td class='num'>".$row['cpu_sock']."<td class='num'>".$row['cpu_core']."<td class='num'>".number_format(round($row['mem']/1024));
	$esxs += $row['esxs'];
	$cpu_sock_tot += $row['cpu_sock'];
	$cpu_core_tot += $row['cpu_core'];
	$mem_tot += $row['mem'];
}
print "<tr><td colspan=2><td class='num'>".$esxs."<td class='num'>".$cpu_sock_tot."<td class='num'>".$cpu_core_tot."<td class='num'>".number_format(round($mem_tot/1024))."\n";
print "</table><br>\n";

// ------------------ Layout table cell (middle)--------------------------
print "\n<td valign='top'>\n";

// ----------------- View Selector -----------------------------------------
print "<table class='BasicTable' align='left' valign='middle'>\n<tr><th width=200 colspan=2>View Selector\n";
if ($clid) {
	print "<tr><td align='center' colspan=2><a href='$self?view=$view$hw_link'>For All Clusters</a>\n";
} else {
	print "<tr><td align='center' colspan=2><b>&lt &lt</b> Filter On Cluster <b>&lt &lt</b>\n";
}
if ($view == "mobo") {
	print "<tr><th rowspan=3 width=40>Show:<td align='center'><b>Motherboard</b>\n";
	print "<tr><td align='center'><a href='$self?$cl_link$hw_link&view=nic'>Network Cards</a>\n";
	print "<tr><td align='center'><a href='$self?$cl_link$hw_link&view=hba'>Host Bus Adapters</a>\n";
} elseif ($view == "nic") {
	print "<tr><th rowspan=3 width=40>Show:<td align='center'><a href='$self?$cl_link&view=mobo'>Motherboard\n";
	print "<tr><td align='center'><b>Network Cards</b>\n";
	print "<tr><td align='center'><a href='$self?$cl_link$hw_link&view=hba'>Host Bus Adapters</a>\n";
} elseif ($view == "hba") {
	print "<tr><th rowspan=3 width=40>Show:<td align='center'><a href=$self?$cl_link&view=mobo>Motherboard\n";
	print "<tr><td align='center'><a href='$self?$cl_link$hw_link&view=nic'>Network Cards</a>\n";
	print "<tr><td align='center'><b>Host Bus Adapters</b>\n";
} else {
	print "<tr>ERROR...!!!\n";
}
if ($hw) {
	print "<tr><td align='center' colspan=2><a href='$self?view=$view$cl_link'>For All Hardware</a>\n";
} else {
	print "<tr><td align='center' colspan=2><b>&gt &gt</b> Filter On Hardware <b>&gt &gt</b>\n";
}
print "</table>";

// ------------------ Layout table cell (right)--------------------------
print "\n<td valign='top'>\n";

//Server hardware summary query
$query = "SELECT COUNT(esxid) AS esxs, model FROM esx JOIN vc USING (vcid) WHERE esx.exist=1 GROUP BY model ORDER BY model;";

$result = mysql_query($query);
//print $query;
if (mysql_num_rows($result) == 0) {
	die ("No Servers found...!");
}

print "<table class='BasicTable' align='center'>\n<tr><th width=130>Server Model<th>ESXs\n";
$esxs = 0;
while ($row = mysql_fetch_assoc($result)) {
	print "<tr>";
	if ($hw === $row['model']) {
		print "<td><b>".$row['model']."</b>";
	} else {
		print "<td><a href='$self?clid=$clid&view=$view&hw=".$row['model']."'>".$row['model']."</a>";
	}
	print "<td class='num'>".$row['esxs'];
	$esxs += $row['esxs'];
}
print "<tr><td><td class='num'>".$esxs."\n";

print "</table><br>\n";

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

// Detail query

if ($view == "mobo") {

	$query = "SELECT vc.name AS vc, cluster.name AS cluster, esx.name AS esx, esx.ver, esx.build, model, cpu_sock, cpu_core, mem, bios_ver FROM esx ";
	$query .= "JOIN vc USING (vcid) JOIN esx_ext USING (esxid) JOIN cluster USING (clid) WHERE esx.exist=1 ";
	if ($clid > 0) {
		$query .= "AND cluster.clid=$clid ";
	}
	if ($hw) {
		$query .= "AND esx.model='$hw' ";
	}
	$query .= "ORDER BY vc.vcid, cluster.name, esx.name;";
	
	if ($debug == 1) {print ($query);}
	$result = mysql_query($query);

	if (mysql_num_rows($result) == 0) {
		die ("None...!");
	}
	print "<table class='BasicTable' align='center'>\n<tr><th>vCentre<th>Cluster<th>ESX<th>Ver<th>Build<th>Model<th>CPU<br>Socks<th>CPU<br>Cores<th>Memory<br><small>GB</small><th>BIOS\n";

	$esxs = mysql_fetch_all($result, MYSQL_ASSOC);
	$odd_row = false;
	foreach ($esxs as $esx) {
		$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['ver']."<td class='num'>".$esx['build']."<td class='num'>".$esx['model']."<td class='num'>".$esx['cpu_sock']."<td class='num'>".$esx['cpu_core']."<td class='num'>".round($esx['mem']/1024)."<td>".$esx['bios_ver']."\n";
	}
	print "</table><br>";
	
} elseif ($view == "hba") {
	
	$query = "SELECT vc.name AS vc, cluster.name AS cluster, esx.name AS esx, esx.ver, esx.build, esx.model AS svr_model, esx_hba.model AS hba_model, driver, drv_ver FROM esx_hba ";
	$query .= "LEFT JOIN esx USING (esxid) JOIN vc USING (vcid) JOIN esx_ext USING (esxid) JOIN cluster USING (clid) WHERE esx.exist=1 ";
	if ($clid > 0) {
		$query .= "AND cluster.clid=$clid ";
	}
	if ($hw) {
		$query .= "AND esx.model='$hw' ";
	}

	$query .= "GROUP BY esx.esxid, drv_ver ORDER BY vc.vcid, cluster.name, esx.name, esx_hba.model;";
	
	if ($debug == 1) {print ($query);}
	$result = mysql_query($query);

	if (mysql_num_rows($result) == 0) {
		die ("None...!");
	}
	print "<table class='BasicTable' align='center'>\n<tr><th>vCentre<th>Cluster<th>ESX<th>Ver<th>Build<th>Model<th>HBA Model<th>HBA Type<th>HBA Driver\n";

	$esxs = mysql_fetch_all($result, MYSQL_ASSOC);
	$odd_row = false;
	$last_esx = false;
	foreach ($esxs as $esx) {
		if ($last_esx <> $esx['esx']) {
			$odd_row = !$odd_row;
			$last_esx = $esx['esx'];
		}
		if ($odd_row) {
			print "<tr class='odd'>";
		} else {
			print "<tr>";
		}
		print "<td>".$esx['vc']."<td>".$esx['cluster']."<td>".$esx['esx']."<td>".$esx['ver']."<td class='num'>".$esx['build']."<td>".$esx['svr_model']."<td>".$esx['hba_model']."<td>".$esx['driver']."<td>".$esx['drv_ver']."\n";
	}
	print "</table><br>";
	
} elseif ($view == "nic") {
	
	$query = "SELECT vc.name AS vc, cluster.name AS cluster, esx.name AS esx, esx.ver, esx.build, esx.model AS svr_model, esx_nic.model AS nic_model, driver, drv_ver, fware FROM esx_nic ";
	$query .= "LEFT JOIN esx USING (esxid) JOIN vc USING (vcid) JOIN esx_ext USING (esxid) JOIN cluster USING (clid) WHERE esx.exist=1 ";
	if ($clid > 0) {
		$query .= "AND cluster.clid=$clid ";
	}
	if ($hw) {
		$query .= "AND esx.model='$hw' ";
	}
	$query .= "GROUP BY esx.esxid, drv_ver ORDER BY vc.vcid, cluster.name, esx.name, esx_nic.model;";
	
	if ($debug == 1) {print ($query);}
	$result = mysql_query($query);

	if (mysql_num_rows($result) == 0) {
		die ("None...!");
	}
	print "<table class='BasicTable' align='center'>\n<tr><th>vCentre<th>Cluster<th>ESX<th>Ver<th>Build<th>Model<th>NIC Model<th>NIC Type<th>NIC Driver<th>NIC Firmware\n";

	$esxs = mysql_fetch_all($result, MYSQL_ASSOC);
	$odd_row = false;
	$last_esx = false;
	foreach ($esxs as $esx) {
		if ($last_esx <> $esx['esx']) {
			$odd_row = !$odd_row;
			$last_esx = $esx['esx'];
		}
		if ($odd_row) {
			print "<tr class='odd'>";
		} else {
			print "<tr>";
		}
		print "<td>".$esx['vc']."<td>".$esx['cluster']."<td>".$esx['esx']."<td>".$esx['ver']."<td class='num'>".$esx['build']."<td>".$esx['svr_model']."<td>".$esx['nic_model']."<td>".$esx['driver']."<td>".$esx['drv_ver']."<td>".$esx['fware']."\n";
	}
	print "</table><br>";

}

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

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

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

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

function bar_graph ($data_percent, $warn_threshold, $alert_threshold) {
	print "<td class='bar' title='$data_percent'><div style='width: $data_percent%' ";
	if ($data_percent >= $alert_threshold) {
		print "class='bar_alert'";
	} elseif ($data_percent >= $warn_threshold) {
		print "class='bar_warn'";
	} else {
		print "class='bar_good'";
	}
	print ">".$data_percent."</div>\n";
}


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