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

Custom SQL Dynamic Graphing Resource

**Link to feature request**

Over the past few years, we have transitioned from simple monitoring tools, such as MRTG and Cacti, to the advanced future-tech of SolarWinds. Surprisingly, though, we found SolarWinds to be lacking some of the basics when it comes to graphing. Sure, they get check marks in a bunch of the advanced categories, but many users just NEED simple, quick loading graphs.

In my world, we have numerous graph intensive pages to load. Some of these pages not only take a long time to load, but also take a long time to edit.

Here is our scenario..

  • Customer 1
    • 50 nodes
      • 3+ interfaces per node
        • 1 interface = main uplink
        • 1+ interface = subinterface vlan 100
        • 1+ interface = subinterface vlan 200
  • Customer 2
    • wash
      • rinse
        • repeat

We NEED a page, showing IN/OUT traffic graphs for each of the interfaces.

So, all of customer 1's main uplink interfaces will be on a single page, listed from interface 1 to interface 50.

Next, all of customer 1's vlan 100 subinterfaces will be listed on the next page, listed from 1-50+.

Finally, all of customer 1's vlan 200 subinterfaces will be listed on the next page, listed from 1-50+.

So, at the very least, we are manually building 3 pages, each with 50 different interfaces, from a total of 50 different nodes.

No problem, right? SolarWinds super advanced future-tech should be able to knock that out in just a minute or two, right?

Well... No... At least not that I can find.

Do we really have to manually build a new graph for every single interface?

No, not really...


You see, the best feature SolarWinds has going for itself, is Thwack Nation...

After reading trillions upon billions upon millions of threads, docs, and ideas, I was able to gather enough information from other users to piece something together and solve our problem.

I was able to take some code from c.gura‌, as well as tdanner‌, from an old post, Help converting ASP Resource to ASP.NET, and modify it enough to work for me.

I am not yet smart enough to fully understand what every part of this code does, so there may be "extra" pieces that can be removed without breaking anything...

Also, I know these graphs are not as pretty as to newer, cooler, my dynamic graphs, so, if anyone can assist with modifying this file to make it work with the better graphs, please post a comment and let me know...


**WARNING! THE INFORMATION YOU ARE ABOUT TO READ COMES FROM THE MIND OF AN UNTRAINED AMATEUR, AND IS MOST LIKELY FAR, FAR FROM THE BEST PRACTICE**

Make It So:

You will need to download the "CustomDynamicGraphing01.ascx", attached to this page, and save it to the following directory on your main SolarWinds IIS server:

\inetpub\SolarWinds\Orion\NetPerfMon\Resources\Misc\

After downloading the file to the directory listed above, you will need to rerun the config wizard for the website. This will create a new resource, called "Custom Dynamic Graphing - Interfaces", which you can will be able to add to your views.

You only need to run the config wizard to initially load the resource into the system.

After it has been loaded into the system, you can edit the contents of the file, and see results in real time, without the need to run the wizard again.

The Goods:

Basically, the resource is hardcoded with a SQL query to show/list all interfaces that have "ABC" for the interface custom property "CBH_Customer".

This gives me all interfaces related to customer 1, across all of the different nodes, as long as that custom property is assigned to the interfaces.

  1.             string sql = @"SELECT Nodes.NodeID,Interfaces.InterfaceID FROM dbo.Nodes JOIN dbo.Interfaces ON Nodes.NodeID=Interfaces.NodeID WHERE Interfaces.CBH_Customer='ABC' {0}", Filter;

Using the scenario stats above, if we were to just stop here, then we would get a single page that would auto populate 150 different graphs, 1 for each interface.

This is not actually what I want to do, so I keep going.

Now, I click the edit button on the resource, which takes me to the filter criteria page.

custom_dynamic_graphing_edit-001.PNGcustom_dynamic_graphing_edit-002.PNG

Use the normal filter syntax to narrow down the resource query.

In the example above, I am only going to show graphs for the "subinterface vlan 100" interfaces, using interface custom property "CBH_VLAN".

Now, after applying this filter, my list of 150+ interfaces/graphs, will be filtered down to only 50+ interfaces/graphs.

When a new interface is added to be monitored, the user can simply assign the proper custom properties.

After those custom properties are assigned, the interface will automatically have a new graph created without ever having to go in a edit the graphing page.

Now we can have a single page that has 3 tabs, and show 50 different graphs for 50 different interfaces across 50 different nodes... And all without having to setup anything other than a simple filter... taking only about 10 seconds.

(The picture below is using different filtering than the above examples, however, it should do well to provide a fairly decent visual... hopefully)

