9 Replies Latest reply on Dec 12, 2017 12:09 PM by RichardLetts

    Filter Nodes (SQL) Question

    pstewart726

      Hi there...

       

      I would like to filter out some nodes from showing up in our stock High Percent Utilization report.  At the moment, all nodes show up that are greater than the threshold however we have some genuine nodes that run at 95% of interface speed intentionally.

       

      Not being an expert on SQL by any means, I would like to use a "Filter Nodes (SQL)" statement to report on all nodes *except* for nodes with a name of *map* (map is in middle of their names) OR nodes with a name of *123* for example. 

       

      I can find documentation on LIKE *123 for example but nothing that says "everything but this and this and this".

       

      Thanks,

       

      Paul

        • Re: Filter Nodes (SQL) Question
          Vinay BY

          1. I am trying to list Top 10 Interfaces which are not bond0 interfaces in this example "Filter Nodes (SQL)": InterfaceName Not Like 'bond0'

          2. The same example when you use regex in "Filter Nodes (SQL)": InterfaceName Not Like '*ond*'

          • Re: Filter Nodes (SQL) Question
            Vinay BY

            My above post was to filter out interfaces, but then from what I understand as per your requirement you don't want to view interfaces related to certain nodes, to achieve this you will have to use the columns available in Interface Table.

            As per your requirement -> " I would like to use a "Filter Nodes (SQL)" statement to report on all nodes *except* for nodes with a name of *map* (map is in middle of their names) OR nodes with a name of *123* for example. " You can use the below filters:

            1. FullName Not Like '*map*'

            2. FullName Not Like '*123*'

             

            FullName will contain node name+ interface name under Interfaces Table, hence the above filters will work as expected.

             

            Hope this helps, let me know how it goes.

            1 of 1 people found this helpful
            • Re: Filter Nodes (SQL) Question
              Vinay BY

              If its Report Writer that you are taking about - create a Advanced SQL report to extract the same report and use the below query:

              Note: MODIFY THE SELECT query accordingly. The below query is just an example

               

              SELECT

              Nodes.Caption AS NodeName, Interfaces.Caption AS Interface_Caption, Interfaces.HighPercentUtil AS High_Percent_Utilization

              FROM

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

              WHERE 

              (

                (Nodes.Caption NOT LIKE '%map%')

              )

              • Re: Filter Nodes (SQL) Question
                ascoyne

                I realise this is an old post, but I am having similar issues.  I would to show all nodes that don't contain the words "BMS".  I have used the following SQL, but it status the syntax is incorrect.  Please can you help.

                 

                FullName Not Like '*BMS*'