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;
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.
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%'
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.