1 Reply Latest reply on Oct 20, 2017 8:01 PM by equalswql

    Duplex mismatch filtered by custom properties

    Jeremiah Holland

      I need a few duplex mismatch alerts to send to specific distribution groups based off the custom properties, City, and Department.  Out of the box Solarwinds uses an advanced SWQL query,

      LEFT JOIN Orion.TopologyConnections tc ON (tc.SrcNodeID = Interfaces.NodeID OR tc.DestNodeID = Interfaces.NodeID) AND (tc.SrcInterfaceID =Interfaces.InterfaceID OR tc.DestInterfaceID =Interfaces.InterfaceID) AND tc.DestInterfaceID IS NOT NULL AND tc.SrcInterfaceID IS NOT NULL AND tc.LayerType = 'L2'

      LEFT JOIN Orion.NPM.Interfaces i ON i.InterfaceID = tc.DestInterfaceID OR i.InterfaceID = tc.SrcInterfaceID WHERE Interfaces.DuplexMode != i.DuplexMode AND Interfaces.DuplexMode > 1 AND i.DuplexMode > 1


      How can i add the City is "Denver", and the Department is "Accounting" fields to this query?

      Thanks!




        • Re: Duplex mismatch filtered by custom properties
          equalswql

          Hello jere557,

           

          You can try the following below, here is what I added:

           

          1) I put parentheses starting after WHERE  and ending at the end of the existing statement.

          2) Added ---> AND (i.Node.CustomProperties.City = 'Denver' AND i.Node.CustomProperties.Department = 'Accounting')

           

          Please let me know if this works - full statement below:

           

          SELECT Interfaces.Uri, Interfaces.DisplayName

          FROM Orion.NPM.Interfaces AS Interfaces

           

          LEFT JOIN Orion.TopologyConnections tc ON

          (tc.SrcNodeID = Interfaces.NodeID OR tc.DestNodeID = Interfaces.NodeID)

          AND

          (tc.SrcInterfaceID =Interfaces.InterfaceID OR tc.DestInterfaceID =Interfaces.InterfaceID)

          AND

          tc.DestInterfaceID IS NOT NULL

          AND tc.SrcInterfaceID IS NOT NULL

          AND tc.LayerType = 'L2'

           

          LEFT JOIN Orion.NPM.Interfaces i ON i.InterfaceID = tc.DestInterfaceID

          OR i.InterfaceID = tc.SrcInterfaceID

           

          WHERE (Interfaces.DuplexMode != i.DuplexMode AND Interfaces.DuplexMode > 1 AND i.DuplexMode > 1) AND (i.Node.CustomProperties.City = 'Denver' AND i.Node.CustomProperties.Department = 'Accounting')

           

          Thank you,

           

          =swql