How can I determine down time from logs in database table

Question

I have a database table which holds error logs reported from an application.

If certain errors occur, the application requires human intervention before becoming active again.

I need to sort through the logs and determine the total amount of time that has accrued between every pair of events.

So when the app goes into ERROR state where intervention is required at a certain time, I need to find the elapsed time to the next error log where the app was restarted.

Then I need to the sum of the total elapsed time between every pair of events.

The table looks like this:

ErrorID |  ErrorMessage | ErrorDateTime
---------------------------------------------
 20     | ex. msg 1     | 2013-09-01 00:10:10 
 21     | ex. msg 2     | 2013-09-01 00:10:15   
 22     | ex. msg 3     | 2013-09-01 00:10:20 
 23     | ERROR         | 2013-09-01 00:10:25
 24     | ex. msg 4     | 2013-09-01 00:10:30 
 25     | ex. msg 5     | 2013-09-01 00:10:35       
 26     | ex. msg 6     | 2013-09-01 00:10:37   
 27     | App Restarted | 2013-09-01 00:11:30 
 28     | ex. msg 7     | 2013-09-01 00:11:35 
 29     | ex. msg 8     | 2013-09-01 00:11:40   
 30     | ex. msg 9     | 2013-09-01 00:11:43 
 31     | ERROR         | 2013-09-01 00:11:45
 32     | ex. msg 10    | 2013-09-01 00:12:10 
 33     | ex. msg 11    | 2013-09-01 00:12:20       
 34     | ex. msg 12     | 2013-09-01 00:12:22   
 35     | App Restarted | 2013-09-01 00:13:30        

So basically I need to find the difference between the timestamps of every ERROR and the subsequent App Restarted log message.

Then get the sum of all of these durations

Can anyone point me in the right direction?

Answer 1

;WITH x AS
(
  SELECT ErrorID, ErrorMessage, ErrorDateTime,
    rn = ROW_NUMBER() OVER (ORDER BY ErrorDateTime, ErrorID)
  FROM dbo.YourLogTable
  WHERE ErrorMessage IN ('ERROR', 'App Restarted')
)
SELECT
  y.ErrorID, 
  x.ErrorID, 
  [Back_Up] = y.ErrorDateTime, 
  SecondsDown = DATEDIFF(SECOND, y.ErrorDateTime, x.ErrorDateTime)
FROM x
LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
WHERE x.ErrorMessage = 'App Restarted';

That gives you each downtime duration. I'm not sure what value the SUM has - over the lifetime of the app? Limited to a certain time frame? Something else? But you can get it this way:

;WITH x AS
(
  SELECT ErrorID, ErrorMessage, ErrorDateTime,
    rn = ROW_NUMBER() OVER (ORDER BY ErrorDateTime)
  FROM dbo.YourLogTable
  WHERE ErrorMessage IN ('ERROR', 'App Restarted')
)
SELECT
   TotalDowntime = SUM(DATEDIFF(SECOND, y.ErrorDateTime, x.ErrorDateTime))
FROM x
LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
WHERE x.ErrorMessage = 'App Restarted';

Answer 2

The following query gets the restart time for each error:

select l.*,
       (select top 1 ErrorDateTime
        from logs l2
        where l2.ErrorId > l.ErrorId and
              l2.ErrorMessage = 'App Restarted'
        order by l2.ErrorId
       ) as RestartTime
from logs l
where l.ErrorMessage = 'ERROR';

To get the sum requires summing times. Here is the sum in seconds:

with errors as (
    select l.*,
           (select top 1 ErrorDateTime
            from logs l2
            where l2.ErrorId > l.ErrorId and
                  l2.ErrorMessage = 'App Restarted'
            order by l2.ErrorId
           ) as RestartTime
    from logs l
    where l.ErrorMessage = 'ERROR'
   )
select sum(datediff(second, ErrorDateTime, RestartTime)) as SecondsDown
from errors;