Uptime Monitor Downtime

/*  Gets the value of an option for a connection.
    First checks to see if the option is set on a per-connection basis, and then
    checks to see if it's set globally.
    Returns whichever it finds first.
    If the option is not found, returns NULL.
*/
CREATE FUNCTION GetConnectionOption (@connection_id int, @option_name varchar(25))
RETURNS varchar(50)
AS
BEGIN

--  Fall through is to return NULL
    DECLARE @return_this varchar(50);
    SET @return_this=NULL;

--  Pre-convert the option_name to upper to save time
    DECLARE @to_upper varchar(25);
    SET @to_upper=UPPER(@option_name);

--  Is this option set on a per-connection basis?
    IF (
        SELECT
            COUNT(*)
        FROM
            connection_options
        WHERE
            @connection_id=connection_id AND
            @to_upper=UPPER(option_name)
    )<>0

        SET @return_this=(
            SELECT TOP 1
                option_value
            FROM
                connection_options
            WHERE
                @connection_id=connection_id AND
                @to_upper=UPPER(option_name)
        );

--  If not, is it set at all?
    ELSE IF (
        SELECT
            COUNT(*)
        FROM
            options
        WHERE
            @to_upper=UPPER(option_name)
    )<>0

        SET @return_this=(
            SELECT TOP 1
                option_value
            FROM
                options
            WHERE
                @to_upper=UPPER(option_name)
        );

--  Return whatever value we've managed to find (if any)
    RETURN @return_this;

END;

GO

/*  Gets four tables which reveals the connection's details:
    1.  Connection information.
    2.  A list of datapoints for display in a graph.
    3.  A list of the downtimes which exceed the set threshold for this connection.
    4.  A list of the high latency periods which exceed the set threshold for this connection.
*/
CREATE PROCEDURE GetConnectionDetail
    @connection_id int
AS

    SET NOCOUNT ON;

--  Make sure the database remains in a consistent state
    BEGIN TRANSACTION get_detail;

--  BEGIN DATA GATHERING

--  Grab the number of minutes per period
    DECLARE @minutes_per_period int;
    SET @minutes_per_period=CONVERT(
        INT,
        dbo.GetConnectionOption(
            @connection_id,
            'minutes_per_period'
        )
    );

--  Convert to number of seconds per period
    DECLARE @seconds_per_period bigint;
    SET @seconds_per_period=@minutes_per_period*60;

--  We need somewhere to store the filtered pings table
    DECLARE @pings_temp TABLE
    (
        row_num bigint NOT NULL PRIMARY KEY,
        datetime datetime2 NOT NULL,
        latency bigint NOT NULL
    );

    INSERT INTO @pings_temp
    (
        row_num,
        datetime,
        latency
    )
    SELECT
        ROW_NUMBER() OVER (ORDER BY datetime ASC),
        datetime,
        latency
    FROM
        pings
    WHERE
        connection_id=@connection_id AND
        datetime>DATEADD(
            minute,
            @minutes_per_period*-1,
            GETDATE()
        );

--  Building incrementally on that, we need somewhere to store only the pings occuring as the first in,
--  or right after a downtime
    DECLARE @downtimes_temp TABLE
    (
        row_num bigint NOT NULL PRIMARY KEY,
        datetime datetime2 NOT NULL,
        latency bigint NOT NULL
    );

    INSERT INTO @downtimes_temp
    (
        row_num,
        datetime,
        latency
    )
    SELECT
        ROW_NUMBER() OVER (ORDER BY row_num ASC),
        datetime,
        latency
    FROM
        @pings_temp AS outer_query
    WHERE
        (
            latency=-1 AND
            (
                SELECT TOP 1
                    subquery.latency
                FROM
                    @pings_temp AS subquery
                WHERE
                    subquery.row_num=outer_query.row_num-1
            )<>-1
        )
        OR
        (
            latency<>-1 AND
            (
                SELECT TOP 1
                    subquery.latency
                FROM
                    @pings_temp AS subquery
                WHERE
                    subquery.row_num=outer_query.row_num-1
            )=-1
        );

