CODEWITHSUNDEEP
phpmysqlsql
Chris L
Chris L

Reputation: 115

Store uptimes in database

I want to monitor some websites from my server. Up- and down-times are stored in a database.

At the moment my table looks like this:

id (INT)

website_id (INT) 

uptime (DATETIME) 

downtime (DATETIME) 

lastState (INT)

The lastState stores the last HTTP-StatusCode

My Website No 5 eg has the following entries:

+----+------------+---------------------+---------------------+-----------+---------------------+---------------------+
| id | website_id | up                  | down                | lastState | created_at          | updated_at          |
+----+------------+---------------------+---------------------+-----------+---------------------+---------------------+
|  5 |          5 | 2018-04-26 13:56:09 | 2018-04-27 10:42:14 |       503 | 2018-04-26 11:56:09 | 2018-04-26 11:56:09 |
| 13 |          5 | 2018-04-27 10:50:06 | 2018-05-10 08:32:13 |       503 | NULL                | NULL                |
| 20 |          5 | 2018-05-10 08:40:06 | 2018-05-14 03:02:14 |       503 | NULL                | NULL                |
| 23 |          5 | 2018-05-14 03:10:06 | NULL                |       301 | NULL                | NULL                |
+----+------------+---------------------+---------------------+-----------+---------------------+---------------------+

I check the sites every 10 Minutes and when a host is down a new entry is created. I think (or better I hope) that this makes sense.

My only problem with this kind of data storage is that I want to calculate the uptime percentage. I think with pure SQL its not possible.

Please share your ideas with me. Thanks!

Upvotes: 1

Views: 778

Answers (1)

Thomas G
Thomas G

Reputation: 10216

Since your log system is pretty well done, it is "easy" to compute the website uptime from your logs, in pure SQL

Here's a way to do it . It will work on any recent version of MySQL. If you run the latest version (8), there is an alternative way to do it, with less instructions, using new windowing functions

I decompose each step. (Note that I named your table monitoring)

First we want to gather on the same row, the uptime and downtime of concurrent logs for the same website:

    SELECT m.website_id, m.uptime, m.downtime, m.lastState ,
            COALESCE((
                SELECT uptime
                FROM monitoring mold
                WHERE  m.downtime < mold.uptime AND mold.website_id=m.website_id
                GROUP BY website_id
            ), NOW()) AS lastuptime
    FROM monitoring m

Returns

| website_id |               uptime |             downtime | lastState |           lastuptime |
|------------|----------------------|----------------------|-----------|----------------------|
|          5 | 2018-04-26T13:56:09Z | 2018-04-27T10:42:14Z |       503 | 2018-04-27T10:50:06Z |
|          5 | 2018-04-27T10:50:06Z | 2018-05-10T08:32:13Z |       503 | 2018-05-10T08:40:06Z |
|          5 | 2018-05-10T08:40:06Z | 2018-05-14T03:02:14Z |       503 | 2018-05-14T03:10:06Z |
|          5 | 2018-05-14T03:10:06Z |               (null) |       301 | 2018-05-15T14:19:06Z |

Notice that the lastuptime column is always the same as the uptime of the next row for the same website. If there's no "next" then it means that the website is up, so we take NOW() as the reference datetime.

Next, with the above query, we can easily make in-row computing to measure the time difference (in second) between uptimes and downtimes

SELECT 
       mm.*,
       TIME_TO_SEC(TIMEDIFF(COALESCE(downtime, NOW()), uptime)) AS uptime_seconds, 
       TIME_TO_SEC(TIMEDIFF(lastuptime, COALESCE(downtime, NOW()))) AS downtime_seconds
FROM
(
    SELECT m.website_id, m.uptime, m.downtime, m.lastState ,
            COALESCE((
                SELECT uptime
                FROM monitoring mold
                WHERE  m.downtime < mold.uptime AND mold.website_id=m.website_id
                GROUP BY website_id
            ), NOW()) AS lastuptime
    FROM monitoring m
) mm

Returns

| website_id |               uptime |             downtime | lastState |           lastuptime | uptime_seconds | downtime_seconds |
|------------|----------------------|----------------------|-----------|----------------------|----------------|------------------|
|          5 | 2018-04-26T13:56:09Z | 2018-04-27T10:42:14Z |       503 | 2018-04-27T10:50:06Z |          74765 |              472 |
|          5 | 2018-04-27T10:50:06Z | 2018-05-10T08:32:13Z |       503 | 2018-05-10T08:40:06Z |        1114927 |              473 |
|          5 | 2018-05-10T08:40:06Z | 2018-05-14T03:02:14Z |       503 | 2018-05-14T03:10:06Z |         325328 |              472 |
|          5 | 2018-05-14T03:10:06Z |               (null) |       301 | 2018-05-15T14:23:16Z |         126790 |                0 |

We can already notice from those results that your downtime is mostly always the same (472 or 473 seconds).

The final step is to compute the total seconds of downtime and uptime for each website, and to determine the percentage of uptime 

SELECT website_id, 
       SUM(uptime_seconds) AS uptime_seconds, 
       SUM(downtime_seconds) AS downtime_seconds,

       (100 - SUM(downtime_seconds)/SUM(uptime_seconds)*100) AS pourc_uptime
FROM 
(
    SELECT 
           mm.*,
           TIME_TO_SEC(TIMEDIFF(COALESCE(downtime, NOW()), uptime)) AS uptime_seconds, 
           TIME_TO_SEC(TIMEDIFF(lastuptime, COALESCE(downtime, NOW()))) AS downtime_seconds
    FROM
    (
        SELECT m.website_id, m.uptime, m.downtime, m.lastState ,
                COALESCE((
                    SELECT uptime
                    FROM monitoring mold
                    WHERE  m.downtime < mold.uptime AND mold.website_id=m.website_id
                    GROUP BY website_id
                ), NOW()) AS lastuptime
        FROM monitoring m
    ) mm
 ) mmm
GROUP BY website_id

Returns

| website_id | uptime_seconds | downtime_seconds | pourc_uptime |
|------------|----------------|------------------|--------------|
|          5 |        1641971 |             1417 |      99.9137 |

SQL FIDDLE

So this only show the results for 1 website and a few logs (that you posted), but I am confident that it should run fine against your whole table, even if you have 1 million rows

You should create some VIEWS out of these queries to make your life easier.

Upvotes: 1

Related Questions