Hello there,
I'm working on pulling a report for our upstream utilization on a specific piece of network equipment. The report works and pulls in the data properly but the overall display is what we need to edit now. We would like the report to have four columns; Node Name, Cable Interface, Cluster Name, Utilization.
I currently have the report pulling those four columns but the Interface column supplies both the 'cable interface' and 'cluster name', it needs to be edited so that the cable interface column contains only the cable interface information and the cluster name column only contains the cluster name. Below is an example of what two of these interfaces would look like, there's two variations.
us-phy-channel1:4/0/1 · Bly 24 <-- In this interface the Cable Interface would be "us-phy-channel1:4/0/1" while the Cluster Name is "Bly 24"
Cable2/0/5-upstream1 · Wilson <-- In this interface the Cable Interface would be "Cable2/0/5-upstream1" while the Cluster Name is "Wilson"
Current SQL:
SELECT
Device
,Interface AS 'Cluster Name'
,Interface AS 'Cable Interface'
,AVG(MaxDailyUtilization) AS 'Average Peak Daily Utilization' FROM
(
SELECT
n.caption AS 'Device'
,i.caption AS 'Interface'
,t.DateTime AS 'Date'
,(t.In_PercentUtilMaxUsage/100) AS MaxDailyUtilization
FROM InterfaceTrafficUtil_Daily t
JOIN Interfaces i on i.InterfaceID = t.InterfaceID
JOIN Nodes n on n.NodeID = t.NodeID
WHERE n.Caption LIKE '%-CMTS-%' AND (i.Caption LIKE '%-upstream%' OR i.Caption LIKE 'us-phy-channel%')
AND t.DateTime BETWEEN GETDATE()-14 AND GETDATE()
GROUP By n.Caption,i.Caption,t.DateTime,t.In_PercentUtilMaxUsage
) inner_query
ORDER BY AVG(MaxDailyUtilization) DESC
