1 Reply Latest reply on Sep 28, 2015 10:15 PM by HolyGuacamole

    Interface Down Report SQL

    @thwack

      Hi, I have requirement to fetch interface down report as of today with particular interface tags, kindly help with below query:

       

      SELECT 

      Nodes.Branch As 'BRANCH',

      Nodes.Caption AS 'Node Name',

      Nodes.Switch_Type As Switch_Type,

      Interfaces.Caption AS Interface_Caption,

      max(Events.EventTime) AS Event_Time

       

       

       

       

       

       

      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 = 10 AND

      Events.EventType = 1)

      OR     

      ( Interfaces.Status = '2' )AND

      ( Nodes.Switch_Type = 'DX' OR

      Nodes.Switch_Type = 'MCR' OR

      Nodes.Switch_Type = 'ADX' OR

      Nodes.Switch_Type = 'BX'  )  AND

      (Interfaces.Caption Like '%UG%' OR

      Interfaces.Caption Like '%OH%' ) AND

      (Events.Message Like '%UG%' OR

      Events.Message Like '%OH%')

       

       

      Group by

      Nodes.Branch

       

       

       

       

      Order by

      Event_Time DESC