MySql Archives - Squidworks

LabTech Vitals Dashboard

Today we release the next round for the Squidworks Labtech Vitals Dashboard.

The Labtech Vitals Dashboard is a set of web pages that query the local Labtech MySql database and provides visual widgets of important data on the main display of the Labtech Support Console. We currently have 17 different widgets with 10 of them having detail views.

To install the Dashboard you will need to download and install PHP 5 for windows at PHP.net on your Labtech server. Afterwards download the SWVDB zip package and unzip to c:\inetpub\wwwroot\SWVDB. Go into this directory and find the header.php file and open it with notepad. You will need to edit the LT server address, Username and password for the LT database access to work.

Once completed you should be able to browse http://your.labtech.server/SWVDB/ and get the dashboard. Now to have the dashboard auto load in the console for everyone who uses support console go to your LT config dashboard -> Config -> Control Center and set the Initial URL Value to your tested URL from above.

Get the dashboard -> Download SWVDB here

Should look like this:

Widgets:

Systems Missing AntiVirus application

Agents missing patches

Agents Online

Offline Servers Alarms

Dell Appassure 5 Backup Core Monitor

ESX Hardware Health Monitor

Hitman Pro Monitor

Agent Monitors

ESX Monitor Details Page (Failed/Missing/All)

Dell Appassure Monitor Details (Completed and Failed)

Some widgets may not function correctly if you are not using the source data tables and extra monitor scripting I’ve made available like the Squidworks ESXi Health Monitor for Labtech located at ESX Health Monitor, The Dell Appassure Backups/Failures also requires my Appassure Backup Scripting to populate the database with data. You must edit the index.php page in notepad and uncomment the 5 function calls noted in index.php for ESX and Appassure counters to function. They have been commented out so that you do not get errors if tables are missing. Install both ESX and Appassure packages before uncommenting code block.

Enjoy

Cubert 😎

Vitals Dashboard for Labtech MSP Providers.

Squidworks Vitals Dashboard for Labtech Software

Download SWVDB here -> SWVDB-0.1

Install instructions are included in the “install.txt” file inside of zip. You will need to have PHP 5.3.27 installed on you Labtech server and can be downloaded at http://php.net

The purpose of the Vitals Dashboard is to get key pieces of information about the health of your client base in front of you and your techs. Each time you launch your control center your dashboard can appear in the console’s display. This brings the vital information to the forefront like Offline Servers and HitmanPro alarms.

Some dashboard items are interactive and will supply more data if selected. Clicking on the HitmanPro Alarms will launch you to the HitmanPro Dashboard.

HitmanPro Dashboard

The HitmanPro Dashboard shows all the current threats found and lists them by client and computer. By clicking on the computer name you will get a detailed account of the threat alarm. At this main view level you can also see what systems were scanned in the last 24 hours and when, displayed by client.

Hitman Pro detailed alarm viewer allows you to see in detail the cause of the alarm and what threat was found. We have color coded the alarm message and added some formatting to better display the details of the alarm from the default view Labtech offers.

Agents with excessive reoccurring logs

Agents Missing Service packs or are out of date

Agents With Disk Space or Failures

I hope this helps some of you out there.

Cubert

😎

PHP Pagination with timestamp / timeline display

Pagination with a timestamp as the center value

We provide a way to scroll page to page of data and show the time stamp of when the data was collected as part of the page navigations.

Here we setup the first query and findout how many pages we have.

$sql = “SELECT COUNT(*) FROM table where name = ‘” . $name . “‘ and type = ‘”

Pagination with a timestamp as the center value

We provide a way to scroll page to page of data and show the time stamp of when the data was collected as part of the page navigations.

Here we setup the first query and findout how many pages we have.

$sql = "SELECT COUNT(*) FROM table where name = '" . $name . "' and type = '" . $type . "'"; $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR); $r = mysql_fetch_row($result); $numrows = $r[0]; $rowsperpage = 1; $totalpages = ceil($numrows / $rowsperpage); if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) { $currentpage = (int) $_GET['currentpage']; } else { $currentpage = 1; } if ($currentpage > $totalpages) { $currentpage = $totalpages; } if ($currentpage < 1) { $currentpage = 1; } $offset = ($currentpage - 1) * $rowsperpage;

