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.

Top Interface Utilization for Last Month

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