custom_dynamic_graphing-001.PNG

You can change the graph type by altering the part of the code towards the bottom of the file:


  1.   <div> 
  2.   <img src="/Orion/NetPerfMon/Chart.aspx?ChartName=AvgBps-Line&Width=675&Height=0&NetObject=I:<%# Eval("InterfaceID") %>&SampleSize=1H&ChartInitialZoom=today&Period=Today&Calculate95thPercentile=False&CalculateSum=0&CalculateTrendLine=False&ChartDateSpan=1&ShowTrend=False&ShowCalculatedPercentile=False"/> 
  3.   </div>



So... The easy way, or the hard way?

Easy Way: (Using this resource)

Use this resource.

Edit the query to suit your needs.

Every time a new interface is added to your NPM environment, and it has the interface properties to meet your filter criteria, it will be automagically added to this resource, and a graph will be created.

Build a single graphing resource that automatically produces a new graph for each interface.

No more adding/copying resources to a view, editing the resource, or manually creating multiple individual graphs.

Hard Way: (Without using this resource)

Manually add new interface to monitoring environment.

Manually go to view that needs the graph for the interface.

Manually edit the view.

Manually add/copy the graphing resource to the view.

Manually edit the resource to show the interface you need.

Repeat for each interface you need to graph.

Listed below, is the code contained within the attached "CustomDynamicGraphing01.ascx" file.

  1. <%@ Control Language="C#" ClassName="dynamicGraphs" Inherits="SolarWinds.Orion.Web.UI.BaseResourceControl" %> 
  2. <%@ Import Namespace="System.Data.SqlClient"%> 
  3. <%@ Import Namespace="SolarWinds.Orion.Common"%> 
  4. <%@ Import Namespace="SolarWinds.Orion.Web.DAL"%> 
  5. <%@ Import Namespace="System.Data"%> 
  6. <%@ Import Namespace="System.Collections.Generic"%> 
  7. <%@ Register TagPrefix="orion" Namespace="SolarWinds.Orion.Web.Controls" %> 
  8.  
  9. <script runat="server"> 
  10.     protected void Page_Load(object sender, EventArgs e) 
  11.     { 
  12.         string filter = this.Resource.Properties["Filter"]; 
  13.         DataTable table; 
  14.  
  15.         try 
  16.         { 
  17.             string sql = @"SELECT Nodes.NodeID,Interfaces.InterfaceID FROM dbo.Nodes JOIN dbo.Interfaces ON Nodes.NodeID=Interfaces.NodeID WHERE Interfaces.CBH_Customer='ABC' {0}", Filter; 
  18.  
  19.             if (!String.IsNullOrEmpty(filter)) 
  20.                 sql = string.Format(sql, "AND " + filter.Replace('*', '%').Replace('?', '_')); 
  21.             else 
  22.                 sql = string.Format(sql, ""); 
  23.               
  24.             using (SqlCommand cmd = SqlHelper.GetTextCommand(Limitation.LimitSQL(sql))) 
  25.             { 
  26.                 table = SqlHelper.ExecuteDataTable(cmd); 
  27.             } 
  28.         } 
  29.         catch (SqlException) 
  30.         { 
  31.             this.SQLErrorPanel.Visible = true
  32.             return; 
  33.         } 
  34.  
  35.         this.dynamicGraphsAuto.DataSource = table
  36.         this.dynamicGraphsAuto.DataBind(); 
  37.     } 
  38.  
  39.     protected override string DefaultTitle 
  40.     { 
  41.         get { return "Custom Dynamic Graphing - Interfaces"; } 
  42.     } 
  43.  
  44.   public override string EditControlLocation 
  45.   { 
  46.   get { return "/Orion/NetPerfMon/Controls/EditResourceControls/FilterEdit.ascx"; } 
  47.   } 
  48. </script> 
  49.  
  50. <orion:resourceWrapper runat="server" ID="wrapper"> 
  51.   <Content> 
  52.   <asp:Panel ID="SQLErrorPanel" runat="server" Visible="false"> 
  53.   <table cellpadding="10px"> 
  54.   <tr> 
  55.   <td style="font-weight: bold; font-size: small; color: Red"> 
  56.   Custom SQL filter is incorrectly formated. 
  57.   </td> 
  58.   </tr> 
  59.   </table> 
  60.   </asp:Panel> 
  61.         <asp:Repeater runat="server" ID="dynamicGraphsAuto"> 
  62.   <ItemTemplate> 
  63.   <table border="0" cellPadding="2" cellSpacing="0" width="100%">          
  64.   <tr> 
  65.   <td class="Property" width="10"> </td> 
  66.   <td class="Property"> 
  67.   <div> 
  68.   <img src="/Orion/NetPerfMon/Chart.aspx?ChartName=AvgBps-Line&Width=675&Height=0&NetObject=I:<%# Eval("InterfaceID") %>&SampleSize=1H&ChartInitialZoom=today&Period=Today&Calculate95thPercentile=False&CalculateSum=0&CalculateTrendLine=False&ChartDateSpan=1&ShowTrend=False&ShowCalculatedPercentile=False"/> 
  69.   </div> 
  70.   </td> 
  71.   <td class="Property" width="10"> </td> 
  72.   <td class="Property" width="10"> </td> 
  73.   </tr> 
  74.   </table> 
  75.             </ItemTemplate> 
  76.         </asp:Repeater> 
  77.   </Content> 
  78. </orion:resourceWrapper>




