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.

        • Re: Looking for a report (Advanced SQL) that combines Down Nodes and Down Interfaces with a Time Stamp of when they went down
          sean.martinez

          I hope this works the way you want it to. By the way this was a Nice little Report that came out.

           

          --Side Note, This has a Left Join to make sure that if there are no Interfaces Assigned that it will still report the Node with no Interface. I took out the NodeID and InterfaceID to clean it up, and also added the Node Status.

           

          SELECT max(Events.EventTime) AS Event_Time, Nodes.Caption AS NodeName, Nodes.StatusLED as Node_Status, Nodes.VendorIcon AS Vendor_Icon,

           Interfaces.InterfaceIcon AS Interface_Icon, Interfaces.Caption AS Interface_Caption, Interfaces.StatusLED AS Interface_Status_Icon

           

          FROM Nodes 

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

          Left JOIN Interfaces ON (Interfaces.NodeID = Nodes.NodeID)

           

          WHERE 

          ((Nodes.Status = '2')  AND

               ((Events.EventType = 5000) OR

                (Events.EventType = 1)))

          OR      

          ((Interfaces.Status = '2') OR 

           (Interfaces.Status = '0'))

          Group by Nodes.Caption, Nodes.NodeID, Interfaces.InterfaceID, Nodes.VendorIcon, Nodes.Caption, Interfaces.InterfaceIcon, Interfaces.Caption, Interfaces.StatusLED, Nodes.StatusLED

          ORDER BY 1 DESC