I need a way to monitor whether a server is up and running OK. I want to know that the server itself is reachable, that Apache is running OK, that PHP is running (not just outputting the source of the page), and that MySQL is running. Further, I want to make the server do a little bit of work. Not a lot, otherwise the monitoring will slow the server down itself, but enough to allow the response times to indicate to me if the server is struggling.

I happen to use Pingdom to run this test, but it would work with any monitoring setup that allows you to check for a specific string in an HTTP response. Pingdom keeps track of Response Time history, which helps too.

There are a number of steps to setting this up. To prevent this one post becoming too long, I'll put the steps as posts within the topic.

Step 1: Set up a sub-domain on your server that you want to test. You don't have to do this; you could run your test page off a page that is on an already existing sub-domain. My reason for doing this is that I want to be able to identify the bandwidth that is being used by the monitor, and cPanel allows me to do this if I have a distinct sub-domain.

So: aleph.ping.oakhosting.net is set up.

Step 2: Create a database in MySQL. We're going to log all tests that succeed, as that may be useful data later in diagnosing a problem (don't know), and also to allow us to check for signs of abuse later. Also, we want to test that MySQL is running, and to do that we need an http page that will use the database.

So, create a database. Let's suppose the cPanel account name being used for this test page is testuser. Let's suppose that we the database we will create is called testdb, and that it has a password of ABC123.

So we create that database, we create the user, and we grant that user permissions to SELECT, EXECUTE and INSERT with that database.

Step 3: Create the tables in the database.

We create 3 tables, using this SQL code. One logs each check. One stores keys to the IP addresses (as that uses less space than storing the IP in full every time), and the other stores keys to the user agent string (enabling us to capture fairly long user agent strings without needing vast amounts of data for each row in the main logging table).

Thus:

