Hi, I'm trying to come up with a way to generate a report showing all nodes, the interfaces on those nodes, and then the ip address assigned to that interface. All the information is in the database (the tables I'm looking at are Nodes, Interfaces and NodeIPAddresses) but I cant work out the relationship between NodeIPAddresses.IPAddessN and the interface. Example Node NameInterfaceIP AddressNode1Interface11.1.1.1Node1Interface22.2.2.2Node1Interface3NULLNode2Interface11.1.1.2Node2Interface22.2.2.3Node3etcetc I'd probably add a few other fields from the nodes and interfaces tables (status for example) and add some groupings, however the above is the base building blocks I'm looking for. Appreciate any advice. Stuart

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Node report with interfaces and IP Addresses

Hi,

I'm trying to come up with a way to generate a report showing all nodes, the interfaces on those nodes, and then the ip address assigned to that interface. All the information is in the database (the tables I'm looking at are Nodes, Interfaces and NodeIPAddresses) but I cant work out the relationship between NodeIPAddresses.IPAddessN and the interface.

Example

Node Name	Interface	IP Address
Node1	Interface1	1.1.1.1
Node1	Interface2	2.2.2.2
Node1	Interface3	NULL
Node2	Interface1	1.1.1.2
Node2	Interface2	2.2.2.3
Node3	etc	etc

I'd probably add a few other fields from the nodes and interfaces tables (status for example) and add some groupings, however the above is the base building blocks I'm looking for.

Appreciate any advice.

Stuart

Find more posts tagged with

Accepted answers

stuartwhyte

Ok, I think I might have it. Just working through the list to see if anything is missing.

select

Interfaces.nodeID AS NodeID,

Nodes.Caption AS NodeName,

interfaces.caption AS InterfaceName,

interfaces.interfaceIndex,

NodeIPAddresses.IPAddress,

NodeIPAddresses.InterfaceIndex

from

Interfaces

Right join nodes on (nodes.nodeID = interfaces.NodeID)

Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

All comments

adatole

Here's a quick-and-dirty that should get you where you are going:

select interfaces.caption, interfaces.interfaceIndex,

NodeIPAddresses.IPAddress, NodeIPAddresses.InterfaceIndex

from NodeIPAddresses

inner join interfaces on

(NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

...etc...

stuartwhyte

That's looking good, now I need to get Nodes.Caption added too.

I've added Interfaces.NodeID - how would I join this to Nodes.nodeID?

Also I need to make sure that the report has ALL nodes (even if they have no interfaces) and from the nodes that DO have interfaces, ALL interfaces (even if they have no ip address)

Thanks for the pointers so far

stuartwhyte

Ok, I think I might have it. Just working through the list to see if anything is missing.

select

Interfaces.nodeID AS NodeID,

Nodes.Caption AS NodeName,

interfaces.caption AS InterfaceName,

interfaces.interfaceIndex,

NodeIPAddresses.IPAddress,

NodeIPAddresses.InterfaceIndex

from

Interfaces

Right join nodes on (nodes.nodeID = interfaces.NodeID)

Left join NodeIPAddresses on (NodeIPAddresses.nodeid = interfaces.nodeid AND NodeIPAddresses.InterfaceIndex = interfaces.interfaceindex)

raymond.chen

Is your problem solved？