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