1

what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime.

enter image description here

I want to get a year to date for total downtime for any particular site (website). I'm using a sitemonitor to register downtime with a timestamp called datetime. It's the start of the downtime and the following up is when the downtime ended.

There can be multiple down times. The table is an example of what the data looks like.

The downtime may not be in 15 second increments. It could be 10 second to 45 seconds. It would be the difference between the downtime and the following uptime in the table. Is this even possible to be done?

0

3 Answers 3

2

Would this work. I just had the query give the totals for each year. You could easily filter by date if you wanted to restrict it for just this year, or a specific year you are looking at.

Also, this assumes the final record in the table is an up time. If you end with the site being down, then that last downtime isn't included. You should get your site back up before worrying about calculating downtime :).

WITH TAB
AS
(
    SELECT * ,
           Row_Number() OVER (Partition BY Site ORDER BY DateTime) AS RowNum
    FROM @Table
)
SELECT UT.Site , DATEPART(Year, UT.DateTime) as Year,
       Sum(DateDiff(second, DT.DateTime, UT.DateTime)) as TotalDownTimeSeconds
FROM       Tab AS DT
INNER JOIN Tab AS UT
    ON DT.SiteStatus = 'Down'
    AND UT.RowNum = DT.RowNum + 1
    AND UT.Site = DT.Site
WHERE DT.Site = 'ZooToys'
GROUP BY UT.Site, DATEPART(Year, UT.DateTime);

If you want to get the down time for the past 60 days, add to the where clause:

UT.DateTime > DateAdd(day, -60, getdate())
0
1

Here is a very simple solution. There is one, very important, assumption here. Which is, that your polling interval is static and never misses a beat. This is a limiting assumption which is why I'm calling this the simple solution.

Code:

DECLARE @SiteStats TABLE ([site] NVARCHAR(50), sitestatus NVARCHAR(4), [datetime] datetime)

INSERT INTO @SiteStats ([site], sitestatus, [datetime])
VALUES
('ZooToys','Up','2010-10-21 13:32:00'),
('ZooToys','Up','2010-10-21 13:31:45'),
('ZooToys','Up','2010-10-21 13:31:30'),
('ZooToys','Down','2010-10-21 13:31:15'),
('ZooToys','Down','2010-10-21 13:31:00'),
('ZooToys','Up','2010-10-21 13:30:45'),
('ZooToys','Up','2010-10-21 13:30:30'),
('ZooToys','Up','2010-10-21 13:30:15'),
('JurassicZooToys','Up','2010-10-21 13:32:00'),
('JurassicZooToys','Down','2010-10-21 13:31:45'),
('JurassicZooToys','Down','2010-10-21 13:31:30'),
('JurassicZooToys','Down','2010-10-21 13:31:15'),
('JurassicZooToys','Up','2010-10-21 13:31:00'),
('JurassicZooToys','Up','2010-10-21 13:30:45'),
('JurassicZooToys','Down','2010-10-21 13:30:30'),
('JurassicZooToys','Up','2010-10-21 13:30:15')

--Weight your results, 15 secs per down.
SELECT [site], COUNT(*) * 15 [DownTime_Secs]
FROM @SiteStats
WHERE sitestatus LIKE 'Down'
GROUP BY [site]

Table results:

+-----------------+---------------+
|      site       | DownTime_Secs |
+-----------------+---------------+
| JurassicZooToys |            60 |
| ZooToys         |            30 |
+-----------------+---------------+
0
0

--First Attempt

SELECT Site, SiteStatus, datetime, timestarteddown = 
(
    SELECT MAX(datetime) 
    FROM [table] table1 
    WHERE table1.datetime < table.datetime
)
    INTO #Downtimes
FROM table
WHERE SiteStatus='Down'

--This gets you your downtimes

SELECT SUM(DATEDIFF(seconds,timestarteddown,datetime)
FROM #Downtimes

-- See if this works for you

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.