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.