Hey All! Now, you’re probably looking at the title of this and thinking “yeah, that’s easy enough, just create a custom chart and get the top 10 already”. Well, this one is going to be a bit different. What if, instead, you want to see the top 10 interfaces, but the interfaces are in separate charts? Perfstack seems the obvious choice, but perfstack only gives you a static list, not a top 10 list. That’s where things get a bit tricky.
But fear not! With the power of SQL, this can be accomplished:
Here I’ve gone and created a view in Orion that contains the top 10 interfaces across the entire environment. Each one of these resources is a custom chart that is based on a custom SQL query. Each query is exactly the same, except there is an offset number that is changed for the first, second, etc. interface. Below are the steps in creating:
Step 1 - Create a new view (Optional, but recommended):
Go to Settings --> All Settings, and click on Manage Views. Click on Add, then create a Summary View called “Top 10 Interfaces”
Step 2 – Add the new view to a menu bar (Optional, but recommended):
Go to Settings --> All Settings, and click on “Customize Menu Bars”. Click on the menu bar you want to add the view to (I used Network_TabMenu), and select Edit.
From the list of available views on the left, find the “Top 10 Interfaces” you created, and drag that to the right.
Now you have the new view under My Dashboards --> Network --> Top 10 Interfaces
Step 3 – Create the widgets:
Click on the view we just created in steps 1 and 2, and on the left side of our blank canvas, click on Customize Page:
Then go to “Add Widgets” in the upper right:
Search for the “Custom Chart” Widget, then drag and drop that widget 10 times to the main part of the screen (5 in each column):
Click “Done Adding Widgets when finished”
Step 4 – Make the charts:
This is the meaty part. On the first chart, click on “Configure this resource” or “edit” (both will take you to the same page):
For “Title”, call this “Interface #1”
Subtitle we can leave blank
Then for datasource, click "Select Datasource", and change the selection method to “Advanced Database Query (SQL, SWQL)”, then change the query type to SQL:
Now for the query, copy and paste the following:
SELECT
(no.caption + ' - ' + interf.interfacename) as [Node Name],
interf.interfacename as [Interface Name],
it.datetime,
it.in_averagebps as [Average BPS In],
it.out_averagebps as [Average BPS Out]
FROM [dbo].[InterfaceTraffic] it
inner join Interfaces interf
on it.interfaceid=interf.interfaceid
inner join nodes no
on it.nodeid=no.nodeid
where it.interfaceID=(
SELECT
it.interfaceid
FROM [dbo].[InterfaceTraffic] it
where it.datetime >= DATEADD(day, -1, GETDATE()) and it.in_averagebps IS NOT NULL and it.out_averagebps IS NOT NULL
group by it.interfaceid
order by(avg(it.in_averagebps)+avg (it.out_averagebps)) desc
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
So, quick explanation as to what this query does. The first part, up to the “where” statement, pulls the interface traffic information. The magic happens in the where clause. The where clause takes the last 24 hours of performance information, per interface, and averages it out and sorts the top interfaces.
The important piece to this is the LAST line;
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
What this line does is return just one line, but offset the rows X number of lines. So, for this chart, we take the top result. For the next chart, we will take an offset of 1, so our query is now:
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)
This query will give us the chart for the 2nd interface in the top ten, and so on.
Step 4 – The rest of the chart setup:
Now that our data source is set for the first chart, you can now set the rest of the options.
- Set your time period (I set mine to last 24 hours)
- On the left y-Axis, click Add data series, select “Average BPS In”
- Once added, click ‘More’, and a button will appear for “Time Column”. Click that and select ‘datetime’
- Again on the left y-Axis, click Add data series, select “Average BPS Out”.
- And again, once added, click ‘More’, and select ‘datetime’ for the “Time Column”
- For “Units Displayed”, set this to “Bit/s”
- Under “Data Grouping”, “Group chart data” by “Interface Name”
- And for “Legend Shows”, select “Node Name”
And that’s it! Hit submit.
Step 5 – Rinse and Repeat:
Repeat steps 3 and 4 for the other ten charts, and make sure you:
Change the offset number for each chart (Interface #2 has an offset of 1, Interface #3 has an offset of 2, etc.)
Change the name of the Charts for Interface 2, 3, etc.
That should do it! From this point, if you want to change the query to include a custom property, or maybe do volumes or cpu performance, you can. The real part of this is the where clause where you look up your information ahead of time, then offset/fetch. Let me know in the comments if you have any questions!
