O cálculo do uptime, do downtime e da disponibilidade em T-SQL

Boa Noite Pessoal,

Olhando aqui a longa lista de emails vi uma newsletter bem interessante. No corpo dela havia um desafio em TSQL para calcular o tempo de uptime e downtime a partir de um registro de log. Achei o desafio bem interessante e pertinente. É muito comum em determinadas situações termos um arquivo com os vários momentos em que um sistema foi iniciado, parado e reestabelecido. Esses dados são importantes para efetuar o cálculo de indicadores importantes como o uptime, o downtime e a disponibilidade de uma solução. Normalmente tais indicadores são utilizados para negociação e contratação serviços, estipulação de SLAs, etc.

Para demonstrar um exemplo de como fazer esse cálculo a partir de uma lista de observações, vou utilizar uma situação hipotética de uma espécie de monitor de serviços. Esse monitor fará consultas esporádicas e anotará em uma lista o momento da consulta e o status do serviço (1 para Online e 0 para Offline). Em termos de script, segue a situação que descrevi:

— Cria uma tabela de Disponibilidade
CREATE TABLE LogDisponibilidade (DataRegistro SMALLDATETIME, OnLine BIT)

— Insere alguns registros de Log
INSERT INTO LogDisponibilidade VALUES (‘20100213 08:00’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100213 14:53’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100213 19:12’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100213 23:15’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100214 09:25’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100214 09:30’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100214 17:49’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100214 22:13’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100215 17:25’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100215 19:44’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100215 20:42’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100215 23:50’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100216 10:37’,1)
INSERT INTO LogDisponibilidade VALUES (‘20100216 17:00’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100216 21:43’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100216 22:59’,0)
INSERT INTO LogDisponibilidade VALUES (‘20100217 08:00’,1)

A lista pode ser exibida conforme a tabela abaixo:

Data da Coleta Status
13/02/2010 08:00:00 Online
13/02/2010 14:53:00 Online
13/02/2010 19:12:00 Online
13/02/2010 23:15:00 Offline
14/02/2010 09:25:00 Offline
14/02/2010 09:30:00 Online
14/02/2010 17:49:00 Offline
14/02/2010 22:13:00 Online
15/02/2010 17:25:00 Offline
15/02/2010 19:44:00 Online
15/02/2010 20:42:00 Offline
15/02/2010 23:50:00 Online
16/02/2010 10:37:00 Online
16/02/2010 17:00:00 Offline
16/02/2010 21:43:00 Offline
16/02/2010 22:59:00 Offline
17/02/2010 08:00:00 Online

Às 8h do dia 13 o serviço estava disponível e manteve-se assim até às 23:15 daquele dia. É verdade que houve duas coletas às 14:53 e 19:12, mas isso apenas constatou que o serviço continuava disponível. Como o serviço manteve-se de pé entre 08:00 até às 23:15, podemos confirmar a duração de 15:15 minutos de uptime. Às 23:15 do dia 13 houve uma queda no serviço que se manteve indisponível até às 09:30 do dia 14/12. Isso representa 10:15 minutos de downtime. Houve uma medição intermediária às 09:25 do dia 14, mas ela apenas constatou que o serviço continuava indisponível até aquele momento.

A visualização completa dos períodos de uptime e downtime poderia ser melhor visualizada da seguinte maneira:

Início Fim Status Duração (Min)
13/02/2010 08:00:00 13/02/2010 23:15:00 Online 915
13/02/2010 23:15:00 14/02/2010 09:30:00 Offline 615
14/02/2010 09:30:00 14/02/2010 17:49:00 Online 499
14/02/2010 17:49:00 14/02/2010 22:13:00 Offline 264
14/02/2010 22:13:00 15/02/2010 17:25:00 Online 1152
15/02/2010 17:25:00 15/02/2010 19:44:00 Offline 139
15/02/2010 19:44:00 15/02/2010 20:42:00 Online 58
15/02/2010 20:42:00 15/02/2010 23:50:00 Offline 188
15/02/2010 23:50:00 16/02/2010 17:00:00 Online 1030
16/02/2010 17:00:00 17/02/2010 08:00:00 Offline 900

