Add Minutes to the outage...

FormerMember

Hi....Team...

I was  trying to add outage duration in minutes to my report for last 30 days outage...however went unsuccessful....appreciate if you can help me ....

Below is my SQL for the report....please advise what i need to add...

Nodes.OfficeName AS OfficeName,

Nodes.City AS City,

Nodes.IP_Address AS IP_Address,

Events_EventTypes.Name AS Event_Type_Name

FROM

Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

WHERE

( EventTime BETWEEN 42034 AND 42061 )

AND 

(

  (Nodes.NetworkPlatform = 'British Telecom') AND

  (

  (Events_EventTypes.Name = 'Node Down') OR

  (Events_EventTypes.Name = 'Node Up'))

)

) As r ORDER BY 2 ASC, 3 DESC

jeremyxmentzell

My SQL is pretty bad - but I'm trying... Here's what I'm using that sounds similar to what you're trying to accomplish though our properties might be skewed a bit.

"SELECT

    StartTime.EventTime,

    Nodes.Caption,

    Nodes.Location,

    StartTime.Message,

    DATEDIFF(Mi, StartTime.EventTime,

    (SELECT TOP 1

        EventTime

        FROM Events AS Endtime

        WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5

            AND EndTime.NetObjectType = 'N'

            AND EndTime.NetworkNode = StartTime.NetworkNode

        ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

WHERE

(Nodes.Admin_Owner = 'NOC')

AND

(StartTime.EventType = 1)

AND

(StartTime.NetObjectType = 'N')

AND

eventtime between dateadd(month, -1, getdate()) and getdate()

ORDER BY Nodes.Caption ASC"

I could probably export and put on content exchange if that's easier? Lemme know - hope this helps!