Hi,
So I'm totally lost with SQL in the first place but with some issues with our product (upgrade in process) Im also unable to go onto the DB to try work out the SQL entry I need to query.
Anyone able to help add Interface configured bandwidth to the below report? Need the none network teams to be able to register what the speed of each port is without asking us each time.
SELECT DISTINCT
PSCView.Node,
PSCView.Location,
PSCView.Inbps,
PSCView.IfName,
PSCView.InterfaceAlias,
PSCView.Internal_Location_ID,
PSCView.Outbps,
PSCView.PollInterval,
PSCView.Status,
PSCView.StatusLED,
REPLACE(PSCView.StatusLED, '.gif', '') AS StatusWord,
PSCView.Caption,
PSCView.IfSort,
--CONCAT (PSCView.IfName, ' - ',PSCView.InterfaceAlias) AS NewInterface,
PSCView.DetailsURL
from (
SELECT
Nodes.Caption AS Node,
Nodes.Location,
NodesCustomProperties.Internal_Location_ID,
Interfaces.Inbps,
Interfaces.IfName,
Interfaces.InterfaceAlias,
Interfaces.Outbps,
Interfaces.PollInterval,
Interfaces.Status,
Interfaces.StatusLED,
Interfaces.Caption,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Interfaces.IfName + 'AAAAAAAAAA','/1AAAAAAAAAA','/01AAAAAAAAAA'),'/2AAAAAAAAAA','/02AAAAAAAAAA'),'/3AAAAAAAAAA','/03AAAAAAAAAA'),'/4AAAAAAAAAA','/04AAAAAAAAAA'),'/5AAAAAAAAAA','/05AAAAAAAAAA'),'/6AAAAAAAAAA','/06AAAAAAAAAA'),'/7AAAAAAAAAA','/07AAAAAAAAAA'),'/8AAAAAAAAAA','/08AAAAAAAAAA'),'/9AAAAAAAAAA','/09AAAAAAAAAA') AS IfSort,
'http://ixnetmon.ptv.com/Orion/Interfaces/InterfaceDetails.aspx?ViewID=229&NetObject=I:'+CAST(Interfaces.InterfaceID as varchar(256)) as 'DetailsURL'
FROM
Nodes
INNER JOIN
Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
INNER JOIN
NodesCustomProperties ON Nodes.NodeID = NodesCustomProperties.NodeID
WHERE
Nodes.Caption LIKE 'GBLEE2O-SW%'
) PSCView
ORDER BY PSCView.Node, PSCView.IfSort;
