SELECT MAX(EVENTTIME) AS "EVENT TIME", n.Caption AS 'DEVICE', MESSAGE, n.NODEID FROM EVENTS JOIN Nodes n ON n.NodeID = NetworkNode WHERE NETWORKNODE IS NOT NULL AND ACKNOWLEDGED = 0 GROUP BY n.CAPTION, MESSAGE, n.NODEID ORDER BY "EVENT TIME" DESC
SELECT MAX(e.EVENTTIME) AS "EVENT TIME", i.FullName AS 'INTERFACE', e.MESSAGE, i.INTERFACEID FROM EVENTS e JOIN Interfaces i on i.InterfaceID = e.NetObjectID WHERE NETWORKNODE IS NOT NULL AND ACKNOWLEDGED = 0 GROUP BY i.FullName, e.MESSAGE, i.INTERFACEID ORDER BY "EVENT TIME" DESC
TIP: If you hide the NodeID and InterfaceID fields when you create your report, your can make your nodes and interfaces in your reports "clickable".
Just a point, this is for events, not alerts. Alerts will only show details of what you actually alert on, events will show you everything.
All thumbs are fingers, but not all fingers are thumbs.
I need to filter some Node by separate group name how?????
Big Thanks to mrxinu for cleaning this up!
Here is a combined report if you don't want them separated:
select * from ( select max(e.eventtime) eventtime, n.nodeid, n.caption nodename, null interfaceid, null interfacename, e.message, e.acknowledged from nodes n join events e on n.nodeid = e.netobjectid and netobjecttype = 'N' group by e.eventtime, n.nodeid, n.caption, e.message, e.acknowledged union select max(e.eventtime) eventtime, n.nodeid, n.caption nodename, i.interfaceid interfaceid, i.caption interfacename, e.message, e.acknowledged from nodes n join events e on n.nodeid = e.netobjectid join interfaces i on n.nodeid = i.nodeid and netobjecttype = 'I' group by e.eventtime, n.nodeid, n.caption, i.interfaceid, i.caption, e.message, e.acknowledged ) tbl where tbl.acknowledged = 0
You are most welcome, sir.
thank you both very much and thanks for correcting me straight before also setting it to only show the last 5-10 minutes helped cutting down duplicates so i didn't get results every time a node was polled