This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Last 30 days bandwidth utilization with charts for each interface

We have a report that runs on the 1st of every month that shows the graph for Min/Max/Average bps over the last 30 days for each of our branch office routers. We recently swapped all that equipment out and we'd like to change this report to be a bit more "dynamic" and flexible to changes without having to go through and manually touch each section of the report. The problem is this report is one long PDF where each graph is its own "content" within the layout, and then each block is drilled down to the individual branch's router's WAN interface.

I've written a SWQL query that grabs the data we want in a table, but I was wondering if there was a way to do this natively with SWQL/SQL that would present a graph for each interface and its values that the query returns and then group them by node/location. I can kind of pseudocode an idea of how you'd do it with Python and a CSV file but I'd prefer to have this hypothetical new report done in Orion. I'm not sure if it can do the recursion that might be necessary, I've asked a DBA about doing that with a SQL query in the past and they basically said SQL's not really built to do that.

Below is the SWQL query that gets the data I want. I am using a custom property on the interface that's a simple Boolean for whether it is a WAN interface or not, as well as one (which is tragically misspelled) for the node's "location," in this case I'm selecting for "Branch Routers." I have those two selections as the first two joins in theory because I don't want to run the rest of the joins against all the nodes, just the ones I care about, but I'm not sure if this actually changes how the data is collected and might be equally as inefficient as having them at the end where I originally tacked them on.

        SELECT
          Interfaces.InterfaceId,
          Nodes.NodeID,
          Nodes.Caption AS NodeName,
          Nodes.DetailsUrl AS NDetailsUrl,
          Nodes.VendorIcon AS Vendor,
          Interfaces.Caption AS Interface_Caption,
          Interfaces.InterfaceIcon AS Interface_Icon,
          Interfaces.DetailsUrl AS IDetailsUrl,
          Interfaces.InBandwidth AS Circuit_Download_BW,
          Interfaces.OutBandwidth AS Circuit_Upload_BW,
          Maxbps_In90,
          Maxbps_Out90,
          Maxbps_In95,
          Maxbps_Out95,
          Maxbps_In99,
          Maxbps_Out99
        FROM Orion.Nodes
        INNER JOIN Orion.NPM.Interfaces ON Nodes.NodeID = Interfaces.NodeID
        INNER JOIN (
          SELECT InterfaceID FROM Orion.NPM.InterfacesCustomProperties WHERE WAN_Interface = 'True') WANInterfaces on Interfaces.InterfaceID = WANInterfaces.InterfaceID
        INNER JOIN (
          SELECT NodeID FROM Orion.NodesCustomProperties WHERE NodesCustomProperties.Locatoin = 'Branch Router') BranchRouters on Nodes.NodeID = BranchRouters.NodeID
        INNER JOIN (
          SELECT
            OuterInterfaceTraffic.InterfaceID,
            (SELECT MAX(InAverageBps) as maxInAverageBps FROM (
              SELECT TOP 90 PERCENT InAveragebps
              FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())
              ORDER BY InAveragebps ASC )) AS Maxbps_In90,
            (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
              SELECT TOP 90 PERCENT OutAveragebps
              FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())
              ORDER BY OutAveragebps ASC )) AS Maxbps_Out90,
            (SELECT MAX(InAveragebps) as maxInMaxbps FROM (
              SELECT TOP 95 PERCENT InAveragebps
              FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())
              ORDER BY InMaxbps ASC )) AS Maxbps_In95,
            (SELECT MAX(OutAveragebps) as maxOutMaxbps FROM (
              SELECT TOP 95 PERCENT OutAveragebps
              FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())
              ORDER BY OutMaxbps ASC )) AS Maxbps_Out95,
            (SELECT MAX(InAverageBps) as maxInAverageBps FROM (
              SELECT TOP 99 PERCENT InAveragebps
              FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())
              ORDER BY InAveragebps ASC )) AS Maxbps_In99,
            (SELECT MAX(OutAverageBps) as maxOutAverageBps FROM (
              SELECT TOP 99 PERCENT OutAveragebps
              FROM Orion.NPM.InterfaceTraffic WHERE InterfaceID = OuterInterfaceTraffic.InterfaceID AND DateTime >= AddDay(-7, GETUTCDATE())
              ORDER BY OutAveragebps ASC )) AS Maxbps_Out99
            FROM Orion.NPM.InterfaceTraffic AS OuterInterfaceTraffic
            WHERE OuterInterfaceTraffic.DateTime >= AddDay(-7, GETUTCDATE())
            GROUP BY OuterInterfaceTraffic.InterfaceID
          ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

As I said this gives me a table of all the values and really we should probably switch this to be a percent utilization rather than just Mbps, but for now I'm just looking to see if there's a way to replicate our old report without having a content block for each branch interface that we would have to manually edit/add/remove as our number of branch offices changes.