0 Replies Latest reply on Apr 18, 2017 10:00 PM by dclark127

    Top Interface Utilization for Last Month

    dclark127

      Totally new to SQL and I was happy to find this SQL Query that seems to work fine to get the Top 15 Vlan Interfaces by Utilization.   I would love to get this to work for last month and last year and show the Month/Year and show the top 15 for the month or month by month.  Everytime I add a Join Statement, such as "JOIN InterfaceTraffic on Interfaces.InterfaceID = InterfaceTraffic.InterfaceID" (with and without INNER, plus all sorts of other combinations) to use the DateTime field, it breaks.  Also tried changing the Where statement to add "( DateTime BETWEEN DATEADD(mm,-1,GETDATE()) AND GETDATE() )", but that just gives me a list of Vlans for each particular date.

       

      I want to be able to pull this report monthly, so this month I will see the top 15 Vlans by Utilization for the Month of March.  First time pulling would love to have last 12 months or last year that will show groups of top vlans by utilization from month to month.

       

      Yes, I have searched all over, tried cobbling together, etc etc.

       

      Here is my Query: (And thank you in advance for any guidance!)

       

      SELECT TOP 15 Nodes.City, Nodes.Caption AS 'Node Name',  Nodes.IP_Address, Nodes.Building AS Building, Interfaces.InterfaceName, Interfaces.Caption AS Description, Interfaces.InPercentUtil, Interfaces.OutPercentUtil FROM Interfaces with(nolock) INNER JOIN Nodes with(nolock) ON Interfaces.NodeID = Nodes.NodeID WHERE (Interfaces.InPercentUtil+Interfaces.OutPercentUtil)>0 AND Nodes.node_description IN ('1', '2') AND interfaces.InterfaceName LIKE '%vlan%' AND Nodes.City LIKE '%City%' ORDER BY (Interfaces.InPercentUtil+Interfaces.OutPercentUtil) DESC, Nodes.Caption, Interfaces.Caption