Advanced SQL query

________

Hi,
I got a problem with a query.

I have built a script that checks if another server is up or down. If the server is down it stores the timestamp in a MySQL database – but if the server is running nothing is stored. The reason why I don’t store the uptime is because the scripts runs every minute, so the database would fill up really fast – so I’m trying to avoid string the uptime.

The problem is when I want to select the last downtime I want to count how many minutes the server was down. The database looks something like this:

Id – Date
01 - 2005-11-17 12:00:00
02 - 2005-11-17 12:01:00
03 - 2005-11-17 12:02:00
04 - 2005-11-17 12:03:00
05 - 2005-11-17 15:00:00
06 - 2005-11-17 15:01:00
07 - 2005-11-17 15:02:00
08 - 2005-11-17 15:03:00

Okay, according to the table the last downtime was November 11 @ 15:03 and then the server was down for 4 minutes.

Does anyone have any ideas how to solve this?

sfullman

this is a clue to the solution but you probably will still need to use some php for this.

suppose we had a table of check written:

id check number
1 1000
2 1001
3 1002
4 1003
5 1007
6 1008
7 1009
8 1010

OK obviously checks 1004-1006 are missing. You can know the highest or lowest check above or below which something is missing:

/ first check missing a check before it /
SELECT a.checknumber, b.checknumber
FROM checks a
LEFT JOIN checks b ON a.checknumber = b.checknumber + 1
WHERE b.id IS NULL

if you add some criteria to your where clause and choose your sort order, then use LIMIT 1, I think you could do this. i hope that helps, and I'll check back on this thread.

Sam

justsomeone

A quick solution would be to select all, sorted by id descending.

Note the tme on this record - this is the last time when it was down.

Now loop through the remaining records, still in descending order, until the difference between the timestamp of the current and the previous record is > 75 seconds. This marks the gap from one outage to the previous. Now in your "previous" record you have the first stamp from the last outage , and in your original record, you have the last stamp from the outage. With a little date subtraction you can tell how long the outage was for (approx), or you could just count how many records you had to read and get the number of minutes that way.

sfullman

Hi ????????

continuing with my query suggested, if you do like js says and sort by "check number" i.e. time descending, and add WHERE checknumber < target check number, you'll have the bottom check number for the contiguous block. Just add LIMIT 1 to get one record.

If you don't do limit 1, you'll get a list of all start times of outages by the way - a second query changing the join could then get the stop times of the outage.

sorry to use check number vs. time stamp, but it's easier for me to think 🙂

________

Those are both good alternatives, but I'm starting to think it easier to add a field to the table, downtime # - for each downtime period I'll just increase the number by one.

To do this I'm thinking of adding a text file storing the last downtime, and if downtime timestamp + 60 sec >= now it is the same period of downtime.

That way I can GROUP BY downtime# to get total of downtime times, and I can select * ... where downtime# is highest to get the duration of the last downtime.

It would seem to the fastes way, or what do you guys think?