In a previous post, I had a system to only log the start and end of an outage condition. To report on the outages, we need to first look at the table where the exceptions get logged:
CREATE TABLE [dbo].[Exceptions](
[ExTime] [datetime] NULL CONSTRAINT [DF_Exceptions_ExTime]
DEFAULT (getdate()),
[Message] [varchar](8000) NULL,
[StackTrace] [varchar](8000) NULL
) ON [PRIMARY]And a sample of the data:
SELECT TOP 12 ExTime, Substring(Message,1,60) Message
FROM Exceptions
| ExTime | Message |
| 5/5/09 13:21 | Outage start of error, ErrorGuid: 595f890d-c337-49 |
| 5/5/09 13:28 | Outage end of error, ErrorGuid: 595f890d-c337-493f |
| 5/11/09 18:08 | dbo.processRequestAll|Thread was being aborted. |
| 5/12/09 14:13 | The actual number of records in insert did not match the exp |
| 5/15/09 13:00 | Data Feed FAILED at 5/15/2009 1:00:04 PM |
| 5/15/09 13:00 | Data Feed Error occurred at 5/15/2009 1:00:04 PM |
| 5/22/09 18:10 | dbo.saveXML|The DELETE statement conflicted |
| 5/26/09 11:14 | The actual number of records in insert did not match the exp |
| 6/3/09 6:39 | Outage start of error, ErrorGuid: c1c430f0-9a8f-4f |
| 6/3/09 6:41 | Outage end of error, ErrorGuid: c1c430f0-9a8f-4fca |
| 6/3/09 12:36 | Outage start of error, ErrorGuid: 06de0545-d152-44 |
| 6/3/09 12:39 | Outage end of error, ErrorGuid: 06de0545-d152-4438 |
Now, to do the reporting, we create a temp table with just the records we are interested in:
select ExTime,
case when charindex('start of',Message) > 0 then 'S'
else 'E' end a,
substring(Message,charindex('Guid',Message)+5,40) g
into #t
from Exceptions
where Message like 'Outage%'The first column (badly named “a”) is just a flag to say if it is a Start or End record. The second column (named “g”) is the Guid for the outage event.
Now create another temp table where the GUID is summarized:
select g, max(case when a = 'S' then ExTime end) startTime,
max(case when a = 'E' then ExTime end) endTime into #t2
from #t
group by g
having count(1) = 2Now we can report on this.
select startTime,
CASE DATEPART(weekday,startTime)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END,
round((datediff(ss,startTime,endTime)+0.0)/60,2) Minutes
from #t2 t
where not exists
( select 1
from #t2 tin
where t.startTime between tin.startTime and tin.endTime
and t.g <> tin.g)
and startTime > '05/13/09'
order by startTimeThis gives us the following report that shows us the start time of the outage and how many minutes it lasted:
| StartTime | Day | Minutes |
| 5/27/09 14:38 | Wednesday | 9.53 |
| 5/28/09 10:06 | Thursday | 9.68 |
| 5/28/09 13:21 | Thursday | 4.52 |
| 5/28/09 23:18 | Thursday | 9.64 |
| 5/30/09 13:21 | Saturday | 3.85 |