Now we need to make the second query for the actual data so we can place our scroll links and then display our data.



$sql = "SELECT * FROM table where name = '" . $name . "' and type = '" . $type . "' ORDER BY data_time DESC LIMIT $offset, $rowsperpage";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

while($row = mysql_fetch_array($result)){

$data = $row['data'];

$data_time = $row['data_time']; // expected in epoch

}

$range = 8;

echo "Reported data";

if ($currentpage > 1) {

echo " << ";

$prevpage = $currentpage - 1;

echo " < ";

}

for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) { if (($x > 0) && ($x <= $totalpages)) { if ($x == $currentpage) { echo " ["; $rectime = date(DATE_RFC822, $data_time); echo $rectime; echo "] "; } else { echo " $x "; } } } if ($currentpage != $totalpages) { $nextpage = $currentpage + 1; echo " > "; echo " >> "; }


echo "

";

echo $data;

echo "";

You should have a database that use epoch time as its time field then set $data_time to that field. You will get a nice output with links to pages forward and previous and the time stamp in local time in the center as the current page you are on. Great for scrolling throught time dlimited data in a time line.

Howto: MySQL Full Text Search in PHP

This tutorial is intended for Text Searching using MySQL (http://www.MySQL.com/) and PHP (http://www.php.net) and will focus on the Full-text capabilities presented by MySQL.

Synopsis

We run a website that hosts web blogs, we have a database that contains blog posts. We might create a table in our database using a statement like this:

CREATE TABLE blogs (body TEXT, title VARCHAR(250), id INT NOT NULL auto_increment, PRIMARY KEY(id);

This tutorial is intended for Text Searching using MySQL (http://www.MySQL.com/) and PHP (http://www.php.net) and will focus on the Full-text capabilities presented by MySQL.

Synopsis

We run a website that hosts web blogs, we have a database that contains blog posts. We might create a table in our database using a statement like this:

CREATE TABLE blogs (body TEXT, title VARCHAR(250), id INT NOT NULL auto_increment, PRIMARY KEY(id);

How to modify your current database to accommodate Full-text searching.

Now we want to provide a simple search form and the ability to search keywords from the body field in the table “blog” of your database. First you will need to turn on Full Text Search for the table “blog” in your MySql database. Here is how to do that in MySql.

ALTER TABLE blog ADD FULLTEXT(body, title);

How to use a simple Full-text search to quickly gather relevant responses.

Lets create our PHP form and database query to support our search. I normally create a single php file called “search.php” then I “include(“search.php”)” where ever I want my search form and returning search data go but I can see where you may want to split this up and have the form and returning data in different areas so change as you see fit.

search.php

<?php
$keyword = $_POST[‘keyword’];
//we will assume your including this search in your index.php
//if not replace index wiht search.php or any page your going to do the sql query and display
//from.
?>
<h2>Search Our Archives</h2>
<form action=”index.php” enctype=”application/x-www-form-urlencoded” method=”post”>
<input name=”keyword” type=”text” />
<input type=”submit” value=”Search” />
</form>
<br>
<?php

if ($keyword != ”) {

mysql_connect(“localhost”, “username”, “password”);

mysql_select_db(“database_name”);

        $sql = ”
            SELECT body,
                MATCH(body) AGAINST(‘$keyword’) AS score
                FROM blog
            WHERE MATCH(body) AGAINST(‘$keyword’)
            ORDER BY score DESC
        “;
        $myresult = mysql_query($sql);
            while($row = mysql_fetch_array($myresult)) {
                echo “<table border=”0″><tbody><tr>”;
                echo “<td>Search – SCORE</td><td>Content</td></tr>”;
                echo “<tr><td><strong>” . $row[‘score’] . “</strong></td>”;
                echo “<td>” . $row[‘body’] . “</td></tr>”;
                echo “</tbody></table>”;
            }
    }
?>

This search.php makes a quick and easy Full Test Search form and display for the data. It checks $keywords to validate data before displaying the data in a table. It includes the MySQL score returned by the Full Text Search. I hope this helps someone out there get a search up and running fast.

Enjoy..