Currently Down Nodes in SQL

FormerMember

SELECT Events.EventTime AS Event_Time,
Nodes.Caption AS NodeName

FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)


WHERE
(Nodes.Status = '2')  AND
(Nodes.Caption LIKE '%BPI%')
AND

(
  (Events.EventType = 5000) OR
  (Events.EventType = 1)

)


ORDER BY 1 DESC

 

Is there any way that we can only see 1 event time per device in this code? Thanks!

jp

Added max(Events.EventTime) & grouped based on Node Caption - tested - worked!

SELECT max(Events.EventTime) AS Event_Time, Nodes.Caption AS NodeName
FROM Nodes
INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

WHERE
(Nodes.Status = '2')  AND
(Nodes.Caption LIKE '%BPI%')
AND

(
  (Events.EventType = 5000) OR
  (Events.EventType = 1)
)

group by Nodes.Caption
ORDER BY 1 DESC