如何从数据库表中的日志确定停机时间

Question

我有一个数据库表，用于保存应用程序报告的错误日志。

如果发生某些错误，应用程序需要人工干预才能再次变为活动状态。

我需要浏览这些日志，并确定每对事件之间累计的总时间。当应用程序进入需要干预的错误状态并在某个时间点出现时，我需要找到到下一个应用程序重新启动时的错误日志的经过时间。

然后，我需要计算每对事件之间总经过时间的总和。

该表如下：

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        

基本上，我需要找到每个错误和随后的应用程序重启日志消息之间时间戳的差异。

然后得到所有这些持续时间的总和。

有人可以指点我正确方向吗？

- stephen776

---

我不久前也问过类似的问题。你可以在https://dev59.com/AXbZa4cB1Zd3GeqPBAvJ看到。 - user2711965

如果出现两个错误（例如一个在ErrorID = 23处，另一个在ErrorID = 24处），该怎么办？ - Aaron Bertrand

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';

这将为您提供每个停机时间的持续时间。我不确定SUM的价值是什么 - 是在应用程序的整个生命周期内吗？还是限于某一个时间范围内？还是其他原因？但是可以通过以下方式获取它：

;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';

- Aaron Bertrand

---

好的回答，朋友。这个fiddle表明你的确是正确的，同时也进一步证明了我在Gordon上方所说的是准确的。如果Gordon只是改为升序ASC而不是降序DESC，并按ErrorDateTime排序，它也会返回正确的停机秒数。 - Mike Perrenoud

Answer 2

以下查询获取每个错误的重启时间：

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';

要获得总和需要对时间进行求和。以下是以秒为单位的总和：

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;

- Gordon Linoff

---

实际上，这是不正确的。这个fiddle显示第一条记录获取的是13:30时间而不是11:30时间。这种方法的另一个问题是你使用ErrorId作为标识符来判断一条记录是否比另一条记录晚。这种方法适用于所有记录都是按顺序插入并在同一线程上执行的情况。但更好的方法可能是使用ErrorDateTime。 - Mike Perrenoud

Answer 3

你也可以尝试这种方式...

;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';

- Sky