9 Replies Latest reply on Jul 27, 2009 5:00 AM by jonchill

    Removing Interfaces from Switches

    jonchill

      Currently I'm monitoring all the interfaces on all our edge switches, so to bring down the number of elements monitored and release some of the load from the server I want to remove the interfaces from monitoring on all edge switches.

      Is it possible to do this with a SQL script of something similar, I've got a custom property for all our edge switches so distinguishing them from other switches isn't a problem?

      Any help would be much appreciated.

      Thanks

      Jon

        • Re: Removing Interfaces from Switches
          brian_duvall

          I would think if you have a custom property that states a switch is "edge" and you want to remove all interfaces associated with that switch, you could write a sql query to find all nodes with the custom property, then find where those nodeID's exist in the Interface table and perform a delete on all those rows that have the nodeID's of edge switches.  I believe db maintenance will remove all the orphaned data for interface IDs that no longer exist in database so that should clear all that out.  I would let a solarwinds support person confirm this behavior but that should do in quickly.

            • Re: Removing Interfaces from Switches
              jonchill

              Thanks Brian, I though it shouldn't be to difficult to achieve. I just need someone to give me an idea of the script to run as I'm not SQL programmer.

                • Re: Removing Interfaces from Switches

                  Here are a couple of queries that should do what you need.  The Where clause in each query will have to be modfied to match the name of your custom property field and the value to match in that field.  XXXXXX in each query should be replaced with the name of your custom property field and YYYYYY needs to be the value in the custom property you want to match (i.e. edge). 

                  The first query will just display a list of which interfaces will be deleted by the delete query.  The second query will actually delete the interface records from the interfaces table.

                  Always make a backup of your database before making changes directly to the tables with SQL commands. 

                  select nodes.caption as NodeName, interfaces.Caption as Interface from Nodes join interfaces on nodes.nodeid = interfaces.nodeid where nodes.XXXXXX = 'YYYYYY' order by nodes.caption

                  delete from interfaces where interfaces.nodeid in (select nodes.nodeid from nodes where nodes.XXXXXX = 'YYYYYY')

                    • Re: Removing Interfaces from Switches

                      Sorry, I had a misplaced comma in the first query on the original post.  I have corrected the problem.

                      • Re: Removing Interfaces from Switches
                        jonchill

                        It works a treat but I've hit a slight problem there are certain types of interfaces I want to keep ie the trunk ports, I've tried adapting the script to allow me to filter based on a string eg all FastEthernet interfaces but can't get it to work.

                        Would it be possible to adapt the script for me, to allow me to filter the interfaces based on a string?

                        Thanks

                        Jon

                          • Re: Removing Interfaces from Switches

                            This is possible but you will need some piece of information in the database that will allow you to uniquely identify the trunk ports on the switches from the other ports you don't want to monitor.  Using FastEthernet ports only as the criteria will work as long as all of your trunk ports are FastEthernet and none of the ports you don't want to monitor are FastEthernet.

                              • Re: Removing Interfaces from Switches
                                jonchill


                                This is possible but you will need some piece of information in the database that will allow you to uniquely identify the trunk ports on the switches from the other ports you don't want to monitor.  Using FastEthernet ports only as the criteria will work as long as all of your trunk ports are FastEthernet and none of the ports you don't want to monitor are FastEthernet.

                                 



                                Thanks, I'll create an interface custom property for the interfaces I don't want to delete. How can I add this extra check to the SQL script?

                                Thanks

                                 

                                Jon

                                • Re: Removing Interfaces from Switches
                                  jonchill

                                  Thanks, I'll create an interface custom property for the interfaces I don't want to delete, how do I add this check to the existing SQL script?

                                  Thanks

                                  Jon