A script to ping servers from a DB

[-fedexer-]

Ok well i've been working on a script which will become a cron job , which will select all the completed servers from a database, and then ping each of those, and if online change the online column to 1 and if offline change it to 0.
Also i am trying to insert the time and date into a datetime field in the database but i think i am calling it wrongly or something as it never changes.
I thought i had the script completed, but it seems there are a few flaws, such as the script does not seem to check the servers at all....

Any help would be appreciated , even if it is modding a previously created script of which you can point me in the direction of.

My script :happysad::

<?php
require_once("../connection/connect.php");
	header("Expires: Thu, 17 May 2001 10:17:17 GMT");    // Date in the past
  	header ("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified
	header ("Cache-Control: no-cache, must-revalidate");  // HTTP/1.1
	header ("Pragma: no-cache"); 

$query = "SELECT * FROM serverlist WHERE serverCompleted = '1'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

$query = "SELECT * FROM serverlistglobal WHERE ID = '1'";
$result = mysql_query($query);
$globalVars = mysql_fetch_array($result);

$lastGlobCheck = $globalVars['serverChecks'];
$newGlobCheck = $lastGlobCheck + 1;
$query = "UPDATE serverlistglobal SET serverChecks = '$newGlobCheck' WHERE ID = '1'";
$result = mysql_query($query);



do{

//check for socket open
$checkSock = @fsockopen($row['serverIP'], $row['serverPort'], $errno, $errstr, 30);
if(!$checkSock){
$uptime = (($row['serverChecks'] / $globalVars['serverChecks']) * 100);
$query = "UPDATE serverlist SET serverOnline = '0', serverUptime = '$uptime' WHERE serverID = '".$row['serverID']."'";
$result = mysql_query($query);
}else{
$lastChecks = $row['serverChecks'];
$newChecks = $lastChecks + 1;
$query = "UPDATE serverlist SET serverOnline = '1', serverChecks = '$newChecks' WHERE serverID = '".$row['serverID']."'";
$result = mysql_query($query);
$uptime = (($row['serverChecks'] / $globalVars['serverChecks']) * 100);
$query = "UPDATE serverlist SET serverOnline = '1', serverChecks = '$newChecks' WHERE serverID = '".$row['serverID']."'";
$result = mysql_query($query);
}
@fclose($checkSock);
}while($row = mysql_fetch_array($result));

$query = "SELECT * FROM serverlistglobal WHERE ID = '1'";
$result = mysql_query($query);
$globalVars = mysql_fetch_array($result);

$time = date('Y/m/d g:i:s');
$query = "UPDATE serverlistglobal SET lastCheckTime = '$time' WHERE ID = '1'";
$result = mysql_query($query);


exit;
?>

Thanks
-fedexer-

 

[DefecTalisman]

Good mysql syntax has a ; at the end.

Another would be what was the string you used to create that 'serverlistglobal', more specifically 'lastCheckTime'?

 

[cowctcat]

What are the @'s in front of the functions for?
Does it work if you take those out?

Good mysql syntax has a ; at the end.

Another would be what was the string you used to create that 'serverlistglobal', more specifically 'lastCheckTime'?

The php manual specifically states that semi colons should not be used when making a query in php.

 

[DefecTalisman]

hmm... ok. I stand corrected then.

The @ would be suppressing an error message. Leave the one on fsockopen() though. Also reduce that timeout. php max execution is set to 60sec default. 2 ports fail and you hit the limit.

 

[-fedexer-]

I have managed to get the time working now, as it should.

$row = @mysql_fetch_array($result));

The @'s infront of that function are due to the fact that if they are not there an error is thrown :

mysql_fetch_array(): supplied argument is not a valid MySQL result resource in line 41 (which is where the above code sits)

Any suggestions? i know that happens when there is no result, but why would that be happening?

hmm... ok. I stand corrected then.

Also reduce that timeout. php max execution is set to 60sec default. 2 ports fail and you hit the limit.

Ok.. say i reduce it, but what if i do go over the limit? what can i do to make it start fresh again? (the database with the servers on it is likely to get very long).

