Yes, I have tried that and that is what I am trying to avoid using. The problem with that procedure is.... It only list the interfaces. you still have to click the interface,,, Then go into the interface edit screen, edit the interface.... scroll all the way to the bottom of the screen,,, click submit.... Then go back into the node management screen, select show interfaces..... scroll through all of the pages of the interfaces that are available.... find the next interface and repeat all of these steps again.... This is a huge wast of time.....
What I am looking for is a way to get all of the interfaces for a node in one table. I want to go through all of the interfaces in that table make the necessary edits and move on to teh next set of interfaces... Individually selecting interfaces will keep me busy for weeks trying to get this done....
You can use the database editor on the server to do this, or write code using the API...
I thought the nodes and interfaces were stored in 2 different tables. Can I create a report in report writer and then show the 'SQL'. And paste that query in the database manager?
I tried the following SQL Query in the Database manager with the interfaces table selected.
Interfaces.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Interfaces.Caption AS Interface_Caption, Interfaces.InterfaceType AS Interface_Type
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
(Nodes.IP_Address = '10.27.254.254') AND
(Interfaces.InterfaceType = 6)
The interfaces I want to change on this node are displayed. But when I select the edit table button and edit the first interface record I get and error that says 'Dynamic SQL generation not allowed on multiple tables' any thoughts on why this is happening?
Use SQL Server Management Studio, create new database view that will use your query. Right click your view in tree and select Edit Top 200 Rows.
I downloaded Server Management Studio. I did connect to the database and was able to execute the query successfully. In the top half of the window I see the query that I wanted to run and in the lower half I see the interfaces that I need to edit. But I am still not able to edit the interfaces. Is there a different way to run this query in the SQL Management Studio?
Have you tried to change them in the database with Database Manager? Should be able to run a SQL pulling back just the interfaces from a specific node, then change them directly moving down the table.
How about renaming the interfaces on the devices itself? You can use Orion NCM to create a template to set the descriptions based on criteria. You can see some examples here
NPM should automatically update the descriptions based on ifIndex.
I always have to call in our DBA's to do something like this. It's a relatively simply SQL query but I'd rather a pro do it
Hi k1gaudineer , you will have to use an UPDATE query to modify the interface names, you would get help on studio for the same against the table.
But then the user that you are using to connect to DB must have appropriate rights to do so.
- I am wary of any such large scale edits in the database
- if you want to press ahead, I would do a controlled test first. edit an interface, rename from the web console first and make sure subsequent polls and rediscovery doesn't overwrite it with the description on the device.
if I may ask, what is the purpose of this renaming exercise?
Good morning HolyGuacamole,
The reason for the renaming is because I don't like the way the caption appears in SolarWinds. Especially for the multislot network chassis where a number of different kind of cards can be used.
As an example without making any edits a new 24 port 1 gig fiber card added to one of my chassis will have a caption like this when displyed in SolarWinds...
GigabitEthernet4/12 - Ethernet4/12
It will stay that way until it is changed even if I do give it a new port name on the chassis. In addition to that I just think this is a bad display for a Slot/port information.....
I would rather see a display like this
4/12 - GigabitEthernet
9/8 - TenGigabitEthernet
The 2 examples above have a more polished look and are easier to find in reports and summary displays when you are trying to find VLAN's or open ports.
Thanks to everyone for their input and help with this question. It looks like the real answer is to go ahead with the way I was originally performing this task by using the nade management screen and editing each interface.....
OK. Just to let you know, I did a very quick test. I renamed 1 of the interfaces on a node. I did a List resources & Save, and also a Re-discovery. Not sure which one did it, but the interface description is now reflecting the new one.
Interesting..... What device are you using? I am working with Brocade and Avaya.... With the view I see It looks like SolarWinds is putting 2 different names together If I don't specify a name... I am going to try an past a screen shot below....... The image below shows what happens... When I insert a new card 2 names are concatenated together (Yellow Shaded Area) and that is what I see when I view the node in Node Management. If you look above the yellow shaded area that kis what the name looks like after I manually go through each interface and edit it. That is how I want the interface to look.....
Now I go into config mode and add a port-name to a new interface in slot 4.
*In config mode Enter these commands*
interface ethernet 4/1
Port-name 4/1 - GigabitEthernet (SFP)
Once I do that and go back to the node management screen select the node and then select 'List Resources' The image below shows what happens then....
The new name is there..... But also concatenated with the unwanted prefix.....
Now I select the interface I want and go into the edit interface screen... The image below what I see when I do that.....
Again the unwanted prefix is back and it has to be edited out.
This chassis has 16 slots and each card can have 24 ports. If I add 5 cards this is a lot of editing to do on an interface by interface basis. That is why I was looking for a way to this in a table and just tab or arrow down through each caption....
As I wrote before:
Use SQL Server Management Studio, create new database view. (use wizard or this query to create new view)
CREATE VIEW [_test]
SELECT Interfaces.NodeID, Interfaces.InterfaceID, Interfaces.Caption AS Interface_Caption, Interfaces.InterfaceType AS Interface_Type
FROM Nodes INNER JOIN
Interfaces ON Nodes.NodeID = Interfaces.NodeID
WHERE (Nodes.IP_Address = '10.27.254.254') AND (Interfaces.InterfaceType = 6)
Right click your view in object tree and select Edit Top 200 Rows.
Now you should be able to edit ingterfaces in the table.
To see different interfaces, delete the view and create new with proper SQL query or edit design of current view.
Thank you for the suggestion. I did download and install the SQL studio management application. The query will produce a table with the interfaces I am needing to change, however, I can't edit the interfaces. I keep receiving an error that this can't be performed on multi table queries. It looks like I need to learn more about the application and how to use it. In the mean time this is the fix that I have come up with so I can continue moving forward....
I created a new Node details view basically this view is just showing 'Percent Utilization of each Interface'. I then limit that view to the nodes I am interested in. Next I will use that view to display one of the nodes. The Percent Utilization for each Interface resource will display all of the interfaces that I need to change. Next I right click each interface and choose display link in a new tab. I open about 15 tabs at a time. After that I just select edit interface in the interface details view make the edit click submit and close the tab. I continue doing this until lall of the tabs have been closed. Then I select the next group of tabs. Not terribly efficient but faster than trying to do this in one window all the time.
I find the Solarwinds Database manager sufficient for most SQL queries.
Anytime I see someone suggest a view for solving a database problem I know they've not understood the problem.
This query will not generate an editable result set:
SELECT Interfaces.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Interfaces.Caption AS Interface_Caption, Interfaces.InterfaceType AS Interface_Type FROM Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID) WHERE ( (Nodes.IP_Address = '10.27.254.254') AND (Interfaces.InterfaceType = 6) )
Why: because it has a join. Note, It is possible for table editors in tools to make a joined-query editable but it is not as trivial as a single-table update tool; an update statement can only update one table at a time, so editing one row in a GUI may generate multiple update statements, join columns need to be protected, and its a lot of work for programmers
SELECT nodeid , interfaceid , caption , interfacetype FROM interfaces WHERE interfacetype = 6 AND nodeid IN (SELECT nodeid FROM nodes WHERE ip_address = '10.27.254.254')
This can be used inside the Database manager to edit the Interface Caption [tested, working]
The select clause inside the round brackets returns a set of nodeids that is then use in the set-matching IN clause. This means that the outer select only has one table in it, and that makes the result set editiable in the Solarwinds database manager. ]
One book I recommend for novices is SQL For Dummies: Allen G. Taylor: 9781118607961: Amazon.com: Books -- it's really quite well written.
Good Afternoon. This has solved my problem. Thank you very much for the example and the explanation. This query does work inside the Database Manager and will produce a table of interfaces that I can edit very easily.
And as you have suggested.. The book is on order.