VI-DB index.php v1

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

<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, 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;
print "<table class='BasicTable' align='center' valign='top'><tr><th>Site<th>vCentre<th>ESXs<th>VMs<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);
	print "<tr><td>".$row['site']."<td><a href='vc-detail.php?vcid=".$row['vcid']."'>".$row['name']."</a><td>$num_esx<td>$num_vm<td>".$row['ver']."<td>".$row['build']."<td>".$row['last_pass']."\n";
	$tot_esx += $num_esx;
	$tot_vm += $num_vm;
}
print "<tr><td colspan = 2>Total for all sites:<td>$tot_esx<td>$tot_vm<td><td>\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>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>Data Collection Runs</h1>\n";

$result = mysql_query("SELECT start, end, version, error, warn FROM script_run ORDER BY start DESC LIMIT 6;");
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>VM IP's are only known if the VM was up with VM Tools running when a data collection run occurred (IP's are not lost if a VM is now down, but was up)</li>\n";
	print "<li>Wildcards...\n";
	print "    <ul><li>* - matches any character none or more times</li>\n";
	print "        <li>? - matches any character exactly once (eg GWA-SR-MSSQL-?0 would match GWA-SR-MSSQL-10, GWA-SR-MSSQL-20, etc</li></ul>\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 (under 100 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>Search page &nbsp <a href='status.php'>Status page</a><br>

<?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') {
		$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 ‘https://vwiki.co.uk/index.php?title=VI-DB_index.php_v1&oldid=1221’