--  Now we build the second table -- a list of downtimes, with their start and end times, total downtime
--  and number of pings missed
    DECLARE @downtimes_final_temp TABLE
    (
        start_time datetime2 NOT NULL,
        end_time datetime2 NOT NULL,
        downtime int NOT NULL,
        count int NOT NULL
    );

    INSERT INTO @downtimes_final_temp
    (
        start_time,
        end_time,
        downtime,
        count
    )
    SELECT
        start_table.datetime AS start_time,
        end_table.datetime AS end_time,
        DATEDIFF(
            second,
            start_table.datetime,
            end_table.datetime
        ),
        (
            SELECT
                COUNT(*)+1
            FROM
                @pings_temp AS pings_temp
            WHERE
                pings_temp.datetime>start_table.datetime AND
                pings_temp.datetime<end_table.datetime
        )
    FROM
        @downtimes_temp AS start_table,
        @downtimes_temp AS end_table
    WHERE
        start_table.row_num=end_table.row_num-1 AND
        start_table.row_num%2=1;

--  Now we need to get a table of all the high latency periods
    DECLARE @latency_temp TABLE
    (
        row_num bigint NOT NULL PRIMARY KEY,
        datetime datetime2 NOT NULL,
        latency bigint NOT NULL
    );

    DECLARE @latency_threshold int;
    SET @latency_threshold=CONVERT(
        INT,
        dbo.GetConnectionOption(
            @connection_id,
            'latency_threshold'
        )
    );

    INSERT INTO @latency_temp
    (
        row_num,
        datetime,
        latency
    )
    SELECT
        ROW_NUMBER() OVER (ORDER BY row_num ASC),
        datetime,
        latency
    FROM
        @pings_temp AS outer_query
    WHERE
        (
            latency<=@latency_threshold AND
            (
                SELECT TOP 1
                    subquery.latency
                FROM
                    @pings_temp AS subquery
                WHERE
                    subquery.row_num=outer_query.row_num-1
            )>@latency_threshold
        )
        OR
        (
            latency>@latency_threshold AND
            (
                SELECT TOP 1
                    subquery.latency
                FROM
                    @pings_temp AS subquery
                WHERE
                    subquery.row_num=outer_query.row_num-1
            )<=@latency_threshold
        );

--  Building the fourth table, same as second for high latency periods
    DECLARE @latency_final_temp TABLE
    (
        start_time datetime2 NOT NULL,
        end_time datetime2 NOT NULL,
        downtime int NOT NULL,
        count int NOT NULL
    );

    INSERT INTO @latency_final_temp
    (
        start_time,
        end_time,
        downtime,
        count
    )
    SELECT
        start_table.datetime AS start_time,
        end_table.datetime AS end_time,
        DATEDIFF(
            second,
            start_table.datetime,
            end_table.datetime
        ),
        (
            SELECT
                COUNT(*)+1
            FROM
                @pings_temp AS pings_temp
            WHERE
                pings_temp.datetime>start_table.datetime AND
                pings_temp.datetime<end_table.datetime
        )
    FROM
        @latency_temp AS start_table,
        @latency_temp AS end_table
    WHERE
        start_table.row_num=end_table.row_num-1 AND
        start_table.row_num%2=1;

    DECLARE @row_skip int;
    SET @row_skip=CONVERT(
        INT,
        (
            SELECT
                COUNT(*)
            FROM
                @pings_temp
        )/CONVERT(
            INT,
            dbo.GetConnectionOption(
                @connection_id,
                'points_per_period'
            )
        )
    );

    IF @row_skip=0 SET @row_skip=1;

--  DATA GATHERING COMPLETE

--  OUTPUT DATA

