Geek Answers Handbook

Summing Upptime Historical Data

I am finishing my first asp.net web application and I am facing a difficult problem. The web application is designed to test network devices in different places throughout the country and record response times. The Windows service regularly checks these devices, usually every 1-10 minutes. The results of each check are then written to a SQL Server table with this construct. (ResponseTime is NULL when the device is turned off.)

CREATE TABLE [dbo].[DeviceStatuses] ( [DeviceStatusID] INT IDENTITY (1, 1) NOT NULL, [DeviceID] INT NOT NULL, [StatusTime] DATETIME NULL, [ResponseTime] INT NULL, CONSTRAINT [PK_DeviceStatuses] PRIMARY KEY CLUSTERED ([DeviceStatusID] ASC), CONSTRAINT [FK_DeviceStatuses_Devices] FOREIGN KEY ([DeviceID]) REFERENCES [dbo].[Devices] ([DeviceID]) );

The service has been running for several months with a minimum number of devices, and the table has about 500,000 rows. A customer would like to access a 3-month downtime summary for each device. Sort of:

Downtime:
12/12/2012 15:20 - 15:42

12/20/2012 1:00 AM - 9:00 AM

As far as I understand, I need to get a StatusTime for the beginning and end of each NULL ResponseTimes block, for a specific DeviceID, of course. I did a few searches on Google and StackOverflow, but didn't find anything similar to what I'm trying to do. (Perhaps I am not using the correct search terms.) My brother, a much more experienced programmer, suggested I use CURSOR in SQL Server, although he admitted that CURSOR's performance is terrible, and this should be a scheduled task. Any recommendations?

+4
source share
1 answer
 declare @DeviceStatuses table( [DeviceStatusID] INT IDENTITY (1, 1) NOT NULL, [DeviceID] INT NOT NULL, [StatusTime] DATETIME NULL, [ResponseTime] INT NULL) Insert into @DeviceStatuses([DeviceID],[StatusTime],[ResponseTime]) Values (1,'20120101 10:10',2),(1,'20120101 10:12',NULL),(1,'20120101 10:14',2), (1,'20120102 10:10',2),(1,'20120102 10:12',NULL),(1,'20120102 10:14',2), (2,'20120101 10:10',2),(2,'20120101 10:12',NULL),(2,'20120101 10:14',2), (2,'20120101 10:19',2),(2,'20120101 10:20',NULL),(2,'20120101 10:21',NULL),(2,'20120101 10:22',2), (2,'20120102 10:10',2),(2,'20120102 10:12',NULL),(2,'20120102 10:14',2); Select [DeviceID],MIN([StatusTime]) as StartDown,MAX([StatusTime]) as EndDown from ( Select [DeviceID],[StatusTime] ,(Select MAX([StatusTime]) from @DeviceStatuses s2 where s2.DeviceID=s1.DeviceID and s2.StatusTime<s1.StatusTime and s2.ResponseTime is not null) as gr from @DeviceStatuses s1 where s1.ResponseTime is null )a Group by [DeviceID],gr order by [DeviceID],gr
+1
source

More articles:


All Articles