5 Replies Latest reply on Feb 20, 2017 9:24 AM by rajasekar

    Custom report only current events

    mstraughan

      Hello, is there a way to report only the current events a single time and the event is removed after it clears but only lists the events once? currently if i look at my events report page, alerts show for each time the node is polled; im looking for a way to only show the current events a single time to easily keep track of new alerts.

        • Re: Custom report only current events
          zackm

          For Nodes:

           

          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
          

           

          For Interfaces:

           

          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.

          • Re: Custom report only current events
            zackm

            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