Com essa visualização fica muito fácil calcular o tempo de uptime e de downtime. A tabela trouxe os dados de forma detalhada, mostrando todos os períodos de uptime e downtime, mas não seria difícil efetuar uma soma para chegar aos totais de 60:54 minutos e 35:06 minutos de uptime e downtime respectivamente. O problema é como chegar nesse resultado a partir dos registros da tabela de disponibilidade ? Vejamos o passo a passo de como fazer sem incorrer em uso de cursores, tabelas temporárias, loops, etc.

Tomando-se por base a lista dos períodos de uptime e downtime e a própria tabela "LogDisponibilidade", se for observado com atenção os primeiros registros da , é possível definir alguns comportamentos ocultos:

  • O "início" da primeira linha da lista corresponde a data do primeiro registro (marcação em verde)
  • O "início" de uma linha da lista (que não seja a primeira) será sempre igual ao "fim" da linha anterior (marcação em roxo)
  • O "fim" da última linha da lista corresponde a data do último registro (marcação em marrom)

Para ficar mais claro, mostro novamente a lista dos períodos de uptime e downtime com as marcações:

Início Fim Status Duração (Min)
13/02/2010 08:00:00 13/02/2010 23:15:00 Online 915
13/02/2010 23:15:00 14/02/2010 09:30:00 Offline 615
14/02/2010 09:30:00 14/02/2010 17:49:00 Online 499
14/02/2010 17:49:00 14/02/2010 22:13:00 Offline 264
14/02/2010 22:13:00 15/02/2010 17:25:00 Online 1152
15/02/2010 17:25:00 15/02/2010 19:44:00 Offline 139
15/02/2010 19:44:00 15/02/2010 20:42:00 Online 58
15/02/2010 20:42:00 15/02/2010 23:50:00 Offline 188
15/02/2010 23:50:00 16/02/2010 17:00:00 Online 1030
16/02/2010 17:00:00 17/02/2010 08:00:00 Offline 900

É importante lembrar dessas regras, pois, podem clarear várias das soluções possíveis.

Voltando à tabela "LogDisponibilidade", pode-se perceber que ela possui vários registros mostrando que em alguns períodos há observações semelhantes (o uptime é seguido de outro uptime e o downtime é seguido de outro downtime) e observações diferentes (o uptime é seguido de um downtime ou vice-versa). O que interessa mesmo são as observações que diferem, pois, é justamente na troca de uma observação de uptime para downtime que há definição de um intervalo seja de uptime ou de downtime. Para facilitar a explicação, irei me restringir aos seis primeiros registros da tabela "LogDisponibilidade" que são suficientes para visualizar essas alternâncias (criei uma view para facilitar o trabalho).

CREATE VIEW vTOP6 As SELECT TOP(6) DataRegistro, OnLine FROM LogDisponibilidade

DataRegistro OnLine
13/02/2010 08:00:00 1
13/02/2010 14:53:00 1
13/02/2010 19:12:00 1
13/02/2010 23:15:00 0
14/02/2010 09:25:00 0
14/02/2010 09:30:00 1

Do primeiro ao terceiro registro há uma continuação do uptime, ou seja, o serviço está de pé. O quarto registro mostra uma alternância, pois, parte-se de um período de uptime para um período de downtime. O período de downtime se mantém no quinto registro. No sexto registro há novamente uma alternância encerrando o downtime fixado entre o quarto e o quinto registro. Para cada data de registro, a primeira tentativa seria recuperar a primeira alternância subsequente. Isso pode ser feito com a consulta abaixo:

SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM vTOP6 As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, OnLine FROM vTOP6 As TOut

Inicio Fim OnLine
13/02/2010 08:00:00 13/02/2010 23:15:00 1
13/02/2010 14:53:00 13/02/2010 23:15:00 1
13/02/2010 19:12:00 13/02/2010 23:15:00 1
13/02/2010 23:15:00 14/02/2010 09:30:00 0
14/02/2010 09:25:00 14/02/2010 09:30:00 0
14/02/2010 09:30:00 NULL 1

A clásula TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine impõe que para cada data retornada, deve-se pegar as datas imediatamente superiores e que possua um status diferente. A data 13/02/2010 às 08:00 possui várias datas superiores. As datas 13/12/2010 às 14:53 e 19:12 são superiores, mas ambas mostram uma continuidade de uptime, pois, possuem o mesmo valor para a coluna OnLine. Para que a data seja realmente relevante é preciso que haja uma variação de status (por isso a diferença TOut.OnLine != TInt.OnLine). As datas 13/02/2010 às 23:15 e 14/02/2010 às 09:25 atendem essa condição, mas o interessante é pegar a menor delas e por isso a função MIN é utilizada. Assim conclui-se que às 23:15 do dia 13/02/2010 e 14/02/2010 houve mudança de status. A data de 14/02/2010 às 09:30 é a última data e por isso não há registro que atenda às condições impostas no predicado da subquery.

