Calculating Total Outage Time

Question

Calculating Total Outage Time

therron134

SSC Rookie

Points: 38
More actions
June 17, 2014 at 1:42 pm

#396924

Hi
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
Hope that explains it but, in summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc. Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

sgmunson SSC Guru Points: 110651 More actions · Answer 1

I had to do something similar for tickets in a problem management system, but each ticket constituted a separate outage. The very fact that your setup is allowing multiple overlapping tickets for the same node suggests that accurate tracking of outages isn't exactly a priority. While you can take such things into account, it's going to be challenging. Think about it this way. For each ticket for each node, do you have a single start / end time pair, or multiple pairs per ticket? If not, it's going to be a lot more difficult, but assuming that you do, then you can create two records in a temp table for each ticket for each node. Each such record will have the Node, Ticket#, RecordType (Start or End), and DateTimeValue. Then you sort those records in order by Node and DateTiimeValue using a ROW_NUMBER() function, thus adding a field you'll call RN. Self-Join this table to itself on T1.RN = T2.RN - 1. You can then filter out second start times or first end times where there are more than one. Give it a shot and see what you come up with.

therron134 (6/17/2014)
Hi
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
Hope that explains it but, in summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc. Thanks.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

therron134 SSC Rookie Points: 38 More actions · Answer 2

Hi Steve, thanks for the prompt reply on this one; I'll try out your suggestion. I should have previously said that the multiple tickets for a node relate to separate elements of the node and are not duplicates, etc.

TJH

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

If you provide some sample data in the form of DDL and DML statements that we can run, you will definitely get a working answer.

See the link in my signature from Jeff Moden about how to post data to get help.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);