Report SQL End Time from eventime

Hi There

I need some help with a SQL query. Below works well but I need to be able to display the starttime, endtime and outage duration..

Since the endtime and startime are from the same field, how I do a seperate query where all fields mentioned above appear on the report?

What I would like to see:

StartTime | Endtime | Nodes.Caption | Outage Durantion

SELECT
    StartTime.EventTime,
    Nodes.Caption,
    Nodes.NodeID,
    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 (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')
ORDER BY StartTime.EventTime DESC

Find more posts tagged with

report_sql_events_outage

Accepted answers

All comments

danrog

Taking the code you already had in place and moving it around a bit (and dropping the datediff) I came up with this. Certainly not pretty but it works.

SELECT 
    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 EventEndTime,
    Nodes.Caption, 
    Nodes.NodeID,
    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 (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N')
ORDER BY StartTime.EventTime DESC

ktucker

Hi there

Thanks for your quick help - This is exactly what I required.

Have you done any work on time zones? The Orion package doesn't allow for this and I am sure with a custom field UTC offset hours/time, that a report can be run in local site times. I manage 80 sites worldwide and local time on reporting would be a great help.

Thanks
Ken