0 Replies Latest reply on Dec 26, 2013 11:36 AM by chambooca

    Top SQL Queries

    chambooca

      I am a Newbie to Solarwinds and Thwack.  I have gotten quite a bit of help from all your posts and articles.  One of the things I thought would be neat is a collection of the top SQL queries that have helped solve a problem or generate a super useful report.  Maybe this could start off as a forum discussion and turn into a blog or discussion category.

       

      I can not claim this one so I am going to reference the creator and the link.  This one was from Mitul Darji

      in post http://thwack.solarwinds.com/ideas/1253

      ----------------------------------------------------------------------------------------------------------------


      SELECT DATEDIFF(d, T1.DownTime, CURRENT_TIMESTAMP) AS DaysDown, Nodes.Caption,  Nodes.IP_Address, T1.DownTime,  Nodes.Site, Nodes.Location

      FROM
      (SELECT
        Max(EventTime) AS DownTime, NetObjectID, NetworkNode

      FROM Events

      WHERE
        (EventType = 1) AND (NetObjectType ='N')

      GROUP BY NetObjectID, NetworkNode
      ) AS T1 INNER JOIN Nodes ON T1.NetworkNode = Nodes.NodeID

      WHERE
        (Nodes.Status = '2' OR Nodes.Status = '0')

      ORDER BY DaysDown DESC, Nodes.Caption ASC,  T1.DownTime DESC

       

      *****Note****
      You may need to remove Nodes.Site because that is a custom property.

       

      ----------------------------------------------------------------------------------------------------------------

      I used it to help me find some nodes that had been 'down' for quite some time and the OSOwner had neglected to update us on.