1 Reply Latest reply on Jan 22, 2015 4:42 PM by mharvey

    Help needed to sort actively used nodes

    azamanov

      Hello everybody

      I have inherited a mess of old/new nodes which needs to be controlled a little bit. I want to have a report that finds a node haven't been reached by the poller for the last 1 month lets say. I am also trying to see if I have a node with 2 or more interfaces and one of them is down but the system is still responding vs completely unresponsive system

      Hope my questions make sense

      Any help will be greatly apreciated

      Andrey

        • Re: Help needed to sort actively used nodes
          mharvey

          For the first part, I've used the query from the alert when a device hasn't been polled in the last 5 tries just expanding it to a month rather than attempts. 

           

          SELECT DISTINCT Nodes.NodeID AS NetObjectID, Nodes.Caption AS Name

          FROM Nodes

          WHERE 

          (

            (DATEDIFF(MM,  Nodes.LastSync, getdate())/Nodes.PollInterval > 1) AND

            (

             NOT (Nodes.Status = '9') AND

             NOT (Nodes.Status = '11'))

          )

           

          This should give you anything that hasn't been successfully polled in over a month.

           

          This should give you any node that is up, with more than one interface, and one or more are down.

           

          SELECT DISTINCT N.NodeID AS NetObjectID, N.Caption AS Name, Count(I.InterfaceID) as Interface_Count

          FROM Nodes n

          Join Interfaces I

          on n.NodeID = I.NodeID

           

           

          Where I.OperStatus = 2 AND N.Status = 1

           

           

          Group by N.NodeID, N.Caption

           

           

          HAVING Count(I.InterfaceID) >= 2