I would love for this to be a native SolarWinds feature, instead of some untrained bum (me), piecing together other users' code to solve our problems...

I would also like to be able to use the new graphs instead of the older ones.

As an extra added bonus, on my eternal monitoring/graphing wishlist, it would be nice to be able to select the different types of graphs via a dropdown list, or some other dynamic selection.


Ultimately, I want to take the "Custom SWQL Query" resource, and combine it with this resource. I want to be able to add my custom SWQL query resource to a page, edit the resource to add my SWQL query, and then, upon submission, instead of listing a table of results, it will use the results to populate the graphs. If I need to change the query, I can simply click edit on the resource, and directly edit the SWQL query from the browser, instead of having it hardcoded in the resource/ascx file.




Here are a few links to some other ways to help you work with, modify, and improve your Orion environment:

Custom SQL Dynamic Graphing Resource (You Are Already Here)

Custom SWQL VM Manager

Custom SWQL Views Manager

Custom SWQL Views Manager Resource

Custom Node &amp; Polling, Combined Details Resource

Custom User Links, Modified User Links Resource

Custom Query Resources, Increased Default Workspace





Thank you,


-Will



Labels (2)
Attachments
Comments

wluther‌ I will have to check this out in our environment.  You are becoming a good SolarWinds expert. Great Work! Thank you for sharing.

c.gura‌‌ Well, from all of the cool and useful things I have seen others do, I do not feel like I am anywhere even remotely close to becoming an expert... However, thank you anyway.  If you have any tips on improving this functionality, and you have time, please let me know.


Thanks again,

-Will

Yowza!  Impressive.

Pretty impressive, nice work!

May I add just a small tip here to make your life easier for your next .NET code, you don't need to run the configuration wizard to include a custom resource in the resource picker as long as you:

a) Delete the file C:\ProgramData\Solarwinds\Orion\WebResourceCache.xml and

b) Restart the SolarWinds Application Pool in IIS.

This will speed up a little bit the process of creating custom resources and will not mess with any current web customizations you may have

antonis.athanasiou‌ Impressive? Nice work? Nah, it's just a bunch of stolen ideas and work done from others that were pieced together by me, having a total lack of knowledge on the matter... But thanks anyway.

In regards to your .NET code tips:

a) ARE YOU

b) KIDDING ME

I want you to know that you have just inflated my hopes to an ultra high level. Now, I will go and test this, and see if I fall hard, like Episode I: TPM, or hit the mark, like Episode VII: TFA...

Thank you for your tips, and I am looking forward to an easier life...

-Will

antonis.athanasiou‌ Okay, I followed your advice, and the results were just as you said they would be. You definitely delivered me an Episode VII: TFA, for which I am grateful! I can't believe that silly little file is the key to all of this... Amazing!

You have really made my day!

Thank you,

-Will

Excellent!!!  Worked like a charm. I'm running NPN12.0.1.

Do you know what are the other chart options?

ChartName=AvgBps-Line

I would like to do the same for Utilization percentage and interface availability.

pastedImage_0.png

I found more chart options. Not sure if this is all..

AvgBps-Line

AvgBps-Step

MMAvgBps

AvgUtil-Step

MMAvgUtil

TotalBytesBar

AvgPps

TotalPackets

InterfaceErrors

InterfaceDiscards

Excellent work! You hav earned the most coveted Bob Marley Bookmark Award!

pastedImage_0.png

Here is an alternative method for building sets of graphs, fairly easily, and without the need for very much customization.

Using PowerShell To Automatically Provision A Series Of Graphs Per View

Version history
Revision #:
1 of 1
Last update:
‎10-13-2015 07:55 AM
Updated by: