0 Replies Latest reply on Oct 15, 2015 8:31 AM by johnny ringo

    Select Last Node Up Event From Up Nodes- NEED SQL HELP

    johnny ringo

      I am trying to create a report showing nodes that are using our test subnet, and have been online for 3 days or more.  Our security team wants to know when these devices have been on the subnet for a few days because if they do, they would not have been getting security updates.  However I am having a hard time selecting distinct events for each node.  I am getting the results I want, except I am getting multiple records for each node, except for the latest Node Up event, event ID 5.  Does anyone have any suggestions?

       

      SELECT top 100

      Nodes.City,
      Nodes.Caption,
      Nodes.IP_Address,


      (SELECT TOP 1 EventTime FROM Events AS Start
      WHERE Start.EventType = 5 AND Start.NetObjectType = 'N'
      ORDER BY Start.EventTime)AS DateUP,

       

      DATEDIFF(Day, StartTime.EventTime,GETDATE()) AS TimeOnlineInDays


      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

       

      WHERE

      (DATEDIFF(Day, StartTime.EventTime,GETDATE())>=3) AND

      (StartTime.EventType = 5) AND (Nodes.IP_Address LIKE '%.251' OR Nodes.IP_Address LIKE '%.252'
      OR Nodes.IP_Address LIKE '%.253' OR Nodes.IP_Address LIKE '%.254') AND Nodes.City NOT Like 'PROD%'

      AND Nodes.IP_Address LIKE '%PROD.SUBNET%' AND Nodes.Status = 1

      ORDER BY 5 DESC