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 Query - Add Interface Configured Bandwidth

Cranberrybadger over 3 years ago

Need to let the other teams know the configured bandwidth of interfaces to stop us being queried each time.

Having some issues with our SW product due to be upgraded next month to resolve so kind of stuck me without being able to query our SQL DB to see what the entry I need is. Also I'm complete novice at SQL and someone made the below previously. Anyone able to help add configured bandwidth into the below?

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;

0 Seashore over 3 years ago

If you have configured a custom bandwith [dbo].[Interfaces].[CustomBandwidth] will be =1 and [InBandwidth]
and [OutBandwidth] will have the values you configured.
So I would just have a column that states if the bandwith was customized or not, and maybe also the bandwidth on all interfaces.
Cancel
Vote Up 0 Vote Down

Cancel

0 stek0 over 3 years ago

Maybe you can go with this:

SELECT	
	Nodes.Caption,
	Nodes.IP_Address,
	Interfaces.FullName, 
	Interfaces.InterfaceName,	
	Interfaces.InterfaceTypeDescription, 
	Interfaces.InterfaceSpeed, 
	Interfaces.InBandwidth,
	Interfaces.OutBandwidth,
	Interfaces.Caption,
	Interfaces.IfName
FROM [dbo].[Interfaces]
	INNER JOIN [dbo].[Nodes]
	ON [dbo].[Interfaces].[NodeID] = [dbo].[Nodes].[NodeID]
WHERE Nodes.Caption LIKE '%'
ORDER BY Nodes.Caption ASC

You can replace the WHERE Nodes.Caption LIKE '%' Statement with the Node name, LIKE '%Switch1%'

0 smttysmth02gt over 3 years ago

I don't use SQL for my reports, but use SWQL instead. I think what you're looking for would be under Orion.NMP.Interfaces under Speed. If I'm understanding your question, you're looking for what the devices currently have configured for bandwidth, not what is configured manually in the interface details page.
Cancel
Vote Up 0 Vote Down

Cancel
0 KMSigma.SWI over 3 years ago

The Current Interface Bandwidth report should contain everything you need. If not, it's a good starting place for crafting your own report.
Cancel
Vote Up 0 Vote Down

Cancel