--  #1
--  Connection information
    SELECT
        connections.mnemonic AS mnemonic,
        connections.remote_addr AS remote_addr,
        @minutes_per_period AS minutes_per_period,
        dbo.GetConnectionOption(@connection_id,'latency_threshold') AS latency_threshold,
        dbo.GetConnectionOption(@connection_id,'downtime_ping_count') AS downtime_ping_count,
        dbo.GetConnectionOption(@connection_id,'downtime_latency_count') AS downtime_latency_count,
        up_down.status AS up_down_status,
        up_down.since_when AS up_down_since_when,
        high_low.status AS high_low_status,
        high_low.since_when AS high_low_since_when,
        downtime_sum.downtime AS up_down_downtime,
        latency_sum.downtime AS high_low_downtime,
        avg_latency.avg_latency AS avg_latency
    FROM
        connections,
        (
            SELECT TOP 1
                CASE WHEN latency=-1
                    THEN 'Down'
                    ELSE 'Up'
                END AS status,
                CASE WHEN latency=-1
                    THEN
                    (
                        SELECT TOP 1
                            datetime
                        FROM
                            @downtimes_temp
                        ORDER BY
                            datetime DESC
                    )
                    ELSE NULL
                END AS since_when
            FROM
                @pings_temp
            ORDER BY
                datetime DESC
        ) AS up_down,
        (
            SELECT TOP 1
                CASE WHEN latency>@latency_threshold
                    THEN 'High"'
                    ELSE 'Low'
                END AS status,
                CASE WHEN latency>@latency_threshold
                    THEN
                    (
                        SELECT TOP 1
                            datetime
                        FROM
                            @latency_temp
                        ORDER BY
                            datetime DESC
                    )
                    ELSE NULL
                END AS since_when
            FROM
                @pings_temp
            ORDER BY
                datetime DESC
        ) AS high_low,
        (
            SELECT
                CASE WHEN subquery.downtime IS NULL OR subquery.downtime=0
                    THEN 100.00
                    ELSE ROUND((1-(subquery.downtime/CAST(@seconds_per_period AS float)))*100,2)
                END AS downtime
            FROM
                (
                    SELECT
                        SUM(downtime) AS downtime
                    FROM
                        @downtimes_final_temp
                ) AS subquery
        ) AS downtime_sum,
        (
            SELECT
                CASE WHEN subquery.downtime IS NULL OR subquery.downtime=0
                    THEN 100.00
                    ELSE ROUND((1-(subquery.downtime/CAST(@seconds_per_period AS float)))*100,2)
                END AS downtime
            FROM
                (
                    SELECT
                        SUM(downtime) AS downtime
                    FROM
                        @latency_final_temp
                ) AS subquery
        ) AS latency_sum,
        (
            SELECT
                CASE WHEN subquery.avg_latency IS NULL
                    THEN 0
                    ELSE subquery.avg_latency
                END AS avg_latency
            FROM
                (
                    SELECT
                        AVG(latency) AS avg_latency
                    FROM
                        @pings_temp
                    WHERE
                        latency<>-1
                ) AS subquery
        ) AS avg_latency
    WHERE
        connections.connection_id=@connection_Id;

--  #2
--  All datapoints
    SELECT
        *
    FROM
        (
            (
                SELECT
                    datetime,
                    latency
                FROM
                    @pings_temp AS pings_temp
                WHERE
                    row_num%@row_skip=0
            )
            UNION
            (
                SELECT
                    datetime,
                    latency
                FROM
                    @latency_temp
            )
            UNION
            (
                SELECT
                    datetime,
                    latency
                FROM
                    @downtimes_temp
            )
        ) AS subquery
    ORDER BY
        datetime ASC;

--  #3
--  List of downtimes
    SELECT
        *
    FROM
        @downtimes_final_temp AS downtimes
    WHERE
        downtimes.count>=CONVERT(
            INT,
            dbo.GetConnectionOption(
                @connection_id,
                'downtime_ping_count'
            )
        );

--  #4
--  List of high latency states
    SELECT
        *
    FROM
        @latency_final_temp as latency
    WHERE
        latency.count>=CONVERT(
            INT,
            dbo.GetConnectionOption(
                @connection_id,
                'downtime_ping_count'
            )
        );

--  End consistent state
    COMMIT TRANSACTION get_detail;

GO