A coluna "Fim" possui dois registros duplicados (13/02/2010 às 23:15 e 14/02/2010 às 09:30). Isso ocorre, porque em períodos de continuidade, seja de uptime ou de downtime, a referência será sempre a próxima data imediatamente superior em que haja uma mudança de status. Se o "Fim" é conhecido, para identificar o "início" correto, basta procurar a menor data que se relacione ao "fim".

SELECT MIN(Inicio) As Inicio, Fim FROM (
    SELECT
        DataRegistro As Inicio, (
            SELECT MIN(DataRegistro) FROM vTOP6 As TInt
            WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
        As Fim, OnLine FROM vTOP6 As TOut) As Res
GROUP BY Fim
ORDER BY Inicio

Inicio Fim
13/02/2010 08:00:00 13/02/2010 23:15:00
13/02/2010 23:15:00 14/02/2010 09:30:00
14/02/2010 09:30:00 NULL

Essa consulta não tem nada novo. Ela apenas faz uma subquery na consulta anterior. Como a coluna "Fim" estava em repetição, ela mantém a coluna "Fim" e recupera a menor data possível para a coluna "Início" em relação à coluna "Fim". Com essa consulta, já é possível visualizar que há dois períodos. O primeiro período é de 08:00 do dia 13/02/2010 até 13/02/2010 às 23:15 e o segundo é de 23:15 do dia 13/02/2010 até às 09:30 do dia 14/02/2010. O terceiro registro apareceu porque 14/02/2010 às 09:30 é o último registro da view e como não possui data superior teve a coluna "Fim" com o valor NULL. De forma a corrigir esse detalhe e deixar a consulta menos poluída, a seguir mostro a CTE equivalente.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM vTOP6 As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, OnLine FROM vTOP6 As TOut),

Intervalos (Inicio, Fim) As (
    SELECT MIN(Inicio) As Inicio, Fim FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim)

SELECT Inicio, Fim FROM Intervalos
ORDER BY Inicio

Inicio Fim
13/02/2010 08:00:00 13/02/2010 23:15:00
13/02/2010 23:15:00 14/02/2010 09:30:00

Com essa última CTE, os períodos estão corretamente definidos. O problema é que não está contemplada a informação de disponibilidade sobre o período, ou seja, se naquele período o serviço estava ou não online. Embora a primeira CTE (Res) tenha a coluna de OnLine, o uso da função MIN na segunda CTE (Intervalos) acabou deixando essa coluna de fora. Ela até pode ser contemplada e participar da cláusula GROUP BY, mas é preciso lembrar que colunas do tipo BIT não podem participar de funções de agregação e por isso ela precisará ser convertida.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM vTOP6 As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM vTOP6 As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine)

SELECT Inicio, Fim, OnLine FROM Intervalos
ORDER BY Inicio

Inicio Fim OnLine
13/02/2010 08:00:00 13/02/2010 23:15:00 1
13/02/2010 23:15:00 14/02/2010 09:30:00 0

Agora que a CTE está formada, basta retirar a referência à view vTOP6 e utilizar a própria tabela "LogDisponibilidade". Acrescentei uma CTE a mais para formatar a coluna OnLine e renomeá-la.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM LogDisponibilidade As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM LogDisponibilidade As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine)

SELECT Inicio, Fim,
    CASE OnLine WHEN 1 THEN ‘OnLine’ ELSE ‘OffLine’ END As Status
FROM Intervalos
ORDER BY Inicio

Inicio Fim Status
13/02/2010 08:00:00 13/02/2010 23:15:00 OnLine
13/02/2010 23:15:00 14/02/2010 09:30:00 OffLine
2010-02-14 09:30:00 2010-02-14 17:49:00 OnLine
2010-02-14 17:49:00 2010-02-14 22:13:00 OffLine
2010-02-14 22:13:00 2010-02-15 17:25:00 OnLine
2010-02-15 17:25:00 2010-02-15 19:44:00 OffLine
2010-02-15 19:44:00 2010-02-15 20:42:00 OnLine
2010-02-15 20:42:00 2010-02-15 23:50:00 OffLine
2010-02-15 23:50:00 2010-02-16 17:00:00 OnLine
2010-02-16 17:00:00 2010-02-17 08:00:00 OffLine

