5 Replies Latest reply on Apr 19, 2012 3:39 PM by mockmyberet

    Down Node cleanup report

    mockmyberet

      I have two questions... first, how do I make this more efficient?

      select 
      n.caption as NodeName, 
      n.Status, 

      ISNULL((
      select 
      top 1 eventtime 
      from events 
      where events.NetworkNode=n.nodeid 
      and EventType = 1 
      order by eventtime desc
      ), 0) as EventTime,

      DATEDIFF(dd,(ISNULL((
      select 
      top 1 eventtime 
      from events 
      where events.NetworkNode=n.nodeid 
      and EventType = 1 
      order by eventtime desc
      ), 0)),GETDATE()) as DaysDown

      from Nodes n 
      where n.Status = 2
      order by EventTime

      Then, how do I also get the actual message from that EventTime...

        • Re: Down Node cleanup report
          Bedrich.Michalek

          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

            • Re: Down Node cleanup report
              mockmyberet

              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.

                • Re: Down Node cleanup report
                  Bedrich.Michalek

                  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

                    • Re: Down Node cleanup report
                      mockmyberet

                      With a few modifications, that was perfect.

                      • Re: Down Node cleanup report
                        mockmyberet

                        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.

                        1 of 1 people found this helpful