CREATE TABLE IF NOT EXISTS `agentLookup` (
`agentId` int(10) NOT NULL AUTO_INCREMENT,
`agentTxt` varchar(255) NOT NULL,
PRIMARY KEY (`agentId`),
UNIQUE KEY `hostTxt` (`agentTxt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `ipLookup` (
`ipId` int(10) NOT NULL AUTO_INCREMENT,
`ipTxt` varchar(15) NOT NULL,
`firstLogged` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ipId`),
UNIQUE KEY `ipTxt` (`ipTxt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `serverCheck` (
`checkId` int(10) NOT NULL AUTO_INCREMENT,
`checkTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ipId` int(10) NOT NULL,
`agentId` int(10) NOT NULL,
PRIMARY KEY (`checkId`),
KEY `checkTime` (`checkTime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Step 4: We're going to insert an entry into the serverCheck table every time someone checks the server. But to do that, we need to see if the IP address is already in the ipLookup entry, and create a new line if not. Then we need to look up our foreign key. We have to do the same thing for the agent name.

This can be a little fiddly to get to work. The code below should work, but sometimes you also have to change the "Delimiter" text box in the PHP My Admin SQL screen to a double semi-colon. (What that does is make the end-of-statement delimiter a double semi-colon. The CREATE PROCEDURE statement then ends with ;;, which allows us to have statements within the procedure ending with a single semi-colon without those semi-colons being confused as denoting the end of the procedure).

DROP PROCEDURE IF EXISTS insertServerCheck ;

DELIMITER ;;
CREATE PROCEDURE insertServerCheck(IN ip varChar(15), IN agent varChar(255))
BEGIN
DECLARE rowCount INT;
DECLARE ipKey INT;
DECLARE agentKey INT;
DECLARE ipCount INT;
DECLARE lastWeek INT;
SET agent = LEFT(agent,255);
SET rowCount = (SELECT COUNT(*) FROM ipLookup WHERE ipTxt = ip);
IF rowCount = 0 THEN
INSERT INTO ipLookup (ipTxt) VALUES (ip);
END IF;
SET ipKey = (SELECT ipId FROM ipLookup WHERE ipTxt = ip);
SET rowCount = (SELECT COUNT(*) FROM agentLookup WHERE agentTxt = agent);
IF rowCount = 0 THEN
INSERT INTO agentLookup (agentTxt) VALUES (agent);
END IF;
SET agentKey = (SELECT agentId FROM agentLookup WHERE agentTxt = agent);

INSERT INTO serverCheck (ipId, agentId) VALUES (ipKey, agentKey);

SET ipCount = (SELECT Count(*) FROM serverCheck WHERE ipId = ipKey);
SET lastWeek = (SELECT Count(*) FROM serverCheck WHERE checkTime > date_sub(now(),INTERVAL 7 DAY));
SELECT ipCount, lastWeek;
END ;;

DELIMITER ;

Step 5, create a PHP page to call that stored procedure

I named it check_server.php.

<html>
<head>
<title>Oakhosting | Server: Aleph | Test Page</title>
<meta NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW"></meta>
</head>
<body>
<h1>Server Test Page for Oakhosting.net server ALEPH</h1>
<p>Testing HTTP, PHP and MYSQL:</p>
<?php
mysql_connect("", "testuser_sqlusr", "ABCD123");
mysql_select_db("testuser_testdb");
$ip = $_SERVER['REMOTE_ADDR'];
$agent = $_SERVER['HTTP_USER_AGENT'];
$query = "CALL insertServerCheck ('" . mysql_real_escape_string($ip) . "', '" . mysql_real_escape_string($agent) . "')";
$result= mysql_query($query);
$error = mysql_error();
if($error!="")
{
print ('<b style="color:red">Error: ' . $error . '</b>');
}
else
{
WHILE(($resultRow = mysql_fetch_assoc($result))!=FALSE)
{
print ('<table><tr><td>Checks from this IP address:</td><td>' . $resultRow['ipCount'] . '</td></tr>');
print ('<tr><td>Checks from all IPs in the past week:</td><td>' . $resultRow['lastWeek'] . '</td></tr></table>');
print ('<p style="color:red">Your IP address has been logged; abuse will be investigated</p>');
// We have to break up the text in the line below, otherwise a failure in the PHP handler that means http simply prints out this file will report success!
print ('<b>Server ' . 'is OK</b>');
}

}

?>

Obviously, replace the username, password, etc. with the ones you chose.

Notice the concatenation of "Server is OK" (commented in the code). If, for some reason, the server started serving up PHP files by outputting the source file rather than processing the PHP, that is a failure and an urgent one at that. So putting the line "print ('Server is OK')" would be a bad idea, as then our test string would appear on the page even if the PHP file had never been run.

Step 6 is to check it works. Go to your web browser, and load http://aleph.ping.oakhosting.net/check_server.php.

Step 7 is to set up your Pingdom account (or monitor of your choice) with a new check that will request this http page, and check for the string "Server is OK".

Step 8 is to clean up after ourselves.

The serverCheck table will clog up fast. In time, this could slow down the queries, which would make the server appear to be slowing down when in fact it is just the monitoring page getting bogged down.

So, we need a second stored procedure:

DELIMITER ;;

DROP PROCEDURE IF EXISTS tidyServerCheck;;

CREATE PROCEDURE tidyServerCheck()
BEGIN
DELETE FROM serverCheck WHERE checkTime<date_sub(now(),INTERVAL 30 DAY);
OPTIMIZE TABLE serverCHECK;
END;;

DELIMITER ;

and a second PHP page on our specially created subdomain; I called mine tidy_server.php

<html>
<head>
<title>Oakhosting | Server: Aleph | Test Page</title>
<meta NAME="ROBOTS" CONTENT="NOINDEX, NOFOLLOW"></meta>
</head>
<body>
<h1>Cleaning data from Server Test Page for Oakhosting.net server ALEPH</h1>
<?php
mysql_connect("", "testuser_sqlusr", "ABC123");
mysql_select_db("testuser_testdb");
$query = "CALL tidyServerCheck ()";
$result= mysql_query($query);
$error = mysql_error();
if($error!="")
{
print ('<b style="color:red">Error: ' . $error . '</b>');
}
else
{
print ('Cleaning complete');
}

?>

This deletes all test entries over 30 days old. (Modify the number if needed)

Create a cron job that runs

wget -O - http://aleph.ping.oakhosting.net/tidy_server.php > dev/null 2>&1

once a day, or once a week. {Please change the domain name - I don't need everyone who follows this tutorial to clean up my server for me}

I think that's it. If you want, or need, to use MySQLi library instead for the check_server.php file, here's the relevant portion of that file

<h1>Server Test Page for Oakhosting.net server ALEPH</h1>
<p>Testing HTTP, PHP and MYSQL:</p>
<?php
$mysqli = new mysqli("localhost", "testuser_sqlusr", "ABC123", "testuser_testdb");
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$ip = $_SERVER['REMOTE_ADDR'];
$agent = $_SERVER['HTTP_USER_AGENT'];
$query = "CALL insertServerCheck ('" . $mysqli->real_escape_string($ip) . "', '" . $mysqli->real_escape_string($agent) . "')";
if($mysqli->multi_query($query))
{
$result= $mysqli->store_result();
WHILE(($resultRow = $result->fetch_row())!=FALSE)
{
print ('<table><tr><td>Checks from this IP address:</td><td>' . $resultRow[0] . '</td></tr>');
print ('<tr><td>Checks from all IPs in the past week:</td><td>' . $resultRow[1] . '</td></tr></table>');
print ('<p style="color:red">Your IP address has been logged; abuse will be investigated</p>');
// We have to break up the text in the line below, otherwise a failure in the PHP handler that means http simply prints out this file will report success!
print ('<b>Server ' . 'is OK</b>');
}
}
else
{
//$error = mysql_error();
$error = $mysqli->error;
print ('<b style="color:red">Error: ' . $error . '</b>');
}

Amend the tidy_server script similarly.

Now: time for comments. Anyone got any questions? Or suggestions as to how I could improve it?

Step 2: Create a database in MySQL. We're going to log all tests that succeed, as that may be useful data later in diagnosing a problem (don't know), and also to allow us to check for signs of abuse later. Also, we want to test that MySQL is running, and to do that we need an http page that will use the database.

So, create a database. Let's suppose the cPanel account name being used for this test page is testuser. Let's suppose that we the database we will create is called testdb, and that it has a password of ABC123.

So we create that database, we create the user, and we grant that user permissions to SELECT, EXECUTE and INSERT with that database.

Obviously I didn't mean that the database had a password etc. Rather we create a MySQL user called testuser_sqlusr, and give that user the password ABC123, and grant that user rights to testuser_testdb.

Very nice. You could package this up as a WordPress plugin pretty easily too, if you're looking for something else to do :D

Must to do.

Thank you!!!!!

Thanks alot james!

Excellent guide.
I'll use part of your code to setup my own monitoring. The integration of pingdom is an excellent idea. I was always setting up cron to do this, but why bother when pingdom will do the cron job for you.

Wow. That was an amazing tutorial. Thanks a lot James.

You're welcome - I hope it helps...

Good tutorial that is for sure. I really like how you have integrated the testing into the whole stack.

Sugestions - for the string you are testing, I would suggest using strrev() for the string you are testing for or even to fetch the string from the database itself.

Additionally, rather than a separate clean-up job, why not simply trigger the clean-up to occur automatically the first time the script is called each day, could even use a trigger such as if hour=0 then cleanup; this would further reduce the need to run a cron job locally.

Thank thank thank! I love this tutorial