5 Replies Latest reply on Apr 15, 2016 4:29 PM by rootbeer21

    Removing interfaces in a down state that are not used.

    cre

      Hi All,

       

      I am new to SolarWinds and currently working my way through some maintenance tasks.

      One of the tasks is to track down and remove network interfaces from nodes that are in a down status that do not need to be monitored.

       

      I've used a mixture of SQL from the report generation tool, and my own adjustments to come up with the following:

       

       

      SQL

      --This script is intended to track down all network interfaces that meet the following criteria:

      --Have a status of 'Down'.

      --And have not sent or received ANY data for the past 12 months.

      --The script then deletes the interfaces it finds from the 'interfaces' table using the interfaceid as the search term.

       

       

      declare @Temporary_Holder TABLE (Number INT)

      --Declares temporary variable to hold the findings of the script.

       

       

      insert into @Temporary_Holder

      --Specifies that all findings of the script will be temporarily stored in @Temporary_Holder

       

       

      select Interfaces.InterfaceID AS InterfaceID

       

      FROM

      (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

       

       

      WHERE

      ( DateTime BETWEEN 41707 AND 42073 )

      AND 

      (

        ((NullIf(In_TotalBytes,-2)+NullIf(Out_TotalBytes,-2)) = 0) AND

        (Interfaces.Status = '2')

      )

       

       

      GROUP BY

      Interfaces.InterfaceID

      --The above section finds all interfaces in a 'Down' state where no data has been sent or received for the last 12 months.

      --It also groups the findings by interfaceID for easier reading/processing.

       

       

      select * from interfaces where interfaces.interfaceid in (select * from @Temporary_Holder)

      --Lists the findings of the script. Uncomment as required.

       

       

      --delete from interfaces where interfaces.interfaceid in (select * from @Temporary_Holder)

      --Deletes the findings of the script. Uncomment as required.

       

      The script searches specifically for interfaces in a 'Down' state that have sent/received 0 data in the last 12 months and then either lists the results or deletes them as desired.

       

      Now my question is, is this the correct way of doing things or is there a more suitable script or process in the GUI that can achieve the same result.

      My concern is whether the interfaces need to be deleted from more than just the interfaces table for the change to take affect without causing ugly issues afterwards such as orphaned data in the database.

       

      I do plan on running a full re-scan in SolarWinds after the script, on the basis that the interfaces will be found again but not 'checked' to report their status.

      Any input welcome. The whole point of the task is to stop SolarWinds reporting issues with interfaces that are not in use.

       

      Note: The number of interfaces is ~4-500, so while it could be done manually via the GUI I would rather avoid it.

       

      Thanks,

      -CRe