Showing results for 
Search instead for 
Did you mean: 
Create Post

Getting more from the data NPM collects

Level 15

SolarWinds NPM network monitor collects a ton of data. Literally, a metric ton. We focus on presenting you the data you need to troubleshoot and understand the long term trends (see the previous post - The Magic 8 Ball in Orion to see how to use the built in trend capabilities). But if you want to leverage stronger analytics on top of this data you might want to leverage our data with other common tools (for example: Microsoft Excel). This blog post will show you how to easily access the NPM data in excel and do fun things like trend analysis and basic analytics (well, fun for some of us....).

Step 1 - Define what you are looking for

If you are like me, you like poking around data sets to find what cool information you could build from it. However, if you are trying to solve a specific problem - for example, when will I run out of bandwidth, you will need to have very specific data. If you want to know when you are going to run out of bandwidth, you will need to collect data about how you are currently using the bandwidth over time (which also means you need to have collected the data for some time).

Step 2 - Get the data

NPM network management software allows you to automatically build charts with trend lines. However, if you want to do more details analysis, you can leverage Excel to directly connect to the NPM database. In Excel 2010, open a new file, click the Data tab, then select From Other Sources and From SQL Server. Enter the server name of the database server used for NPM and set credentials that have rights to read data from the database.


After you have connected to the database server, you will need to select the table you want to pull data from. In this example, we are going to look at interface traffic so the InterfaceTraffic_Hourly table (pay attention to the difference between tables and views, you'll need to scroll down about halfway for this table).


On the next screen about saving the data connection, you can just click Finish (unless you want to save the connection information). Finally, specify where you want to import it to (existing sheet or new sheet). You should now be seeing your NPM data in Excel!


Step 3 - Organize the data

Now that you have all of this data, what should you do? If you want to target specific interfaces, you'll need to correlate the interface ID (number) with the interface as you know it. The quickest way to do this is to open NPM and go to the node or interface for an interface you are interested in and look at the URL. You will see something similar to I:9 for an interface or N:2 for a node.


Step 4 - Add any necessary formulas

Use the trend function to create trend data for each of your interfaces so you can get an easy to use "Top 10 Fastest Growing Interfaces by Utilization".

Step 5 - Make it easy and re-usable

  We're working on making a spreadsheet that is easy for you to re-use. Check back here and we'll post a template spreadsheet that can be applied to your data! If you are interested in this specifically, post below and we will make sure to notify you when it's available.

Level 16

Very nice.

Is any one try to use "splunk" for some kind of BI?

Level 7

template would be awesome!

Level 12

One year later do you have any template for this ?

Level 17

Folks, set this up using the Windows Data Connection Wizard, once you have that Data Source entity in place you can then load that 'Data Connection' into Excel and further set to update/refresh each time you open so the data is current. Or refresh manually.

I would not expect a template for this, you have to set this up custom per your environment.

Start with Control Panel->Administrative Tools->Data Sources(ODBC)

     in this setup your SQL DB Server as the source, use your service account so there are no PW issues.

Once this data source is set, either in the setting or importing into excel you have to further select what tables/view you want and that should correlate to the data you want to gather.


All these tons of data is great but hampers the performance of the database.

Need to port off the older data (detail) into a data warehouse for offline reporting and trending.

About the Author
  Mav has been with SW since 2009. He has 10 years of IT experience on both the Network and Systems side. His favorite text editor is vi (if you listen closely, you can hear him muttering command sequence incantations under his breath).