Report Writer Advanced SQL Help

jedski

Hi need some assistance on the script on the report writer.

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,

    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 DurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
(Nodes.caption like 'XXX%')
ORDER BY StartTime.EventTime DESC

please advise on how we can customize this a report where the currently down nodes are displayed and not the nodes that have been recently up.

Thanks,
Jed

Yann

Hi,

where the currently down nodesare displayed

What about the following statement in the WHERE condition:

AND Nodes.Status = 2