VI-DB index.php v1.1

From vwiki
Jump to navigation Jump to search 
<html xmlns='http://www.w3.org/TR/REC-html40'>

<!--Version 1.1 -->

<html>
<head>
<title>vi-db</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';

// 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 "<!--[if IE]>\n";
print "<span style='color:red;'><b>Internet Explorer is not supported - the search form will not work properly due to a long standing IE bug, use any other browser</b></span>\n";
print "<![endif]-->\n";


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

// ----------------- vCentre table -------------------------------
print "<h1>Virtual Infrastructure Summary</h1>\n";

$result = mysql_query("SELECT site, name, ver, build, last_pass, vcid FROM vc ORDER BY site, name;");
if (mysql_num_rows($result) == 0) {
	die ("No VC's found in db!!! Unexpected error!");
}
$tot_esx = 0;
$tot_vm= 0;
$tot_vm_on = 0;
print "<table class='BasicTable' align='center' valign='top'><tr><th>Site<th>vCentre<th>ESXs<th>VMs<br><small>(all)</small><th>VMs<br><small>(on)</small><th>Ver<th>Build<th>Last Check\n";
while ($row = mysql_fetch_assoc($result)) {
	$num_esx = mysql_result(mysql_query("SELECT COUNT(*) FROM esx WHERE vcid=". $row['vcid'] ." AND exist=1;"), 0);
	$num_vm = mysql_result(mysql_query("SELECT COUNT(*) FROM vm WHERE vcid=". $row['vcid'] ." AND exist=1;"), 0);
	$num_vm = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS total, SUM(IF(is_on=1,1,0)) AS is_on FROM vm WHERE vcid=".$row['vcid']." AND exist=1;"));
	print "<tr><td>".$row['site']."<td><a href='vc-detail.php?vcid=".$row['vcid']."'><b>".$row['name']."</b></a><td align='right'>$num_esx<td align='right'>".$num_vm['total'];
	print "<td align='right'>".$num_vm['is_on']."<td>".$row['ver']."<td>".$row['build']."<td>".$row['last_pass']."\n";
	$tot_esx += $num_esx;
	$tot_vm += $num_vm['total'];
	$tot_vm_on += $num_vm['is_on'];
}
print "<tr><th colspan = 2>Total for all sites:<td align='right'>$tot_esx<td align='right'>$tot_vm<td align='right'>$tot_vm_on";
print "<td colspan=3><i>".round($tot_vm/$tot_esx)." (". round($tot_vm_on/$tot_esx) ." powered) per ESX</i>\n";
print "</table>";

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

// ------------------- VM Search Form ----------------------------
print "<h1>VM Search</h1>\n";

print "<table class='SearchTable' align='center'><form method='GET'>\n";
print "<tr><td>By Name:<td>";
print "<input type='text' name='name' size=20";
if (array_key_exists('name', $_REQUEST)) {
	print " value='".$_REQUEST["name"]."'";
}
print	"> &nbsp<button type='submit' name='search' value='name'>Find VM</button>\n";

print "<tr><td colspan=2 align='center'>";
if ((array_key_exists('fuzz', $_REQUEST)) and ($_REQUEST["fuzz"] == 1)) {
	print "<input type='checkbox' name='fuzz' value=1 checked>Fuzzy name search";
} else {
	print "<input type='checkbox' name='fuzz' value=1>Fuzzy name search";
}

