2 Replies Latest reply on May 25, 2011 10:44 AM by bfreking

    Looking for a report (Advanced SQL) that combines Down Nodes and Down Interfaces with a Time Stamp of when they went down

    bfreking

      I am looking to combine these two reports into one easy to read report that shows ALL Down Nodes and All Down Interfaces on one chart with a Time Stamp of when each event took place.

      Report 1 (shows Down Nodes and a time stamp)

      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


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

      group by Nodes.Caption
      ORDER BY 1 DESC

       

      Report 2 Shows Down Interfaces

      SELECT
      Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Nodes.VendorIcon AS Vendor_Icon, Nodes.Caption AS NodeName, Interfaces.InterfaceIcon AS Interface_Icon, Interfaces.Caption AS Interface_Caption, Interfaces.StatusLED AS Status_Icon
       FROM
      Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
       WHERE 
      (
        (Interfaces.Status = '2') OR
        (Interfaces.Status = '0')
      )

       

      Can I have some input on how I combine these two reports into one to get one chart that shows all down nodes and all down interfaces with a time stamp of when they occured?

       

      Thanks for any and all help.