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.

SQL Configured Speed Entry - Add to report

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;