print "<tr><td>By IP:<td>";
print "<input type='text' name='ip' size=20";
if (array_key_exists('ip', $_REQUEST)) {
	print " value='".$_REQUEST["ip"]."'";
}
print "> &nbsp<button type='submit' name='search' value='ip'>Find VM</button>\n";
print "<tr><td>By VC Folder:<td>";
print "<input type='text' name='folder' size=20";
if (array_key_exists('folder', $_REQUEST)) {
	print " value='".$_REQUEST["folder"]."'";
}
print "> &nbsp<button type='submit' name='search' value='folder'>Find VM</button>\n";
print "<tr><td colspan=2 align='center'>";
if ((array_key_exists('vm_on', $_REQUEST)) and ($_REQUEST["vm_on"] == 1)) {
	print "<input type='checkbox' name='vm_on' value=1 checked>Only Powered On";
} else {
	print "<input type='checkbox' name='vm_on' value=1>Only Powered On";
}
if ((array_key_exists('vm_gone', $_REQUEST)) and ($_REQUEST["vm_gone"] == 1)) {
	print "&nbsp<input type='checkbox' name='vm_gone' value=1 checked>Include old/deleted";
} else {
	print "&nbsp<input type='checkbox' name='vm_gone' value=1>Include old/deleted";
}
print "<tr><td colspan=2 align='center'>";
if ((array_key_exists('show', $_REQUEST)) and ($_REQUEST["show"] == 'vm')) {
	print "<input type='radio' name='show' value='esx'>Show Cluster ESXs &nbsp<input type='radio' name='show' value='vm' checked>Show VM Detail";
} else {
	print "<input type='radio' name='show' value='esx' checked>Show Cluster ESXs &nbsp<input type='radio' name='show' value='vm'>Show VM Detail";
}
print "<tr><td colspan=2><span style='font-size:8pt;'>Wildcards (* and ?) are supported</span>";
print "</form></table>\n";

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

// ----------------- Last Passes table ----------------------------
print "<h1>Last Data Collection Runs</h1>\n";

$result = mysql_query("SELECT start, end, version, error, warn FROM script_run ORDER BY start DESC LIMIT 7;");
print "<table class='BasicTable' align='center' valign='top'><tr><th>Run start<th>Run end<th>Ver<th>Errors<th>Warns\n";
while ($row = mysql_fetch_assoc($result)) {
	if (is_null($row['end'])) {
		print "<tr><td>".$row['start']."<td colspan=4>Script run did not complete (fatal error)\n";
	} else {
		print "<tr><td>".$row['start']."<td>".$row['end']."<td>".$row['version']."<td>".$row['error']."<td>".$row['warn']."\n";
	}
}
print "</table>";

// ------------------- Layout table new row ------------------------
print "\n<tr><td colspan=3>\n";

// ------------------- Handle seach request ------------------------
// perform any required db actions
if (array_key_exists('search', $_REQUEST)) {
	print "<h1>Search results...</h1>";
	find_vm();
} else {
	print "<h1>Hints and Tips</h1>";
	print "<ul><li>Both VM names and OS hostnames are searched\n";
	print "<li>Wildcards etc...\n";
	print "    <ul><li>* - matches any character none or more times</li>\n";
	print "        <li>? - matches any character exactly once</li>\n";
	print "        <li>Fuzzy name searches provide broader results, which will normally find VM's despite typos etc</li></ul>\n";
	print "<li>VM IP's and Hostnames are only known if the VM was up with VM Tools running when a data collection run occurred</li>\n";
	print "<li>To bookmark the page with your preferred options pre-selected, tick the boxes, do a blank search, then bookmark the URL</li>\n";
	print "<li>Errors are generally caused by the data collection script not being able to connect to a vCentre</li>\n";
	print "<li>Warns are generally caused by the data collection script not being able to link bits of data together (around 100 or so is normal)</li>\n";
	print "</ul>\n";
}	
	
	
// ------------------- Layout table end ------------------------------
print "</table>\n";

// ------------------- Footer -----------------------------------------
?>

<!--[if IE]>
<span style='color:red;'><b>Internet Explorer is not supported - the search form will not work properly, use any other browser</b></span>
<![endif]--> 

<br>
<table class='NavTable' align='center' valign='top'><th>Main Pages:<td>Search page <td><a href='status.php'>Status page</a></table>
<table class='NavTableSub' align='center' valign='top'><th>Reports:<td><a href='snaps.php'>Snapshots</a></table>

<?php
$time = round((microtime(true) - $time_start)*1000);
print "Page took $time msecs to execute on server";

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

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