Also this little formula i created:

$uptime = (($row['serverChecks'] / $globalVars['serverChecks']) * 100);

Does not ever seem to add a value to the database, the field it is entered into is a float field, should it be a double? The fields being devided by eachother are int's incase thats needed info.


Also i updated my first post with the newer version of code i had on my computer, that version fixes the date (however strange it may be as it is not in GMT but that doesn't matter).

 

[DefecTalisman]

There servers time is EST so -4GMT.

 

[DefecTalisman]

Maybe try

 decimal(5,2)

for the average.

The timeout only applies when the port can't be opened. So if you had 100 servers in your script to check and the timeout was set to 1sec (which my server alert is) then you would have around enough time for 59 of those to fail (small chance in hell).

 

[-fedexer-]

Maybe try

 decimal(5,2)

for the average.

The timeout only applies when the port can't be opened. So if you had 100 servers in your script to check and the timeout was set to 1sec (which my server alert is) then you would have around enough time for 59 of those to fail (small chance in hell).

Ok.. but i think i may end up with a few hundred servers in the list.. then what? cause it is likely that they will not all be online for sure.. some more than 60 will likely be offline.. what would happen then? or need to be done?

 

[DefecTalisman]

Not to sure what would happen. run a test, try 100 or so dummy ports on a non-x10 server.

 

[woiwky]

As far as I know, anything done with streams doesn't count towards php's execution time. So there shouldn't be anything to worry about there. However, you can try using set_time_limit() if you run into any problem with that, but your php config may not allow its use.

But anyway, I would start with using:

mysql_query($query);

by itself for update queries, excluding the "$result =". Your while's condition is passing $result to mysql_fetch_array(), but in your if/else blocks you have set its value to a boolean (the value which mysql_query() returns with update queries). The only exception is the first iteration since it's a do-while loop, which means the same $row variable is being used that you defined for the first select query.

With that out of the way, which select query is it that you want to be fetching rows from? Whichever one you *aren't* using, don't use $result with it if you want to use that variable with the while loop.

Furthermore, there are a few things I'm confused about:

First, you make a new select query and reset $globalVars with it after the loop, but I don't see any use of this variable afterwards.

Second, not only is $uptime not even used in the else clause of the while loop, but I believe the calculation of it is off a bit since it doesn't look like the current server status/server check are being taken into account.

I'm not 100% sure on any of this since I don't completely understand your db setup, but from what I can figure, I would try this instead:

<?php
require_once("../connection/connect.php");
header("Expires: Thu, 17 May 2001 10:17:17 GMT");    // Date in the past
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified
header("Cache-Control: no-cache, must-revalidate");  // HTTP/1.1
header("Pragma: no-cache"); 

$query = "SELECT * FROM serverlist WHERE serverCompleted = '1'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);

$query = "SELECT * FROM serverlistglobal WHERE ID = '1'";
$res = mysql_query($query);
$globalVars = mysql_fetch_array($res);

$globalVars['serverChecks']++;
$query = "UPDATE serverlistglobal SET serverChecks = '{$globalVars['serverChecks']}' WHERE ID = '1'";
mysql_query($query);

do{
    //check for socket open
    $checkSock = @fsockopen($row['serverIP'], $row['serverPort'], $errno, $errstr, 30);
    if (!$checkSock) {
        $uptime = (($row['serverChecks'] / $globalVars['serverChecks']) * 100);
        $query = "UPDATE serverlist SET serverOnline = '0', serverUptime = '$uptime' WHERE serverID = '".$row['serverID']."'";
        mysql_query($query);
    }
    else {
        $row['serverChecks']++;
        $uptime = (($row['serverChecks'] / $globalVars['serverChecks']) * 100);
        $query = "UPDATE serverlist SET serverOnline = '1', serverChecks = '{$row['serverChecks']}', serverUptime = '$uptime' WHERE serverID = '".$row['serverID']."'";
        mysql_query($query);
    }
    @fclose($checkSock);
}
while($row = mysql_fetch_array($result));

//I'm not sure why the other code wasn't working, it looked fine. Try this though
$query = "UPDATE serverlistglobal SET lastCheckTime = NOW() WHERE ID = '1'";
mysql_query($query);

exit;
?>

I'm quite sure that there will be something wrong with that since I'm rather confused with this system as I said. But hopefully it's a step forward.

 

[-fedexer-]

Woah thanks for all of the input guys, and also woiwky , you seem to have spent a while looking through my code. Ok by the variable you don't see a use for afterwards do you mean the 'lastCheckTime' ?.

Secondly thanks for informing me about the proper use of or even the proper way to have an update statement, i just figured you needed a mysql query attached to a variable, silly me.

Also this:

Second, not only is $uptime not even used in the else clause of the while loop, but I believe the calculation of it is off a bit since it doesn't look like the current server status/server check are being taken into account.

The uptime is calculated by dividing the amount of server checks the server in the db has sucessfully been on for, by the total amount of checks ever made. If that makes sense, so in that case would it be correct , or is it still not being calculated properly?

Thanks for all your input

-fedexer-

 

[woiwky]

Ok by the variable you don't see a use for afterwards do you mean the 'lastCheckTime' ?

Well, I was actually talking about this code you had right after the while loop:

$query = "SELECT * FROM serverlistglobal WHERE ID = '1'";
$result = mysql_query($query);
$globalVars = mysql_fetch_array($result);

It's a repeat of some code you had before the while loop, and it appeared before the update of that table. So I didn't see its purpose. Were you planning to add more or was that just an accident?

The uptime is calculated by dividing the amount of server checks the server in the db has sucessfully been on for, by the total amount of checks ever made. If that makes sense, so in that case would it be correct , or is it still not being calculated properly?

My point about the uptime calculation was that the variables used in the calculation were slightly off. Although you did update 'serverlistglobal' to increment the number of server checks, $globalVars['serverChecks'] was still one less than this new value. And for the else clause(a successful connection), $row['serverChecks'] should be incremented to account for the current successful connection. Does that make sense, or am I misunderstanding what you were trying to do?

 

[DefecTalisman]

Rather than keeping a global check gen. keep 2 gens for each server, up and down. this way if you add a server at a later stage it wont come in at a funky percentage.

 

[-fedexer-]

Well, I was actually talking about this code you had right after the while loop:

$query = "SELECT * FROM serverlistglobal WHERE ID = '1'";
$result = mysql_query($query);
$globalVars = mysql_fetch_array($result);

It's a repeat of some code you had before the while loop, and it appeared before the update of that table. So I didn't see its purpose. Were you planning to add more or was that just an accident?

I now see what you mean, clearly when i was typing this up i had got my mind thinking that i had to call it again because i had changed the table in some way, but infact i haven't and therefore you are correct i do not need it. (thanks for pointing that out, i would never have noticed that, say i had changed a field in the serverlistglobal, would i then need to re-call it like i thought i had to?) I'll remove that.

Rather than keeping a global check gen. keep 2 gens for each server, up and down. this way if you add a server at a later stage it wont come in at a funky percentage.

Yes i think i will change my initial idea to that, because as you say , now that i have had time to think about it.. the way i am doing it would cause some very obscure precentages, which means a server would never have had a high uptime, which would be false information.

Also would a timeout of just a second or 2 be sufficient for the fsockopen to connect to a computer elsewhere?

Right im off to bed, but i will reply in the morning

Thanks guys
-fedexer-

 

[DefecTalisman]

Yes, as I said. The server alert image in my sig is set to 1sec. It works fine (except when the hardware firewall on ciroc blocks mysql port)

 

[-fedexer-]

Right, and as it seems that streams don't count towards the php max execution it should work on a large basis.

Also , now that most of this has been covered, what sort of command would i use in the standard cron jobs on cpanel? (just an example please )

Thanks
-fedexer-

 

[Slothie]

Here's a quick question. Do you want a real ping or just a server status check?

 

[DefecTalisman]

Please can you post an example slothie.

 

[bigjoe4]

DefecTalisman, If someone continually refreshed a page in which you had posted would your sig ping the server so much that it did a DOS attack on x10?