Down Node cleanup report

Hi,

please try the query below and let me know if you'll need help with some further tweaks.

SELECT
    N.Caption as NodeName
    , N.Status
    , E.EventTime
    , E.Message
    , DATEDIFF(DAY, E.EventTime, GETDATE()) AS DaysDown
FROM Nodes N
INNER JOIN (
    SELECT
        NetworkNode
        , EventTime
        , Message
        , RANK() OVER (PARTITION BY NetworkNode ORDER BY EventTime DESC) AS RankedColumn
    FROM Events
    WHERE EventType = 1
) E ON E.NetworkNode = N.NodeID
WHERE E.RankedColumn = 1 AND N.Status = 2

It returns what all the joins that I have tried returns... everything that has data in the Events... but not all the down devices. Since the program prunes the Events at 30 days... there is no info in the events table for any device that has been down for more than 30 days. I have handled that in mine, but I realize making multiple, nested SELECTs is not the most efficient design. I am by no means a DBA and have rather rudimentary knowledge of SQL as a language. I was wondering if there was a way to make sure that all the down devices end up in the report (replacing NULL values with anything, really, in mine I put 0) without having to do the nested SELECTs.

I would really love to both see this happen and understand how to do such a thing in the future. 

I truly appreciate the managing to get the message in there. Thanks for the insight and any additional insight you may be able to provide.

Looks like my reply I sent on Friday is gone so here it's again. This should give better results

SELECT

    N.Caption as NodeName

    , N.Status

    , CASE ISNULL(E.EventTime, 0)

            WHEN 0 THEN ''

            ELSE CONVERT(NVARCHAR(MAX), E.EventTime, 121)

        END AS EventTime

    , ISNULL(E.Message, '') AS Message

    , CASE ISNULL(E.EventTime, 0)

            WHEN 0 THEN '30+'

            ELSE CAST(DATEDIFF(DAY, E.EventTime, GETDATE()) AS NVARCHAR(MAX))

        END AS DaysDown

FROM Nodes N

LEFT OUTER JOIN (

    SELECT

        NetworkNode

        , EventTime

        , Message

        , RANK() OVER (PARTITION BY NetworkNode ORDER BY EventTime DESC) AS RankedColumn

    FROM Events

    WHERE EventType = 1

) E ON E.NetworkNode = N.NodeID

WHERE (E.RankedColumn IS NULL OR E.RankedColumn = 1) AND N.Status = 2

With a few modifications, that was perfect.

So, some modifications to the code and I came up with a filter that can be added to a nodes list on a view:

(SELECT CASE ISNULL(E.EventTime, 0) WHEN 0 THEN DATEDIFF(DAY, LastEvent.EventTime, GETDATE()) ELSE DATEDIFF(DAY, E.EventTime, GETDATE()) END AS DaysDown FROM Nodes N LEFT OUTER JOIN (SELECT NetworkNode, EventTime, RANK() OVER (PARTITION BY NetworkNode ORDER BY EventTime DESC) AS RankedColumn FROM Events WHERE EventType = 1) E ON E.NetworkNode = N.NodeID,(SELECT TOP (1) EventTime FROM Events ORDER BY EventTime ) LastEvent WHERE (E.RankedColumn IS NULL OR E.RankedColumn = 1) AND N.Status = 2 and N.NodeID = Nodes.NodeID) >= 30

This will work for any amount of time up to the last event time in the database. Since Orion cleans up the Events table based on a period of time defined in the settings, I set it up to reflect the oldest event timestamp.