function find_vm() {
	global $debug;
	
	$query = "SELECT vm.name AS vm, INET_NTOA(vm2ip.ip) AS ip, vc.name AS vc, cluster.name AS cluster, cluster.clid, vm.vmid, vm.exist, vm.is_on";
	if ($_REQUEST["show"] == 'esx') {
		//nada
	} elseif ($_REQUEST["show"] == 'vm') {
		$query .= ", vm_ext.cpu, vm_ext.mem, vm_ext.vc_path ";
	}
	$query .= " FROM vm JOIN vm_ext USING (vmid) LEFT JOIN vm2ip USING (vmid) JOIN vc USING (vcid) JOIN cluster USING (clid) ";
	if ($_REQUEST["search"] == 'name') {
		if ((array_key_exists('fuzz', $_REQUEST)) and ($_REQUEST["fuzz"] == 1)) {
			$query .= "WHERE MATCH (vm.name,vm.hostname) AGAINST ('". search_char($_REQUEST["name"])."') ";
		} else {
			$query .= "WHERE (vm.name LIKE '". search_char($_REQUEST["name"]) ."' OR vm.hostname LIKE '". search_char($_REQUEST["name"])."%') ";
		}
	} elseif ($_REQUEST["search"] == 'ip') {
		$query .= "WHERE INET_NTOA(vm2ip.ip) LIKE '". search_char($_REQUEST["ip"]) ."' ";
	} elseif ($_REQUEST["search"] == 'folder') {
		$query .= "WHERE vm_ext.vc_path LIKE '". search_char($_REQUEST["folder"]) ."' ";
	} else {
		die ("Unexpected params ...!!");
	}
	
	if ((array_key_exists('vm_on', $_REQUEST)) and ($_REQUEST["vm_on"] == 1)) {
		$query .= "AND vm.is_on = 1 ";
	}
	if (!((array_key_exists('vm_gone', $_REQUEST)) and (!$_REQUEST["vm_gone"] == 0))) {
			$query .= "AND vm.exist = 1 ";
	}
	
	$query .= "GROUP BY vm.vmid ORDER BY vc.name, cluster.name, vm.name;";

	if ($debug) {
		print ($query);
	}

	$result = mysql_query($query);
	if (!$result) {
		print ("<b>Query failed !!!!<br>Query:</b> $query<br><b>Error:</b> ". mysql_error());
	}
	if (mysql_num_rows($result) == 0) {
		print ("No results found....");
		return;
	}
	
	if ($_REQUEST["show"] == 'esx') {
		print "<table class='BasicTable' align='center' valign='top'><tr><th>Name<th>IP<th>State<th>vCentre<th>Cluster<th>ESXs\n";
	} elseif ($_REQUEST["show"] == 'vm') {
		print "<table class='BasicTable' align='center' valign='top'><tr><th>Name<th>IP<th>State<th>Cluster<th>vCPU<th>Mem(MB)<th>Folder<th>Cluster\n";
	}
	$current_vmid = 0;
	$odd_row = false;
	while ($row = mysql_fetch_assoc($result)) {
		$odd_row = !$odd_row;
		if ($odd_row) {
			print "<tr class='odd'>";
		} else {
			print "<tr>";
		}
		if ($row['exist']) {
			if ($row['is_on']) {
				$vm_state = "ON";
			} else {
				$vm_state = "OFF";
			}
		} else {
			$vm_state = "gone";
		}
		print "<td><a href='vm-detail.php?vmid=".$row['vmid']."'><b>".$row['vm']."</b></a><td>".$row['ip']."<td>$vm_state<td>".$row['vc'];
		if ($_REQUEST["show"] == 'esx') {
			$esxs = mysql_result(mysql_query("SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM esx WHERE clid=". $row['clid'] .";"), 0);
			$res = mysql_query("SELECT esx.name FROM vm JOIN esx USING (esxid) WHERE vmid=". $row['vmid'] .";");
			if (mysql_num_rows($res) > 0) {
				$esx = mysql_result($res, 0);
				$esxs = str_replace($esx, "<span class='current_esx'>$esx</span>", $esxs);
			}
			print "<td><a href='cluster-detail.php?clid=".$row['clid']."'>".$row['cluster']."</a><td>$esxs\n";
		} elseif ($_REQUEST["show"] == 'vm') {
			print "<td>".$row['cpu']."<td>".$row['mem']."<td>".$row['vc_path']."<td><a href='cluster-detail.php?clid=".$row['clid']."'>".$row['cluster']."</a>\n";
		}
	}
	print "</table>\n";
	print "Found ". mysql_num_rows($result) ." VMs";
}

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


?>
Retrieved from ‘http://vwiki.co.uk/index.php?title=VI-DB_index.php_v1.1&oldid=1415’