SQL - Node down duration Report

makecrazy

Hi 

from mentioned SQL query i need last Down/UP Time event only with Down duration, can any one help me in this matter.

SELECT * FROM (
SELECT
Nodes.Status,
Nodes.Caption,
nodes.SP1,
--Nodes.NodeID,
StartTime.Message,
StartTime.EventTime AS DownEventTime ,
(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
ORDER BY EndTimeTable.EventTime
) AS UpEventTime,
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 nodes.IP_Address = '172.40.177.246'
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
--group by
ORDER BY Caption ASC, DownEventTime DESC

maciej.sandecki

Please see if this gives you the results you expect:

SELECT *
FROM (
	SELECT Nodes.STATUS
		,Nodes.Caption
		,StartTime.Message
		,StartTime.DownEventTime
		,(
			SELECT TOP 1 EventTime
			FROM Events AS EndTimeTable
			WHERE EndTimeTable.EventTime >= StartTime.DownEventTime
				AND EndTimeTable.EventType = 5
				AND EndTimeTable.NetObjectType = 'N'
				AND EndTimeTable.NetworkNode = StartTime.NetworkNode
				AND EventTime IS NOT NULL
			ORDER BY EndTimeTable.EventTime
			) AS UpEventTime
		,DATEDIFF(Mi, StartTime.DownEventTime, (
				SELECT TOP 1 EventTime
				FROM Events AS Endtime
				WHERE EndTime.EventTime > StartTime.DownEventTime
					AND EndTime.EventType = 5
					AND EndTime.NetObjectType = 'N'
					AND EndTime.NetworkNode = StartTime.NetworkNode
				ORDER BY EndTime.EventTime
				)) AS OutageDurationInMinutes
	FROM (
		SELECT e.NetworkNode
			,e.Message
			,MAX(e.EventTime) AS DownEventTime
		FROM Events AS e
		WHERE e.EventType = 1
		GROUP BY e.NetworkNode
			,e.Message
		) AS StartTime
	INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
	) AS UpTimeTable
WHERE outageDurationInMinutes IS NOT NULL
ORDER BY Caption ASC
	,DownEventTime DESC