Solarwinds NPM Bulk Deletion of Interfaces via SQL/SWQL

Bulk Removal of “Down” and “Unknown” Interfaces in Solarwinds via SQL and the GUI

With SQL

Often with larger Solarwinds environments, you will find that over time Nodes will accumulate a large number of interfaces that don’t really get monitored anymore. This build up can be caused by retiring equipment, relocating traffic use, or issues with removing devices that have had duplicates added in the NPM. Many times your organization may just choose to stop monitoring this equipment, and these “dead” interfaces will spend the remainder of their time as down or unknown, this causes a lot of clutter and disorganization in your monitoring environment.

The best way to remove large quantities of these interfaces is using SQL, and managing the removal with the database manager. The below query will provide you with a list of interfaces split by their status.  Interfaces marked as “0” are unknown and “2” are down.

Once you run this query it’s important to be conscious of what select nodes you would like to keep.  If you have this list you can add an exception to the query using SQL “AND NOT” statements. Simply replace the ID’s on line 5 below with Interface ID’s you intend to keep.

This will provide you with a list of all selected interfaces for removal. To then delete these interfaces we must run a command with the following information.

In the XYZ fields we need to insert the list of interface ID’s we generated in the first query. This will do two things, delete the interfaces, and also add these deleted interfaces into the DeletedInterfaces table. 

With 12.2 Environments- GUI Removal and Manage Entities Page

NPM version 12.2 added a feature within the GUI that utilizes SQL to generate a more visually appealing way to bulk manage nodes and interfaces. You will need a solarwinds account with high enough SQL privileges to modify your database. It’s important to pay attention to good data management practices as this method does directly modify your database.

1) Navigate to the “Manage Entities” page in your 12.2 NPM environment. You can view the interfaces on a node by clicking the arrow at the end of a column. Interfaces will show up on the right in the “Related Entities” table. 

2) Select interfaces and click delete. 

Shopping Cart