Query nodes down with the most recent note and last time seen up

This query will pull from both the NetObjectDownTime table, and the Events table (default to NetObjectDownTime via the ISNULL function, fallback to Events) to pull the last time node was seen up along with the most recent note.

SELECT
     n.Caption
    ,n.DetailsUrl AS [_LinkFor_Caption]
    ,n.Status
    ,CONCAT('/NetPerfMon/images/Small-', n.StatusLED) AS [_IconFor_Caption]
    ,nn2.Note AS [Most Recent Note]
    ,TOLOCAL(ISNULL(d.DateTimeUntil, e.EventTime)) AS [Last Time Reported UP (EST)]
FROM Orion.Nodes AS n
LEFT JOIN ( 
SELECT 
	 d.NodeId
	,MAX(d.DateTimeUntil) AS [DateTimeUntil]
FROM Orion.NetObjectDowntime AS d
WHERE d.EntityType = 'Orion.Nodes' AND d.DateTimeUntil IS NOT NULL AND d.State = 1
GROUP BY d.NodeId ) AS d ON d.NodeId = n.NodeID
LEFT JOIN ( 
SELECT 
	 e.NetObjectID
	,MAX(e.EventTime) AS [EventTime]
FROM Orion.Events AS e
WHERE e.NetObjectType = 'N' AND e.EventType = 1
GROUP BY e.NetObjectID ) AS e ON e.NetObjectID = n.NodeID
LEFT JOIN (
    SELECT
     no.NodeID
    ,MAX(no.NodeNoteID) AS [NodeNoteID]
    ,MAX(no.TimeStamp) AS [TimeStamp]
    FROM Orion.NodeNotes AS no
    GROUP BY no.NodeID
) AS nn1 ON n.NodeID = nn1.NodeID
LEFT JOIN Orion.NodeNotes AS nn2 ON nn2.NodeID = n.NodeID AND nn2.NodeNoteID = nn1.NodeNoteID
WHERE n.Status = 2

Anonymous