Custom SQL Dynamic Graphing Resource

Version 6

    **Link to feature request**

    Automatically Generate New Graph(s) Based On Custom Property Value


    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...




    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:



    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.




    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)


    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" %> 
    9. <script runat="server"> 
    10.     protected void Page_Load(object sender, EventArgs e) 
    11.     { 
    12.         string filter = this.Resource.Properties["Filter"]; 
    13.         DataTable table; 
    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; 
    19.             if (!String.IsNullOrEmpty(filter)) 
    20.                 sql = string.Format(sql, "AND " + filter.Replace('*', '%').Replace('?', '_')); 
    21.             else 
    22.                 sql = string.Format(sql, ""); 
    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.         } 
    35.         this.dynamicGraphsAuto.DataSource = table
    36.         this.dynamicGraphsAuto.DataBind(); 
    37.     } 
    39.     protected override string DefaultTitle 
    40.     { 
    41.         get { return "Custom Dynamic Graphing - Interfaces"; } 
    42.     } 
    44.   public override string EditControlLocation 
    45.   { 
    46.   get { return "/Orion/NetPerfMon/Controls/EditResourceControls/FilterEdit.ascx"; } 
    47.   } 
    48. </script> 
    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 *** (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 & Polling, Combined Details Resource

    Custom User Links, Modified User Links Resource

    Custom Query Resources, Increased Default Workspace


    Thank you,