Sstrutt: Minor correction

2011-09-08T10:42:09Z

Minor correction

← Older revision		Revision as of 10:42, 8 September 2011
Line 159:		Line 159:
	print "<li>Wildcards etc...\n";		print "<li>Wildcards etc...\n";
	print " <ul><li>* - matches any character none or more times</li>\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>\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>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>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";

Sstrutt: Initial creation

2011-03-30T12:16:26Z

Initial creation

New page

<source lang="php">

<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 "\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 (eg GWA-SR-MSSQL-?0 would match GWA-SR-MSSQL-10, GWA-SR-MSSQL-20, etc)</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 -----------------------------------------
?>



<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)));
}

?>

</source>

[[Category:PHP]]

VI-DB index.php v1.1 - Revision history

Sstrutt: Minor correction

Sstrutt: Initial creation