Se os intervalos estão corretamente definidos, não há muita dificuldade em calcular a diferença entre as datas de início e de fim para obter a duração dos mesmos.

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM LogDisponibilidade As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM LogDisponibilidade As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine),

IntervalosDuracao (Inicio, Fim, Status, Duracao) As (
    SELECT Inicio, Fim,
        CASE OnLine WHEN 1 THEN ‘OnLine’ ELSE ‘OffLine’ END,
        DATEDIFF(MI,Inicio, Fim)
    FROM Intervalos)

SELECT Inicio, Fim, Status, Duracao
FROM IntervalosDuracao
ORDER BY Inicio

Início Fim Status Duração (Min)
13/02/2010 08:00:00 13/02/2010 23:15:00 Online 915
13/02/2010 23:15:00 14/02/2010 09:30:00 Offline 615
14/02/2010 09:30:00 14/02/2010 17:49:00 Online 499
14/02/2010 17:49:00 14/02/2010 22:13:00 Offline 264
14/02/2010 22:13:00 15/02/2010 17:25:00 Online 1152
15/02/2010 17:25:00 15/02/2010 19:44:00 Offline 139
15/02/2010 19:44:00 15/02/2010 20:42:00 Online 58
15/02/2010 20:42:00 15/02/2010 23:50:00 Offline 188
15/02/2010 23:50:00 16/02/2010 17:00:00 Online 1030
16/02/2010 17:00:00 17/02/2010 08:00:00 Offline 900

Agora que os intervalos foram corretamente delimitados e as durações devidamente calculadas, não é difícil calcular o tempo total de uptime, downtime e disponibilidade. A fórmula da disponibilidade é calculada dividindo-se o tempo total que um serviço esteja no ar (uptime) pelo tempo total (uptime + downtime) desconsiderando-se as paradas planejadas (que não foram tratadas nesse exemplo).

;WITH Res (Inicio, Fim, OnLine) As (
SELECT
    DataRegistro As Inicio, (
        SELECT MIN(DataRegistro) FROM LogDisponibilidade As TInt
        WHERE TInt.DataRegistro > TOut.DataRegistro AND TOut.OnLine != TInt.OnLine)
    As Fim, CAST(OnLine As TINYINT) FROM LogDisponibilidade As TOut),

Intervalos (Inicio, Fim, OnLine) As (
    SELECT MIN(Inicio) As Inicio, Fim, OnLine FROM Res
    WHERE Fim IS NOT NULL
    GROUP BY Fim, OnLine),

IntervalosDuracao (Inicio, Fim, Status, Duracao) As (
    SELECT Inicio, Fim,
        CASE OnLine WHEN 1 THEN ‘OnLine’ ELSE ‘OffLine’ END,
        DATEDIFF(MI,Inicio, Fim)
    FROM Intervalos),

Indicadores (Uptime, Downtime) As (
    SELECT
        (SELECT CAST(SUM(Duracao) As Decimal)
        FROM IntervalosDuracao WHERE Status = ‘OnLine’),
        (SELECT CAST(SUM(Duracao) As Decimal)
        FROM IntervalosDuracao WHERE Status = ‘OffLine’))

SELECT
    Uptime, Downtime, Uptime / (Uptime + Downtime) As Disponibilidade
FROM Indicadores

Uptime Downtime Disponibilidade
3654 2106 0.634375

Acho que com isso é finalizado o cálculo da disponibilidade. O desafio original não é exatamente igual a esse, mas eu diria que o correto entedimento do cálculo apresentado até então é o caminho das pedras para resolvê-lo. Há maiores detalhes no link abaixo:

http://beyondrelational.com/blogs/tc/archive/2010/02/08/tsql-challenge-23-calculate-the-uptime-and-downtime-of-a-system-by-reading-the-data-from-an-audit-log.aspx

Há um incentivo para quem conseguir resolver o desafio. Os participantes estarão disputando uma licença da ferramenta Red Gate SQL Backup. Além da ferramenta, há ainda alguns bonés, camisetas, etc. Os interessados devem apressar-se, pois, o desafio termina agora no dia 22/02/2010.

[ ]s,

Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s